CODEWITHSUNDEEP
javajpajdbcinsert
Snekse
Snekse

Reputation: 15799

JPA: How to INSERT setting PK to MAX(PK) + 1

Scenario: I came across some code that is mixing JPA with JDBC within a transaction. The JDBC is doing an INSERT into a table with basically a blank row, setting the Primary Key to (SELECT MAX(PK) + 1) and the middleName to a temp timestamp. The method is then selecting from that same table for max(PK) + that temp timestamp to check if there was a collision. If successful, it then nulls out the middleName and updates. The method returns the newly created Primary Key.

Question: Is there a better way to insert an entity into the database, setting the PK to max(pk) + 1 and gaining access to that newly created PK (preferably using JPA)?

Environment: Using EclipseLink and need to support several versions of both Oracle and MS SqlServer databases.

Bonus Background: The reason I'm asking this question is because I run into a java.sql.BatchUpdateException when calling this method as part of a chain when running integration tests. The upper part of the chain uses JPA EntityManager to persist some objects.

Method in question 

@Override
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public int generateStudentIdKey() {
    final long now = System.currentTimeMillis();
    int id = 0;

    try {

        try (final Connection connection = dataSource.getConnection()) {

            if (connection.getAutoCommit()) {
                connection.setAutoCommit(false);
            }

            try (final Statement statement = connection.createStatement()) {
                // insert a row into the generator table
                statement.executeUpdate(
                    "insert into student_demo (student_id, middle_name) " + 
                    "select (max(student_id) + 1) as student_id, '" + now + 
                        "' as middle_name from student_demo");
                try (final ResultSet rs = statement.executeQuery(
                    "select max(student_id) as student_id " + 
                    "from student_demo where middle_name = '" + now + "'")) {

                        if (rs.next()) {
                            id = rs.getInt(1);
                        }
                }

                if (id == 0) {
                    connection.rollback();
                    throw new RuntimeException("Key was not generated");
                }

                statement.execute("update student_demo set middle_name = null " + 
                                  "where student_id = " + id);

            } catch (SQLException statementException) {
                connection.rollback();
                throw statementException;
            }
        }
    } catch (SQLException exception) {
        throw new RuntimeException(
           "Exception thrown while trying to generate new student_ID", exception);
    }

    return id;
}

Upvotes: 0

Views: 4953

Answers (2)

skirsch
skirsch

Reputation: 1747

First off: it hurts to answer this. But I know, sometimes you have to deal with the devil :(

So technically, it's not JPA, but if you are using Hibernate as JPA-Provider, you can go with

@org.hibernate.annotations.GenericGenerator(
    name = “incrementGenerator”,
    strategy = “org.hibernate.id.IncrementGenerator”)
@GeneratedValue(generator="incrementGenerator")
private Long primaryKey;

The Hibernate solution is "thread-safe", but not "cluster-safe", i.e. if you run your application on several hosts, this may fail. You may catch the appropriate exception and try again.

If you stick with your solution: close the ResultSet, Statement and the Connection. Sorry, didn't catch the try-with-resources initially.

Upvotes: 3

James
James

Reputation: 18379

The JDBC code is pathological, makes no sense, and will not work in a multi user environment.

I would strongly recommend fixing the code to use a sequence object, or sequence table.

In JPA you can just use sequencing.

See, http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Sequencing

If you really want to do your own sequencing, you can either assign the Id yourself, use PrePersist to assign your own id, or in EclipseLink implement your own Sequence subclass that does whatever you desire. You will need to register this Sequence object using a SessionCustomizer.

See, http://wiki.eclipse.org/EclipseLink/Examples/JPA/CustomSequencing

Upvotes: 0

Related Questions