Introduction

Jdbi is is built on top of JDBC - you can use Jdbi wherever you have a JDBC driver. There’s a great overview in the Jdbi Developer Guide.

If you’ve used alternatives to JDBC such as DbUtils, then Jdbi covers a lot of the same ground - and a whole lot more, besides.

If you’ve used ORMs… I’m not going to get into the debate over when, how - or even whether - to use ORMs. I’m going to assume you already know what approach works for you, in your current situation.

Take a look at the Table of Contents in the Jdbi Developer Guide, and you will see the scope of Jdbi.  There’s a lot to it.  Here, I will focus on a very small subset of Jdbi:

• Using JavaBeans to populate prepared statements.
• Using JavaBeans to capture result sets.
• The @Nested annotation.

Creating a Jdbi Object

There are a few ways of doing this, but it’s more-or-less one line of code. In my demo, there’s a bit more to it, because I also create a database connection pool.

Take a look at the official documentation for more info.

Quick Note on @ColumnName

This Jdbi annotation lets you map a field name in a Java Bean to its equivalent column name in a table:

1
2

@ColumnName("title_id")  
private String titleID;

It’s just mapping names - nothing more than that. But it makes working with SQL much easier.

Selecting One Record

In my demo web application, described here (with source code here), I have various JavaBeans representing my business model - such as Title, for a movie or TV show. When we want to select a title record from the database, we first create a new Title object containing only the requested Title ID. In my application I refer to this bean as a “bind parameters” bean.  It’s an instance of Title, but it’s not a business object - users won’t get to see it.

Here is an example where our user has selected record tt2884018 from a filtered list, and wants to see the detailed movie record:

And here is the TitleController where the ID is extracted from the selected URL, and loaded into a new bind parameters bean.

We also have a SQL select statement here, containing a named placeholder :titleID. No need to use question marks for your prepared statement placeholders, with Jdbi.

Finally, we have to tell Jdbi that our result is going to be an instance of the Title class.

(In my code I wrap these items in a DaoData object as a convenient way to pass data into and out of my Jdbi processors.)

Here is the data access code which executes our select statement:

1
2
3
4
5

Optional<T> opt = jdbi.withHandle(handle -> handle  
        .select(daoData.getSql())  
        .bindBean(daoData.getBindParamsBean())  
        .mapToBean(daoData.getClazz())  
        .findOne());

There’s not much to say about this, except the obvious: It executes a prepared statement, using an input bean to provide any required bind parameters, and providing an output bean with the results. We can then check that Optional to see if a record was found or not.

The method signature is perhaps more interesting:

1

public <T extends DemoBean> DaoData<T> selectRecord(final DaoData<T> daoData)

It’s a generic method - it knows nothing about Title objects - and is therefore highly reusable across the beans in our business model. Our DaoData class is a generic class, for the same reason.

Other Types of Statement

Selecting a set of records, or performing updates, inserts, and deletes with Jdbi: They all follow the same broad pattern as our single-record select above.

Handling Reference Data with @Nested

The Title bean contains a “content type” field.  This is a constrained list of values, such as “movie” “TV show” and so on.

There is a separate table for Content Type in the database - and this is what I mean by “reference data” in this specific context: Such tables typically consist of an ID and a description - and often not much more than that. Other tables - such as the title table - will store the content type ID. A record may consist of several such reference data values.

That’s the relational database.  By contrast, our business model beans are objects not relational records - and so we have this field in our Title bean:

1

private ContentType contentType

Jdbc allows us to populate this contentType object directly from the Title select statement, by using Jdbi’s @Nested annotation.  The annotation is placed on our getter:

1
2
3
4

@Nested  
public ContentType getContentType() {  
    return contentType;  
}

The title select statement explicitly refers to the content type ID and content type description fields:

That’s all Jdbi needs to figure out how to build the ContentType object and add it to the Title bean.

At this point I think we have crossed over into ORM territory - and I am fine with that. It gets the job done. Handling this type of reference data is such a common situation I am happy to use the @Nested annotation here.