Oracle Autoincrement Functionality: Triggers or Oracle JDBC CallableStatement in 11.2?

Question

Which is the best way (in terms of insert performance) to implement autoincrement functionality in Oracle (11.2) when you need to retrieved the newly generated key using JDBC?

I know there are identity columns in Oracle 12, but I'm stuck with 11.2 right now.

Like many others, I have had no luck in getting the JDBC getGeneratedKeys() to work with Oracle. I ended up having trigger in my Oracle (11.2) database that acts like a MySQL autoincrement function and inserts the NextVal from a table specific sequence to act as its primary key whenever there is an insert into that table. This made getting the newly inserted key difficult though, I ended up doing a 2nd query to get the newly generated key.

More recently I discovered CallableStatement with return values and I see how these could be used to do everything with 1 call.

Is this last method generally a faster way of doing inserts when you also need that newly generated key or is there a better option for me that I am missing?

Answer 1

I've got interesing result in my mini-benchmark and decide to share it.

The test code:

import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;
import org.springframework.util.StopWatch;

import java.sql.*;

public class TriggerPerformanceTest {
    private static final int STEPS_COUNT = 1000;

    public static void main(String[] args) throws SQLException {
        final Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@host:1521/oradev", "user", "pass");

        prepare(connection);

        final StopWatch stopWatch = new StopWatch("mini-bench");

        testTrigger(connection, stopWatch);
        testSequence(connection, stopWatch);
        testSeparateCalls(connection, stopWatch);

        JdbcUtils.closeConnection(connection);

        System.out.println(stopWatch.prettyPrint());
    }

    private static void testTrigger(Connection connection, StopWatch stopWatch) throws SQLException {
        final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_trigger (text) VALUES (?)", new String[]{"ID"});
        stopWatch.start("with trigger");
        for (int i = 0; i < STEPS_COUNT; i++) {
            preparedStatement.setString(1, "test");
            preparedStatement.executeUpdate();

            final ResultSet resultSet = preparedStatement.getGeneratedKeys();
            final boolean next = resultSet.next();
            Assert.state(next, "Expected not empty result set with generated keys");
            final long id = resultSet.getLong(1);
            Assert.state(id > 0, "Expected generated key value");
            JdbcUtils.closeResultSet(resultSet);
        }
        stopWatch.stop();
        JdbcUtils.closeStatement(preparedStatement);
    }

    private static void testSequence(Connection connection, StopWatch stopWatch) throws SQLException {
        final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_sequence (id, text) VALUES (sq_test2.NEXTVAL, ?)", new String[]{"ID"});
        stopWatch.start("without trigger");
        for (int i = 0; i < STEPS_COUNT; i++) {
            preparedStatement.setString(1, "test");
            preparedStatement.executeUpdate();

            final ResultSet resultSet = preparedStatement.getGeneratedKeys();
            final boolean next = resultSet.next();
            Assert.state(next, "Expected not empty result set with generated keys");
            final long id = resultSet.getLong(1);
            Assert.state(id > 0, "Expected generated key value");
            JdbcUtils.closeResultSet(resultSet);
        }
        stopWatch.stop();
        JdbcUtils.closeStatement(preparedStatement);
    }

    private static void testSeparateCalls(Connection connection, StopWatch stopWatch) throws SQLException {
        final PreparedStatement preparedStatementSeq = connection.prepareStatement("SELECT sq_test3.NEXTVAL FROM dual");
        final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_generated (id, text) VALUES (?, ?)");

        stopWatch.start("separate calls");
        for (int i = 0; i < STEPS_COUNT; i++) {
            final ResultSet resultSet = preparedStatementSeq.executeQuery();
            resultSet.next();
            final long id = resultSet.getLong(1);
            JdbcUtils.closeResultSet(resultSet);
            preparedStatement.setLong(1, id);
            preparedStatement.setString(2, "test");
            preparedStatement.executeUpdate();
        }
        stopWatch.stop();
        JdbcUtils.closeStatement(preparedStatementSeq);
        JdbcUtils.closeStatement(preparedStatement);
    }

    private static void prepare(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        try {
            statement.execute("DROP TABLE test_table_sequence");
            statement.execute("DROP TABLE test_table_trigger");
            statement.execute("DROP TABLE test_table_generated");
            statement.execute("DROP SEQUENCE sq_test1");
            statement.execute("DROP SEQUENCE sq_test2");
            statement.execute("DROP SEQUENCE sq_test3");
        } catch (SQLException sqle) {
            //ignore
        }

        try {
            statement.execute("CREATE TABLE test_table_sequence (id NUMBER, text VARCHAR2(10))");
            statement.execute("CREATE TABLE test_table_trigger (id NUMBER, text VARCHAR2(10))");
            statement.execute("CREATE TABLE test_table_generated (id NUMBER, text VARCHAR2(10))");
            statement.execute("CREATE SEQUENCE sq_test1 START WITH 1 INCREMENT BY 1 CACHE 20");
            statement.execute("CREATE SEQUENCE sq_test2 START WITH 1 INCREMENT BY 1 CACHE 20");
            statement.execute("CREATE SEQUENCE sq_test3 START WITH 1 INCREMENT BY 1 CACHE 20");
            statement.execute("CREATE OR REPLACE TRIGGER trg_increment BEFORE INSERT ON test_table_trigger FOR EACH ROW\n" +
                              "BEGIN\n" +
                              "  SELECT sq_test1.NEXTVAL INTO :new.id FROM dual;\n" +
                              "END;");
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        try {
            statement.execute("TRUNCATE TABLE test_table_sequence");
            statement.execute("TRUNCATE TABLE test_table_trigger");
            statement.execute("TRUNCATE TABLE test_table_generated");
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
}

The output:

StopWatch 'mini-bench': running time (millis) = 27430
-----------------------------------------
ms     %     Task name
-----------------------------------------
09214  034%  with trigger
08916  033%  without trigger
09300  034%  separate calls

The conclusion: the difference quite small... to take it into consideration.

PS. Dedicated Oracle 11.2.0.4, LAN 1Gb/s, Java 1.7.0_65.

Answer 2

Like many others, I have had no luck in getting the JDBC getGeneratedKeys() to work with Oracle

That is actually quite easy.

The following works for me with a trigger and Oracle 11.2 and driver version 11.2.0.3.0 (and 11.2.0.43.0)

create sequence foo_seq;
create table foo (id integer not null primary key, some_data varchar(20));

The trigger:

create trigger foo_trg 
  before insert on foo
  for each row
begin
  :new.id := foo_seq.nextval;
end;
/

And the Java code:

String insert = "insert into foo (some_data) values (?)"; 
PreparedStatement pstmt = conection.prepareStatement(insert, new String[]{"ID"});

pstmt.setString(1, "bla");
pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys();
while (rs.next()) {
  long id = rs.getLong(1);
  System.out.println("The generated ID was: " + id);
}
rs.close();

If you don't want the trigger because of performance considerations, the above code works without a trigger, if you change the insert statement to use the sequence:

String insert = "insert into foo (id, some_data) values (foo_seq.nextval, ?)";