1

Good day,

I have a table in db2, where the primary is_autoincrement is set to Yes. Means that everytime insert data to this table, no need to pass in the primary key value, because it will auto generate.

However, I need to get back the primary key value after insertion. The code is something as follow:

public integer insertRecord() {

    //insertion sql code here
KeyHolder keyHolder = new GeneratedKeyHolder();

PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(
                getInsertBatch2( ), new int[] { 
                        java.sql.Types.TIMESTAMP, java.sql.Types.VARCHAR,
                        java.sql.Types.SMALLINT, java.sql.Types.DATE,
                        java.sql.Types.INTEGER } );

int updated = JdbcTemplate.update(
                factory.newPreparedStatementCreator( params ),  keyHolder);

log.trace("key value is " + keyHolder.getKey( );

    //after insertion, return primary key
    return id;
}

private String getInsertBatch2() {
    StringBuilder sb = new StringBuilder( );
    sb.append( "insert into " )
            .append( getDefaultSchema( ) )
            .append(
                    ".batches( startdatetime, type, manual, processdate, originalbatchid) " );
    sb.append( "values(?,?,?,?,?) " );
    return sb.toString( );
}

The insertion query is something as follow:

insert into table1(startdatetime, type, manual, processdate, originalbatchid) value (-- all the value here -- );

When I try to print out the keyHolder, it is null.

Kindly advise how to get the primary key.

Panadol Chong
  • 1,793
  • 13
  • 54
  • 119
  • 1
    Have you tried [`Statement#executeUpdate(String, int)`](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)) and [`Statement#getGeneratedKeys`](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys())? This will depend on the JDBC driver you are using as well – MadProgrammer Jul 04 '14 at 06:05
  • 3
    Answer in http://stackoverflow.com/questions/3087836/db2-how-to-get-the-last-insert-id-from-a-table may help you. – Tariq Jul 04 '14 at 06:07
  • Hi all, just edit my question. Kindly advise. – Panadol Chong Jul 04 '14 at 09:09
  • Your code currently has a compile error in it - you declare `updated` twice. Fix your code first. How often do you need to get changes to the default schema? Are you logging people in based on their own id, or just using a connection default? If the latter (second), you should be able to make all the prepared statement stuff instance variables, and not need to re-prepare the statement each time... – Clockwork-Muse Jul 04 '14 at 12:42
  • done fixed my mistake. – Panadol Chong Jul 04 '14 at 18:10
  • Hi @Clockwork-Muse, I done edit the compile error code. However, when I try to print out the `keyHolder.getKey( )`, it return null. Kindly advise on what mistake i make. – Panadol Chong Jul 07 '14 at 02:09

0 Answers0