Java JDBC – Multiple prepared statement bulk insert

javajdbcprepared-statement

Using JDBC (Oracle) I need to insert about thousand rows into each of two tables. Something like this:

"INSERT INTO TABLE_A (A_ID, A_NAME, A_LAST_NAME) VALUES (MY_SEQUENCE.NEXTVAL, ?, ?)";
"INSERT INTO TABLE_B (B_ID, B_DESCRIPTION) VALUES (MY_SEQUENCE.CURRVAL, ?)";

The problem is that both tables are connected through common sequence, so that order of statements is important.

It would be quite easy if I had only one table. In that case I used code:

String insert = "Insert into TABLE_A(A_ID, A_NAME, A_LAST_NAME) values(MY_SEQUENCE.NEXTVAL, ?, ?)";
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(insert);
for(MyObject obj : myCollection) {
    ps.setString(1, obj.getName());
    ps.setString(2, obj.getLastName());
    ps.addBatch();
}
ps.executeBatch();
conn.commit();
ps.close();

But this approach can work only with one prepared statment and thus with only one Insert. How can I provide a solution for this problem?

Best Answer

You can try

PreparedStatement ps = conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
...
ps.executeBatch();

then

ResultSet rs = ps.getGeneratedKeys();
ps = conn.prepareStatement("INSERT INTO TABLE_B (B_ID, B_DESCRIPTION) VALUES (?, ?)");

for ( int counter =0;rs.next(); counter++ ) { 
  ps.setInt(1,rs.getInt(0));
  ps.setString(2, myCollection.get(counter).getDescription());
  ps.addBatch();
}
...

Related Solutions

Java – How to get the insert ID in JDBC

If it is an auto generated key, then you can use Statement#getGeneratedKeys() for this. You need to call it on the same Statement as the one being used for the INSERT. You first need to create the statement using Statement.RETURN_GENERATED_KEYS to notify the JDBC driver to return the keys.

Here's a basic example:

public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }
    }
}

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

Spring JDBCTemplate vs Plain JDBC for inserting large numbers of records

If you want to move a lot of data, then using JDBC (or any library building on top of JDBC) may be a bad choice, compared to using bulk copy tools (like db2import). JDBC is going to be orders of magnitude slower, because

JDBC is a very chatty protocol, and
usually bulk copy tools relax constraints during the copy process.

The difference in time can be extreme: what takes the bulk copy tool 10 minutes can take hours using JDBC. You'll want to create a prototype and do some timings and be certain about what kind of performance you'll get before you commit to something like this.

Best Answer

Related Solutions

Java – How to get the insert ID in JDBC

Spring JDBCTemplate vs Plain JDBC for inserting large numbers of records

Related Topic