@Test
public void retainKeysInBatch() throws SQLException {
stat.executeUpdate("create table test (c1, c2);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
prep.setInt(1, 10);
prep.setString(2, "ten");
prep.addBatch();
prep.setInt(1, 100);
prep.setString(2, "hundred");
prep.addBatch();
prep.setString(2, "one hundred");
prep.addBatch();
prep.setInt(1, 1000);
prep.setString(2, "thousand");
prep.addBatch();
prep.executeBatch();
prep.close();
ResultSet rs = stat.executeQuery("select * from test;");
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
assertEquals("ten", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("one hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
assertEquals("thousand", rs.getString(2));
assertFalse(rs.next());
rs.close();
}
@Test
public void set() throws SQLException {
ResultSet rs;
PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;");
// integers
prep.setInt(1, Integer.MIN_VALUE);
prep.setInt(2, Integer.MAX_VALUE);
prep.setInt(3, 0);
rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, rs.getInt(2));
assertEquals(0, rs.getInt(3));
// strings
String name = "Winston Leonard Churchill";
String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25);
prep.clearParameters();
prep.setString(1, fn);
prep.setString(2, mn);
prep.setString(3, sn);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(fn, rs.getString(1));
assertEquals(mn, rs.getString(2));
assertEquals(sn, rs.getString(3));
// mixed
prep.setString(1, name);
prep.setString(2, null);
prep.setLong(3, Long.MAX_VALUE);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(name, rs.getString(1));
assertNull(rs.getString(2));
assertTrue(rs.wasNull());
assertEquals(Long.MAX_VALUE, rs.getLong(3));
// bytes
prep.setBytes(1, b1);
prep.setBytes(2, b2);
prep.setBytes(3, b3);
prep.executeQuery();
assertTrue(rs.next());
assertArrayEq(rs.getBytes(1), b1);
assertArrayEq(rs.getBytes(2), b2);
assertArrayEq(rs.getBytes(3), b3);
assertFalse(rs.next());
rs.close();
}
@Test
public void batch() throws SQLException {
ResultSet rs;
stat.executeUpdate("create table test (c1, c2, c3, c4);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.setFloat(2, Float.MIN_VALUE + i);
prep.setString(3, "Hello " + i);
prep.setDouble(4, Double.MAX_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
assertEquals(0, prep.executeBatch().length);
prep.close();
rs = stat.executeQuery("select * from test;");
for (int i = 0; i < 10; i++) {
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
assertEquals("Hello " + i, rs.getString(3));
assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
}
rs.close();
stat.executeUpdate("drop table test;");
}
/**
* aggregation - group by
*
* @throws Exception
*/
@Test
public void testDateGroupBy() throws Exception {
printTestDescription();
Date date = new Date(1);
initATableValues(date);
try {
ResultSet rs;
String query = null;
if (tgtPH())
query =
"SELECT a_date, count(1) FROM atable WHERE organization_id='"
+ tenantId
+ "' group by a_date";
else if (tgtSQ() || tgtTR())
query =
"SELECT a_date, count(1) FROM atable WHERE organization_id='"
+ tenantId
+ "' group by a_date order by 1";
rs = getResultSet(conn, query);
/* 3 rows in expected result:
* 1969-12-31 3
* 1970-01-01 3
* 1970-01-02 3
* */
assertTrue(rs.next());
if (tgtPH()) assertEquals(date, rs.getDate(1));
else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
assertEquals(3, rs.getInt(2));
// the following assertions fails
assertTrue(rs.next());
assertEquals(3, rs.getInt(2));
assertTrue(rs.next());
assertEquals(3, rs.getInt(2));
assertFalse(rs.next());
} finally {
}
}
@Test
public void setmaxrows() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
prep.setMaxRows(1);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
prep.close();
}
@Test
public void singleRowRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ?;");
prep.setInt(1, Integer.MAX_VALUE);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1));
assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1));
assertFalse(rs.next());
rs.close();
prep.close();
}
@Test
public void reusingSetValues() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ?,?;");
prep.setInt(1, 9);
for (int i = 0; i < 10; i++) {
prep.setInt(2, i);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(9, rs.getInt(1));
assertEquals(i, rs.getInt(2));
}
for (int i = 0; i < 10; i++) {
prep.setInt(2, i);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(9, rs.getInt(1));
assertEquals(i, rs.getInt(2));
rs.close();
}
prep.close();
}
@Test
public void batchOneParam() throws SQLException {
stat.executeUpdate("create table test (c1);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
prep.close();
ResultSet rs = stat.executeQuery("select count(*) from test;");
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
rs.close();
}
@Test
public void insert1000() throws SQLException {
stat.executeUpdate("create table in1000 (a);");
PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);");
conn.setAutoCommit(false);
for (int i = 0; i < 1000; i++) {
prep.setInt(1, i);
prep.executeUpdate();
}
conn.commit();
ResultSet rs = stat.executeQuery("select count(a) from in1000;");
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
rs.close();
}
@Test
public void update() throws SQLException {
assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0);
PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);");
prep.setInt(1, 3);
assertEquals(1, prep.executeUpdate());
prep.setInt(1, 5);
assertEquals(1, prep.executeUpdate());
prep.setInt(1, 7);
assertEquals(1, prep.executeUpdate());
prep.close();
// check results with normal statement
ResultSet rs = stat.executeQuery("select sum(c1) from s1;");
assertTrue(rs.next());
assertEquals(15, rs.getInt(1));
rs.close();
}
@Ignore
@Test
public void getObject() throws SQLException {
stat.executeUpdate(
"create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");
prep.setInt(1, Integer.MAX_VALUE);
prep.setFloat(2, Float.MAX_VALUE);
prep.setDouble(3, Double.MAX_VALUE);
prep.setLong(4, Long.MAX_VALUE);
prep.setBoolean(5, false);
prep.setByte(6, (byte) 7);
prep.setBytes(7, b1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
assertEquals(Float.MAX_VALUE, rs.getFloat(2));
assertEquals(Double.MAX_VALUE, rs.getDouble(3));
assertEquals(Long.MAX_VALUE, rs.getLong(4));
assertFalse(rs.getBoolean(5));
assertEquals((byte) 7, rs.getByte(6));
assertArrayEq(rs.getBytes(7), b1);
assertNotNull(rs.getObject(1));
assertNotNull(rs.getObject(2));
assertNotNull(rs.getObject(3));
assertNotNull(rs.getObject(4));
assertNotNull(rs.getObject(5));
assertNotNull(rs.getObject(6));
assertNotNull(rs.getObject(7));
assertTrue(rs.getObject(1) instanceof Integer);
assertTrue(rs.getObject(2) instanceof Double);
assertTrue(rs.getObject(3) instanceof Double);
assertTrue(rs.getObject(4) instanceof String);
assertTrue(rs.getObject(5) instanceof Integer);
assertTrue(rs.getObject(6) instanceof Integer);
assertTrue(rs.getObject(7) instanceof byte[]);
rs.close();
}
@Test(expected = SQLException.class)
public void noSuchColName() throws SQLException {
ResultSet rs = conn.prepareStatement("select 1;").executeQuery();
assertTrue(rs.next());
rs.getInt("noSuchColName");
}