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

Example 91 with Insert

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

the class TestGetGeneratedKeys method testPrepareStatement_int_ExecuteLargeBatch.

/**
 * Test method for {@link Connection#prepareStatement(String, int)}
 * .{@link PreparedStatement#executeLargeBatch()}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testPrepareStatement_int_ExecuteLargeBatch(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.addBatch();
    prep.addBatch();
    prep.executeLargeBatch();
    ResultSet rs = prep.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (20)", Statement.RETURN_GENERATED_KEYS);
    prep.addBatch();
    prep.addBatch();
    prep.executeLargeBatch();
    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(3L, rs.getLong("ID"));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertEquals(UUID.class, rs.getObject("UID").getClass());
    assertEquals(UUID.class, rs.getObject("UID", UUID.class).getClass());
    assertTrue(rs.next());
    assertEquals(4L, rs.getLong(1));
    assertEquals(4L, rs.getLong("ID"));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertEquals(UUID.class, rs.getObject("UID").getClass());
    assertEquals(UUID.class, rs.getObject("UID", UUID.class).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}

Example 92 with Insert

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

the class TestGetGeneratedKeys method testPrepareStatement_intArray_ExecuteLargeUpdate.

/**
 * Test method for
 * {@link Connection#prepareStatement(String, int[])}
 * .{@link PreparedStatement#executeLargeUpdate()}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testPrepareStatement_intArray_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)");
    JdbcPreparedStatement prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (10)", new int[0]);
    prep.executeLargeUpdate();
    ResultSet rs = prep.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (20)", new int[] { 1, 2 });
    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();
    prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (30)", new int[] { 2, 1 });
    prep.executeLargeUpdate();
    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(3L, rs.getLong(2));
    assertFalse(rs.next());
    rs.close();
    prep = (JdbcPreparedStatement) conn.prepareStatement("INSERT INTO TEST(VALUE) VALUES (40)", new int[] { 2 });
    prep.executeLargeUpdate();
    rs = prep.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");
}

Example 93 with Insert

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

the class TestGetGeneratedKeys method testPrepareStatement_StringArray_ExecuteBatch.

/**
 * Test method for
 * {@link Connection#prepareStatement(String, String[])}
 * .{@link PreparedStatement#executeBatch()}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testPrepareStatement_StringArray_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 String[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 String[] { "ID", "UID" });
    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 String[] { "UID", "ID" });
    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 String[] { "UID" });
    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 94 with Insert

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

the class TestGetGeneratedKeys method testStatementExecuteUpdate_StringArray.

/**
 * Test method for {@link Statement#executeUpdate(String, String[])}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testStatementExecuteUpdate_StringArray(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 String[0]);
    ResultSet rs = stat.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    stat.executeUpdate("INSERT INTO TEST(VALUE) VALUES (20)", new String[] { "ID", "UID" });
    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 String[] { "UID", "ID" });
    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 String[] { "UID" });
    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");
}

Example 95 with Insert

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

the class TestGetGeneratedKeys method testMergeUsing.

/**
 * Test method for MERGE USING operator.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testMergeUsing(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE SOURCE (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + " UID INT NOT NULL UNIQUE, VALUE INT NOT NULL)");
    stat.execute("CREATE TABLE DESTINATION (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + " UID INT NOT NULL UNIQUE, VALUE INT NOT NULL)");
    PreparedStatement ps = conn.prepareStatement("INSERT INTO SOURCE(UID, VALUE) VALUES (?, ?)");
    for (int i = 1; i <= 100; i++) {
        ps.setInt(1, i);
        ps.setInt(2, i * 10 + 5);
        ps.executeUpdate();
    }
    // Insert first half of a rows with different values
    ps = conn.prepareStatement("INSERT INTO DESTINATION(UID, VALUE) VALUES (?, ?)");
    for (int i = 1; i <= 50; i++) {
        ps.setInt(1, i);
        ps.setInt(2, i * 10);
        ps.executeUpdate();
    }
    // And merge second half into it, first half will be updated with a new values
    ps = conn.prepareStatement("MERGE INTO DESTINATION USING SOURCE ON (DESTINATION.UID = SOURCE.UID)" + " WHEN MATCHED THEN UPDATE SET VALUE = SOURCE.VALUE" + " WHEN NOT MATCHED THEN INSERT (UID, VALUE) VALUES (SOURCE.UID, SOURCE.VALUE)", Statement.RETURN_GENERATED_KEYS);
    // All rows should be either updated or inserted
    assertEquals(100, ps.executeUpdate());
    ResultSet rs = ps.getGeneratedKeys();
    // Only 50 keys for inserted rows should be generated
    for (int i = 1; i <= 50; i++) {
        assertTrue(rs.next());
        assertEquals(i + 50, rs.getLong(1));
    }
    assertFalse(rs.next());
    rs.close();
    // Check merged data
    rs = stat.executeQuery("SELECT ID, UID, VALUE FROM DESTINATION ORDER BY ID");
    for (int i = 1; i <= 100; i++) {
        assertTrue(rs.next());
        assertEquals(i, rs.getLong(1));
        assertEquals(i, rs.getInt(2));
        assertEquals(i * 10 + 5, rs.getInt(3));
    }
    assertFalse(rs.next());
    stat.execute("DROP TABLE SOURCE");
    stat.execute("DROP TABLE DESTINATION");
}