SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 219 posts at DZone. You can read more from them at their website. View Full User Profile

jDBI: A Simple Convenience Layer on Top of JDBC

04.21.2012
| 7505 views |
  • submit to reddit

I’m always looking out for similar tools like jOOQ, or at least tools that work in the same domain – the domain of database access abstraction. jDBI looks lovely. It provides simple solutions for what JDBC is lacking in general. Here are a couple of features (taken from the intro):

Fluent API

JDBC is quite verbose in that it usually requires three steps to get to a result:

  1. Obtain a connection
  2. Prepare a statement
  3. Fetch results (meaning iterate over a result set, even if you only need one value)

Here’s how jDBI models its fluent API, to ease some of that pain:

// using in-memory H2 database
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test",
                                          "username",
                                          "password");
DBI dbi = new DBI(ds);
Handle h = dbi.open();
h.execute(
  "create table something (id int primary key, name varchar(100))");
h.execute(
  "insert into something (id, name) values (?, ?)", 1, "Brian");

String name = h.createQuery("select name from something where id = :id")
               .bind("id", 1)
               .map(StringMapper.FIRST)
               .first();
                    
assertThat(name, equalTo("Brian"));

h.close();

DAO layer simplification

In the DAO layer, you’re often writing the same SQL code again and again. Hibernate / JPA are quite convenient in handling this, but you don’t always want to have such big dependencies. So jDBI offers the essence of EJB 3.0. Simple annotations for named queries (although, I do think that Brian McCallister could use JPA annotations instead of his own ones):

public interface MyDAO
{
  @SqlUpdate(
    "create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);

  /**
   * close with no args is used to close the connection
   */
  void close();
}

Here’s how to use the above DAO:

// using in-memory H2 database via a pooled DataSource
JdbcConnectionPool ds = JdbcConnectionPool.create("jdbc:h2:mem:test2",
                                                  "username",
                                                  "password");
DBI dbi = new DBI(ds);
MyDAO dao = dbi.open(MyDAO.class);

dao.createSomethingTable();
dao.insert(2, "Aaron");

String name = dao.findNameById(2);
assertThat(name, equalTo("Aaron"));

dao.close();
ds.dispose();

Summary

There are a few other very nice features, which I am now going to check for their usefulness in jOOQ. Read the manual here and discover this little gem:

http://jdbi.codehaus.org/archive.html

Or get the sources here:

https://github.com/brianm/jdbi

 

 

 

 

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)