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.
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.
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.
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).
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 email Brian McCallister if you have any problems, or want to submit fixes or patches!
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:
|
JDBC Connection URL, ie jdbc:derby:my_database,
required
|
|
JDBC Driver class name, ie org.apache.derby.jdbc.EmbeddedDriver,
required
|
|
Username to be used when obtaining connections from the database, optional |
|
Password to be used when obtaining connections from the database, optional |
|
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.
|
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!