jdbi / jdbi

The Jdbi library provides convenient, idiomatic access to relational databases in Java and other JVM technologies such as Kotlin, Clojure or Scala.

| | | |

The Jdbi library provides convenient, idiomatic access to relational databases in Java and other JVM technologies such as Kotlin, Clojure or Scala.

Jdbi is built on top of JDBC. If your database has a JDBC driver, you can use Jdbi with it.

• Developer Guide
• Javadoc
• User forums
• Mailing List
• Stack Overflow

Also check out the code examples in the Examples module.

Acknowledgements and Funding

• Jdbi is a recipient of the Spotify FOSS 2023 Fund
• Jdbi is supported by Tidelift

Prerequisites

Jdbi requires Java 11 or better to run.

We run CI tests against Java 11, 17 and 21.

Compatibility with older Java versions

Java 8, 9 and 10 are supported by any Jdbi version before 3.40.0.

Building

Jdbi requires a JDK version 17 or better to build. We enforce the latest LTS (currently Java 21) for releases.

Jdbi is "batteries included" and uses the Apache Maven Wrapper. If an external Maven installation is used, Apache Maven 3.9 or later is required. Using the make targets requires GNU make.

All build tasks are organized as make targets.

Build the code an install it into the local repository:

$ make install

Running make or make help displays all available build targets with a short explanation. Some of the goals will require project membership privileges. The CONTRIBUTING.md document contains a list of all supported targets.

To add command line parameters to the maven executions from the Makefile, set the MAVEN_CONFIG variable:

% MAVEN_CONFIG="-B -fae" make install

Testing

Running make tests runs all unit and integration tests.

Some tests use Postgres and H2 databases (the tests will spin up temporary database servers as needed). Most modern OS (Windows, MacOS, Linux) and host architecture (x86_64, aarch64) should work.

Docker requirements

For a full release build, docker or a docker compatible environment must be available. A small number of tests use testcontainers which in turn requires docker.

make install-nodocker skips the tests when building and installing Jdbi locally. make tests-nodocker skips the tests when only running tests.

Supported configurations are

• Docker Desktop on MacOS
• docker-ce on Linux
• podman 3 or better on Linux and MacOS

Other docker installations such as Colima may work but are untested and unsupported.

For podman on Linux, the podman socket must be activated (see https://stackoverflow.com/questions/71549856/testcontainers-with-podman-in-java-tests) for details. SELinux sometimes interferes with testcontainers if SELinux is active; make sure that there is an exception configured.

For podman on MacOS, it is necessary to set the DOCKER_HOST environment variable correctly.

Contributing

Please read CONTRIBUTING.md for instructions to set up your development environment to build Jdbi.

Versioning

Jdbi uses SemVer to version its public API.

License

This project is licensed under the Apache 2.0 license.

Project Members

• Brian McCallister (@brianm) - Project Founder
• Steven Schlansker (@stevenschlansker)
• Henning Schmiedehausen (@hgschmie)
• Matthew Hall (@qualidafial)
• Artem Prigoda (@arteam)
• Marnick L'Eau (@TheRealMarnes)

Special Thanks

• Alex Harin (@aharin) - Kotlin plugins.
• Ali Shakiba (@shakiba) - JPA plugin
• @alwins0n - Vavr plugin.
• Fred Deschenes (@FredDeschenes) - Kotlin unchecked extensions for Jdbi functions. @BindFields, @BindMethods annotations.

jdbi's Issues

404s on jdbi.org/dbi_handle_and_statement/

Several links on http://jdbi.org/dbi_handle_and_statement/ are not working:

• http://jdbi.org/externalizing_sql/
• http://jdbi.org/fluent_calls
• http://jdbi.org/fluent_batches/
• http://jdbi.org/named_parameters/

Added support for alias'ed column names to BeanMapper

Ideally the BeanMapper should recognize column name aliases when they're present. I've noticed that at least with the H2 JDBC driver, the alias name is accessible via ResultSetMetaData.getColumnLabel, though I'm not sure if this holds for other drivers.

Either way, this would be a feature worth having since db columns are typically named according to a different convention than bean properties, and aliases are one way of bridging the gap.

Dynamic return types

As a JDBI 3 Request:

Currently what I am doing:

   public <T extends PhotoAlbum> Optional<T> select(final Class<T> type, final Integer albumId) {
      T select = dbiHelper.select(dbi,
                                  type,
                                  "SELECT * FROM <type> WHERE ALBUM_ID = ?",
                                  albumId);
      return Optional.fromNullable(select);
   }

What I want is:

   @SqlQuery("SELECT * FROM <type> WHERE ALBUM_ID = ?")
   public abstract  <T extends PhotoAlbum> Optional<T> select2(@DefineClass("type") final Class<T> type, final Integer albumId);

Return type can be resolve at runtime not compile time. This would help us to resolve T extends PhotoAlbum correctly. Currently this is limited to PhotoAlbum for this case.

And if it is possible removing @SingleValueResult would be awesome too. Iterating in already registered containers can solve this problem too.

Cannot set null String value with Oracle Driver

We are having a problem inserting a null value with jdbi 2.32 and the oracle driver (ojdbc 14).

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Exception while binding positional param at (0 based) position 2 [statement:"insert into eventLog (id, event, freeTextMessage, timestamp) values (?, ?, ?, current_date)", located:"insert into eventLog(id, event, freeTextMessage, timestamp) values (?, ?, ?, current_date)", rewritten:"insert into eventLog (id, event, freeTextMessage, timestamp) values (?, ?, ?, current_date)", arguments:{ positional:{0:e29269b6-4dd7-40c9-9af4-11092de27444,1:eventName,2:null}, named:{}, finder:[]}]

    at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter$MyRewrittenStatement.bind(ColonPrefixNamedParamStatementRewriter.java:123)
    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1290)
    at org.skife.jdbi.v2.Update.execute(Update.java:57)
    at org.skife.jdbi.v2.BasicHandle.update(BasicHandle.java:278)
    at org.skife.jdbi.v2.BasicHandle.insert(BasicHandle.java:268)
    at myapp.dbi.DbiLogRepository.logEvent(DbiMigrationRepository.java:78)
    at 

Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3433)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4261)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4250)
at org.skife.jdbi.v2.ObjectArgument.apply(ObjectArgument.java:43)
at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter$MyRewrittenStatement.bind(ColonPrefixNamedParamStatementRewriter.java:120)
... 53 more

