Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

jDBI: A Simple Convenience Layer on Top of JDBC

DZone's Guide to

jDBI: A Simple Convenience Layer on Top of JDBC

· Java Zone ·
Free Resource

How do you break a Monolith into Microservices at Scale? This ebook shows strategies and techniques for building scalable and resilient microservices.

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

 

 

 

 

How do you break a Monolith into Microservices at Scale? This ebook shows strategies and techniques for building scalable and resilient microservices.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}