brianm/jdbi — pull request #116 by jackgene (2014-10-05 01:09:47+00:00)
Title: Added the "columns" attribute to @GetGeneratedKeys to allow users to explicitly specify the key columns

Hi,
We recently ran into a problem with @GetGeneratedKeys and Oracle, as a result of how Oracle returns an internally generated "row id" (a hash of some sort) rather than the primary key columns during record insertions.

Others seemed to have ran into similar problems (with JDBI, as well as with just straight JDBC):

1. This shows the problem in JDBI, and a proposed solution that this pull request is based on - https://groups.google.com/forum/#!msg/jdbi/5RzG8kJGLsI/o-CAOl_1NdAJ
2. The problem is not with JDBI, but with how Oracle's JDBC driver works - http://stackoverflow.com/questions/1976625/value-from-last-inserted-row-in-db

This pull request adds an attribute "columns" to @GetGeneratedKeys, which affects the PreparedStatement creation in the DefaultStatementBuilder in the following way:

1. If the "columns" attribute is provided, it is used to create the PreparedStatement, using the following JDBC API operation - http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20java.lang.String[]) (which gets us around the Oracle problem).
2. If the "columns" attribute is not provided, the PreparedStatement is created in the same manner as before.

The following tests were added to verify the new behavior:

1. TestDefaultStatementBuilder - Verifies that the appropriate calls are used to create PreparedStatements depending on how @GetGeneratedKeys is used.
2. TestGetGeneratedKeys - Updated to includes a DAO method that inserts using @GeneratedKeys with columns. This is mainly there to make sure that explicitly specifying key columns doesn't break things in H2.
3. TestGetGeneratedKeysWithColumns - This is a copy of TestGetGeneratedKeys, but is expected to run against an Oracle database. It is @Ignored by default, as Oracle is commercial software, and the Oracle JDBC driver cannot be included here. However, I have run this test locally, and have included instructions on enabling these tests for those interested in running them.