Here is the table definition:

CREATE TABLE eventLog
(
id VARCHAR2 (255) NOT NULL,
event VARCHAR2 (255) NOT NULL,
freeTextMessage VARCHAR2 (255),
timestamp DATE NOT NULL
);

Trying to set a null value via JDBI and Oracle ojdbc6.jar

I'm getting an UnableToCreateStatementException when trying to execute a SQL update that contains a null value.

I think this is related to #17

  @SqlUpdate
  public abstract void updateUserInfo(
      @Bind("userName") final String userName,
      @Bind("roleName") final String roleName,
      @Bind("firstName") final String firstName,
      @Bind("lastName") final String lastName,
      @Bind("email") final String email
  );





ERROR [2014-04-22 22:07:31,739] com.yammer.dropwizard.jersey.LoggingExceptionMapper: Error handling a request: 8f847dd2fa8cd5f7
! org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception while binding 'lastName' [statement:"updateUserInfo", located:"    update "Users" set
"RoleName" = :roleName, "FirstName" = :firstName, "LastName" = :lastName, "Email" = :email where "UserName" = :userName", rewritten:"/* UserDao.updateUserInfo
*/     update "Users" set "RoleName" = ?, "FirstName" = ?, "LastName" = ?, "Email" = ? where "UserName" = ?", arguments:{ positional:{}, named:{lastName:null,em
ail:null,userName:demo,roleName:admin,firstName:eric}, finder:[]}]
! at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter$MyRewrittenStatement.bind(ColonPrefixNamedParamStatementRewriter.java:155) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1290) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.Update.execute(Update.java:57) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.UpdateHandler$2.value(UpdateHandler.java:45) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:57) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:147) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:60) ~[jdbi-2.41.jar:na]
! at api.core.UserDao$$EnhancerByCGLIB$$68abc0d7.updateUserInfo(<generated>) ~[jdbi-2.41.jar:na]
! at resources.UsersResource.put(UsersResource.java:126) ~[main/:na]
! at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_17]
! at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_17]
! at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_17]
! at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_17]
! at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider
.java:205) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) ~[jersey-server-1.17.1.jar:1.1
7.1]
! at com.yammer.metrics.jersey.InstrumentedResourceMethodDispatchProvider$TimedRequestDispatcher.dispatch(InstrumentedResourceMethodDispatchProvider.java:32) ~[
metrics-jersey-2.2.0.jar:na]
! at com.yammer.dropwizard.jersey.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37) ~[drop
wizard-core-0.6.2.jar:na]
! at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) [jersey-servlet-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) [jersey-servlet-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) [jersey-servlet-1.17.1.jar:1.17.1]
! at javax.servlet.http.HttpServlet.service(HttpServlet.java:848) [javax.servlet-3.0.0.v201112011016.jar:na]
! at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1448) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlets.CrossOriginFilter.handle(CrossOriginFilter.java:247) [jetty-servlets-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlets.CrossOriginFilter.doFilter(CrossOriginFilter.java:210) [jetty-servlets-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.yammer.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29) [dropwizard-core-0.6.2.jar:na]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:455) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1075) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:384) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1009) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.yammer.metrics.jetty.InstrumentedHandler.handle(InstrumentedHandler.java:200) [metrics-jetty-2.2.0.jar:na]
! at org.eclipse.jetty.server.handler.GzipHandler.handle(GzipHandler.java:264) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.yammer.dropwizard.jetty.BiDiGzipHandler.handle(BiDiGzipHandler.java:123) [dropwizard-core-0.6.2.jar:na]
! at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.Server.handle(Server.java:368) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.BlockingHttpConnection.handleRequest(BlockingHttpConnection.java:53) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:953) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1014) [jetty-server-8.1.10.v20130312.jar:8.1.10.v2013031
2]
! at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:865) [jetty-http-8.1.12.v20130726.jar:8.1.12.v20130726]
! at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240) [jetty-http-8.1.12.v20130726.jar:8.1.12.v20130726]
! at org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:72) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.nio.BlockingChannelConnector$BlockingChannelEndPoint.run(BlockingChannelConnector.java:298) [jetty-server-8.1.10.v20130312.jar:8.1
.10.v20130312]
! at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) [jetty-util-8.1.12.v20130726.jar:8.1.12.v20130726]
! at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) [jetty-util-8.1.12.v20130726.jar:8.1.12.v20130726]
! at java.lang.Thread.run(Thread.java:722) [na:1.7.0_17]
Caused by: ! java.sql.SQLException: Invalid column type: 1111
! at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3963) ~[ojdbc6.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4596) ~[ojdbc6.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4578) ~[ojdbc6.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1285) ~[ojdbc6.jar:11.2.0.3.0]
! at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108) ~[tomcat-dbcp-7.0.37.jar:7.0.37]
! at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108) ~[tomcat-dbcp-7.0.37.jar:7.0.37]
! at org.skife.jdbi.v2.ObjectArgument.apply(ObjectArgument.java:43) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter$MyRewrittenStatement.bind(ColonPrefixNamedParamStatementRewriter.java:152) ~[jdbi-2.41.jar:na]
!... 62 common frames omitted

JDBI tries to open connections during finalize

I recently upgraded to Tomcat DBCP 8.0.0-RC3 and started seeing exceptions like the following in our unit tests:

