001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.jdbc;
007:
008: import java.io.ByteArrayInputStream;
009: import java.io.StringReader;
010: import java.math.BigDecimal;
011: import java.sql.Connection;
012: import java.sql.Date;
013: import java.sql.PreparedStatement;
014: import java.sql.ResultSet;
015: import java.sql.ResultSetMetaData;
016: import java.sql.SQLException;
017: import java.sql.Statement;
018: import java.sql.Time;
019: import java.sql.Timestamp;
020:
021: import org.h2.test.TestBase;
022:
023: /**
024: * Updatable result set tests.
025: */
026: public class TestUpdatableResultSet extends TestBase {
027:
028: public void test() throws Exception {
029: testScroll();
030: testUpdateDeleteInsert();
031: testUpdateDataType();
032: }
033:
034: private void testScroll() throws Exception {
035: deleteDb("updatableResultSet");
036: Connection conn = getConnection("updatableResultSet");
037: Statement stat = conn.createStatement(
038: ResultSet.TYPE_SCROLL_INSENSITIVE,
039: ResultSet.CONCUR_READ_ONLY);
040: stat
041: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
042: stat
043: .execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'Test')");
044:
045: ResultSet rs = stat
046: .executeQuery("SELECT * FROM TEST ORDER BY ID");
047:
048: check(rs.isBeforeFirst());
049: checkFalse(rs.isAfterLast());
050: check(rs.getRow(), 0);
051:
052: rs.next();
053: checkFalse(rs.isBeforeFirst());
054: checkFalse(rs.isAfterLast());
055: check(rs.getInt(1), 1);
056: check(rs.getRow(), 1);
057:
058: rs.next();
059:
060: try {
061: rs.insertRow();
062: error();
063: } catch (SQLException e) {
064: checkNotGeneralException(e);
065: }
066:
067: checkFalse(rs.isBeforeFirst());
068: checkFalse(rs.isAfterLast());
069: check(rs.getInt(1), 2);
070: check(rs.getRow(), 2);
071:
072: rs.next();
073: checkFalse(rs.isBeforeFirst());
074: checkFalse(rs.isAfterLast());
075: check(rs.getInt(1), 3);
076: check(rs.getRow(), 3);
077:
078: checkFalse(rs.next());
079: checkFalse(rs.isBeforeFirst());
080: check(rs.isAfterLast());
081: check(rs.getRow(), 0);
082:
083: check(rs.first());
084: check(rs.getInt(1), 1);
085: check(rs.getRow(), 1);
086:
087: check(rs.last());
088: check(rs.getInt(1), 3);
089: check(rs.getRow(), 3);
090:
091: check(rs.relative(0));
092: check(rs.getRow(), 3);
093:
094: check(rs.relative(-1));
095: check(rs.getRow(), 2);
096:
097: check(rs.relative(1));
098: check(rs.getRow(), 3);
099:
100: checkFalse(rs.relative(100));
101: check(rs.isAfterLast());
102:
103: checkFalse(rs.absolute(0));
104: check(rs.getRow(), 0);
105:
106: check(rs.absolute(1));
107: check(rs.getRow(), 1);
108:
109: check(rs.absolute(2));
110: check(rs.getRow(), 2);
111:
112: check(rs.absolute(3));
113: check(rs.getRow(), 3);
114:
115: checkFalse(rs.absolute(4));
116: check(rs.getRow(), 0);
117:
118: try {
119: checkFalse(rs.absolute(0));
120: // actually, we allow it for compatibility
121: // error("absolute 0 not allowed");
122: } catch (SQLException e) {
123: checkNotGeneralException(e);
124: }
125:
126: check(rs.absolute(3));
127: check(rs.getRow(), 3);
128:
129: check(rs.absolute(-1));
130: check(rs.getRow(), 3);
131:
132: checkFalse(rs.absolute(4));
133: check(rs.isAfterLast());
134:
135: checkFalse(rs.absolute(5));
136: check(rs.isAfterLast());
137:
138: check(rs.previous());
139: check(rs.getRow(), 3);
140:
141: check(rs.previous());
142: check(rs.getRow(), 2);
143:
144: conn.close();
145: }
146:
147: private void testUpdateDataType() throws Exception {
148: deleteDb("updatableResultSet");
149: Connection conn = getConnection("updatableResultSet");
150: Statement stat = conn.createStatement();
151: stat
152: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), "
153: + "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), "
154: + "D DATE, T TIME, TS TIMESTAMP, DB DOUBLE, R REAL, L BIGINT, "
155: + "O_I INT, SH SMALLINT, CL CLOB, BL BLOB)");
156: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
157: ResultSetMetaData meta = rs.getMetaData();
158: check(meta.getColumnClassName(1), "java.lang.Integer");
159: check(meta.getColumnClassName(2), "java.lang.String");
160: check(meta.getColumnClassName(3), "java.math.BigDecimal");
161: check(meta.getColumnClassName(4), "java.lang.Boolean");
162: check(meta.getColumnClassName(5), "java.lang.Byte");
163: check(meta.getColumnClassName(6), "[B");
164: check(meta.getColumnClassName(7), "java.sql.Date");
165: check(meta.getColumnClassName(8), "java.sql.Time");
166: check(meta.getColumnClassName(9), "java.sql.Timestamp");
167: check(meta.getColumnClassName(10), "java.lang.Double");
168: check(meta.getColumnClassName(11), "java.lang.Float");
169: check(meta.getColumnClassName(12), "java.lang.Long");
170: check(meta.getColumnClassName(13), "java.lang.Integer");
171: check(meta.getColumnClassName(14), "java.lang.Short");
172: check(meta.getColumnClassName(15), "java.sql.Clob");
173: check(meta.getColumnClassName(16), "java.sql.Blob");
174:
175: rs.moveToInsertRow();
176: rs.updateInt(1, 0);
177: rs.updateNull(2);
178: rs.updateNull("DEC");
179: // 'not set' values are set to null
180: rs.insertRow();
181:
182: rs.moveToInsertRow();
183: rs.updateInt(1, 1);
184: rs.updateString(2, null);
185: rs.updateBigDecimal(3, null);
186: rs.updateBoolean(4, false);
187: rs.updateByte(5, (byte) 0);
188: rs.updateBytes(6, null);
189: rs.updateDate(7, null);
190: rs.updateTime(8, null);
191: rs.updateTimestamp(9, null);
192: rs.updateDouble(10, 0.0);
193: rs.updateFloat(11, (float) 0.0);
194: rs.updateLong(12, 0L);
195: rs.updateObject(13, null);
196: rs.updateShort(14, (short) 0);
197: rs.updateCharacterStream(15, new StringReader("test"), 0);
198: rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] {
199: (byte) 0xff, 0x00 }), 0);
200: rs.insertRow();
201:
202: rs.moveToInsertRow();
203: rs.updateInt("ID", 2);
204: rs.updateString("NAME", "+");
205: rs.updateBigDecimal("DEC", new BigDecimal("1.2"));
206: rs.updateBoolean("BOO", true);
207: rs.updateByte("BYE", (byte) 0xff);
208: rs.updateBytes("BIN", new byte[] { 0x00, (byte) 0xff });
209: rs.updateDate("D", Date.valueOf("2005-09-21"));
210: rs.updateTime("T", Time.valueOf("21:46:28"));
211: rs.updateTimestamp("TS", Timestamp
212: .valueOf("2005-09-21 21:47:09.567890123"));
213: rs.updateDouble("DB", 1.725);
214: rs.updateFloat("R", (float) 2.5);
215: rs.updateLong("L", Long.MAX_VALUE);
216: rs.updateObject("O_I", new Integer(10));
217: rs.updateShort("SH", Short.MIN_VALUE);
218: // auml, ouml, uuml
219: rs.updateCharacterStream("CL", new StringReader(
220: "\u00ef\u00f6\u00fc"), 0);
221: rs.updateBinaryStream("BL", new ByteArrayInputStream(
222: new byte[] { (byte) 0xab, 0x12 }), 0);
223: rs.insertRow();
224:
225: rs = stat
226: .executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST");
227: rs.next();
228: check(rs.getInt(1) == 0);
229: check(rs.getString(2) == null && rs.wasNull());
230: check(rs.getBigDecimal(3) == null && rs.wasNull());
231: check(!rs.getBoolean(4) && rs.wasNull());
232: check(rs.getByte(5) == 0 && rs.wasNull());
233: check(rs.getBytes(6) == null && rs.wasNull());
234: check(rs.getDate(7) == null && rs.wasNull());
235: check(rs.getTime(8) == null && rs.wasNull());
236: check(rs.getTimestamp(9) == null && rs.wasNull());
237: check(rs.getDouble(10) == 0.0 && rs.wasNull());
238: check(rs.getFloat(11) == 0.0 && rs.wasNull());
239: check(rs.getLong(12) == 0 && rs.wasNull());
240: check(rs.getObject(13) == null && rs.wasNull());
241: check(rs.getShort(14) == 0 && rs.wasNull());
242: check(rs.getCharacterStream(15) == null && rs.wasNull());
243: check(rs.getBinaryStream(16) == null && rs.wasNull());
244:
245: rs.next();
246: check(rs.getInt(1) == 1);
247: check(rs.getString(2) == null && rs.wasNull());
248: check(rs.getBigDecimal(3) == null && rs.wasNull());
249: check(!rs.getBoolean(4) && !rs.wasNull());
250: check(rs.getByte(5) == 0 && !rs.wasNull());
251: check(rs.getBytes(6) == null && rs.wasNull());
252: check(rs.getDate(7) == null && rs.wasNull());
253: check(rs.getTime(8) == null && rs.wasNull());
254: check(rs.getTimestamp(9) == null && rs.wasNull());
255: check(rs.getDouble(10) == 0.0 && !rs.wasNull());
256: check(rs.getFloat(11) == 0.0 && !rs.wasNull());
257: check(rs.getLong(12) == 0 && !rs.wasNull());
258: check(rs.getObject(13) == null && rs.wasNull());
259: check(rs.getShort(14) == 0 && !rs.wasNull());
260: check(rs.getString(15), "test");
261: check(rs.getBytes(16), new byte[] { (byte) 0xff, 0x00 });
262:
263: rs.next();
264: check(rs.getInt(1) == 2);
265: check(rs.getString(2), "+");
266: check(rs.getBigDecimal(3).toString(), "1.20");
267: check(rs.getBoolean(4));
268: check((rs.getByte(5) & 0xff) == 0xff);
269: check(rs.getBytes(6), new byte[] { 0x00, (byte) 0xff });
270: check(rs.getDate(7).toString(), "2005-09-21");
271: check(rs.getTime(8).toString(), "21:46:28");
272: check(rs.getTimestamp(9).toString(),
273: "2005-09-21 21:47:09.567890123");
274: check(rs.getDouble(10) == 1.725);
275: check(rs.getFloat(11) == (float) 2.5);
276: check(rs.getLong(12) == Long.MAX_VALUE);
277: check(((Integer) rs.getObject(13)).intValue(), 10);
278: check(rs.getShort(14) == Short.MIN_VALUE);
279: check(rs.getString(15), "\u00ef\u00f6\u00fc"); // auml ouml uuml
280: check(rs.getBytes(16), new byte[] { (byte) 0xab, 0x12 });
281:
282: checkFalse(rs.next());
283: stat.execute("DROP TABLE TEST");
284: conn.close();
285: }
286:
287: private void testUpdateDeleteInsert() throws Exception {
288: deleteDb("updatableResultSet");
289: Connection c1 = getConnection("updatableResultSet");
290: Connection c2 = getConnection("updatableResultSet");
291: Statement stat = c1.createStatement();
292: stat.execute("DROP TABLE IF EXISTS TEST");
293: stat
294: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
295: int max = 8;
296: for (int i = 0; i < max; i++) {
297: stat.execute("INSERT INTO TEST VALUES(" + i + ", 'Hello"
298: + i + "')");
299: }
300: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
301: int j = max;
302: while (rs.next()) {
303: int id = rs.getInt(1);
304: if (id % 2 == 0) {
305: Statement s2 = c2.createStatement();
306: s2
307: .execute("UPDATE TEST SET NAME = NAME || '+' WHERE ID = "
308: + rs.getInt(1));
309: if (id % 4 == 0) {
310: rs.refreshRow();
311: }
312: rs.updateString(2, "Updated " + rs.getString(2));
313: rs.updateRow();
314: } else {
315: rs.deleteRow();
316: }
317: rs.moveToInsertRow();
318: rs.updateString(2, "Inserted " + j);
319: rs.updateInt(1, j);
320: j += 2;
321: rs.insertRow();
322: }
323: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
324: while (rs.next()) {
325: int id = rs.getInt(1);
326: String name = rs.getString(2);
327: check(id % 2, 0);
328: if (id >= max) {
329: check("Inserted " + id, rs.getString(2));
330: } else {
331: if (id % 4 == 0) {
332: check(rs.getString(2), "Updated Hello" + id + "+");
333: } else {
334: check(rs.getString(2), "Updated Hello" + id);
335: }
336: }
337: trace("id=" + id + " name=" + name);
338: }
339: c2.close();
340: c1.close();
341:
342: // test scrollable result sets
343: Connection conn = getConnection("updatableResultSet");
344: for (int i = 0; i < 5; i++) {
345: testScrollable(conn, i);
346: }
347: conn.close();
348: }
349:
350: void testScrollable(Connection conn, int rows) throws Exception {
351: Statement stat = conn.createStatement();
352: stat
353: .execute("CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
354: stat.execute("DELETE FROM TEST");
355: PreparedStatement prep = conn
356: .prepareStatement("INSERT INTO TEST VALUES(?, ?)");
357: for (int i = 0; i < rows; i++) {
358: prep.setInt(1, i);
359: prep.setString(2, "Data " + i);
360: prep.execute();
361: }
362: Statement regular = conn.createStatement();
363: testScrollResultSet(regular, ResultSet.TYPE_FORWARD_ONLY, rows);
364: Statement scroll = conn.createStatement(
365: ResultSet.TYPE_SCROLL_INSENSITIVE,
366: ResultSet.CONCUR_READ_ONLY);
367: testScrollResultSet(scroll, ResultSet.TYPE_SCROLL_INSENSITIVE,
368: rows);
369: }
370:
371: void testScrollResultSet(Statement stat, int type, int rows)
372: throws Exception {
373: boolean error = false;
374: if (type == ResultSet.TYPE_FORWARD_ONLY) {
375: error = true;
376: }
377: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
378: check(rs.getType(), type);
379:
380: checkState(rs, true, false, false, rows == 0);
381: for (int i = 0; i < rows; i++) {
382: rs.next();
383: checkState(rs, rows == 0, i == 0, i == rows - 1,
384: (rows == 0 || i == rows));
385: }
386: try {
387: rs.beforeFirst();
388: checkState(rs, true, false, false, rows == 0);
389: } catch (SQLException e) {
390: if (!error) {
391: throw e;
392: }
393: }
394: try {
395: rs.afterLast();
396: checkState(rs, false, false, false, true);
397: } catch (SQLException e) {
398: if (!error) {
399: throw e;
400: }
401: }
402: try {
403: boolean valid = rs.first();
404: check(valid, rows > 0);
405: if (valid) {
406: checkState(rs, false, true, rows == 1, rows == 0);
407: }
408: } catch (SQLException e) {
409: if (!error) {
410: throw e;
411: }
412: }
413: try {
414: boolean valid = rs.last();
415: check(valid, rows > 0);
416: if (valid) {
417: checkState(rs, false, rows == 1, true, rows == 0);
418: }
419: } catch (SQLException e) {
420: if (!error) {
421: throw e;
422: }
423: }
424: }
425:
426: void checkState(ResultSet rs, boolean beforeFirst, boolean first,
427: boolean last, boolean afterLast) throws Exception {
428: check(rs.isBeforeFirst(), beforeFirst);
429: check(rs.isFirst(), first);
430: check(rs.isLast(), last);
431: check(rs.isAfterLast(), afterLast);
432: }
433:
434: }
|