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

Example 96 with Update

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

the class TestLob method testBlobInputStreamSeek.

private void testBlobInputStreamSeek(boolean upgraded) throws Exception {
    deleteDb("lob");
    Connection conn;
    conn = getConnection("lob");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, data blob)");
    PreparedStatement prep;
    Random random = new Random();
    byte[] buff = new byte[500000];
    for (int i = 0; i < 10; i++) {
        prep = conn.prepareStatement("insert into test values(?, ?)");
        prep.setInt(1, i);
        random.setSeed(i);
        random.nextBytes(buff);
        prep.setBinaryStream(2, new ByteArrayInputStream(buff), -1);
        prep.execute();
    }
    if (upgraded) {
        if (!config.mvStore) {
            if (config.memory) {
                stat.execute("update information_schema.lob_map set pos=null");
            } else {
                stat.execute("alter table information_schema.lob_map drop column pos");
                conn.close();
                conn = getConnection("lob");
            }
        }
    }
    prep = conn.prepareStatement("select * from test where id = ?");
    for (int i = 0; i < 1; i++) {
        random.setSeed(i);
        random.nextBytes(buff);
        for (int j = 0; j < buff.length; j += 10000) {
            prep.setInt(1, i);
            ResultSet rs = prep.executeQuery();
            rs.next();
            InputStream in = rs.getBinaryStream(2);
            in.skip(j);
            int t = in.read();
            assertEquals(t, buff[j] & 0xff);
        }
    }
    conn.close();
}

Example 97 with Update

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

the class TestLob method testLobTransactions.

private void testLobTransactions(int spaceLen) throws SQLException {
    deleteDb("lob");
    Connection conn = reconnect(null);
    conn.createStatement().execute("CREATE TABLE TEST(ID IDENTITY, " + "DATA CLOB, DATA2 VARCHAR)");
    conn.setAutoCommit(false);
    Random random = new Random(0);
    int rows = 0;
    Savepoint sp = null;
    int len = getSize(100, 400);
    // config.traceTest = true;
    for (int i = 0; i < len; i++) {
        switch(random.nextInt(10)) {
            case 0:
                trace("insert " + i);
                conn.createStatement().execute("INSERT INTO TEST(DATA, DATA2) VALUES('" + i + "' || SPACE(" + spaceLen + "), '" + i + "')");
                rows++;
                break;
            case 1:
                if (rows > 0) {
                    int x = random.nextInt(rows);
                    trace("delete " + x);
                    conn.createStatement().execute("DELETE FROM TEST WHERE ID=" + x);
                }
                break;
            case 2:
                if (rows > 0) {
                    int x = random.nextInt(rows);
                    trace("update " + x);
                    conn.createStatement().execute("UPDATE TEST SET DATA='x' || DATA, " + "DATA2='x' || DATA2 WHERE ID=" + x);
                }
                break;
            case 3:
                if (rows > 0) {
                    trace("commit");
                    conn.commit();
                    sp = null;
                }
                break;
            case 4:
                if (rows > 0) {
                    trace("rollback");
                    conn.rollback();
                    sp = null;
                }
                break;
            case 5:
                trace("savepoint");
                sp = conn.setSavepoint();
                break;
            case 6:
                if (sp != null) {
                    trace("rollback to savepoint");
                    conn.rollback(sp);
                }
                break;
            case 7:
                if (rows > 0) {
                    trace("checkpoint");
                    conn.createStatement().execute("CHECKPOINT");
                    trace("shutdown immediately");
                    conn.createStatement().execute("SHUTDOWN IMMEDIATELY");
                    trace("shutdown done");
                    conn = reconnect(conn);
                    conn.setAutoCommit(false);
                    sp = null;
                }
                break;
            default:
        }
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        while (rs.next()) {
            int id = rs.getInt("ID");
            String d1 = rs.getString("DATA").trim();
            String d2 = rs.getString("DATA2");
            assertEquals("id:" + id, d2, d1);
        }
    }
    conn.close();
}

Example 98 with Update

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

the class TestLob method testUpdateLob.

