Retrieving autoincrement value when using @JdbcIns

问题:

I'm trying to store a row in a DB2 database table where the primary key is an autoincrement. This works fine but I'm having trouble wrapping my head around how to retrieve the primary key value for further processing after successfully inserting the row. How do you achieve this? @JdbcInsert only returns the amount of rows that were inserted ...

回答1:

Since there does not seem to be a way to do this with SSJS (at least to me), I moved this particular piece of logic from my SSJS controller to a Java helper bean I created for JDBC related tasks. A Statement is capable of handing back generated keys (using the method executeUpdate()). So I still create my connection via @JdbcGetConnection, but then hand it in into the bean. This is the interesting part of the bean:

/**
 * SQL contains the INSERT Statement
 */    
public int executeUpdate(Connection conn, String SQL){

  int returnVal;
  Statement stmt = conn.createStatement();

  stmt.executeUpdate(SQL,
    Statement.RETURN_GENERATED_KEYS);

  if(!conn.getAutoCommit()) conn.commit();

  ResultSet keys = stmt.getGeneratedKeys();

  if(keys.next()){
    returnVal = keys.getInt(1);
  } else {
    returnVal = -1;
  }

  return returnVal;
}

If you insert more than one row at a time, you'll need to change the key retrieval handling, of course.

回答2:

In newer DB2 Versions you can transform every Insert into a Select to get automatic generated key columns. An example is:

select keycol from Final Table (insert into table (col1, col2) values (?,?))

keycol is the name of your identity column

The Select can be executed with the same @Function than your usual queries.