E 10-07 14:05:21.450 Finalizer o.a.t.jdbc.pool.ConnectionPool:182 ::] Unable to create initial connections of pool.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'test2'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0_40]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_40]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_40]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_40]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709) ~[mysql-connector-java-5.1.26.jar:na]        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.26.jar:na]
        at sun.reflect.GeneratedConstructorAccessor42.newInstance(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_40]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_40]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) ~[mysql-connector-java-5.1.26.jar:na]
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346) ~[mysql-connector-java-5.1.26.jar:na]
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278) ~[tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182) ~[tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:701) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:635) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:486) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:144) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127) [tomcat-jdbc-8.0.0-RC3.jar:na]
        at org.skife.jdbi.v2.DataSourceConnectionFactory.openConnection(DataSourceConnectionFactory.java:36) [jdbi-2.50.jar:na]
        at org.skife.jdbi.v2.DBI.open(DBI.java:207) [jdbi-2.50.jar:na]
        at org.skife.jdbi.v2.sqlobject.OnDemandHandleDing.getHandle(OnDemandHandleDing.java:22) [jdbi-2.50.jar:na]
        at org.skife.jdbi.v2.sqlobject.OnDemandHandleDing.retain(OnDemandHandleDing.java:29) [jdbi-2.50.jar:na]
        at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:146) [jdbi-2.50.jar:na]
        at org.skife.jdbi.v2.sqlobject.SqlObject$2.intercept(SqlObject.java:74) [jdbi-2.50.jar:na]
        at net.floatingsun.MyDAO$$EnhancerByCGLIB$$4b821501.finalize(<generated>) [jdbi-2.50.jar:na]
        at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method) [na:1.7.0_40]
        at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:101) [na:1.7.0_40]
        at java.lang.ref.Finalizer.access$100(Finalizer.java:32) [na:1.7.0_40]
        at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:190) [na:1.7.0_40]

The exception seems to be thrown during test tear down. So the database is no longer present. But note the call to DBI.open, via the generated proxy, which obviously fails.

Note further that I don't see this behavior with Tomcat DBCP 7.0.42 but this seems incorrect regardless.

NPE in MapArguments - using bindFromMap with one of values null

As per title, using bindFromMap with any values being null causes NPE. I have written a simple test case for this. Not sure if this is relevant, but I'm using PostgreSQL.
Test fails in MapArguments in find() on line 44. If value for given entry is null, this bit

return foreman.waffle(args.get(name).getClass(), args.get(name), ctx);

causes NPE.

import java.util.HashMap;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.postgresql.ds.PGSimpleDataSource;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.HashPrefixStatementRewriter;
import org.skife.jdbi.v2.Update;

public class JDBI_Tests {

    static Handle dbHandle;

    @BeforeClass
    public static void prepare() {
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setDatabaseName("testdb");
        ds.setUser("testuser");
        ds.setPassword("testpassword");
        ds.setServerName("localhost");

        DBI dbi = new DBI(ds);
        dbHandle = dbi.open();
    }

    @AfterClass
    public static void cleanup() {
        dbHandle.close();
    }

    @Test
    public void MapArguments_NPE() {
        HashMap<String, Object> params = new HashMap<>(1);
        params.put("colA", null);
        Update insert = dbHandle.createStatement("INSERT INTO test (colA) values (#colA)");
        insert.setStatementRewriter(new HashPrefixStatementRewriter());
        insert.bindFromMap(params);
        insert.execute();
    }

}

JPA Style ResultSetMapper

I got tired of writing mappers over and over again, so I made a JPA style ResultSetMapper.

Here's the code:
https://gist.github.com/efenderbosch/11253284

Now my mappers just look like this:

package com.company.services.partner.dao;

import com.company.jdbi.JpaMapper;
import com.company.services.partner.model.Partner;

public class PartnerMapper extends JpaMapper<Partner> {
    // no-op
}

Then I annotate my models with the standard JPA @column notation. Done.

The JdbcUtil methods in the gist are all similar to this:

    public static Double getDouble(ResultSet r, String columnLabel) throws SQLException {
        double d = r.getDouble(columnLabel);
        return r.wasNull() ? null : d;
    }

You can use the code if you want, or if you have suggestions I can clean it up and submit a pull request.

Support Inheritance with SqlObject Api Return Types

I have different kind of Users and I would like to use a common select for all of them. It seems that API does not support this.

   @SingleValueResult
   @SqlQuery("SELECT * FROM <type> limit 1")
   public <T extends User> Optional<T> select(@Define("type") Class<T> type);

or

   @MapResultAsBean
   @SqlQuery("SELECT * FROM <type> limit 1")
   public  <T extends User> T select(@Define("type") Class<T> type);

Is there any way to do it?

Add support for composite types in @BindBean

It would be nice if composite classes would be supported using the @BindBean annotation.

For example:

select * from entity where field = :bean.compositeAttribute.id

I would be very convenient if JDBI would be able to traverse the object's relationships and bind the result to the query.

Make @SqlUpdate able to return the generated keys

As I checked, the Update does have a method executeAndReturnGeneratedKeys to return the insert statement's generated keys. But when I use the SqlObject api @SqlUpdate, it only returns the updated rows count. What about to add a property in @SqlUpdate to tell the UpdateHandler to return the generated keys?

Memory leak at ClasspathStatementLocator

https://github.com/brianm/jdbi/blob/master/src/main/java/org/skife/jdbi/v2/ClasspathStatementLocator.java#L85

In case of common SQL string, this string will be placed in global cache. There is no need for it, and also for applications, that uses many different requests it causes OOM Error.

