|
1
5 package org.h2.test.db;
6
7 import java.sql.Connection  ;
8 import java.sql.DatabaseMetaData ;
9 import java.sql.ResultSet ;
10 import java.sql.SQLException ;
11 import java.sql.Savepoint ;
12 import java.sql.Statement ;
13 import java.util.Random ;
14 import java.util.Vector ;
15
16 import org.h2.test.TestBase;
17
18 public class TestTransaction extends TestBase {
19
20 public void test() throws Exception {
21 testSavepoint();
22 testIsolation();
23 }
24
25 public void testSavepoint() throws Exception {
26 deleteDb("transaction");
27 Connection conn = getConnection("transaction");
28 Statement stat = conn.createStatement();
29 stat.execute("CREATE TABLE TEST0(ID IDENTITY, NAME VARCHAR)");
30 stat.execute("CREATE TABLE TEST1(NAME VARCHAR, ID IDENTITY, X TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
31 conn.setAutoCommit(false);
32 int[] count = new int[2];
33 int[] countCommitted = new int[2];
34 int[] countSave = new int[2];
35 int len = getSize(2000, 10000);
36 Random random = new Random (10);
37 Savepoint sp = null;
38 for(int i=0; i<len; i++) {
39 int tableId = random.nextInt(2);
40 String table="TEST" + tableId;
41 int op = random.nextInt(6);
42 switch(op) {
43 case 0:
44 stat.execute("INSERT INTO "+table+"(NAME) VALUES('op"+i+"')");
45 count[tableId]++;
46 break;
47 case 1:
48 if(count[tableId] > 0) {
49 stat.execute("DELETE FROM "+table+" WHERE ID=SELECT MIN(ID) FROM "+table);
50 count[tableId]--;
51 }
52 break;
53 case 2:
54 sp = conn.setSavepoint();
55 countSave[0] = count[0]; countSave[1] = count[1];
56 break;
57 case 3:
58 if(sp != null) {
59 conn.rollback(sp);
60 count[0] = countSave[0]; count[1] = countSave[1];
61 }
62 break;
63 case 4:
64 conn.commit();
65 sp = null;
66 countCommitted[0] = count[0]; countCommitted[1] = count[1];
67 break;
68 case 5:
69 conn.rollback();
70 sp = null;
71 count[0] = countCommitted[0]; count[1] = countCommitted[1];
72 break;
73 }
74 checkTableCount(stat, "TEST0", count[0]);
75 checkTableCount(stat, "TEST1", count[1]);
76 }
77 conn.close();
78 }
79
80 private void checkTableCount(Statement stat, String tableName, int count) throws Exception {
81 ResultSet rs;
82 rs = stat.executeQuery("SELECT COUNT(*) FROM " + tableName);
83 rs.next();
84 check(count, rs.getInt(1));
85 }
86
87 public void testIsolation() throws Exception {
88 Connection conn = getConnection("transaction");
89 trace("default TransactionIsolation="+conn.getTransactionIsolation());
90 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
91 check(conn.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED);
92 conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
93 check(conn.getTransactionIsolation()==Connection.TRANSACTION_SERIALIZABLE);
94 Statement stat=conn.createStatement();
95 check(conn.getAutoCommit());
96 conn.setAutoCommit(false);
97 checkFalse(conn.getAutoCommit());
98 conn.setAutoCommit(true);
99 check(conn.getAutoCommit());
100 test(stat, "CREATE TABLE TEST(ID INT PRIMARY KEY)");
101 conn.commit();
102 test(stat, "INSERT INTO TEST VALUES(0)");
103 conn.rollback();
104 testValue(stat, "SELECT COUNT(*) FROM TEST","1");
105 conn.setAutoCommit(false);
106 test(stat, "DELETE FROM TEST");
107 conn.rollback();
109 testValue(stat, "SELECT COUNT(*) FROM TEST", "1");
110 conn.commit();
111 conn.setAutoCommit(true);
112 testNestedResultSets(conn);
113 conn.setAutoCommit(false);
114 testNestedResultSets(conn);
115 conn.close();
116 }
117
118 void testNestedResultSets(Connection conn) throws Exception {
119 Statement stat = conn.createStatement();
120 test(stat, "CREATE TABLE NEST1(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
121 test(stat, "CREATE TABLE NEST2(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
122 DatabaseMetaData meta=conn.getMetaData();
123 Vector result;
124 ResultSet rs1,rs2;
125 result=new Vector ();
126 rs1=meta.getTables(null,null,"NEST%",null);
127 while(rs1.next()) {
128 String table=rs1.getString("TABLE_NAME");
129 rs2=meta.getColumns(null,null,table,null);
130 while(rs2.next()) {
131 String column=rs2.getString("COLUMN_NAME");
132 trace("Table: "+table+" Column: "+column);
133 result.add(table+"."+column);
134 }
135 }
136 if(result.size()!=4) {
137 error("Wrong result, should be NEST1.ID, NEST1.NAME, NEST2.ID, NEST2.NAME but is "+result);
138 }
139 result=new Vector ();
140 test(stat, "INSERT INTO NEST1 VALUES(1,'A')");
141 test(stat, "INSERT INTO NEST1 VALUES(2,'B')");
142 test(stat, "INSERT INTO NEST2 VALUES(1,'1')");
143 test(stat, "INSERT INTO NEST2 VALUES(2,'2')");
144 Statement s1=conn.createStatement();
145 Statement s2=conn.createStatement();
146 rs1=s1.executeQuery("SELECT * FROM NEST1 ORDER BY ID");
147 while(rs1.next()) {
148 rs2=s2.executeQuery("SELECT * FROM NEST2 ORDER BY ID");
149 while(rs2.next()) {
150 String v1=rs1.getString("VALUE");
151 String v2=rs2.getString("VALUE");
152 result.add(v1+"/"+v2);
153 }
154 }
155 if(result.size()!=4) {
156 error("Wrong result, should be A/1, A/2, B/1, B/2 but is "+result);
157 }
158 result=new Vector ();
159 rs1=s1.executeQuery("SELECT * FROM NEST1 ORDER BY ID");
160 rs2=s1.executeQuery("SELECT * FROM NEST2 ORDER BY ID");
161 try {
162 rs1.next();
163 error("next worked on a closed result set");
164 } catch(SQLException e) {
165 checkNotGeneralException(e);
166 }
167 rs1.close();
169 while(rs2.next()) {
170 String v1=rs2.getString("VALUE");
171 result.add(v1);
172 }
173 if(result.size()!=2) {
174 error("Wrong result, should be A, B but is "+result);
175 }
176 test(stat, "DROP TABLE NEST1");
177 test(stat, "DROP TABLE NEST2");
178 }
179
180 void testValue(Statement stat, String sql, String data) throws Exception {
181 ResultSet rs=stat.executeQuery(sql);
182 rs.next();
183 String s=rs.getString(1);
184 if(s==null ? (data!=null) : (!s.equals(data))) {
185 error("s= "+s+" should be: "+data);
186 }
187 }
188
189 void test(Statement stat, String sql) throws Exception {
190 trace(sql);
191 stat.execute(sql);
192 }
193
194 }
195 |
Popular Tags |
Java API By Example, From Geeks To Geeks.