title: JPA: How to INSERT setting PK to MAX(PK) + 1
body: <p><strong>Scenario:</strong> 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 <code>(SELECT MAX(PK) + 1)</code> and the <code>middleName</code> to a temp timestamp.  The method is then selecting from that same table for <code>max(PK)</code> + that temp timestamp to check if there was a collision. If successful, it then nulls out the <code>middleName</code> and updates.  The method returns the newly created Primary Key.</p>
  
  <p><strong>Question:</strong>
  Is there a better way to insert an entity into the database, setting the PK to <code>max(pk) + 1</code> and gaining access to that newly created PK (preferably using JPA)?</p>
  
  <p><strong>Environment:</strong>
  Using EclipseLink and need to support several versions of both Oracle and MS SqlServer databases.</p>
  
  <p><strong>Bonus Background:</strong> The reason I'm asking this question is because I run into a <code>java.sql.BatchUpdateException</code> when calling this method as part of a chain when running integration tests.  The upper part of the chain uses JPA <code>EntityManager</code> to persist some objects.</p>
  
  <p><strong>Method in question</strong>
  </p>
  
  <pre><code>@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;
  }
  </code></pre>