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

Example 71 with Insert

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

the class TestStatement method testIdentityMerge.

private void testIdentityMerge() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("drop table if exists test1");
    stat.execute("create table test1(id identity, x int)");
    stat.execute("drop table if exists test2");
    stat.execute("create table test2(id identity, x int)");
    stat.execute("merge into test1(x) key(x) values(5)", Statement.RETURN_GENERATED_KEYS);
    ResultSet keys;
    keys = stat.getGeneratedKeys();
    keys.next();
    assertEquals(1, keys.getInt(1));
    stat.execute("insert into test2(x) values(10), (11), (12)");
    stat.execute("merge into test1(x) key(x) values(5)", Statement.RETURN_GENERATED_KEYS);
    keys = stat.getGeneratedKeys();
    assertFalse(keys.next());
    stat.execute("merge into test1(x) key(x) values(6)", Statement.RETURN_GENERATED_KEYS);
    keys = stat.getGeneratedKeys();
    keys.next();
    assertEquals(2, keys.getInt(1));
    stat.execute("drop table test1, test2");
}

Example 72 with Insert

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

the class TestStatement method testTraceError.

private void testTraceError() throws Exception {
    if (config.memory || config.networked || config.traceLevelFile != 0) {
        return;
    }
    Statement stat = conn.createStatement();
    String fileName = getBaseDir() + "/statement.trace.db";
    stat.execute("DROP TABLE TEST IF EXISTS");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY)");
    stat.execute("INSERT INTO TEST VALUES(1)");
    try {
        stat.execute("ERROR");
    } catch (SQLException e) {
    // ignore
    }
    long lengthBefore = FileUtils.size(fileName);
    try {
        stat.execute("ERROR");
    } catch (SQLException e) {
    // ignore
    }
    long error = FileUtils.size(fileName);
    assertSmaller(lengthBefore, error);
    lengthBefore = error;
    try {
        stat.execute("INSERT INTO TEST VALUES(1)");
    } catch (SQLException e) {
    // ignore
    }
    error = FileUtils.size(fileName);
    assertEquals(lengthBefore, error);
    stat.execute("DROP TABLE TEST IF EXISTS");
}

Example 73 with Insert

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

the class TestStatement method testPreparedStatement.

private void testPreparedStatement() 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')");
    PreparedStatement ps = conn.prepareStatement("select name from test where id in (select id from test where name REGEXP ?)");
    ps.setString(1, "Hello");
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString("name"));
    assertFalse(rs.next());
    ps.setString(1, "World");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("World", rs.getString("name"));
    assertFalse(rs.next());
    // Changes the table structure
    stat.execute("create index t_id on test(name)");
    // Test the prepared statement again to check if the internal cache attributes were reset
    ps.setString(1, "Hello");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString("name"));
    assertFalse(rs.next());
    ps.setString(1, "World");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("World", rs.getString("name"));
    assertFalse(rs.next());
    ps = conn.prepareStatement("insert into test values(?, ?)");
    ps.setInt(1, 3);
    ps.setString(2, "v3");
    ps.addBatch();
    ps.setInt(1, 4);
    ps.setString(2, "v4");
    ps.addBatch();
    assertTrue(Arrays.equals(new int[] { 1, 1 }, ps.executeBatch()));
    ps.setInt(1, 5);
    ps.setString(2, "v5");
    ps.addBatch();
    ps.setInt(1, 6);
    ps.setString(2, "v6");
    ps.addBatch();
    assertTrue(Arrays.equals(new long[] { 1, 1 }, ((JdbcStatementBackwardsCompat) ps).executeLargeBatch()));
    ps.setInt(1, 7);
    ps.setString(2, "v7");
    assertEquals(1, ps.executeUpdate());
    assertEquals(1, ps.getUpdateCount());
    ps.setInt(1, 8);
    ps.setString(2, "v8");
    assertEquals(1, ((JdbcPreparedStatementBackwardsCompat) ps).executeLargeUpdate());
    assertEquals(1, ((JdbcStatementBackwardsCompat) ps).getLargeUpdateCount());
    stat.execute("drop table test");
}

Example 74 with Insert

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

the class TestLobApi method testLobStaysOpenUntilCommitted.

/**
 * According to the JDBC spec, BLOB and CLOB objects must stay open even if
 * the result set is closed (see ResultSet.close).
 */
private void testLobStaysOpenUntilCommitted() throws Exception {
    Connection conn = getConnection(getTestName());
    stat = conn.createStatement();
    stat.execute("create table test(id identity, c clob, b blob)");
    PreparedStatement prep = conn.prepareStatement("insert into test values(null, ?, ?)");
    prep.setString(1, "");
    prep.setBytes(2, new byte[0]);
    prep.execute();
    Random r = new Random(1);
    char[] charsSmall = new char[20];
    for (int i = 0; i < charsSmall.length; i++) {
        charsSmall[i] = (char) r.nextInt(10000);
    }
    String dSmall = new String(charsSmall);
    prep.setCharacterStream(1, new StringReader(dSmall), -1);
    byte[] bytesSmall = new byte[20];
    r.nextBytes(bytesSmall);
    prep.setBinaryStream(2, new ByteArrayInputStream(bytesSmall), -1);
    prep.execute();
    char[] chars = new char[100000];
    for (int i = 0; i < chars.length; i++) {
        chars[i] = (char) r.nextInt(10000);
    }
    String d = new String(chars);
    prep.setCharacterStream(1, new StringReader(d), -1);
    byte[] bytes = new byte[100000];
    r.nextBytes(bytes);
    prep.setBinaryStream(2, new ByteArrayInputStream(bytes), -1);
    prep.execute();
    conn.setAutoCommit(false);
    ResultSet rs = stat.executeQuery("select * from test order by id");
    rs.next();
    Clob c1 = rs.getClob(2);
    Blob b1 = rs.getBlob(3);
    rs.next();
    Clob c2 = rs.getClob(2);
    Blob b2 = rs.getBlob(3);
    rs.next();
    Clob c3 = rs.getClob(2);
    Blob b3 = rs.getBlob(3);
    assertFalse(rs.next());
    // now close
    rs.close();
    // but the LOBs must stay open
    assertEquals(0, c1.length());
    assertEquals(0, b1.length());
    assertEquals("", c1.getSubString(1, 0));
    assertEquals(new byte[0], b1.getBytes(1, 0));
    assertEquals(charsSmall.length, c2.length());
    assertEquals(bytesSmall.length, b2.length());
    assertEquals(dSmall, c2.getSubString(1, (int) c2.length()));
    assertEquals(bytesSmall, b2.getBytes(1, (int) b2.length()));
    assertEquals(chars.length, c3.length());
    assertEquals(bytes.length, b3.length());
    assertEquals(d, c3.getSubString(1, (int) c3.length()));
    assertEquals(bytes, b3.getBytes(1, (int) b3.length()));
    stat.execute("drop table test");
    conn.close();
}

Example 75 with Insert

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

the class TestGetGeneratedKeys method testPrepareStatement_StringArray_ExecuteUpdate.

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