JDBI throws MismatchedTokenException(-1!=39) encountering a quote (') inside comment

Affects: JDBI 2.49

The culprit is: /* in warehouse's time zone */ in the query, changing it to /* in warehouse time zone */ makes JDBI happy.

Stacktrace:

Caused by: org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception parsing for named parameter replacement [statement:"null", located:"null", rewritten:"null", arguments:null]
    org.skife.jdbi.v2.DBI.withHandle(DBI.java:277) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.DBI.inTransaction(DBI.java:299) ~[jdbi-2.49.jar:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository.createQtyTable(JournalPerpetualInventoryRepository.java:220) ~[classes/:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository.createMissingTables(JournalPerpetualInventoryRepository.java:256) ~[classes/:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository.initTables(JournalPerpetualInventoryRepository.java:196) ~[classes/:na]
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)(NativeMethodAccessorImpl.java) ~[na:1.7.0_25]
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_25]
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_25]
    java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_25]
    org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:344) ~[spring-beans-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:295) ~[spring-beans-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:130) ~[spring-beans-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    ... 65 more
Caused by: org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception parsing for named parameter replacement [statement:"null", located:"null", rewritten:"null", arguments:null]
    org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.rewrite(ColonPrefixNamedParamStatementRewriter.java:60) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.Batch.execute(Batch.java:107) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.Script.execute(Script.java:72) ~[jdbi-2.49.jar:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository.executeScript(JournalPerpetualInventoryRepository.java:288) ~[classes/:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository.access$1(JournalPerpetualInventoryRepository.java:282) ~[classes/:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository$1.inTransaction(JournalPerpetualInventoryRepository.java:233) ~[classes/:na]
    id.co.bippo.inventory.perpetual.JournalPerpetualInventoryRepository$1.inTransaction(JournalPerpetualInventoryRepository.java:1) ~[classes/:na]
    org.skife.jdbi.v2.tweak.transactions.LocalTransactionHandler.inTransaction(LocalTransactionHandler.java:193) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.BasicHandle.inTransaction(BasicHandle.java:309) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.DBI$4.withHandle(DBI.java:302) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.DBI.withHandle(DBI.java:274) ~[jdbi-2.49.jar:na]
    ... 76 more
Caused by: java.lang.IllegalArgumentException: MismatchedTokenException(-1!=39)
    org.skife.jdbi.rewriter.colon.ColonStatementLexer.reportError(ColonStatementLexer.java:24) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.org.antlr.runtime.Lexer.nextToken(Lexer.java:98) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.parseString(ColonPrefixNamedParamStatementRewriter.java:93) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.rewrite(ColonPrefixNamedParamStatementRewriter.java:56) ~[jdbi-2.49.jar:na]
    ... 86 more
Caused by: org.skife.jdbi.org.antlr.runtime.MismatchedTokenException
    org.skife.jdbi.org.antlr.runtime.Lexer.match(Lexer.java:188) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.rewriter.colon.ColonStatementLexer.mQUOTED_TEXT(ColonStatementLexer.java:269) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.rewriter.colon.ColonStatementLexer.mTokens(ColonStatementLexer.java:557) ~[jdbi-2.49.jar:na]
    org.skife.jdbi.org.antlr.runtime.Lexer.nextToken(Lexer.java:84) ~[jdbi-2.49.jar:na]
    ... 88 more

Complete query as follows:

CREATE TABLE jnlinv_qty (
    qty_id bigserial primary key,
    /* Built-in dimensions */
    movement_time timestamptz NOT NULL,
    warehouse_id varchar(255) NOT NULL REFERENCES jnlinv_warehouse(warehouse_id),
    storage_bin_id varchar(255) NOT NULL REFERENCES jnlinv_storage_bin(storage_bin_id),
    product_id varchar(255) NOT NULL REFERENCES jnlinv_product(product_id),
    /* Principals */
    base_color varchar(255) NOT NULL REFERENCES jnlinv_base_color(base_color),
    base_size varchar(255) NOT NULL REFERENCES jnlinv_base_size(base_size),

    /* Measures */
    qty_on_hand numeric(24, 8) NOT NULL,
    qty_reserved numeric(24, 8) NOT NULL,
    qty_picked numeric(24, 8) NOT NULL,
    qty_available numeric(24, 8) NOT NULL,
    qty_produced numeric(24, 8) NOT NULL,
    /* Convenience for mutation report */
    qty_on_hand_delta numeric(24,8),         
    qty_on_hand_update numeric(24,8),        
    qty_reserved_delta numeric(24,8),        
    qty_picked_delta numeric(24,8),          
    /* Informational columns */
    physical_inventory_id varchar(36),
    stock_reservation_id varchar(36),
    goods_receipt_id varchar(36),
    return_to_vendor_shipment_id varchar(36),
    goods_shipment_id varchar(36),
    return_material_receipt_id varchar(36),
    movement_local_time timestamp NOT NULL, /* in warehouse's time zone */
    /* Administrative columns */
    creation_time timestamptz NOT NULL default 'now()',
    /* Key (combined dimensions) must be unique */
    CONSTRAINT jnlinv_dimensions_key UNIQUE (movement_time, warehouse_id, storage_bin_id, product_id, 
                                                                                                   base_color, 
                                                                                                   base_size)
);

CREATE INDEX idx_jnlinv_qty_movement_time_id ON jnlinv_qty (movement_time);
CREATE INDEX idx_jnlinv_qty_movement_local_time_id ON jnlinv_qty (movement_local_time);
/* Built-in dimensions */
CREATE INDEX idx_jnlinv_qty_warehouse_id ON jnlinv_qty (warehouse_id);
CREATE INDEX idx_jnlinv_qty_storage_bin_id ON jnlinv_qty (storage_bin_id);
CREATE INDEX idx_jnlinv_qty_product_id ON jnlinv_qty (product_id);
/* Principals */
CREATE INDEX idx_jnlinv_qty_base_color ON jnlinv_qty (base_color);
CREATE INDEX idx_jnlinv_qty_base_size ON jnlinv_qty (base_size);

Weird code generation issue

Just ugpraded from 2.34 to 2.36.1, and I'm seeing this error in Dropwizard's unit tests:

org.skife.jdbi.net.sf.cglib.core.CodeGenerationException: java.lang.reflect.InvocationTargetException-->null
    at org.skife.jdbi.net.sf.cglib.core.AbstractClassGenerator.create(AbstractClassGenerator.java:237)
    at org.skife.jdbi.net.sf.cglib.proxy.Enhancer.createHelper(Enhancer.java:377)
    at org.skife.jdbi.net.sf.cglib.proxy.Enhancer.create(Enhancer.java:285)
    at org.skife.jdbi.v2.sqlobject.SqlObject.buildSqlObject(SqlObject.java:61)
    at org.skife.jdbi.v2.sqlobject.SqlObjectBuilder.open(SqlObjectBuilder.java:54)
    at org.skife.jdbi.v2.DBI.open(DBI.java:301)
    at com.yammer.dropwizard.db.tests.DatabaseTest.sqlObjectsCanAcceptOptionalParams(DatabaseTest.java:97)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:76)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:195)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:63)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at org.skife.jdbi.net.sf.cglib.core.ReflectUtils.defineClass(ReflectUtils.java:384)
    at org.skife.jdbi.net.sf.cglib.core.AbstractClassGenerator.create(AbstractClassGenerator.java:219)
    ... 34 more
Caused by: java.lang.ClassFormatError: Duplicate method name&signature in class file org/skife/jdbi/v2/sqlobject/CloseInternal$$EnhancerByCGLIB$$d17a2f8a
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClassCond(ClassLoader.java:631)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:615)
    ... 40 more

