jDBI: SQL Made Easy

jDBI is designed to provide convenient tabular data access in Java(tm). It uses the Java collections framework for query results, provides a convenient means of externalizing sql statements, and provides named parameter support for any database being used.

Current Stable Release

Current Development Release

Basics

The entry point for the library is the org.skife.jdbi.DBI class. The DBI class is used to provide Handle instances, which are analogous to a JDBC Connection. Much like a Connection, a Handle will hold a connection to the database while it is open. Handle instances must be explicitely closed by the client to return, or close, the connection.

The DBI can be used in one of two ways. The first is to use its static methods to directly open Handle instances. The second is to instantiate a DBI instance and use the instance to retrieve Handle instances. The second approach, instantiating the DBI has the advantage of allowing all of the Handle instances created by it to share a named statement cache, which can be convenient and improve performance.

The Handle provides for two styles of usage. The first is a traditional statement execution and result set model. Usage here would look like:

    List rows = handle.query("select name from foo where name like 'Brian@'");
    for (Iterator i = rows.iterator(); i.hasNext();) {
        Map row = (Map)i.next();
        System.out.println(row.get("name"));
    }

Alternately, a callback style interface can be used, where the above snippet would be expressed as:

    handle.query("select name from foo where like 'Brian@'", new RowCallback() {
        public void eachRow(Handle handle, Map row) {
            System.out.println(row.get("name"));
        }
    });

In the callback, the query is executed in a single transaction, and throwing an exception from the callback will stop iteration, and abort the transaction. Speaking of transactions, the same type of callback or imperitive API is available for working with them, as well.

Queries

The Handle provides three means of identifying SQL statements. The first is to provide the SQL directly to the query call:

    List rows = handle.query("select name from foo where name like 'Brian@'");

The second is to name queries before calling them, and then call them by name:

    handle.name("wombats!", "select name from foo where name like 'Brian@'");
    List rows = handle.query("wombats!);

The final means is to use externalized statements:

    List rows = handle.query("squirrel");

Using an externalized statement requires having a text file with the name of the query, appended with .sql (squirrel.sql in this case), on the classpath. The query will be retrieved via ClassLoader#getResource. The query will only be loaded from the classpath once (usually, see the named statement cache docs) and stored as a named statement.

In addition to the implicit external statement load, you can explicit load an external statement via:

    handle.load("jackals");

The default behavior for finding queries is to look for a named query matching the string, then attempt to parse it as SQL, then to look for an external sql file.

Parameterized Queries

jDBI support positional and named parameterized queries. Parameterized statements can be described in the form select id, name from bar where id = ? or select id, name from bar where id = :id. The advantage to using the second form is that you can then use named parameters to execute the statement, as well as positional.

Executing a statement with named parameters requires passing in a map with the name ("id" in the above example) as a key, and the value to substitute as the matching value. Calling a positionally parameterized query involves passing in an object array, or collection, of arguments which will bind the elements to the statement in iteration order. There are convenience methods for common cases, as well as a convenient class for building maps of named arguments (Args).

Named Statement Cache

If an instance of DBI is used to obtain Handle instances, all handles will share a named statement cache. This allows for only having to name, or load, queries once across all Handle instances obtained from a DBI. This can make a significant performance difference if externalized statements are used (no searching the classpath for each Handle), otherwise it is pretty much just a convenience.

The DBI instance provides convenience methods for manipulating the named statement cache.

You can join the jdbi-users or jdbi-dev mailing lists if you have any questions. We're happy to help, and try hard to accomodate feature requests!

Configuration

jDBI supports many configuration options via the various org.skife.jdbi.DBI constructors. The no-arg constuctor is worth mentioning, however, as it attempts to figure out how to best configure itself via looking for relevant properties.

It first looks for an org.skife.jdbi.properties-file system property which represents a properties file to be loaded via the classpath. If that is not found, it looks for jdbi.properties, then jdbc.properties, then dbi.properties, then finally database.properties on the classpath. It will use the first it finds and stop looking for others once it finds one.

Once a suitable configuration properties file has been loaded, jDBI will look for various properties used to configure it. There are multiple possible values for each logical property, and the order of preference if it should find multiple is the order listed here:

  • jdbi.url
  • jdbc.url
  • connection.string
JDBC Connection URL, ie jdbc:derby:my_database, required
  • jdbi.driver
  • jdbc.driver
  • driver
  • drive
JDBC Driver class name, ie org.apache.derby.jdbc.EmbeddedDriver, required
  • jdbi.username
  • jdbi.user
  • jdbc.username
  • jdbc.user
  • username
  • user
Username to be used when obtaining connections from the database, optional
  • jdbi.password
  • jdbi.pass
  • jdbc.password
  • jdbc.pass
  • password
  • pass
Password to be used when obtaining connections from the database, optional
  • jdbi.transaction-handler
  • jdbc.transaction-handler
  • transaction-handler
OPTIONAL class name of a TransactionHandler which should be used to override default transaction handling. Must supply a no-arg constructor. Optional.
  • jdbi.statement-locator
  • jdbc.statement-locator
  • statement-locator
OPTIONAL class name of a StatementLocator which should be used to override default (cached from classpath) external sql statement locating. Optional.
  • jdbi.handle-decorator-builder
  • jdbc.handle-decorator-builder
  • handle-decorator-builder
Unstable Feature class name of a HandleDecoratorBuilder to be used to decorate Handle instances obtained from the DBI instance instantiated. This feature is functionally stable, but the specific api may change somewhat while it remains offically unstable. Please read the notes regarding the org.skife.jdbi.unstable package before using this. Optional.

Unstable Features

Various features or interfaces may be exposed via packages in the the org.skife.jdbi.unstable package. These features or interfaces will be functionally stable (ie, should be bug free, and behave as expected, etc), but their exact interfaces, or exact behavior, is subject to change between releases because they are still being hammered out. The features are exposed to be used, and may certainly be used, but if you do use them be aware that you may need to make changes to the code using them between minor or bug fix releases.

If you do rely on an officially unstable feature, please let us know so that we can attempt to make sure to not cause you harm, and so that we can prioritize the feature for interface stabilization to get it out of an unstable sub-package. If it is un unstable we think it is useful, and may be using it ourselves, but are probably just not convinced that the exact API is correct yet. Feedback is much appreciated!

Resources

Old Versions