|
1
21
22 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
23
24 import java.lang.reflect.*;
25
26 import java.sql.CallableStatement  ;
27 import java.sql.Connection ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.ResultSetMetaData ;
31 import java.sql.SQLException ;
32 import java.sql.Statement ;
33 import java.sql.Types ;
34 import java.sql.*;
35 import java.io.*;
36
37 import org.apache.derby.tools.ij;
38 import org.apache.derby.tools.JDBCDisplayUtil;
39 import org.apache.derbyTesting.functionTests.util.TestUtil;
40
41
42 public class setTransactionIsolation{
43
44 static String conntype = null;
45 static boolean shortTest = true;
46
47 public static void main (String args[])
48 {
49
50 try {
51 ij.getPropertyArg(args);
54 Connection conn = ij.startJBMS();
55
56 createAndPopulateTable(conn);
57 runTests(conn);
58 conn.rollback();
59 cleanUp(conn);
60 conn.close();
61 } catch (Throwable e) {
62 e.printStackTrace();
63 }
64 }
65
66 private static void dropTable(Statement stmt,String tab)
67 {
68 try {
69 stmt.executeUpdate("drop table " + tab);
70 }
71 catch (SQLException se)
72 {
73 }
74 }
75
76 private static void createAndPopulateTable(Connection conn) throws SQLException {
78 Statement stmt = conn.createStatement();
79
80
81 String [] tabsToDrop = {"tab1", "t1", "t1copy", "t2", "t3"};
82 for (int i = 0; i < tabsToDrop.length; i++)
83 dropTable(stmt,tabsToDrop[i]);
84
85
86 System.out.println("Creating table...");
87 final int stringLength = 400;
88 stmt.executeUpdate("CREATE TABLE TAB1 (c11 int, " +
89 "c12 varchar(" + stringLength + "))");
90 PreparedStatement insertStmt =
91 conn.prepareStatement("INSERT INTO TAB1 VALUES(?,?)");
92 final int rows = 40000 / stringLength;
97 StringBuffer buff = new StringBuffer (stringLength);
98 for (int i = 0; i < stringLength; i++) {
99 buff.append(" ");
100 }
101 for (int i = 1; i <= rows; i++) {
102 insertStmt.setInt(1, i);
103 insertStmt.setString(2, buff.toString());
104 insertStmt.executeUpdate();
105 }
106 insertStmt.close();
107
108 stmt.execute("create table t1(I int, B char(15))");
109 stmt.execute("create table t1copy(I int, B char(15))");
110
111 stmt.executeUpdate("INSERT INTO T1 VALUES(1,'First Hello')");
112 stmt.executeUpdate("INSERT INTO T1 VALUES(2,'Second Hello')");
113 stmt.executeUpdate("INSERT INTO T1 VALUES(3,'Third Hello')");
114
115
116 stmt.executeUpdate("create table t3 (i integer)");
117
118 System.out.println("done creating table and inserting data.");
119
120 stmt.close();
121 }
122
123
124
125 public static void runTests( Connection conn) throws Throwable
126 {
127 try {
128 testIsolation(conn, true);
130 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
133 testIsolation(conn, false);
134 testSetTransactionIsolationInHoldCursor(conn);
135 testSetTransactionIsolationCommits(conn);
136 } catch (SQLException sqle) {
137 System.out.print("FAIL:");
138 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
139 sqle.printStackTrace(System.out);
140 }
141
142 }
143
144
145
146
147
148 public static int[] isoLevels = {
149 Connection.TRANSACTION_READ_UNCOMMITTED,
150 Connection.TRANSACTION_REPEATABLE_READ,
151 Connection.TRANSACTION_READ_COMMITTED,
152 Connection.TRANSACTION_SERIALIZABLE};
153
154
155 private static void testIsolation(Connection conn, boolean makeNewStatements) throws SQLException
156 {
157
158 Connection conn2 = null;
159 try {
160 conn2 = ij.startJBMS();
161
162 }
163 catch (Exception e){
164 System.out.println(e.getMessage());
165 e.printStackTrace();
166 }
167 Statement stmt = conn.createStatement();
168 Statement stmt2 = conn2.createStatement();
169 System.out.println("*** testIsolation. makeNewStatements =" + makeNewStatements);
170
171 conn.setAutoCommit(false);
172
173 conn2.setAutoCommit(false);
174
175 stmt2.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
176 String [] sql = { "select * from t1",
177 "insert into t1copy (select * from t1)"};
178
179 PreparedStatement ps = null;
180 System.out.println("*** Test with no lock timeouts ***");
181 for (int s = 0; s < sql.length; s++)
182 testLevelsAndPrintStatistics(conn2,sql[s],makeNewStatements);
183 System.out.println("*** Test with lock timeouts on everything but read uncommitted***");
185 System.out.println("conn :insert into t1 values(4,'Forth Hello') (no commit)");
186 stmt.executeUpdate("insert into t1 values(4,'Fourth Hello')");
187 for (int s = 0 ; s < sql.length;s++)
188 testLevelsAndPrintStatistics(conn2,sql[s],makeNewStatements);
189 stmt.close();
190 stmt2.close();
191 conn.rollback();
193
194 }
195
196
199 public static void testSetTransactionIsolationInHoldCursor(Connection conn)
200 {
201 try {
202
203 PreparedStatement ps = conn.prepareStatement("SELECT * from TAB1");
204 ResultSet rs = ps.executeQuery();
205 rs.next();
206 conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
209 rs.next(); } catch (SQLException se)
211 {
212 System.out.println("EXPECTED EXCEPTION SQLSTATE:" +
213 se.getSQLState() + " " +
214 se.getMessage());
215 return;
216 }
217 System.out.println("FAIL: setTransactionIsolation() did not throw exception with open hold cursor");
218 }
219
220
223 public static void testSetTransactionIsolationCommits(Connection conn)
224 {
225 try {
230 conn.rollback();
231 conn.setAutoCommit(false);
232 conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
233 Statement s = conn.createStatement();
234 s.executeUpdate("delete from t3");
235 s.executeUpdate("insert into t3 values(1)");
236 conn.commit();
237 s.executeUpdate("insert into t3 values(2)");
238 conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
239 conn.rollback();
240 ResultSet rs = s.executeQuery("select count(*) from t3");
241 rs.next();
242 int count = rs.getInt(1);
243 switch (count) {
244 case 1:
245 System.out.println("count="+ count +
246 ", setTransactionIsolation() does not commit");
247 break;
248 case 2:
249 System.out.println("count="+ count +
250 ", setTransactionIsolation() commits");
251 break;
252 default:
253 System.out.println("FAIL: count="+ count +
254 ", unexepected behaviour from testSetTransactionIsolationCommits");
255 break;
256 }
257 rs.close();
258 s.close();
259 } catch (SQLException se) {
260 System.out.println(se.getMessage());
261 }
262 }
263
264 public static void testLevelsAndPrintStatistics(Connection con, String sql,
265 boolean makeNewStatements)
266 throws SQLException
267 {
268 System.out.println("***testLevelsAndPrintStatistics sql:" + sql +
269 " makenewStatements:" + makeNewStatements);
270 PreparedStatement ps = con.prepareStatement(sql);
271 Statement stmt = con.createStatement();
272
273 System.out.println("con.prepareStatement(" +sql +")");
274 for (int i = 0; i < isoLevels.length; i++)
275 {
276
277 try {
278 System.out.println("con.setTransactionIsolation(" +
279 getIsoLevelName(isoLevels[i]) +")");
280 con.setTransactionIsolation(isoLevels[i]);
281
282 System.out.println("con.getTransactionIsolation() =" +
283 getIsoLevelName(con.getTransactionIsolation()));
284 if (makeNewStatements)
285 {
286 ps.close();
287 ps = con.prepareStatement(sql);
288 System.out.println("con.prepareStatement(" +sql +")");
289 }
290
291 System.out.println(sql);
292 ps.execute();
293 ResultSet rs = ps.getResultSet();
294 rs.next();
297 showScanStatistics(rs,con);
298
299
313 } catch (Exception e)
314 {
315 System.out.println(e.getMessage());
316 }
318 con.commit();
319 stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
320 }
321
322 ps.close();
323 stmt.close();
324 System.out.println("\n\n");
325 }
326
327 public static String getIsoLevelName(int level)
328 {
329 switch (level) {
330 case java.sql.Connection.TRANSACTION_REPEATABLE_READ:
331 return "TRANSACTION_REAPEATABLE_READ:" + level;
332
333 case java.sql.Connection.TRANSACTION_READ_COMMITTED:
334 return "TRANSACTION_READ_COMMITTED:" + level;
335 case java.sql.Connection.TRANSACTION_SERIALIZABLE:
336 return "TRANSACTION_SERIALIZABLE:" + level;
337 case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED:
338 return "TRANSACTION_READ_UNCOMMITTED:" + level;
339
340 }
341 return "UNEXPECTED_ISO_LEVEL";
342 }
343
344
345 private static void statementExceptionExpected(Statement s, String sql) {
346 System.out.println(sql);
347 try {
348 s.execute(sql);
349 System.out.println("FAIL - SQL expected to throw exception");
350 } catch (SQLException sqle) {
351 System.out.println("EXPECTED " + sqle.toString());
352 }
353 }
354
355 public static void showResultsAndStatistics(Statement s, Connection conn,
356 boolean expectException)
357 {
358 ResultSet rs = null;
359 try {
360 rs = s.getResultSet();
361 if (rs == null)
362 {
363 System.out.println("UPDATE COUNT " + s.getUpdateCount());
364 return;
365 }
366 else
367 showResultsAndStatistics(rs,conn,expectException);
368 }
369 catch (SQLException se)
370 {
371 System.out.print("FAIL: UNEXPECTED EXCEPTION:");
374 JDBCDisplayUtil.ShowSQLException(System.out,se);
375 }
376 }
377 public static void showResultsAndStatistics(ResultSet rs,Connection conn,
378 boolean expectException)
379 {
380
381
382 try {
383 System.out.println("CursorName:" + rs.getCursorName());
384 JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
385 showScanStatistics(rs,conn);
386 }catch (SQLException se)
387 {
388 if(expectException )
389 System.out.print("EXPECTED SQL EXCEPTION:");
390 else
391 System.out.print("FAIL: UNEXPECTED EXCEPTION:");
392 JDBCDisplayUtil.ShowSQLException(System.out,se);
393 }
394
395
396 }
397
398 public static void showScanStatistics(ResultSet rs, Connection conn)
399 {
400 Statement s = null;
401 ResultSet infors = null;
402
403
404 try {
405 rs.close(); s =conn.createStatement();
407 infors = s.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
408 JDBCDisplayUtil.setMaxDisplayWidth(2000);
409 JDBCDisplayUtil.DisplayResults(System.out,infors,conn);
410 infors.close();
411 }
412 catch (SQLException se)
413 {
414 System.out.print("FAIL:");
415 JDBCDisplayUtil.ShowSQLException(System.out,se);
416 }
417 }
418
419 static void cleanUp(Connection conn) throws SQLException
420 {
421 String [] testObjects = {"table t1"};
422 Statement stmt = conn.createStatement();
423 TestUtil.cleanUpTest(stmt, testObjects);
424 conn.commit();
425 stmt.close();
426 }
427
428
429 }
430
431
432
433 |
Popular Tags |
Java API By Example, From Geeks To Geeks.