Added support for mapping java.util.Calendar to BeanMapper

Some user map database timestamp column to java.util.Calendar ,
I Hope iDBI Added support for mapping java.util.Calendar to BeanMapper!

Positional arguments for SQL Object Queries

Hi,

I am finally about to implement the support for the positional arguments as promised in this topic. Are you OK with the suggested solution like this:

void insertIntoSomething(@Pos(0) String param1, @Pos(1) int param2, @Pos(2) int param3)

Thanks.

Problem with bindings and PostgreSQL casting

There doesn't seem to be a way to use PostgreSQL casting, which uses ::, in SQL statements because the query parser gobbles them up. So something like this fails:

create table sample (created timestamp not null default timezone('UTC'::text, now()));

It gets parsed to:

create table sample (created timestamp not null default timezone('UTC'?, now()));

which is wrong.

SQLStatement.bind(int,Long) exception

Using SQLStatement.bind(int, Long) throws an IllegalStateException.

It looks like the code in SQLStatement.java calls Foreman.waffle() with "int.class" instead of "Long.class".

New feature Request: Add supporting Stored Procedure which returns ResultSets (one and multiple)

JDBC callable statements can return multiple resultsets.
Currently in the jDBI doesn't seam to support retrieving result data from callable statements
(only output parameters!).

Annotation based Mapper

Is there any annotation based mapper?
I didn't find any so I developed a simple one and would be very happy to share it if there isn't any other.

Make GetGeneratedKeys able to return the generated keys base on column name

Due to how PrimitivesMapperFactory initialized the Mappers for primitive types, you will get the right generated key only if it's the first column of the result set. For example

some_fk	some_other_id	id (sequence)
123	123	1
123	567	2

won't work and will return 123 after consequent inserts.

Pound sign in column names causes NoViableAltException

When working with a legacy database that contains '#' and '$' in the column names, my program blows up with a NoViableAltException when running a query.

org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception parsing for named parameter replacement [statement:"select ranMemb# from Corevision.dbo.Bananas where ranDude# = :id", located:"select ranMemb# from Corevision.dbo.Bananas where ranDude# = :id", rewritten:"null", arguments:{ positional:{}, named:{id:'0727c035ae6d11e29a5c0000ac10e364'}, finder:[]}]
at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.rewrite(ColonPrefixNamedParamStatementRewriter.java:64)
at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1297)
at org.skife.jdbi.v2.Query.fold(Query.java:167)
at org.skife.jdbi.v2.Query.first(Query.java:262)
at org.skife.jdbi.v2.Query.first(Query.java:254)
at org.skife.jdbi.v2.sqlobject.ResultReturnThing$SingleValueResultReturnThing.result(ResultReturnThing.java:111)
at org.skife.jdbi.v2.sqlobject.ResultReturnThing.map(ResultReturnThing.java:47)
at org.skife.jdbi.v2.sqlobject.QueryHandler.invoke(QueryHandler.java:44)
at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:174)
at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:75)
at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$7fefb45.findAgreement()
at com.abcfinancial.fullsync.agreement.services.AgreementService.processAgreement(AgreementService.java:80)
at com.abcfinancial.fullsync.agreement.services.AgreementService.processAgreements(AgreementService.java:56)
at com.abcfinancial.fullsync.Scheduler.lambda$scheduleAgreementsProcessing$41557bed$1(Scheduler.java:39)
at com.abcfinancial.fullsync.Scheduler$$Lambda$2/1741345807.run(Unknown Source)
at co.paralleluniverse.strands.Strand$SuspendableRunnableRunnable.run(Strand.java:817)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: NoViableAltException('#'@[])
at org.skife.jdbi.rewriter.colon.ColonStatementLexer.reportError(ColonStatementLexer.java:25)
at org.skife.jdbi.org.antlr.runtime.Lexer.nextToken(Lexer.java:107)
at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.parseString(ColonPrefixNamedParamStatementRewriter.java:99)
at org.skife.jdbi.v2.ColonPrefixNamedParamStatementRewriter.rewrite(ColonPrefixNamedParamStatementRewriter.java:60)
... 16 common frames omitted
Caused by: org.skife.jdbi.org.antlr.runtime.NoViableAltException: null
at org.skife.jdbi.rewriter.colon.ColonStatementLexer.mTokens(ColonStatementLexer.java:767)
at org.skife.jdbi.org.antlr.runtime.Lexer.nextToken(Lexer.java:89)
... 18 common frames omitted

Anomolous behavior with JRuby

JRuby interacting with jDBI very strangely. The first invokation of DBI.withHandle works great. The second time it's called, however, I get the following exception. (Report continues after exception listing)

