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.
