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

Example 16 with Merge

use of org.h2.command.dml.Merge 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 17 with Merge

use of org.h2.command.dml.Merge 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 18 with Merge

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

Example 19 with Merge

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

the class TestOptimizations method testRowId.

private void testRowId() throws SQLException {
    if (config.memory) {
        return;
    }
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    ResultSet rs;
    stat.execute("create table test(data varchar)");
    stat.execute("select min(_rowid_ + 1) from test");
    stat.execute("insert into test(_rowid_, data) values(10, 'Hello')");
    stat.execute("insert into test(data) values('World')");
    stat.execute("insert into test(_rowid_, data) values(20, 'Hello')");
    stat.execute("merge into test(_rowid_, data) key(_rowid_) values(20, 'Hallo')");
    rs = stat.executeQuery("select _rowid_, data from test order by _rowid_");
    rs.next();
    assertEquals(10, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    rs.next();
    assertEquals(11, rs.getInt(1));
    assertEquals("World", rs.getString(2));
    rs.next();
    assertEquals(21, rs.getInt(1));
    assertEquals("Hallo", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("drop table test");
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("insert into test values(0, 'Hello')");
    stat.execute("insert into test values(3, 'Hello')");
    stat.execute("insert into test values(2, 'Hello')");
    rs = stat.executeQuery("explain select * from test where _rowid_ = 2");
    rs.next();
    assertContains(rs.getString(1), ".tableScan: _ROWID_ =");
    rs = stat.executeQuery("explain select * from test where _rowid_ > 2");
    rs.next();
    assertContains(rs.getString(1), ".tableScan: _ROWID_ >");
    rs = stat.executeQuery("explain select * from test order by _rowid_");
    rs.next();
    assertContains(rs.getString(1), "/* index sorted */");
    rs = stat.executeQuery("select _rowid_, * from test order by _rowid_");
    rs.next();
    assertEquals(0, rs.getInt(1));
    assertEquals(0, rs.getInt(2));
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertEquals(2, rs.getInt(2));
    rs.next();
    assertEquals(3, rs.getInt(1));
    assertEquals(3, rs.getInt(2));
    stat.execute("drop table test");
    conn.close();
}

Example 20 with Merge

use of org.h2.command.dml.Merge in project ignite by apache.

the class DmlStatementsProcessor method rowToKeyValue.

/**
     * Convert row presented as an array of Objects into key-value pair to be inserted to cache.
     * @param cctx Cache context.
     * @param row Row to process.
     * @param plan Update plan.
     * @throws IgniteCheckedException if failed.
     */
@SuppressWarnings({ "unchecked", "ConstantConditions", "ResultOfMethodCallIgnored" })
private IgniteBiTuple<?, ?> rowToKeyValue(GridCacheContext cctx, List<?> row, UpdatePlan plan) throws IgniteCheckedException {
    GridH2RowDescriptor rowDesc = plan.tbl.rowDescriptor();
    GridQueryTypeDescriptor desc = rowDesc.type();
    Object key = plan.keySupplier.apply(row);
    if (QueryUtils.isSqlType(desc.keyClass())) {
        assert plan.keyColIdx != -1;
        key = convert(key, rowDesc, desc.keyClass(), plan.colTypes[plan.keyColIdx]);
    }
    Object val = plan.valSupplier.apply(row);
    if (QueryUtils.isSqlType(desc.valueClass())) {
        assert plan.valColIdx != -1;
        val = convert(val, rowDesc, desc.valueClass(), plan.colTypes[plan.valColIdx]);
    }
    if (key == null)
        throw new IgniteSQLException("Key for INSERT or MERGE must not be null", IgniteQueryErrorCode.NULL_KEY);
    if (val == null)
        throw new IgniteSQLException("Value for INSERT or MERGE must not be null", IgniteQueryErrorCode.NULL_VALUE);
    Map<String, Object> newColVals = new HashMap<>();
    for (int i = 0; i < plan.colNames.length; i++) {
        if (i == plan.keyColIdx || i == plan.valColIdx)
            continue;
        String colName = plan.colNames[i];
        GridQueryProperty prop = desc.property(colName);
        assert prop != null;
        Class<?> expCls = prop.type();
        newColVals.put(colName, convert(row.get(i), rowDesc, expCls, plan.colTypes[i]));
    }
    // We update columns in the order specified by the table for a reason - table's
    // column order preserves their precedence for correct update of nested properties.
    Column[] cols = plan.tbl.getColumns();
    // First 3 columns are _key, _val and _ver. Skip 'em.
    for (int i = DEFAULT_COLUMNS_COUNT; i < cols.length; i++) {
        if (plan.tbl.rowDescriptor().isKeyValueOrVersionColumn(i))
            continue;
        String colName = cols[i].getName();
        if (!newColVals.containsKey(colName))
            continue;
        Object colVal = newColVals.get(colName);
        desc.setValue(colName, key, val, colVal);
    }
    if (cctx.binaryMarshaller()) {
        if (key instanceof BinaryObjectBuilder)
            key = ((BinaryObjectBuilder) key).build();
        if (val instanceof BinaryObjectBuilder)
            val = ((BinaryObjectBuilder) val).build();
    }
    return new IgniteBiTuple<>(key, val);
}