/*
* Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.test.jdbc;
import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import org.h2.constant.ErrorCode;
import org.h2.constant.SysProperties;
import org.h2.test.TestBase;
/**
* Updatable result set tests.
*/
public class TestUpdatableResultSet extends TestBase {
/**
* Run just this test.
*
* @param a ignored
*/
public static void main(String... a) throws Exception {
TestBase.createCaller().init().test();
}
public void test() throws SQLException {
testDetectUpdatable();
testUpdateLob();
testScroll();
testUpdateDeleteInsert();
testUpdateDataType();
testUpdateResetRead();
deleteDb("updatableResultSet");
}
private void testDetectUpdatable() throws SQLException {
deleteDb("updatableResultSet");
Connection conn = getConnection("updatableResultSet");
Statement stat;
ResultSet rs;
stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stat.execute("create table test(id int primary key, name varchar)");
rs = stat.executeQuery("select * from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
stat.execute("drop table test");
stat.execute("create table test(a int, b int, name varchar, primary key(a, b))");
rs = stat.executeQuery("select * from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select a, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select b, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select b, name, a from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
stat.execute("drop table test");
stat.execute("create table test(a int, b int, name varchar)");
stat.execute("create unique index on test(b, a)");
rs = stat.executeQuery("select * from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select a, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select b, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select b, name, a from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
stat.execute("drop table test");
stat.execute("create table test(a int, b int, c int unique, name varchar, primary key(a, b))");
rs = stat.executeQuery("select * from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select a, name, c from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select b, a, name, c from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
stat.execute("drop table test");
stat.execute("create table test(id int primary key, a int, b int, i int, j int, k int, name varchar)");
stat.execute("create unique index on test(b, a)");
stat.execute("create unique index on test(i, j)");
stat.execute("create unique index on test(a, j)");
rs = stat.executeQuery("select * from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select a, name, b from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select a, name, b from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
rs = stat.executeQuery("select i, b, k, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select a, i, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select b, i, k, name from test");
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
rs = stat.executeQuery("select a, k, j, name from test");
assertEquals(ResultSet.CONCUR_UPDATABLE, rs.getConcurrency());
stat.execute("drop table test");
conn.close();
}
private void testUpdateLob() throws SQLException {
deleteDb("updatableResultSet");
Connection conn = getConnection("updatableResultSet");
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE object_index (id integer primary key, object other, number integer)");
PreparedStatement prep = conn.prepareStatement("INSERT INTO object_index (id,object) VALUES (1,?)");
prep.setObject(1, "hello", Types.JAVA_OBJECT);
prep.execute();
ResultSet rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
rs.updateInt(2, 1);
rs.updateRow();
rs.close();
stat = conn.createStatement();
rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
conn.close();
}
private void testUpdateResetRead() throws SQLException {
deleteDb("updatableResultSet");
Connection conn = getConnection("updatableResultSet");
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
stat.execute("INSERT INTO TEST VALUES(2, 'World')");
ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
rs.next();
rs.updateInt(1, 10);
rs.updateRow();
rs.next();
rs.updateString(2, "Welt");
rs.updateRow();
rs.beforeFirst();
rs.next();
assertEquals(10, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
rs.next();
assertEquals(2, rs.getInt(1));
assertEquals("Welt", rs.getString(2));
conn.close();
}
private void testScroll() throws SQLException {
deleteDb("updatableResultSet");
Connection conn = getConnection("updatableResultSet");
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'Test')");
ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
assertTrue(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
assertEquals(0, rs.getRow());
rs.next();
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
assertEquals(1, rs.getInt(1));
assertEquals(1, rs.getRow());
rs.next();
assertThrows(ErrorCode.RESULT_SET_READONLY, rs).insertRow();
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
assertEquals(2, rs.getInt(1));
assertEquals(2, rs.getRow());
rs.next();
assertFalse(rs.isBeforeFirst());
assertFalse(rs.isAfterLast());
assertEquals(3, rs.getInt(1));
assertEquals(3, rs.getRow());
assertFalse(rs.next());
assertFalse(rs.isBeforeFirst());
assertTrue(rs.isAfterLast());
assertEquals(0, rs.getRow());
assertTrue(rs.first());
assertEquals(1, rs.getInt(1));
assertEquals(1, rs.getRow());
assertTrue(rs.last());
assertEquals(3, rs.getInt(1));
assertEquals(3, rs.getRow());
assertTrue(rs.relative(0));
assertEquals(3, rs.getRow());
assertTrue(rs.relative(-1));
assertEquals(2, rs.getRow());
assertTrue(rs.relative(1));
assertEquals(3, rs.getRow());
assertFalse(rs.relative(100));
assertTrue(rs.isAfterLast());
assertFalse(rs.absolute(0));
assertEquals(0, rs.getRow());
assertTrue(rs.absolute(1));
assertEquals(1, rs.getRow());
assertTrue(rs.absolute(2));
assertEquals(2, rs.getRow());
assertTrue(rs.absolute(3));
assertEquals(3, rs.getRow());
assertFalse(rs.absolute(4));
assertEquals(0, rs.getRow());
// allowed for compatibility
assertFalse(rs.absolute(0));
assertTrue(rs.absolute(3));
assertEquals(3, rs.getRow());
assertTrue(rs.absolute(-1));
assertEquals(3, rs.getRow());
assertFalse(rs.absolute(4));
assertTrue(rs.isAfterLast());
assertFalse(rs.absolute(5));
assertTrue(rs.isAfterLast());
assertTrue(rs.previous());
assertEquals(3, rs.getRow());
assertTrue(rs.previous());
assertEquals(2, rs.getRow());
conn.close();
}
private void testUpdateDataType() throws SQLException {
deleteDb("updatableResultSet");
Connection conn = getConnection("updatableResultSet");
Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), "
+ "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), "
+ "D DATE, T TIME, TS TIMESTAMP, DB DOUBLE, R REAL, L BIGINT, "
+ "O_I INT, SH SMALLINT, CL CLOB, BL BLOB)");
ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
ResultSetMetaData meta = rs.getMetaData();
assertEquals("java.lang.Integer", meta.getColumnClassName(1));
assertEquals("java.lang.String", meta.getColumnClassName(2));
assertEquals("java.math.BigDecimal", meta.getColumnClassName(3));
assertEquals("java.lang.Boolean", meta.getColumnClassName(4));
assertEquals("java.lang.Byte", meta.getColumnClassName(5));
assertEquals("[B", meta.getColumnClassName(6));
assertEquals("java.sql.Date", meta.getColumnClassName(7));
assertEquals("java.sql.Time", meta.getColumnClassName(8));
assertEquals("java.sql.Timestamp", meta.getColumnClassName(9));
assertEquals("java.lang.Double", meta.getColumnClassName(10));
assertEquals("java.lang.Float", meta.getColumnClassName(11));
assertEquals("java.lang.Long", meta.getColumnClassName(12));
assertEquals("java.lang.Integer", meta.getColumnClassName(13));
assertEquals("java.lang.Short", meta.getColumnClassName(14));
if (SysProperties.RETURN_LOB_OBJECTS) {
assertEquals("java.sql.Clob", meta.getColumnClassName(15));
assertEquals("java.sql.Blob", meta.getColumnClassName(16));
} else {
assertEquals("java.io.Reader", meta.getColumnClassName(15));
assertEquals("java.io.InputStream", meta.getColumnClassName(16));
}
rs.moveToInsertRow();
rs.updateInt(1, 0);
rs.updateNull(2);
rs.updateNull("DEC");
// 'not set' values are set to null
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt(1, 1);
rs.updateString(2, null);
rs.updateBigDecimal(3, null);
rs.updateBoolean(4, false);
rs.updateByte(5, (byte) 0);
rs.updateBytes(6, null);
rs.updateDate(7, null);
rs.updateTime(8, null);
rs.updateTimestamp(9, null);
rs.updateDouble(10, 0.0);
rs.updateFloat(11, (float) 0.0);
rs.updateLong(12, 0L);
rs.updateObject(13, null);
rs.updateShort(14, (short) 0);
rs.updateCharacterStream(15, new StringReader("test"), 0);
rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] { (byte) 0xff, 0x00 }), 0);
rs.insertRow();
rs.moveToInsertRow();
rs.updateInt("ID", 2);
rs.updateString("NAME", "+");
rs.updateBigDecimal("DEC", new BigDecimal("1.2"));
rs.updateBoolean("BOO", true);
rs.updateByte("BYE", (byte) 0xff);
rs.updateBytes("BIN", new byte[] { 0x00, (byte) 0xff });
rs.updateDate("D", Date.valueOf("2005-09-21"));
rs.updateTime("T", Time.valueOf("21:46:28"));
rs.updateTimestamp("TS", Timestamp.valueOf("2005-09-21 21:47:09.567890123"));
rs.updateDouble("DB", 1.725);
rs.updateFloat("R", (float) 2.5);
rs.updateLong("L", Long.MAX_VALUE);
rs.updateObject("O_I", 10);
rs.updateShort("SH", Short.MIN_VALUE);
// auml, ouml, uuml
rs.updateCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"), 0);
rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] { (byte) 0xab, 0x12 }), 0);
rs.insertRow();
rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST");
rs.next();
assertTrue(rs.getInt(1) == 0);
assertTrue(rs.getString(2) == null && rs.wasNull());
assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
assertTrue(!rs.getBoolean(4) && rs.wasNull());
assertTrue(rs.getByte(5) == 0 && rs.wasNull());
assertTrue(rs.getBytes(6) == null && rs.wasNull());
assertTrue(rs.getDate(7) == null && rs.wasNull());
assertTrue(rs.getTime(8) == null && rs.wasNull());
assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
assertTrue(rs.getDouble(10) == 0.0 && rs.wasNull());
assertTrue(rs.getFloat(11) == 0.0 && rs.wasNull());
assertTrue(rs.getLong(12) == 0 && rs.wasNull());
assertTrue(rs.getObject(13) == null && rs.wasNull());
assertTrue(rs.getShort(14) == 0 && rs.wasNull());
assertTrue(rs.getCharacterStream(15) == null && rs.wasNull());
assertTrue(rs.getBinaryStream(16) == null && rs.wasNull());
rs.next();
assertTrue(rs.getInt(1) == 1);
assertTrue(rs.getString(2) == null && rs.wasNull());
assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
assertTrue(!rs.getBoolean(4) && !rs.wasNull());
assertTrue(rs.getByte(5) == 0 && !rs.wasNull());
assertTrue(rs.getBytes(6) == null && rs.wasNull());
assertTrue(rs.getDate(7) == null && rs.wasNull());
assertTrue(rs.getTime(8) == null && rs.wasNull());
assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
assertTrue(rs.getDouble(10) == 0.0 && !rs.wasNull());
assertTrue(rs.getFloat(11) == 0.0 && !rs.wasNull());
assertTrue(rs.getLong(12) == 0 && !rs.wasNull());
assertTrue(rs.getObject(13) == null && rs.wasNull());
assertTrue(rs.getShort(14) == 0 && !rs.wasNull());
assertEquals("test", rs.getString(15));
assertEquals(new byte[] { (byte) 0xff, 0x00 }, rs.getBytes(16));
rs.next();
assertTrue(rs.getInt(1) == 2);
assertEquals("+", rs.getString(2));
assertEquals("1.20", rs.getBigDecimal(3).toString());
assertTrue(rs.getBoolean(4));
assertTrue((rs.getByte(5) & 0xff) == 0xff);
assertEquals(new byte[] { 0x00, (byte) 0xff }, rs.getBytes(6));
assertEquals("2005-09-21", rs.getDate(7).toString());
assertEquals("21:46:28", rs.getTime(8).toString());
assertEquals("2005-09-21 21:47:09.567890123", rs.getTimestamp(9).toString());
assertTrue(rs.getDouble(10) == 1.725);
assertTrue(rs.getFloat(11) == (float) 2.5);
assertTrue(rs.getLong(12) == Long.MAX_VALUE);
assertEquals(10, ((Integer) rs.getObject(13)).intValue());
assertTrue(rs.getShort(14) == Short.MIN_VALUE);
// auml ouml uuml
assertEquals("\u00ef\u00f6\u00fc", rs.getString(15));
assertEquals(new byte[] { (byte) 0xab, 0x12 }, rs.getBytes(16));
assertFalse(rs.next());
stat.execute("DROP TABLE TEST");
conn.close();
}
private void testUpdateDeleteInsert() throws SQLException {
deleteDb("updatableResultSet");
Connection c1 = getConnection("updatableResultSet");
Connection c2 = getConnection("updatableResultSet");
Statement stat = c1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
stat.execute("DROP TABLE IF EXISTS TEST");
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
int max = 8;
for (int i = 0; i < max; i++) {
stat.execute("INSERT INTO TEST VALUES(" + i + ", 'Hello" + i + "')");
}
ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
int j = max;
while (rs.next()) {
int id = rs.getInt(1);
if (id % 2 == 0) {
Statement s2 = c2.createStatement();
s2.execute("UPDATE TEST SET NAME = NAME || '+' WHERE ID = " + rs.getInt(1));
if (id % 4 == 0) {
rs.refreshRow();
}
rs.updateString(2, "Updated " + rs.getString(2));
rs.updateRow();
} else {
rs.deleteRow();
}
rs.moveToInsertRow();
rs.updateString(2, "Inserted " + j);
rs.updateInt(1, j);
j += 2;
rs.insertRow();
}
rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
assertEquals(0, id % 2);
if (id >= max) {
assertEquals("Inserted " + id, rs.getString(2));
} else {
if (id % 4 == 0) {
assertEquals("Updated Hello" + id + "+", rs.getString(2));
} else {
assertEquals("Updated Hello" + id, rs.getString(2));
}
}
trace("id=" + id + " name=" + name);
}
c2.close();
c1.close();
// test scrollable result sets
Connection conn = getConnection("updatableResultSet");
for (int i = 0; i < 5; i++) {
testScrollable(conn, i);
}
conn.close();
}
private void testScrollable(Connection conn, int rows) throws SQLException {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("DELETE FROM TEST");
PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
for (int i = 0; i < rows; i++) {
prep.setInt(1, i);
prep.setString(2, "Data " + i);
prep.execute();
}
Statement regular = conn.createStatement();
testScrollResultSet(regular, ResultSet.TYPE_FORWARD_ONLY, rows);
Statement scroll = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
testScrollResultSet(scroll, ResultSet.TYPE_SCROLL_INSENSITIVE, rows);
}
private void testScrollResultSet(Statement stat, int type, int rows) throws SQLException {
boolean error = false;
if (type == ResultSet.TYPE_FORWARD_ONLY) {
error = true;
}
ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
assertEquals(type, rs.getType());
assertState(rs, rows > 0, false, false, false);
for (int i = 0; i < rows; i++) {
rs.next();
assertState(rs, rows == 0, i == 0, i == rows - 1, rows == 0 || i == rows);
}
try {
rs.beforeFirst();
assertState(rs, rows > 0, false, false, false);
} catch (SQLException e) {
if (!error) {
throw e;
}
}
try {
rs.afterLast();
assertState(rs, false, false, false, rows > 0);
} catch (SQLException e) {
if (!error) {
throw e;
}
}
try {
boolean valid = rs.first();
assertEquals(rows > 0, valid);
if (valid) {
assertState(rs, false, true, rows == 1, rows == 0);
}
} catch (SQLException e) {
if (!error) {
throw e;
}
}
try {
boolean valid = rs.last();
assertEquals(rows > 0, valid);
if (valid) {
assertState(rs, false, rows == 1, true, rows == 0);
}
} catch (SQLException e) {
if (!error) {
throw e;
}
}
}
private void assertState(ResultSet rs, boolean beforeFirst,
boolean first, boolean last, boolean afterLast) throws SQLException {
assertEquals(beforeFirst, rs.isBeforeFirst());
assertEquals(first, rs.isFirst());
assertEquals(last, rs.isLast());
assertEquals(afterLast, rs.isAfterLast());
}
}