Querying the table failed -- attempting to create a new table and pre-populate it.
org/skife/jdbi/v2/DBI.java:229:in withHandle': org.skife.jdbi.v2.exceptions.CallbackFailedException: org.jruby.exceptions.RaiseException: wrong # of arguments(1 for 2) (NativeException) from sun/reflect/NativeMethodAccessorImpl.java:-2:ininvoke0'
from sun/reflect/NativeMethodAccessorImpl.java:39:in invoke' from sun/reflect/DelegatingMethodAccessorImpl.java:25:ininvoke'
from java/lang/reflect/Method.java:597:in invoke' from org/jruby/javasupport/JavaMethod.java:298:ininvokeWithExceptionHandling'
from org/jruby/javasupport/JavaMethod.java:259:in invoke' from org/jruby/java/invokers/InstanceMethodInvoker.java:95:incall'
from org/jruby/runtime/callsite/CachingCallSite.java:263:in cacheAndCall' ... 15 levels... from org/jruby/Main.java:110:inrun'
from org/jruby/Main.java:94:in `main'
from test.rb:31
Complete Java stackTrace
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.jruby.exceptions.RaiseException: wrong # of arguments(1 for 2)
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:229)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jruby.javasupport.JavaMethod.invokeWithExceptionHandling(JavaMethod.java:298)
at org.jruby.javasupport.JavaMethod.invoke(JavaMethod.java:259)
at org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:95)
at org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:263)
at org.jruby.runtime.callsite.CachingCallSite.callBlock(CachingCallSite.java:81)
at org.jruby.runtime.callsite.CachingCallSite.callIter(CachingCallSite.java:96)
at test.method__0$RUBY$hooey(test.rb:16)
at testInvokermethod__0$RUBY$hooeyFixed1.call(test#hooey)
at testInvokermethod__0$RUBY$hooeyFixed1.call(test#hooey)
at org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:273)
at org.jruby.runtime.callsite.CachingCallSite.call(CachingCallSite.java:112)
at test.rescue_line_27_2(test.rb:31)
at test.rescue_1$RUBY$rescue___1(test.rb:25)
at test.__file(test.rb:24)
at test.load(test.rb)
at org.jruby.Ruby.runScript(Ruby.java:577)
at org.jruby.Ruby.runNormally(Ruby.java:480)
at org.jruby.Ruby.runFromMain(Ruby.java:354)
at org.jruby.Main.run(Main.java:229)
at org.jruby.Main.run(Main.java:110)
at org.jruby.Main.main(Main.java:94)
Caused by: org.jruby.exceptions.RaiseException: wrong # of arguments(1 for 2)
at (unknown).initialize(test.rb:17)
at Object.hooey(test.rb:16)
at Object.hooey(test.rb:31)
at (unknown).(unknown)(:1)

I have tried to move the code outside of the begin/rescue/end block to see if any existing context would affect it. I've also attempted to re-instantiate a completely new DBI instance. Nothing seems to work. It's as though there exists a global lock on the ability to invoke withHandle, and that lock never gets released. Moreover, attempting to manually pass a second argument invariably gives me a type error.

Here is my source code to reproduce the issue:

require 'java'
require 'ojdbc6-11.1.0.7.0.jar'
require 'jdbi-2.6.0.jar'

import org.skife.jdbi.v2.DBI
import org.skife.jdbi.v2.util.StringMapper
import org.skife.jdbi.v2.exceptions.CallbackFailedException

dbUrl = "jdbc:oracle:thin://@myDB.myHost.net:1521/myDB.myHost.net"
dbName = "anyUserNameYouLike"
dbPassword = "anyPasswordYouLike"

dbi = DBI.new(dbUrl, dbName, dbPassword)
begin
dbi.withHandle do |h|
result = h.createQuery("select version from SCHEMA_VERSION").map(StringMapper::FIRST).first()
end
rescue CallbackFailedException => e
puts "Querying the table failed -- attempting to create a new table and pre-populate it."

dbi.withHandle do |h|
    h.execute("create table SCHEMA_VERSION");
    h.execute("insert into SCHEMA_VERSION values ('1.2.3.4')");
    result = "1.2.3.4"
end

end

puts "Result: #{result}"
puts "Done."

Document @Transaction annotation

It would be great to have some documentation on the @transaction annotation. I just lost several hours of debugging to find @transaction handling was behaving exceedingly unintuitively.

My end goal was to hand-write a dynamic query but join an existing wrapping transaction started explicitly by a unit test:

@Before
h.begin()

@After
h.rollback

@Test
userDao.getUserMoreComplicated(...)

public interface MyDAO implements GetHandle {
  @SqlQuery("thought this was transactional...")
  @Transaction // nope.
  public User getUser(@Bind("id") String id);

  @Transaction // does not join existing transaction either...
  public User getUserMoreComplicated(String arg1, Integer arg2, etc.) {
    return withHandle(new HandleCallback<User>() {
      public User withHandle(Handle handle) throws Exception
      {
        return  handle.createQuery("my super duper dynamically constructed query").map(User.class).list(); 
      }
    }
  }
}

What I found was that getUserMoreComplicated was always committing, while getUser was apparently correctly rolling back (not committing). Similarly, explicitly using handle.withTransaction resulted in commit. And leaving them all off naturally resulted in no transactionn and no rollback if no wrapping transaction was explicitly set up.

There are several things wrong here. To start getUser was not working as I assumed. It turns out that @Transaction is mutually exclusive with every other annotation. See below in SqlObject:

// these else ifs are all mutually exclusive...
for (final ResolvedMethod method : d.getMemberMethods()) {
    final Method raw_method = method.getRawMember();

    if (raw_method.isAnnotationPresent(SqlQuery.class)) {
        handlers.put(raw_method, new QueryHandler(sqlObjectType, method, ResultReturnThing.forType(method)));
    }
    else if (raw_method.isAnnotationPresent(SqlUpdate.class)) {
        handlers.put(raw_method, new UpdateHandler(sqlObjectType, method));
    }
    else if (raw_method.isAnnotationPresent(SqlBatch.class)) {
        handlers.put(raw_method, new BatchHandler(sqlObjectType, method));
    }
    else if (raw_method.isAnnotationPresent(SqlCall.class)) {
        handlers.put(raw_method, new CallHandler(sqlObjectType, method));
    }
    else if(raw_method.isAnnotationPresent(CreateSqlObject.class)) {
        handlers.put(raw_method, new CreateSqlObjectHandler(raw_method.getReturnType()));
     }
     else if (method.getName().equals("close") && method.getRawMember().getParameterTypes().length == 0) {
         handlers.put(raw_method, new CloseHandler());
     }
     else if (raw_method.isAnnotationPresent(Transaction.class)) {
         handlers.put(raw_method, new PassThroughTransactionHandler(raw_method, raw_method.getAnnotation(Transaction.class)));
     }
     else if (mixinHandlers.containsKey(raw_method)) {
         handlers.put(raw_method, mixinHandlers.get(raw_method));
     }
     else {
         handlers.put(raw_method, new PassThroughHandler(raw_method));
     }
}

You can't have @Transaction @SqlUpdate or @Transaction @SqlQuery, it just doesn't work like Spring transactions. Secondly, this explains why getUserMoreComplicated was always committing. @Transaction is the only annotation present on that method, so @Transaction functionality is enabled, and what this actually does is unilaterally commit:

 try {
     handle.begin();
     returnValue = callback.inTransaction(handle, status);
     if (!failed.get()) {
         handle.commit();
     }
}

Could it be I missed critical docs on @Transaction? At the very least its scope of functionality seems very counter-intuitive to me, and probably anybody coming from a declarative transaction background. Is the intent really for @Transaction to not apply to SQL Object methods, and for it to forcefully commit? If so, is there any way at all to write reusable code that joins an existing transaction (e.g. unit tests) or starts a new one on demand (production)?

Would it be better if SqlObject mixin handlers applied type based customizers?

I came up with this because I thought @UseStringTemplate3StatementLocator on an SqlObject also affected calls to getHandle() , but currently mixin handlers doesn't apply any type based customizers.

Unless there is a good reason not to do this, I can certainly contribute.

@BindingAnnotation is package private

@BindingAnnotation is package private and so cannot be used to create custom bindings. There is a work around through implementing the Binder interface, but this is less concise than declaring a new annotation type.

SqlObject proxy doesn't support equals(Object)

SqlObjects created by DBI.onDemand do not support "equals(Object)", and will instead throw NPE at SqlObject line 109. Intellij also complains about NPE on "toString" when stepping through the debugger, and I suspect hashCode is broken as well.

org.skife.jdbi.rewriter package missing

Generic CRUD DAO

@brianm Could you suggest any idea to create a generic dao? The only idea came to my mind is creating a custom statement writer for this special case which is trying to generate a mapping between java and sql codes based on some strategies as BeanMapper.

ResultSetMapper for List<Map<String, Object>> for SqlQuery

Hi,
I'm trying to create mapper for regular SqlQuery in the same manner like handle.select that is returning
List of Map String, Object

Code example:

public interface SomeQueries
{
    @SqlQuery("select * from SOMETABLE")
    @Mapper(MapMappper.class)
    public List<Map<String, Object>> sqlQuery();
}

public class MapMappper implements ResultSetMapper<List<Map<String, Object>>> {

    @Override
    public List<Map<String, Object>> map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        System.out.println(r);
        Map<String, Object> obj = new HashMap<String, Object>();
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        list.add(obj);
        return list;
    }
}

But I'm getting exception: unable to access mapper

Make Argument implementors public

Why not making classes like TimestampArgument etc public?
I use DBI for several years now and every time I update it I have to make it manually?

"Query did not have a result set, perhaps you meant update?"

from https://groups.google.com/forum/?fromgroups=#!topic/jdbi/4Y-pnEyn_0s

I'm trying to lookup person by their name such as:

Long personId = dao.findPersonByName("name")

and my dao looks like this:

@SqlQuery
Long findPersonByName(@Bind("name") String name);

however, whenever no records are found, i get exception crash:
"Query did not have a result set, perhaps you meant update?"

Multiple SqlBatch annotations

Hi, it would be convenient if we could assign more than one SqlBatch annotations to a method of a DAO class.

Example:

    @SqlBatch("CREATE TABLE IF NOT EXISTS <table> (`id` int(11), `name` varchar(16))")
    @SqlBatch("ALTER TABLE <table> ADD COLUMN `id` int(11)")
    @SqlBatch("ALTER TABLE <table> ADD COLUMN `name` varchar(16)")
    void createTable(@Define("table") tableName);

I need access to Foreman or ArgumentFactory list...

I'm building some automatic mapper for my beans, using custom annotations.
And i want reuse this method from Foreman:

class Foreman
...
    Argument waffle(Class expectedType, Object it, StatementContext ctx)
    {
        for (int i = factories.size() - 1; i >= 0; i--) {
            ArgumentFactory factory = factories.get(i);
            if (factory.accepts(expectedType, it, ctx)) {
                return factory.build(expectedType, it, ctx);
            }
        }
        throw new IllegalStateException("Unbindable argument passed: " + String.valueOf(it));
    }
...

I thought about using NamedArgumentFinder, but I do not have the "privilege" that BeanPropertyArguments has, because he gets Foreman in the constructor.

Today, i need to make some ugly hacking.

SLF4JLog incorrectly depends on Log4J

I dont have Log4J in my app, so i cant use SLF4JLog because this class depends on org.apache.log4j.Level

ClasspathStatementLocator.locate Doesn't Always Detect SQL Comments

In cases where a comment line starts with whitespace, ClasspathStatementLocator.locate via won't filter these out due to isWhitespace only checking the starting characters.

Additionally, any line ending in a SQL comment (e.g. SELECT ... -- something\n) will also not get filtered correctly, breaking the remaining part of the SQL script due to the wrapping of newlines.

Possible changes:

1. Document handling of whitespace and comments in SQL files when createScript is used.
2. Allow whitespace before comments in isWhitespace
3. Handle SQL lines ending with a comment
4. Remove stripping of newlines

Dynamic Sql query

I'm looking for answer to the same topic created at stackoverflow:
http://stackoverflow.com/questions/18642862/how-do-i-create-a-dynamic-sql-query-at-runtime-using-jdbis-sql-object-api

Is it possible in some way to make dynamic sql queries in interfaces?
For example I want to create dynamic Sql like this:

public interface myDAO
{
    @SqlQuery("select * from TABLE")
    @Mapper(MapMapper.class)
    public List<Map<String, Object>> sqlQuery();

    @SqlUpdate("update TABLE set :property = :value where id = :id")
    int update(@Bind("property") String property, @Bind("value") String value, @Bind("id") int id);
}

Upgrade to findbugs jsr305 2.0.3 + make jsr305 dependency optional

Replace com.google.code.findbugs:annotations:2.0.1 with:

<dependency>
    <groupId>com.google.code.findbugs</groupId>
    <artifactId>jsr305</artifactId>
    <version>2.0.3</version>
    <optional>true</optional>
</dependency>

It should also be optional.

(On a related note: jsr305, not annotations, is also the dependency used by Guava 17.)

Use readOnly connections when possible

Specifically, if a connection is obtained automatically via onDemand objects, and callers are using the SqlObject API, then for SqlQuery calls, JDBI could call setReadOnly(true) on the underlying connection, which can hint the DB to use some optimizations.

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly(boolean)

Add withHandle utility method to GetHandle sqlobject mixin

That way, it's very easy to execute custom queries using a sqlobject instance:

DBI dbi = new DBI("jdbc:h2:mem:test");

MyDAO dao = dbi.open(MyDAO.class);
dao.withHandle(new HandleCallback<Void>()
{
  public List<Integer> withHandle(Handle handle) throws Exception
  {
    return handle.createQuery("select id from users").list();
  }
});

Query generation by method signatures

It would be great to have on demand query generation by method signatures. There is an already implementation for Spring Data project for JPA. Check 2.2.2. Query creation section please.

What do you think about this?

ClasspathStatementLocator.looksLikeSql support merge

ClasspathStatementLocator.looksLikeSql should recognise that statements starting "merge" are valid SQL statements as of SQL:2003

Regular expressions in jDBI

Postgres offers some features for regular expression. So,

SELECT title FROM books WHERE title ~* 'The';

is a valid expression in Postgres. But when I use this expression in my Java code with jDBI, I get an exception (version 2.7.1, class ColonPrefixNamedParamStatementRewriter.java, line 83, exception: "Exception parsing for named parameter replacement"). Are regular expressions in queries not supported or did I make an mistake? Are there any workarounds?

Support snake case/camel case together.

As a Jdbi 3 request:

Please support configuring JDBI to utilize naming strategy for snake_case naming.

Automatically bind SQL Object arguments using parameter names

This seems rather redundant:

void insert(@Bind("id") int id, @Bind("name") String name);

I would like an option to use the method parameter names by default:

void insert(int id, String name);

This should be rather easy to do using ParaNamer. Sure, it breaks if you rename your parameters, but that seems like a reasonable tradeoff in an internal interface used solely to reduce boilerplate.

BeanMapper: unable to access mapper

I'm starting with JDBI...

I have mapper like below, but get "unable to access mapper" error. When i implements ResultSetMapper, everything works. Am I doing something wrong?

public class UserMapper extends BeanMapper<User>{

    public UserMapper(Class<User> type) {
        super(type);
    }

}

Base64 lookupBase64Alphabet broken

In https://github.com/brianm/jdbi/blob/8b5e24f0b58b37cace131115ac604afec941a7cb/src/main/java/org/skife/jdbi/v2/sqlobject/stringtemplate/Base64.java starting at line 45:

    private static byte [] lookUpBase64Alphabet = new byte[LOOKUPLENGTH];

    static {

        /* snip */

        base64Alphabet['+']  = 62;
        base64Alphabet['/']  = 63;

        for (int i = 0; i<=25; i++ ) {
            lookUpBase64Alphabet[i] = (byte) ('A'+i );
        }

        for (int i = 26,  j = 0; i<=51; i++, j++ ) {
            lookUpBase64Alphabet[i] = (byte) ('a'+ j );
        }

        for (int i = 52,  j = 0; i<=61; i++, j++ ) {
            lookUpBase64Alphabet[i] = (byte) ('0' + j );
        }

    }

LOOKUP_LENGTH is set to 63 - making the array one short of the full 64 it needs. Additionally, index 62 isn't set. This causes the following exception on rare occasions:

! java.lang.ArrayIndexOutOfBoundsException: 63
! at org.skife.jdbi.v2.sqlobject.stringtemplate.Base64.encode(Base64.java:143) ~[jdbi-2.55.jar:2.55]
! at org.skife.jdbi.v2.sqlobject.stringtemplate.StringTemplate3StatementLocator.locate(StringTemplate3StatementLocator.java:254) ~[jdbi-2.55.jar:2.55]
! at org.skife.jdbi.v2.SQLStatement.wrapLookup(SQLStatement.java:1286) ~[jdbi-2.55.jar:2.55]

I've left the two base64Alphabet lines in place above because I found it a little funny that the values were set in that array, but not the lookup array.

NPE in BindBeanFactory when no getter

When using @BindBean and a POJO that has setters with no corresponding getters, I get NullPointerExceptions. Trace below. I think what's going on is that BindBeanFactory loops through the property descriptors and doesn't check for null in the result of prop.getReadMethod(). But per PropertyDescriptor JavaDoc, getReadMethod "may return null if the property can't be read." It looks like it simply skipping over any null results would achieve the desired result.

java.lang.IllegalStateException: unable to bind bean properties
    at org.skife.jdbi.v2.sqlobject.BindBeanFactory$1.bind(BindBeanFactory.java:34)
    at org.skife.jdbi.v2.sqlobject.BindBeanFactory$1.bind(BindBeanFactory.java:15)
    at org.skife.jdbi.v2.sqlobject.Bindifier.bind(Bindifier.java:38)
    at org.skife.jdbi.v2.sqlobject.CustomizingStatementHandler.applyBinders(CustomizingStatementHandler.java:108)
    at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:56)
    at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:147)
    at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:60)
    at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$a7db5f40.record(<generated>)
...
Caused by: java.lang.NullPointerException
    at org.skife.jdbi.v2.sqlobject.BindBeanFactory$1.bind(BindBeanFactory.java:30)
    ... 40 more

Confusing exception when using SqlUpdate instead of SqlQuery

If you accidentally use @SqlUpdate instead of @SqlQuery, you get a confusing ClassCastException:

Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.util.List
    at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$f05b82e8.getViews(<generated>)

Example incorrect usage:

@SqlUpdate("select name from something")
List<String> getNames();

OutParameters getters incorrectly throw IllegalArgumentException when OUT value is null

The index and field-name based values are correctly placed in OutParameters.map, but the retrieval methods (getString, getTimestamp, etc.) throw if the value returned from the map is null.

For example: OutParameters.getString

public String getString(String name) {
        Object obj = map.get(name);
        if (obj != null) {
            return obj.toString();
        }
        throw new IllegalArgumentException(String.format("Parameter %s does not exist", name));
    }

I suggest this should be changed to something like:

public String getString(String name) {

        if (!map.containsKey(name)) {
             throw new IllegalArgumentException(String.format("Parameter %s does not exist", name)); 
        }

        Object obj = map.get(name);
        if (obj == null) {
             return null;
        }

        return obj.toString();
    }

Am I missing something?

If this is a reasonable approach, I can submit a pull request. I also have a couple additional tests for the Callable class that demonstrate the issue (and the fix.)