Simpler Java database access with jDBI
One of most common tasks on server-side Java development is that of interacting with relational databases. Given that support for doing this is almost as old as stable versions of Java (JDBC API was added in JDK as of Java 1.1, and drivers for most databases were quickly published), one might think this as a well-known and covered area of Java development.
But it is not really: on one hand, there are rather complex ORM
solutions that work acceptably for cases where complexity is on
object-hierarchies and where significant overhead is accepted; and on
the other hand, there is mostly just "raw" JDBC. If JDBC itself was a
good API, this would not be so bad: unfortunately, it is not. JDBC is a
victim of having been written back when apparently few knew how to write
decent Java APIs (it has distinct feel of C style); and of course it was
heavily influenced by companies not known for producing good Java APIs
or code (relational database vendors -- Oracle may well win the title of
"most crap Java shipped world-wide", and don't even get me started on
catastrophe known as MySQL).
But I digress. It is enough to know that
JDBC is a crappy API, first and foremost, and direct use should be
avoided.
Which brings us to the actual point of this blog entry: there is hope for developers who feel squeezed between intrinsic ugliness of JDBC-based code, and torturous complexity of Hibernate and similar frameworks. There is a simpler way, using library called jDBI (which I briefly mentioned a few blog entries ago).
To here is a simple tutorial for jDBI.
1. It all starts with a Handle
To do anything useful, you need to start by creating DBI instance, which basically represents the database instance (schema). This in turn allows you to create Handle, which is essentially wrapper around JDBC Connection.
DBI instances can be created from DataSource instances (commonly the case when using JNDI or connection pools), or by giving the usual connect information (user, password, JDBC URL). So we will start with something like:
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", userName, pwd); // using in-mem H2 database
DBI database = new DBI(ds);
(for more information, check out jDBI javadocs)
Now: DBI instance does allow creating Handles directly; however, it is usually more convenient to use callbacks, so that we do not have to worry about cleaning up after operation (especially not have to worry about exception cases).
2. Inquisitive Idiots vs Simple Queries
If we are just doing one-off queries, we can use DBI.withHandle(), like so:
Integer rowCount = database.withHandle(new HandleCallback<Integer>() {
public Integer withHandle(Handle h) {
return h.createQuery("select count(1) from USER").map(IntegerMapper.FIRST).first();
}});
and not to have to worry about handling of all underlying Connections, ResultSets and so on.
So what's with the map() and first() calls? Map() if for mapping first column of ResultSet rows into Integer (using one of existing standard mappers), and first() is for choosing value for first result row.
In this case latter may seem superfluous, but we could also have done something like:
List<Integer> ids = dbi.withHandle(new HandleCallback<List<Integer>>() {
public List<Integer> withHandle(Handle h) {
return h.createQuery("select ID from USER").map(IntegerMapper.FIRST).list();
}});
and so on. You get the idea.
3. S/M section: binding your parameters
Well, simple queries are simple; and similarly simple updates (INSERTs, DELETEs; basically stuff other than queries) are simple. Next common need is for parameter binding. For example, to delete a user, you can do:
final String lastName = "Foobar"; // final since it is reference from inner class
Integer deleted = database.withHandle(new HandleCallback<Integer>() {
public Integer withHandle(Handle g) {
return h.createStatement("delete from USER where LAST_NAME = ?").bind(0, lastName); // note: 0-based index
// or:
// h.createStatement("delete from USER where LAST_NAME = :lastName).bind("lastName", lastName);
}
});
You can bind both positional parameters (like in JDBC, except that indexes start at 0), or more conveniently, by name.
Parameter binding works similarly with queries; but there is even more convenient way to
3b. VIP: De Luxe Parameter Binding
Ok, so basic binding looks simple enough. But wait! There's more!
In fact, there are convenience short cuts for common CRUD operations, so that you could insert a new user like so:
h.insert("insert into USER (FIRST_NAME, LAST_NAME) values (?, ?)", firstName, lastName);
and similarly for Handle.update() (SQL update statement) and Handle.select() (for SQL select statement)
4. And even more bondage: binding the results
The other place where more convenient data binding is needed is that of handling query results. There are two main ways to do this. First one is to use explicit user-provided ("custom") converters; these are implementations of jDBI ResultSetMapper:
static class UserMapper implements ResultSetMapper<User>
{
public User map(int rowIndex, ResultSet rs, StatementContext ctxt) throws SQLException {
return new User(rs.getString(1), rs.getString(2));
}
}
static class User {
private String firstName, lastName;
public String getFirstName(String n) { return firstName; }
public String getLastName(String n) { return lastName; }
public void setFirstName(String n) { firstName = n; }
public void setLastName(String n) { lastName = n; }
}
And then actual result binding:
List<User> users = database.withHandle(new HandleCallback<List<User>>() {
public List<User> withHandle(Handle h) {
return h.createQuery("select FIRST_NAME, LAST_NAME from USER").map(new BeanMapper()).list();
}
});
Alternatively (and even more conveniently since you need not write UserMapper class) you can use basic Bean-based binding: if value classes conform Java Beans specification (has appropriately named "getters" and "setters"), you can also just use:
List<User> users = database.withHandle(new HandleCallback<List<User>>() {
public List<User> withHandle(Handle h) {
return h.createQuery("select FIRST_NAME, LAST_NAME from USER")
.map(new BeanMapper<User>(User.class)).list();
}
});
Nifty, eh? In fact, not very much less convenient than Hibernate for many common cases.
5. Transactions
So far all code has used "withHandle" callback, which basically uses JDBC auto-commit mode. It usually works well for queries and one-step CRUD operations; but not when regular transactions are needed. But we could have as easily used transactions for operations, like:
final String account1 = "1234";
final String account2 = "1235";
database.inTransaction(new TransactionCallback<Integer>() { // we don't use nominal return value for anything
public Integer inTransaction(Handle h, TransactionStatus status) {
// transfer money as a transaction
h.createStatement("update ACCOUNT set BALANCE = BALANCE - 10 where ACCOUNT_ID = :account1")
.bind(0, account1).execute();
h.createStatement("update ACCOUNT set BALANCE = BALANCE + 10 where ACCOUNT_ID = :account2")
.bind(0, account2).execute();
}
}
}
So how does this differ from "withHandle"? Transaction is basically committed if execution terminates normally (no exception), but rolled back if an exception is thrown by code within callback block. This is the intuitive way of handling things, although not something basic JDBC would guarantee.
6. Life is a Batch
One simple way to improve efficiency of database code in Java is to use batches. As with other basic operations, JDBC does not offer much convenience support, so jDBI can simplify things here as well
final List<User> users = figureOutUsersToCreate(); // however these are determined
int inserted = database.inTransaction(new TransactionCallback<Integer>() {
public Integer inTransaction(Handle h, TransactionStatus status) { PreparedBatch b = h.prepareBatch("insert into USER (FIRST_NAME, LAST_NAME) values (?, ?)");
for (User user : users) {
b = b.add() // start new batch entry
.bind(0, user.getFirstName())
.bind(1, user.getLastName());
}
int[] stmtResults = b.execute(); // should be all 1s; let's count for fun
int count = 0;
for (int c : stmtResults) {
count += c;
}
return count;
}
});
which is relatively simple; the only gotcha comes with differing values that difference JDBC drivers return for updates (for example, MySQL has most bizarre values ever; when using 'merge' statement, inserting one row can return 1, 2 or even 3 as value!).
Nonetheless, jDBI makes process little bit more tolerable; and still transparent and efficient (no excessive magic involved)
7. Anything more?
There is lots more to learn -- like how to call stored procedures; how to deal with database-specific access and so on -- but so far I have only used quite basic functionality. So if you need other features, make sure to check out author's blog and Javadocs.
