|
1
5 package org.h2.test.jdbc;
6
7 import java.io.ByteArrayInputStream  ;
8 import java.io.StringReader ;
9 import java.math.BigDecimal ;
10 import java.sql.*;
11
12 import org.h2.test.TestBase;
13
14 public class TestUpdatableResultSet extends TestBase {
15
16 public void test() throws Exception {
17 testScroll();
18 testUpdateDeleteInsert();
19 testUpdateDataType();
20 }
21
22 private void testScroll() throws Exception {
23 deleteDb("updatableResultSet");
24 Connection conn = getConnection("updatableResultSet");
25 Statement stat = conn.createStatement();
26 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
27 stat.execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'Test')");
28
29 ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
30 check(rs.isBeforeFirst());
31 checkFalse(rs.isAfterLast());
32 check(rs.getRow(), 0);
33
34 rs.next();
35 checkFalse(rs.isBeforeFirst());
36 checkFalse(rs.isAfterLast());
37 check(rs.getInt(1), 1);
38 check(rs.getRow(), 1);
39
40 rs.next();
41 checkFalse(rs.isBeforeFirst());
42 checkFalse(rs.isAfterLast());
43 check(rs.getInt(1), 2);
44 check(rs.getRow(), 2);
45
46 rs.next();
47 checkFalse(rs.isBeforeFirst());
48 checkFalse(rs.isAfterLast());
49 check(rs.getInt(1), 3);
50 check(rs.getRow(), 3);
51
52 checkFalse(rs.next());
53 checkFalse(rs.isBeforeFirst());
54 check(rs.isAfterLast());
55 check(rs.getRow(), 0);
56
57 check(rs.first());
58 check(rs.getInt(1), 1);
59 check(rs.getRow(), 1);
60
61 check(rs.last());
62 check(rs.getInt(1), 3);
63 check(rs.getRow(), 3);
64
65 check(rs.relative(0));
66 check(rs.getRow(), 3);
67
68 check(rs.relative(-1));
69 check(rs.getRow(), 2);
70
71 check(rs.relative(1));
72 check(rs.getRow(), 3);
73
74 checkFalse(rs.relative(100));
75 check(rs.isAfterLast());
76
77 checkFalse(rs.absolute(0));
78 check(rs.getRow(), 0);
79
80 check(rs.absolute(1));
81 check(rs.getRow(), 1);
82
83 check(rs.absolute(2));
84 check(rs.getRow(), 2);
85
86 check(rs.absolute(3));
87 check(rs.getRow(), 3);
88
89 checkFalse(rs.absolute(4));
90 check(rs.getRow(), 0);
91
92 try {
93 checkFalse(rs.absolute(0));
94 } catch(SQLException e) {
97 checkNotGeneralException(e);
98 }
99
100 check(rs.absolute(3));
101 check(rs.getRow(), 3);
102
103 check(rs.absolute(-1));
104 check(rs.getRow(), 3);
105
106 checkFalse(rs.absolute(4));
107 check(rs.isAfterLast());
108
109 checkFalse(rs.absolute(5));
110 check(rs.isAfterLast());
111
112 check(rs.previous());
113 check(rs.getRow(), 3);
114
115 check(rs.previous());
116 check(rs.getRow(), 2);
117
118 conn.close();
119 }
120
121 private void testUpdateDataType() throws Exception {
122 deleteDb("updatableResultSet");
123 Connection conn = getConnection("updatableResultSet");
124 Statement stat = conn.createStatement();
125 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), " +
126 "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), "+
127 "D DATE, T TIME, TS TIMESTAMP, DOU DOUBLE, REA REAL, LON BIGINT, "+
128 "OBI INT, SHO SMALLINT, CLO CLOB, BLO BLOB)");
129 ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
130 ResultSetMetaData meta = rs.getMetaData();
131 check(meta.getColumnClassName(1), "java.lang.Integer");
132 check(meta.getColumnClassName(2), "java.lang.String");
133 check(meta.getColumnClassName(3), "java.math.BigDecimal");
134 check(meta.getColumnClassName(4), "java.lang.Boolean");
135 check(meta.getColumnClassName(5), "java.lang.Byte");
136 check(meta.getColumnClassName(6), "[B");
137 check(meta.getColumnClassName(7), "java.sql.Date");
138 check(meta.getColumnClassName(8), "java.sql.Time");
139 check(meta.getColumnClassName(9), "java.sql.Timestamp");
140 check(meta.getColumnClassName(10), "java.lang.Double");
141 check(meta.getColumnClassName(11), "java.lang.Float");
142 check(meta.getColumnClassName(12), "java.lang.Long");
143 check(meta.getColumnClassName(13), "java.lang.Integer");
144 check(meta.getColumnClassName(14), "java.lang.Short");
145 check(meta.getColumnClassName(15), "java.sql.Clob");
146 check(meta.getColumnClassName(16), "java.sql.Blob");
147
148 rs.moveToInsertRow();
149 rs.updateInt(1, 0);
150 rs.updateNull(2);
151 rs.updateNull("DEC");
152 rs.insertRow();
154
155 rs.moveToInsertRow();
156 rs.updateInt(1, 1);
157 rs.updateString(2, null);
158 rs.updateBigDecimal(3, null);
159 rs.updateBoolean(4, false);
160 rs.updateByte(5, (byte)0);
161 rs.updateBytes(6, null);
162 rs.updateDate(7, null);
163 rs.updateTime(8, null);
164 rs.updateTimestamp(9, null);
165 rs.updateDouble(10, 0.0);
166 rs.updateFloat(11, (float)0.0);
167 rs.updateLong(12, 0L);
168 rs.updateObject(13, null);
169 rs.updateShort(14, (short)0);
170 rs.updateCharacterStream(15, new StringReader ("test"), 0);
171 rs.updateBinaryStream(16, new ByteArrayInputStream (new byte[]{(byte)0xff, 0x00}), 0);
172 rs.insertRow();
173
174 rs.moveToInsertRow();
175 rs.updateInt("ID", 2);
176 rs.updateString("NAME", "+");
177 rs.updateBigDecimal("DEC", new BigDecimal ("1.2"));
178 rs.updateBoolean("BOO", true);
179 rs.updateByte("BYE", (byte)0xff);
180 rs.updateBytes("BIN", new byte[]{0x00, (byte)0xff});
181 rs.updateDate("D", Date.valueOf("2005-09-21"));
182 rs.updateTime("T", Time.valueOf("21:46:28"));
183 rs.updateTimestamp("TS", Timestamp.valueOf("2005-09-21 21:47:09.567890123"));
184 rs.updateDouble("DOU", 1.725);
185 rs.updateFloat("REA", (float)2.5);
186 rs.updateLong("LON", Long.MAX_VALUE);
187 rs.updateObject("OBI", new Integer (10));
188 rs.updateShort("SHO", Short.MIN_VALUE);
189 rs.updateCharacterStream("CLO", new StringReader ("\u00ef\u00f6\u00fc"), 0); rs.updateBinaryStream("BLO", new ByteArrayInputStream (new byte[]{(byte)0xab, 0x12}), 0);
191 rs.insertRow();
192
193 rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST");
194 rs.next();
195 check(rs.getInt(1) == 0);
196 check(rs.getString(2) == null && rs.wasNull());
197 check(rs.getBigDecimal(3) == null && rs.wasNull());
198 check(rs.getBoolean(4) == false && rs.wasNull());
199 check(rs.getByte(5) == 0 && rs.wasNull());
200 check(rs.getBytes(6) == null && rs.wasNull());
201 check(rs.getDate(7) == null && rs.wasNull());
202 check(rs.getTime(8) == null && rs.wasNull());
203 check(rs.getTimestamp(9) == null && rs.wasNull());
204 check(rs.getDouble(10) == 0.0 && rs.wasNull());
205 check(rs.getFloat(11) == 0.0 && rs.wasNull());
206 check(rs.getLong(12) == 0 && rs.wasNull());
207 check(rs.getObject(13) == null && rs.wasNull());
208 check(rs.getShort(14) == 0 && rs.wasNull());
209 check(rs.getCharacterStream(15) == null && rs.wasNull());
210 check(rs.getBinaryStream(16) == null && rs.wasNull());
211
212 rs.next();
213 check(rs.getInt(1) == 1);
214 check(rs.getString(2)==null && rs.wasNull());
215 check(rs.getBigDecimal(3) == null && rs.wasNull());
216 check(rs.getBoolean(4)==false && !rs.wasNull());
217 check(rs.getByte(5) == 0 && !rs.wasNull());
218 check(rs.getBytes(6) == null && rs.wasNull());
219 check(rs.getDate(7) == null && rs.wasNull());
220 check(rs.getTime(8) == null && rs.wasNull());
221 check(rs.getTimestamp(9) == null && rs.wasNull());
222 check(rs.getDouble(10) == 0.0 && !rs.wasNull());
223 check(rs.getFloat(11) == 0.0 && !rs.wasNull());
224 check(rs.getLong(12) == 0 && !rs.wasNull());
225 check(rs.getObject(13) == null && rs.wasNull());
226 check(rs.getShort(14) == 0 && !rs.wasNull());
227 check(rs.getString(15), "test");
228 check(rs.getBytes(16), new byte[]{(byte)0xff, 0x00});
229
230 rs.next();
231 check(rs.getInt(1) == 2);
232 check(rs.getString(2), "+");
233 check(rs.getBigDecimal(3).toString(), "1.20");
234 check(rs.getBoolean(4)==true);
235 check((rs.getByte(5)&0xff) == 0xff);
236 check(rs.getBytes(6), new byte[]{0x00, (byte)0xff});
237 check(rs.getDate(7).toString(), "2005-09-21");
238 check(rs.getTime(8).toString(), "21:46:28");
239 check(rs.getTimestamp(9).toString(), "2005-09-21 21:47:09.567890123");
240 check(rs.getDouble(10) == 1.725);
241 check(rs.getFloat(11) == (float)2.5);
242 check(rs.getLong(12) == Long.MAX_VALUE);
243 check(((Integer )rs.getObject(13)).intValue(), 10);
244 check(rs.getShort(14) == Short.MIN_VALUE);
245 check(rs.getString(15), "\u00ef\u00f6\u00fc"); check(rs.getBytes(16), new byte[]{(byte)0xab, 0x12});
247
248 checkFalse(rs.next());
249 stat.execute("DROP TABLE TEST");
250 conn.close();
251 }
252
253 private void testUpdateDeleteInsert() throws Exception {
254 deleteDb("updatableResultSet");
255 Connection c1 = getConnection("updatableResultSet");
256 Connection c2 = getConnection("updatableResultSet");
257 Statement stat = c1.createStatement();
258 stat.execute("DROP TABLE IF EXISTS TEST");
259 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
260 int max = 8;
261 for(int i=0; i<max; i++) {
262 stat.execute("INSERT INTO TEST VALUES("+i+", 'Hello"+i+"')");
263 }
264 ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
265 int j=max;
266 while(rs.next()) {
267 int id = rs.getInt(1);
268 if(id % 2 == 0) {
269 Statement s2 = c2.createStatement();
270 s2.execute("UPDATE TEST SET NAME = NAME || '+' WHERE ID = "+rs.getInt(1));
271 if(id % 4 == 0) {
272 rs.refreshRow();
273 }
274 rs.updateString(2, "Updated " + rs.getString(2));
275 rs.updateRow();
276 } else {
277 rs.deleteRow();
278 }
279 rs.moveToInsertRow();
280 rs.updateString(2, "Inserted " + j);
281 rs.updateInt(1, j);
282 j+= 2;
283 rs.insertRow();
284 }
285 rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
286 while(rs.next()) {
287 int id = rs.getInt(1);
288 String name = rs.getString(2);
289 check(id % 2, 0);
290 if(id >= max) {
291 check("Inserted " + id, rs.getString(2));
292 } else {
293 if(id % 4 == 0) {
294 check(rs.getString(2), "Updated Hello" + id + "+");
295 } else {
296 check(rs.getString(2), "Updated Hello" + id);
297 }
298 }
299 trace("id="+id+" name="+name);
300 }
301 c2.close();
302 c1.close();
303
304 Connection conn = getConnection("updatableResultSet");
306 for(int i=0; i<5; i++) {
307 testScrollable(conn, i);
308 }
309 conn.close();
310 }
311
312 void testScrollable(Connection conn, int rows) throws Exception {
313 Statement stat = conn.createStatement();
314 stat.execute("CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
315 stat.execute("DELETE FROM TEST");
316 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
317 for (int i = 0; i < rows; i++) {
318 prep.setInt(1, i);
319 prep.setString(2, "Data "+i);
320 prep.execute();
321 }
322 Statement regular = conn.createStatement();
323 testScrollResultSet(regular, ResultSet.TYPE_FORWARD_ONLY, rows);
324 Statement scroll = conn.createStatement(
325 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
326 testScrollResultSet(scroll, ResultSet.TYPE_SCROLL_INSENSITIVE, rows);
327 }
328
329 void testScrollResultSet(Statement stat, int type, int rows) throws Exception {
330 boolean error = false;
331 if (type == ResultSet.TYPE_FORWARD_ONLY) {
332 error = true;
333 }
334 ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
335 check(rs.getType(), type);
336
337 checkState(rs, true, false, false, rows == 0);
338 for (int i = 0; i < rows; i++) {
339 rs.next();
340 checkState(rs, rows == 0, i == 0, i == rows - 1,
341 (rows == 0 || i == rows));
342 }
343 try {
344 rs.beforeFirst();
345 checkState(rs, true, false, false, rows == 0);
346 } catch (SQLException e) {
347 if (!error) {
348 throw e;
349 }
350 }
351 try {
352 rs.afterLast();
353 checkState(rs, false, false, false, true);
354 } catch (SQLException e) {
355 if (!error) {
356 throw e;
357 }
358 }
359 try {
360 boolean valid = rs.first();
361 check(valid, rows > 0);
362 if (valid) {
363 checkState(rs, false, true, rows == 1, rows == 0);
364 }
365 } catch (SQLException e) {
366 if (!error) {
367 throw e;
368 }
369 }
370 try {
371 boolean valid = rs.last();
372 check(valid, rows > 0);
373 if (valid) {
374 checkState(rs, false, rows == 1, true, rows == 0);
375 }
376 } catch (SQLException e) {
377 if (!error) {
378 throw e;
379 }
380 }
381 }
382
383 void checkState(ResultSet rs, boolean beforeFirst, boolean first,
384 boolean last, boolean afterLast) throws Exception {
385 check(rs.isBeforeFirst(), beforeFirst);
386 check(rs.isFirst(), first);
387 check(rs.isLast(), last);
388 check(rs.isAfterLast(), afterLast);
389 }
390
391 }
392 |
Popular Tags |
Java API By Example, From Geeks To Geeks.