Java jdbc batch insert only new rows

Question

How can I efficiently perform a batch insert to a Sql Server table using jdbc only for new rows (ID column value not present in the target table)?

Actually I am using the PreparedStatement object to create the batches, but without the "insert only if new row" logic:

String sqlInsertStub = "INSERT INTO mytable (id, col2, col3) values (?, ?, ?)";

try (PreparedStatement preparedStatement = connection.prepareStatement(sqlInsertStub)) {

    connection.setAutoCommit(false);

    int processed = 0;

    while (resultSet.next()) {

        // [...]

        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, col2);
        preparedStatement.setString(3, col3);

        preparedStatement.addBatch();

        if (++processed % BATCH_SIZE == 0) {
            preparedStatement.executeBatch();
            connection.commit();
        }

    }

    preparedStatement.executeBatch();
    connection.commit();    


catch (Exception e) {

    connection.rollBack();
    e.printStackTrace();
    System.exit(1);

}

Tim Biegeleisen · Accepted Answer · 2019-01-17 11:34:47Z

1

One option would be to add a unique constraint on the three columns in your table which determine uniqueness. Then, trying to insert a duplicate record should result in a Java exception. But, this might cause an entire batch to fail. If you want another option, you may change your query to the following:

INSERT INTO mytable
SELECT ?, ?, ?
WHERE NOT EXISTS (SELECT 1 FROM mytable
                  WHERE id = ? AND col2 = ? AND col3 = ?);

To this query, you bind the values twice as follows:

preparedStatement.setInt(1, id);
preparedStatement.setString(2, col2);
preparedStatement.setString(3, col3);
preparedStatement.setInt(4, id);
preparedStatement.setString(5, col2);
preparedStatement.setString(6, col3);

answered Jan 17, 2019 at 11:34

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gord Thompson Over a year ago

Could also use an anonymous code block to avoid having to pass the parameter values twice.

Mark Rotteveel Over a year ago

@GordThompson A row value constructor might be even simpler.

revy · Accepted Answer · 2019-01-18 15:12:48Z

0

I've also found this other possible solution using merge:

String sqlInsertStub = "MERGE INTO mytable a USING (SELECT ? ID) b " +
                        "ON (a.ID = b.ID)" +
                        "WHEN NOT MATCHED " +
                        "THEN INSERT (ID, COL1, COL2) VALUES(b.ID, ?, ?)";

answered Jan 18, 2019 at 15:12

revy

4,81711 gold badges49 silver badges105 bronze badges

Collectives™ on Stack Overflow

Java jdbc batch insert only new rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related