Examples with Insert org.h2.command.dml.Insert used on opensource projects

Example 81 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestGetGeneratedKeys method testCalledSequences.

/**
 * Test for keys generated by sequences.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testCalledSequences(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE SEQUENCE SEQ");
    stat.execute("CREATE TABLE TEST(ID INT)");
    PreparedStatement prep;
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
    prep.execute();
    ResultSet rs = prep.getGeneratedKeys();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
    prep.execute();
    rs = prep.getGeneratedKeys();
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[] { 1 });
    prep.execute();
    rs = prep.getGeneratedKeys();
    rs.next();
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String[] { "ID" });
    prep.execute();
    rs = prep.getGeneratedKeys();
    rs.next();
    assertEquals(4, rs.getInt(1));
    assertFalse(rs.next());
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    prep.execute();
    rs = prep.getGeneratedKeys();
    rs.next();
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
    stat.execute("DROP SEQUENCE SEQ");
    stat.execute("CREATE TABLE TEST(ID BIGINT)");
    stat.execute("CREATE SEQUENCE SEQ");
    prep = conn.prepareStatement("INSERT INTO TEST VALUES (30), (NEXT VALUE FOR SEQ)," + " (NEXT VALUE FOR SEQ), (NEXT VALUE FOR SEQ), (20)", Statement.RETURN_GENERATED_KEYS);
    prep.executeUpdate();
    rs = prep.getGeneratedKeys();
    rs.next();
    assertEquals(1L, rs.getLong(1));
    rs.next();
    assertEquals(2L, rs.getLong(1));
    rs.next();
    assertEquals(3L, rs.getLong(1));
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
    stat.execute("DROP SEQUENCE SEQ");
}

Example 82 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestGetGeneratedKeys method testPrepareStatement_intArray_ExecuteBatch.

/**
 * Test method for
 * {@link Connection#prepareStatement(String, int[])}
 * .{@link PreparedStatement#executeBatch()}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testPrepareStatement_intArray_ExecuteBatch(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + "UID UUID NOT NULL DEFAULT RANDOM_UUID(), VALUE INT NOT NULL)");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (10)", new int[0]);
    prep.addBatch();
    prep.addBatch();
    prep.executeBatch();
    ResultSet rs = prep.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    prep = conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (20)", new int[] { 1, 2 });
    prep.addBatch();
    prep.addBatch();
    prep.executeBatch();
    rs = prep.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("ID", rs.getMetaData().getColumnName(1));
    assertEquals("UID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(3L, rs.getLong(1));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertTrue(rs.next());
    assertEquals(4L, rs.getLong(1));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertFalse(rs.next());
    rs.close();
    prep = conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (30)", new int[] { 2, 1 });
    prep.addBatch();
    prep.addBatch();
    prep.executeBatch();
    rs = prep.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertEquals("ID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertEquals(5L, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertEquals(6L, rs.getLong(2));
    assertFalse(rs.next());
    rs.close();
    prep = conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (40)", new int[] { 2 });
    prep.addBatch();
    prep.addBatch();
    prep.executeBatch();
    rs = prep.getGeneratedKeys();
    assertEquals(1, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}

Example 83 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestGetGeneratedKeys method testPrepareStatement_int_ExecuteLargeUpdate.

/**
 * Test method for
 * {@link Connection#prepareStatement(String, int)}
 * .{@link PreparedStatement#executeLargeUpdate()}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testPrepareStatement_int_ExecuteLargeUpdate(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + "UID UUID NOT NULL DEFAULT RANDOM_UUID(), VALUE INT NOT NULL, OTHER INT DEFAULT 0)");
    JdbcPreparedStatement prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (10)", Statement.NO_GENERATED_KEYS);
    prep.executeLargeUpdate();
    ResultSet rs = prep.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (20)", Statement.RETURN_GENERATED_KEYS);
    prep.executeLargeUpdate();
    rs = prep.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("ID", rs.getMetaData().getColumnName(1));
    assertEquals("UID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(2L, rs.getLong(1));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}

Example 84 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestGetGeneratedKeys method testMultithreaded.

/**
 * Test method for shared connection between several statements in different
 * threads.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testMultithreaded(final Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + "VALUE INT NOT NULL)");
    final int count = 4, iterations = 10_000;
    Thread[] threads = new Thread[count];
    final long[] keys = new long[count * iterations];
    for (int i = 0; i < count; i++) {
        final int num = i;
        threads[num] = new Thread("getGeneratedKeys-" + num) {

            @Override
            public void run() {
                try {
                    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
                    for (int i = 0; i < iterations; i++) {
                        int value = iterations * num + i;
                        prep.setInt(1, value);
                        prep.execute();
                        ResultSet rs = prep.getGeneratedKeys();
                        rs.next();
                        keys[value] = rs.getLong(1);
                        rs.close();
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        };
    }
    for (int i = 0; i < count; i++) {
        threads[i].start();
    }
    for (int i = 0; i < count; i++) {
        threads[i].join();
    }
    ResultSet rs = stat.executeQuery("SELECT VALUE, ID FROM TEST ORDER BY VALUE");
    for (int i = 0; i < keys.length; i++) {
        assertTrue(rs.next());
        assertEquals(i, rs.getInt(1));
        assertEquals(keys[i], rs.getLong(2));
    }
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}

Example 85 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestGetGeneratedKeys method testStatementExecuteUpdate_intArray.

/**
 * Test method for {@link Statement#executeUpdate(String, int[])}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testStatementExecuteUpdate_intArray(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + "UID UUID NOT NULL DEFAULT RANDOM_UUID(), VALUE INT NOT NULL)");
    stat.executeUpdate("INSERT INTO TEST(VALUE) VALUES (10)", new int[0]);
    ResultSet rs = stat.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    stat.executeUpdate("INSERT INTO TEST(VALUE) VALUES (20)", new int[] { 1, 2 });
    rs = stat.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("ID", rs.getMetaData().getColumnName(1));
    assertEquals("UID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(2L, rs.getLong(1));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.executeUpdate("INSERT INTO TEST(VALUE) VALUES (30)", new int[] { 2, 1 });
    rs = stat.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertEquals("ID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertEquals(3L, rs.getLong(2));
    assertFalse(rs.next());
    rs.close();
    stat.executeUpdate("INSERT INTO TEST(VALUE) VALUES (40)", new int[] { 2 });
    rs = stat.getGeneratedKeys();
    assertEquals(1, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}