Examples with Statement java.sql.Statement used on opensource projects

Example 76 with Statement

use of java.sql.Statement in project jdbc-shards by wplatform.

the class UpdatableResultSetTestCase method testUpdateLob.

private void testUpdateLob() throws SQLException {
    Connection conn = getConnection();
    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();
}

Example 77 with Statement

use of java.sql.Statement in project jdbc-shards by wplatform.

the class UpdatableResultSetTestCase method testScroll.

private void testScroll() throws SQLException {
    Connection conn = getConnection();
    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();
}

Example 78 with Statement

use of java.sql.Statement in project jdbc-shards by wplatform.

the class PreparedStatementTestCase method testUUID.

private void testUUID(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("create table test_uuid(id uuid primary key)");
    UUID uuid = new UUID(-2, -1);
    PreparedStatement prep = conn.prepareStatement("insert into test_uuid values(?)");
    prep.setObject(1, uuid);
    prep.execute();
    ResultSet rs = stat.executeQuery("select * from test_uuid");
    rs.next();
    assertEquals("ffffffff-ffff-fffe-ffff-ffffffffffff", rs.getString(1));
    Object o = rs.getObject(1);
    assertEquals("java.util.UUID", o.getClass().getName());
    stat.execute("drop table test_uuid");
}

Example 79 with Statement

use of java.sql.Statement in project jdbc-shards by wplatform.

the class PreparedStatementTestCase method testTempView.

private void testTempView(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;
    stat.execute("CREATE TABLE TEST(FIELD INT PRIMARY KEY)");
    stat.execute("INSERT INTO TEST VALUES(1)");
    stat.execute("INSERT INTO TEST VALUES(2)");
    prep = conn.prepareStatement("select FIELD FROM " + "(select FIELD FROM (SELECT FIELD  FROM TEST " + "WHERE FIELD = ?) AS T2 " + "WHERE T2.FIELD = ?) AS T3 WHERE T3.FIELD = ?");
    prep.setInt(1, 1);
    prep.setInt(2, 1);
    prep.setInt(3, 1);
    ResultSet rs = prep.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    prep.setInt(1, 2);
    prep.setInt(2, 2);
    prep.setInt(3, 2);
    rs = prep.executeQuery();
    rs.next();
    assertEquals(2, rs.getInt(1));
    stat.execute("DROP TABLE TEST");
}

Example 80 with Statement

use of java.sql.Statement in project jdbc-shards by wplatform.

the class PreparedStatementTestCase method testLikeIndex.

private void testLikeIndex(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    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')");
    stat.execute("create index idxname on test(name);");
    PreparedStatement prep, prepExe;
    prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
    assertEquals(1, prep.getParameterMetaData().getParameterCount());
    prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
    prep.setString(1, "%orld");
    prepExe.setString(1, "%orld");
    ResultSet rs = prep.executeQuery();
    rs.next();
    String plan = rs.getString(1);
    assertTrue(plan.contains(".tableScan"));
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());
    prep.setString(1, "H%");
    prepExe.setString(1, "H%");
    rs = prep.executeQuery();
    rs.next();
    String plan1 = rs.getString(1);
    assertTrue(plan1.contains("IDXNAME"));
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("DROP TABLE IF EXISTS TEST");
}