GetGeneratedKeys not working for DB2 4.19.26

[Raúl Cancino]

Hi,

I've been working with jdbi for a project, but @GetGeneratedKeys does not seem to work, I've even created a Mapper Class.  code as follows:

@GetGeneratedKeys(GeneratedKeysMapper.class)

@SqlUpdate(INSERT_CUSTOMIZING)

public abstract long  insertCustomization(@Bind("logicalName")String logicalName,

@Bind("configurationId")int configurationId,@Bind("customizationCriteriaId") int customizationCriteriaId);

public class GeneratedKeysMapper implements

ResultSetMapper<GeneratedKeysMapper> {

@Override

public GeneratedKeysMapper map(int index, ResultSet r, StatementContext ctx)

throws SQLException {

System.out.println("mapper");

System.out.println("index:"+index);

System.out.println("rs:"+r.getFetchSize());

System.out.println("context:"+ctx.isReturningGeneratedKeys());

return null;

}

}

I've followed all documentation regarding @GetGeneratedKeys, but still it does not seem to return a ResultSet with the key, It works using plain JDBC, but i'll have to reimplement all the code and it's a no go for my project and for using jDBI in other projects .

//@Test

public void oldJDBCInsertTest(){

DataSource ds = DBCPDataSourceFactory.getDataSource();

try {

Connection con=ds.getConnection();

PreparedStatement prepStmt;

prepStmt = con.prepareStatement("INSERT INTO INTERACTION_TYPE (ID, LOGICALNAME, REGULAREXP) "

+ "VALUES (NEXT VALUE FOR SEQ_INTERACTION_TYPE, 'test', 'test')", new String[]{"ID"});

prepStmt.executeUpdate();

   ResultSet rs = prepStmt.getGeneratedKeys();  

       while (rs.next() == true)

       {      

           

        System.out.println(rs.getInt(1));

       }

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}

I've attached an Eclipse debug variable tab, wich shows returningGeneratedKeys=true but the empty result set.

I'm using the following jars for db2

               <dependency>

 <groupId>com.ibm.db2.jcc</groupId>

 <artifactId>db2jcc</artifactId>

 <version>4.19.26</version>

</dependency>

<dependency>

<groupId>com.ibm.db2.jcc</groupId>

<artifactId>db2jcc_license_cu</artifactId>

<version>3.64.96</version>

</dependency>

and the following jar for jdbi:

<dependency>

<groupId>org.jdbi</groupId>

<artifactId>jdbi</artifactId>

<version>2.62</version>

</dependency>

Thanks

[Jacob Buck]

Hi Raúl,

I was confused by @GetGeneratedKeys as well at first expecting that I needed it to get number of rows affected by UPDATE but found it is only needed for INSERT. If you want to get the row id of the new inserted row in your table you just need to have @GetGeneratedKeys and return type long for your insertCustomization() method and jDBI will handle the rest. No need for the mapper. And if it's an UPDATE you have to NOT have @GetGeneratedKeys but still return type long to get the number of rows updated.

Best regards,
Jacob

[Raúl Cancino]

Hi Jacob,

Thanks so much for your answer.

The mapper its only for debug purposes as i wasn't sure if the resultSet was empty.  My updates work fine, problem comes with the inserts:

As the following won't work:

public static final String INSERT_CUSTOMIZING = "INSERT INTO CUSTOMIZING(ID,LOGICALNAME,CONFIGURATION_ID," +

"CUSTOMIZING_CRITERIA_ID) VALUES (NEXT VALUE FOR SEQ_CUSTOMIZING,:logicalName,:configurationId," +

":customizationCriteriaId)";

        @SqlUpdate(INSERT_CUSTOMIZING)

@GetGeneratedKeys

public abstract long  insertCustomization(@Bind("logicalName")String logicalName,

@Bind("configurationId")int configurationId,@Bind("customizationCriteriaId") int customizationCriteriaId);

i'm using this instead as a temporary solution (as advised by  IBM), for now:

public static final String SELECT_INSERT_CUSTOMIZING = "SELECT ID FROM  FINAL TABLE (INSERT INTO CUSTOMIZING(ID,LOGICALNAME,CONFIGURATION_ID," +

"CUSTOMIZING_CRITERIA_ID) VALUES (NEXT VALUE FOR SEQ_CUSTOMIZING,:logicalName,:configurationId," +

":customizationCriteriaId))";

@SqlQuery(SELECT_INSERT_CUSTOMIZING)

public abstract List<Map<String,Object>> selectInsertCustomization(@Bind("logicalName")String logicalName,

@Bind("configurationId")int configurationId,@Bind("customizationCriteriaId") int customizationCriteriaId);

¿is it ok to use  @SqlUpdate and @GetGeneratedKeys at the same time in insertCustomization()?

Thank you so much

[Jacob Buck]

Yes, you neeed to use both @SqlUpdate and @GetGeneratedKeys for your insertCustomization() in order to return the generated id. You can check out this test as an example:

https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestGetGeneratedKeys.java

Best regards,

Jacob