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

Example 86 with Update

use of org.h2.command.dml.Update 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");
}

Example 87 with Update

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

the class TestXA method testRollbackAfterPrepare.

private void testRollbackAfterPrepare() throws Exception {
    if (config.memory) {
        return;
    }
    Xid xid = new Xid() {

        @Override
        public int getFormatId() {
            return 3145;
        }

        @Override
        public byte[] getGlobalTransactionId() {
            return new byte[] { 1, 2, 3, 4, 5, 6, 6, 7, 8 };
        }

        @Override
        public byte[] getBranchQualifier() {
            return new byte[] { 34, 43, 33, 3, 3, 3, 33, 33, 3 };
        }
    };
    deleteDb("xa");
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL(getURL("xa", true));
    ds.setPassword(getPassword());
    Connection dm = ds.getConnection();
    Statement stat = dm.createStatement();
    stat.execute("CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, VAL INT)");
    stat.execute("INSERT INTO TEST(ID,VAL) VALUES (1,1)");
    dm.close();
    XAConnection c = ds.getXAConnection();
    XAResource xa = c.getXAResource();
    Connection connection = c.getConnection();
    xa.start(xid, XAResource.TMJOIN);
    PreparedStatement ps = connection.prepareStatement("UPDATE TEST SET VAL=? WHERE ID=?");
    ps.setInt(1, new Random().nextInt());
    ps.setInt(2, 1);
    ps.close();
    xa.prepare(xid);
    xa.rollback(xid);
    connection.close();
    c.close();
    deleteDb("xa");
}

Example 88 with Update

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

the class TestXA method testRollbackWithoutPrepare.

private void testRollbackWithoutPrepare() throws Exception {
    if (config.memory) {
        return;
    }
    Xid xid = new Xid() {

        @Override
        public int getFormatId() {
            return 3145;
        }

        @Override
        public byte[] getGlobalTransactionId() {
            return new byte[] { 1, 2, 3, 4, 5, 6, 6, 7, 8 };
        }

        @Override
        public byte[] getBranchQualifier() {
            return new byte[] { 34, 43, 33, 3, 3, 3, 33, 33, 3 };
        }
    };
    deleteDb("xa");
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL(getURL("xa", true));
    ds.setPassword(getPassword());
    Connection dm = ds.getConnection();
    Statement stat = dm.createStatement();
    stat.execute("CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, VAL INT)");
    stat.execute("INSERT INTO TEST(ID,VAL) VALUES (1,1)");
    dm.close();
    XAConnection c = ds.getXAConnection();
    XAResource xa = c.getXAResource();
    Connection connection = c.getConnection();
    xa.start(xid, XAResource.TMJOIN);
    PreparedStatement ps = connection.prepareStatement("UPDATE TEST SET VAL=? WHERE ID=?");
    ps.setInt(1, new Random().nextInt());
    ps.setInt(2, 1);
    ps.close();
    xa.rollback(xid);
    connection.close();
    c.close();
    deleteDb("xa");
}

Example 89 with Update

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

the class TestMvccMultiThreaded method testConcurrentSelectForUpdate.

private void testConcurrentSelectForUpdate() throws Exception {
    deleteDb(getTestName());
    Connection conn = getConnection(getTestName() + ";MULTI_THREADED=TRUE");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int not null primary key, updated int not null)");
    stat.execute("insert into test(id, updated) values(1, 100)");
    ArrayList<Task> tasks = new ArrayList<>();
    int count = 3;
    for (int i = 0; i < count; i++) {
        Task task = new Task() {

            @Override
            public void call() throws Exception {
                Connection conn = getConnection(getTestName());
                Statement stat = conn.createStatement();
                try {
                    while (!stop) {
                        try {
                            stat.execute("select * from test where id=1 for update");
                        } catch (SQLException e) {
                            int errorCode = e.getErrorCode();
                            assertTrue(e.getMessage(), errorCode == ErrorCode.DEADLOCK_1 || errorCode == ErrorCode.LOCK_TIMEOUT_1);
                        }
                    }
                } finally {
                    conn.close();
                }
            }
        }.execute();
        tasks.add(task);
    }
    for (int i = 0; i < 10; i++) {
        Thread.sleep(100);
        ResultSet rs = stat.executeQuery("select * from test");
        assertTrue(rs.next());
    }
    for (Task t : tasks) {
        t.get();
    }
    conn.close();
    deleteDb(getTestName());
}

Example 90 with Update

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

the class TestMvcc2 method testConcurrentUpdate.

private void testConcurrentUpdate() throws Exception {
    Connection conn = getConnection();
    final Connection conn2 = getConnection();
    Statement stat = conn.createStatement();
    final Statement stat2 = conn2.createStatement();
    stat2.execute("set lock_timeout 1000");
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("insert into test values(0, 'Hello')");
    conn.setAutoCommit(false);
    Task t = new Task() {

        @Override
        public void call() throws SQLException {
            stat2.execute("update test set name = 'Hallo'");
        }
    };
    stat.execute("update test set name = 'Hi'");
    t.execute();
    Thread.sleep(500);
    conn.commit();
    t.get();
    ResultSet rs;
    rs = stat.executeQuery("select name from test");
    rs.next();
    assertEquals("Hallo", rs.getString(1));
    stat.execute("drop table test");
    conn2.close();
    conn.close();
}