private void testUpdateLob() throws SQLException {
    deleteDb("lob");
    Connection conn;
    conn = reconnect(null);
    PreparedStatement prep = conn.prepareStatement("CREATE TABLE IF NOT EXISTS p( id int primary key, rawbyte BLOB ); ");
    prep.execute();
    prep.close();
    prep = conn.prepareStatement("INSERT INTO p(id) VALUES(?);");
    for (int i = 0; i < 10; i++) {
        prep.setInt(1, i);
        prep.execute();
    }
    prep.close();
    prep = conn.prepareStatement("UPDATE p set rawbyte=? WHERE id=?");
    for (int i = 0; i < 8; i++) {
        prep.setBinaryStream(1, getRandomStream(10000, i), 0);
        prep.setInt(2, i);
        prep.execute();
    }
    prep.close();
    conn.commit();
    conn = reconnect(conn);
    conn.setAutoCommit(true);
    prep = conn.prepareStatement("UPDATE p set rawbyte=? WHERE id=?");
    for (int i = 8; i < 10; i++) {
        prep.setBinaryStream(1, getRandomStream(10000, i), 0);
        prep.setInt(2, i);
        prep.execute();
    }
    prep.close();
    prep = conn.prepareStatement("SELECT * from p");
    ResultSet rs = prep.executeQuery();
    while (rs.next()) {
        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
            rs.getMetaData().getColumnName(i);
            rs.getString(i);
        }
    }
    conn.close();
}

Example 99 with Update

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

the class TestSpatial method testNullableGeometryUpdate.

private void testNullableGeometryUpdate() throws SQLException {
    deleteDb("spatial");
    Connection conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create memory table test" + "(id int primary key, the_geom geometry, description varchar2(32))");
    stat.execute("create spatial index on test(the_geom)");
    for (int i = 0; i < 1000; i++) {
        stat.execute("insert into test values(" + (i + 1) + ", null, null)");
    }
    ResultSet rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertNull(rs.getObject(2));
    stat.execute("update test set description='DESCRIPTION' where id = 1");
    stat.execute("update test set description='DESCRIPTION' where id = 2");
    stat.execute("update test set description='DESCRIPTION' where id = 3");
    conn.close();
    deleteDb("spatial");
}

Example 100 with Update

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

the class TestMultiThread method testConcurrentUpdate.

private void testConcurrentUpdate() throws Exception {
    deleteDb("lockMode");
    final int objectCount = 10000;
    final String url = getURL("lockMode;MULTI_THREADED=1;LOCK_TIMEOUT=10000", true);
    int threadCount = 25;
    ExecutorService executor = Executors.newFixedThreadPool(threadCount);
    Connection conn = getConnection(url);
    try {
        conn.createStatement().execute("CREATE TABLE IF NOT EXISTS ACCOUNT" + "(ID NUMBER(18,0) not null PRIMARY KEY, BALANCE NUMBER null)");
        final PreparedStatement mergeAcctStmt = conn.prepareStatement("MERGE INTO Account(id, balance) key (id) VALUES (?, ?)");
        for (int i = 0; i < objectCount; i++) {
            mergeAcctStmt.setLong(1, i);
            mergeAcctStmt.setBigDecimal(2, BigDecimal.ZERO);
            mergeAcctStmt.execute();
        }
        final ArrayList<Callable<Void>> callables = new ArrayList<>();
        for (int i = 0; i < threadCount; i++) {
            callables.add(new Callable<Void>() {

                @Override
                public Void call() throws Exception {
                    try (Connection taskConn = getConnection(url)) {
                        taskConn.setAutoCommit(false);
                        final PreparedStatement updateAcctStmt = taskConn.prepareStatement("UPDATE account SET balance = ? WHERE id = ?");
                        for (int j = 0; j < 1000; j++) {
                            updateAcctStmt.setDouble(1, Math.random());
                            updateAcctStmt.setLong(2, (int) (Math.random() * objectCount));
                            updateAcctStmt.execute();
                            taskConn.commit();
                        }
                    }
                    return null;
                }
            });
        }
        final ArrayList<Future<Void>> jobs = new ArrayList<>();
        for (int i = 0; i < threadCount; i++) {
            jobs.add(executor.submit(callables.get(i)));
        }
        // check for exceptions
        for (Future<Void> job : jobs) {
            job.get(5, TimeUnit.MINUTES);
        }
    } finally {
        IOUtils.closeSilently(conn);
        executor.shutdown();
        executor.awaitTermination(20, TimeUnit.SECONDS);
    }
    deleteDb("lockMode");
}