JDBI, Oracle and Null
JDBI is a pretty neat framework for a light-touch integration with JDBC in your application. t’s SQL Object API lets you annotate an interface with straight SQL queries like so:
1 2 3 4
then get dbi to create you an instance of this dao which will magically prepare and execute the statements you’ve bound to the methods in the interface.
But try supplying null as a parameter to your dao, and you’ll get a nasty surprise:
1 2 3
results in a lengthy stack trace culminating in:
Caused by: ! java.sql.SQLException: Invalid column type: 1111
Why is this happening? Well, under the hood JDBI is unable to work out the type of your null argument, and falls back to treating your parameter as an instance of Object, leading to a JDBI ObjectArgument being created to bind your parameter into the PreparedStatement it’s building for you under the covers. When this ObjectArgument is applied to your query it does this:
1 2 3 4 5 6 7 8 9
Our null parameter is successfully mapped to a setNull on the statement, but the sql type supplied to statement gets the Oracle driver’s knickers in a twist, as it doesn’t recognise
java.sql.Types.OTHER as a valid sql type, and throws up at your feet.
Fortunately, JDBI gives us an opportunity to extend it’s functionality by registering somethigng with it which will set the correct sql type for the finicky Oracle driver:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
then register this with your DBI instance when youre setting things up: