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

Example 86 with Statement

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

the class PreparedStatementTestCase method testCasewhen.

private void testCasewhen(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
    PreparedStatement prep;
    ResultSet rs;
    prep = conn.prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST " + "WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    rs.next();
    String plan = rs.getString(1);
    trace(plan);
    rs.close();
    prep = conn.prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST " + "WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID " + "ELSE ID END=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    rs.next();
    plan = rs.getString(1);
    trace(plan);
    prep = conn.prepareStatement("SELECT COUNT(*) FROM TEST " + "WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("SELECT COUNT(*) FROM TEST " + "WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID " + "ELSE ID END=? GROUP BY ID");
    prep.setInt(1, 1);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("SELECT * FROM TEST WHERE ? IS NULL");
    prep.setString(1, "Hello");
    rs = prep.executeQuery();
    assertFalse(rs.next());
    assertThrows(ErrorCode.UNKNOWN_DATA_TYPE_1, conn).prepareStatement("select ? from dual union select ? from dual");
    prep = conn.prepareStatement("select cast(? as varchar) " + "from dual union select ? from dual");
    assertEquals(2, prep.getParameterMetaData().getParameterCount());
    prep.setString(1, "a");
    prep.setString(2, "a");
    rs = prep.executeQuery();
    rs.next();
    assertEquals("a", rs.getString(1));
    assertEquals("a", rs.getString(1));
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
}

Example 87 with Statement

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

the class PreparedStatementTestCase method testPrepareRecompile.

private void testPrepareRecompile(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;
    ResultSet rs;
    prep = conn.prepareStatement("SELECT COUNT(*) " + "FROM DUAL WHERE ? IS NULL");
    prep.setString(1, null);
    prep.executeQuery();
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("DROP TABLE TEST");
    prep.setString(1, null);
    prep.executeQuery();
    prep.setString(1, "X");
    rs = prep.executeQuery();
    rs.next();
    assertEquals(0, rs.getInt(1));
    stat.execute("CREATE TABLE t1 (c1 INT, c2 VARCHAR(10))");
    stat.execute("INSERT INTO t1 SELECT X, CONCAT('Test', X)  " + "FROM SYSTEM_RANGE(1, 5);");
    prep = conn.prepareStatement("SELECT c1, c2 FROM t1 WHERE c1 = ?");
    prep.setInt(1, 1);
    prep.executeQuery();
    stat.execute("CREATE TABLE t2 (x int PRIMARY KEY)");
    prep.setInt(1, 2);
    rs = prep.executeQuery();
    rs.next();
    assertEquals(2, rs.getInt(1));
    prep.setInt(1, 3);
    rs = prep.executeQuery();
    rs.next();
    assertEquals(3, rs.getInt(1));
    stat.execute("DROP TABLE t1, t2");
}

Example 88 with Statement

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

the class PreparedStatementTestCase method testColumnMetaDataWithEquals.

private void testColumnMetaDataWithEquals(Connection conn) throws SQLException {
    Statement stmt = conn.createStatement();
    stmt.execute("CREATE TABLE TEST( id INT, someColumn INT )");
    PreparedStatement ps = conn.prepareStatement("INSERT INTO TEST VALUES(?,?)");
    ps.setInt(1, 0);
    ps.setInt(2, 999);
    ps.execute();
    ps = conn.prepareStatement("SELECT * FROM TEST WHERE someColumn = ?");
    assertEquals(Types.INTEGER, ps.getParameterMetaData().getParameterType(1));
    stmt.execute("DROP TABLE TEST");
}

Example 89 with Statement

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

the class PreparedStatementTestCase method testUUIDGeneratedKeys.

private void testUUIDGeneratedKeys(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST_UUID(id UUID DEFAULT " + "random_UUID() PRIMARY KEY)");
    stat.execute("INSERT INTO TEST_UUID() VALUES()");
    ResultSet rs = stat.getGeneratedKeys();
    rs.next();
    byte[] data = rs.getBytes(1);
    assertEquals(16, data.length);
    stat.execute("INSERT INTO TEST_UUID VALUES(random_UUID())");
    rs = stat.getGeneratedKeys();
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST_UUID");
}

Example 90 with Statement

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

the class ManyJdbcObjectsTestCase method testOneConnectionPrepare.

private void testOneConnectionPrepare() throws SQLException {
    Connection conn = getConnection();
    PreparedStatement prep;
    Statement stat;
    int size = 1000;
    for (int i = 0; i < size; i++) {
        conn.getMetaData();
    }
    for (int i = 0; i < size; i++) {
        conn.createStatement();
    }
    stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    for (int i = 0; i < size; i++) {
        stat.executeQuery("SELECT * FROM TEST WHERE 1=0");
    }
    for (int i = 0; i < size; i++) {
        stat.executeQuery("SELECT * FROM TEST");
    }
    for (int i = 0; i < size; i++) {
        conn.prepareStatement("SELECT * FROM TEST");
    }
    prep = conn.prepareStatement("SELECT * FROM TEST WHERE 1=0");
    for (int i = 0; i < size; i++) {
        prep.executeQuery();
    }
    prep = conn.prepareStatement("SELECT * FROM TEST");
    for (int i = 0; i < size; i++) {
        prep.executeQuery();
    }
    conn.close();
}