|
1
5 package org.h2.test.jdbc;
6
7 import java.sql.Connection  ;
8 import java.sql.ResultSet ;
9 import java.sql.SQLException ;
10 import java.sql.Savepoint ;
11 import java.sql.Statement ;
12
13 import org.h2.test.TestBase;
14
15 public class TestStatement extends TestBase {
16
17 Connection conn;
18
19 public void test() throws Exception {
20 deleteDb("statement");
21 conn = getConnection("statement");
22 if(config.jdk14) {
23 testSavepoint();
24 }
25 testStatement();
26 if(config.jdk14) {
27 testIdentity();
28 }
29 conn.close();
30 }
31
32 void testSavepoint() throws Exception {
33 Statement stat=conn.createStatement();
34 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
35 conn.setAutoCommit(false);
36 stat.execute("INSERT INTO TEST VALUES(0, 'Hi')");
37 Savepoint savepoint1 = conn.setSavepoint();
38 int id1 = savepoint1.getSavepointId();
39 try {
40 savepoint1.getSavepointName();
41 error("exception expected");
42 } catch(SQLException e) {
43 checkNotGeneralException(e);
44 }
45 stat.execute("DELETE FROM TEST");
46 conn.rollback(savepoint1);
47 stat.execute("UPDATE TEST SET NAME='Hello'");
48 Savepoint savepoint2a = conn.setSavepoint();
49 Savepoint savepoint2 = conn.setSavepoint();
50 conn.releaseSavepoint(savepoint2a);
51 try {
52 savepoint2a.getSavepointId();
53 error("exception expected");
54 } catch(SQLException e) {
55 checkNotGeneralException(e);
56 }
57 int id2 = savepoint2.getSavepointId();
58 check(id1 != id2);
59 stat.execute("UPDATE TEST SET NAME='Hallo' WHERE NAME='Hello'");
60 Savepoint savepointTest = conn.setSavepoint("Joe's");
61 stat.execute("DELETE FROM TEST");
62 check(savepointTest.getSavepointName(), "Joe's");
63 try {
64 savepointTest.getSavepointId();
65 error("exception expected");
66 } catch(SQLException e) {
67 checkNotGeneralException(e);
68 }
69 conn.rollback(savepointTest);
70 conn.commit();
71 ResultSet rs = stat.executeQuery("SELECT NAME FROM TEST");
72 rs.next();
73 String name = rs.getString(1);
74 check(name, "Hallo");
75 checkFalse(rs.next());
76 try {
77 conn.rollback(savepoint2);
78 error("exception expected");
79 } catch(SQLException e) {
80 checkNotGeneralException(e);
81 }
82 stat.execute("DROP TABLE TEST");
83 conn.setAutoCommit(true);
84 }
85
86 void testStatement() throws Exception {
87
88 Statement stat=conn.createStatement();
89 ResultSet rs;
90 int count;
91 boolean result;
92
93 stat.execute("CREATE TABLE TEST(ID INT)");
94 stat.execute("SELECT * FROM TEST");
95 stat.execute("DROP TABLE TEST");
96
97 conn.getTypeMap();
98
99
101 if(config.jdk14) {
102 check(stat.getResultSetHoldability(), ResultSet.HOLD_CURSORS_OVER_COMMIT);
103 }
104 check(stat.getResultSetConcurrency(), ResultSet.CONCUR_UPDATABLE);
105
106 stat.cancel();
107 stat.setQueryTimeout(10);
108 check(stat.getQueryTimeout()==10);
109 stat.setQueryTimeout(0);
110 check(stat.getQueryTimeout()==0);
111 try {
113 stat.setQueryTimeout(-1);
114 error("setQueryTimeout(-1) didn't throw an exception");
115 } catch(SQLException e) {
116 checkNotGeneralException(e);
117 }
118 check(stat.getQueryTimeout()==0);
119 trace("executeUpdate");
120 count=stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
121 check(count,0);
122 count=stat.executeUpdate("INSERT INTO TEST VALUES(1,'Hello')");
123 check(count,1);
124 count=stat.executeUpdate("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
125 check(count,1);
126 count=stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=2 OR ID=1");
127 check(count,2);
128 count=stat.executeUpdate("UPDATE TEST SET VALUE='\\LDBC\\' WHERE VALUE LIKE 'LDBC' ");
129 check(count,2);
130 count=stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE VALUE LIKE '\\\\LDBC\\\\'");
131 trace("count:"+count);
132 check(count,2);
133 count=stat.executeUpdate("DELETE FROM TEST WHERE ID=-1");
134 check(count,0);
135 count=stat.executeUpdate("DELETE FROM TEST WHERE ID=2");
136 check(count,1);
137 try {
138 stat.executeUpdate("SELECT * FROM TEST");
139 error("executeUpdate allowed SELECT");
140 } catch(SQLException e) {
141 checkNotGeneralException(e);
142 trace("no error - SELECT not allowed with executeUpdate");
143 }
144 count=stat.executeUpdate("DROP TABLE TEST");
145 check(count==0);
146
147 trace("execute");
148 result=stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
149 check(result==false);
150 result=stat.execute("INSERT INTO TEST VALUES(1,'Hello')");
151 check(result==false);
152 result=stat.execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
153 check(result==false);
154 result=stat.execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
155 check(result==false);
156 result=stat.execute("DELETE FROM TEST WHERE ID=3");
157 check(result==false);
158 result=stat.execute("SELECT * FROM TEST");
159 check(result==true);
160 result=stat.execute("DROP TABLE TEST");
161 check(result==false);
162
163 trace("executeQuery");
164 try {
165 stat.executeQuery("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
166 error("executeQuery allowed CREATE TABLE");
167 } catch(SQLException e) {
168 checkNotGeneralException(e);
169 trace("no error - CREATE not allowed with executeQuery");
170 }
171 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
172 try {
173 stat.executeQuery("INSERT INTO TEST VALUES(1,'Hello')");
174 error("executeQuery allowed INSERT");
175 } catch(SQLException e) {
176 checkNotGeneralException(e);
177 trace("no error - INSERT not allowed with executeQuery");
178 }
179 try {
180 stat.executeQuery("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
181 error("executeQuery allowed UPDATE");
182 } catch(SQLException e) {
183 checkNotGeneralException(e);
184 trace("no error - UPDATE not allowed with executeQuery");
185 }
186 try {
187 stat.executeQuery("DELETE FROM TEST WHERE ID=3");
188 error("executeQuery allowed DELETE");
189 } catch(SQLException e) {
190 checkNotGeneralException(e);
191 trace("no error - DELETE not allowed with executeQuery");
192 }
193 stat.executeQuery("SELECT * FROM TEST");
194 try {
195 stat.executeQuery("DROP TABLE TEST");
196 error("executeQuery allowed DROP");
197 } catch(SQLException e) {
198 checkNotGeneralException(e);
199 trace("no error - DROP not allowed with executeQuery");
200 }
201 rs=stat.executeQuery("SELECT * FROM TEST");
203 checkFalse(stat.getMoreResults());
204 try {
205 rs.next();
207 error("getMoreResults didn't close this result set");
208 } catch(SQLException e) {
209 checkNotGeneralException(e);
210 trace("no error - getMoreResults is supposed to close the result set");
211 }
212 check(stat.getUpdateCount()==-1);
213 count=stat.executeUpdate("DELETE FROM TEST");
214 checkFalse(stat.getMoreResults());
215 check(stat.getUpdateCount()==-1);
216
217 stat.execute("DROP TABLE TEST");
218 stat.executeUpdate("DROP TABLE IF EXISTS TEST");
219
220 check(stat.getWarnings()==null);
221 stat.clearWarnings();
222 check(stat.getWarnings()==null);
223 check(conn==stat.getConnection());
224
225 stat.close();
226 }
227
228 private void testIdentity() throws Exception {
229 Statement stat = conn.createStatement();
230 stat.execute("CREATE SEQUENCE SEQ");
231 stat.execute("CREATE TABLE TEST(ID INT)");
232 stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
233 ResultSet rs = stat.getGeneratedKeys();
234 rs.next();
235 check(rs.getInt(1), 1);
236 checkFalse(rs.next());
237 stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
238 rs = stat.getGeneratedKeys();
239 rs.next();
240 check(rs.getInt(1), 2);
241 checkFalse(rs.next());
242 stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[]{1});
243 rs = stat.getGeneratedKeys();
244 rs.next();
245 check(rs.getInt(1), 3);
246 checkFalse(rs.next());
247 stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String []{"ID"});
248 rs = stat.getGeneratedKeys();
249 rs.next();
250 check(rs.getInt(1), 4);
251 checkFalse(rs.next());
252 stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
253 rs = stat.getGeneratedKeys();
254 rs.next();
255 check(rs.getInt(1), 5);
256 checkFalse(rs.next());
257 stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[]{1});
258 rs = stat.getGeneratedKeys();
259 rs.next();
260 check(rs.getInt(1), 6);
261 checkFalse(rs.next());
262 stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String []{"ID"});
263 rs = stat.getGeneratedKeys();
264 rs.next();
265 check(rs.getInt(1), 7);
266 checkFalse(rs.next());
267 stat.execute("DROP TABLE TEST");
268 }
269
270 }
271 |
Popular Tags |
Java API By Example, From Geeks To Geeks.