Using Java Database Interface (jDBI) with PostgreSQL

jDBI is designed to be the middle ground between high level ORMs like JPA & Hibernate on one hand and raw JDBC on the other. You can think of it as a layer of convenience on top of JDBC that is still at a lower level than ORMs.

When working with JDBC, you are exposed to the raw workings of the SQL language and that of the database you are connecting to. jDBI tries to expose API that encapsulates this and is oriented towards Java programmers rather than database developers. It does so in two different styles, namely fluent style and SQL object style. Fluent style provides the facility of inline querying within your Java program whereas the SQL object style simplifies DAO creation using annotations. Both styles are demonstrated below.

Why Use jDBI?

You should consider using jDBI if you want to avoid getting into nitty gritties of the JDBC interface while still trying to maintain some low level control over how your application talks to your database.

While much of the JDBC functionality starts getting abstracted with jDBI, you still maintain a certain low level control over your application’s database interaction. With proper annotation, you have the flexibility to map incoming columns to class objects created in your Java application. This provides a layer of abstraction over JDBC for effort that is basically repetitive and tends to be error prone.

Similar annotations can also help you create INSERT, UPDATE, DELETE, & SELECT queries that you can use to create abstracted DAOs.

Prominent Features

We shall continue using the ‘largecities’ example for demonstrating this section as well.

PreRequisites

In order to use jDBI, you need to first download its jar file from http://jdbi.org/getting_jdbi/ and make sure it is in your project’s classpath. PostgreSQL JDBC driver should also be on your classpath. Then, you need to have the class that will store objects coming in from the database. In our case, the class looks like the following:

public class LargeCities {
        private int rank;
        private String name;
        
        public LargeCities(int int1, String string) {
                this.setRank(int1);
                this.setName(string);
        }
        public int getRank() {
                return rank;
        }
        public void setRank(int rank) {
                this.rank = rank;
        }
        public String getName() {
                return name;
        }
        public void setName(String name) {
                this.name = name;
        }         
}

You also need to create a mapper class, which will spell out to jDBI how to map incoming columns from the database to Java objects. For our example, the class will look like the following:

public class LargeCitiesMapper implements ResultSetMapper<LargeCities>{
        @Override
        public LargeCities map(int arg0, ResultSet arg1, StatementContext arg2) throws SQLException {
                return new LargeCities(arg1.getInt("rank"), arg1.getString("name"));
        }
}

The Fluent API

To demonstrate the use of fluent API for simply querying the database, consider the following program:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                Handle h = dbi.open(); 
                List<LargeCities> list = h.createQuery("SELECT rank, name FROM largecities").map(new LargeCitiesMapper()).list(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                h.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

The very first step here is to get a connection using a database interface object, from which then a handle is created. This handle is used for query creation. Notice how this query creation uses the mapper class we created as a pre-req in order to tell jDBI exactly how to map columns from largecities table to the LargeCities object.

Here, jDBI basically takes away the low level iteration of the result set you would have needed to do with raw JDBC.

Output of this simple program is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Inserting Data

The following program demonstrates insertion and then retrieiving the data:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                Handle h = dbi.open(); 
                h.execute("INSERT INTO largecities VALUES (?, ?)", 11, "Mumbai");
                List<LargeCities> list = h.createQuery("SELECT rank, name FROM largecities").map(new LargeCitiesMapper()).list(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                h.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

The output hence is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 11 Name: Mumbai

Updating Data

Updating data is demonstrated in the following program:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                Handle h = dbi.open(); 
                h.execute("UPDATE largecities SET rank = ?, name = ? WHERE rank = ?", 12, "New York", 11);
                List<LargeCities> list = h.createQuery("SELECT rank, name FROM largecities").map(new LargeCitiesMapper()).list(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                h.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

The output of the above code is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 12 Name: New York

Deleting Data

Following program demonstrates deletion of data:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                Handle h = dbi.open(); 
                h.execute("DELETE FROM largecities WHERE rank = ?", 12);
                List<LargeCities> list = h.createQuery("SELECT rank, name FROM largecities").map(new LargeCitiesMapper()).list(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                h.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

Output comes back to:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

The SQL Object API

The SQL Object API provides a level of abstraction over JDBC to help with DAO creation. Essentially, in this API, a single method maps to a single statement to be executed against your database.

In addition to the pre-reqs mentioned above, to use the SQL object API, you need an additional interface that implements the DAO. For a simple SELECT query, the DAO would look like:

public interface JdbiDAO {
        @SqlQuery("SELECT rank, name FROM largecities")
        List<LargeCities> getCities(); 
        
        void close();         
}

Notice how a simple annotation tells jDBI on how to map the function to the SQL query.

In order to use this DAO, your program will look like the following:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                dbi.registerMapper(new LargeCitiesMapper());
                JdbiDAO dao = dbi.open(JdbiDAO.class);
                
                List<LargeCities> list = dao.getCities(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                dao.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

This is the exact equivalent of the program we created above in the Fluent API section. The output, hence, is also exactly the same:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Let’s explore now how we can insert, update, & delete data using SQL object API.

Inserting Data

In order to insert data, let’s create another function within our DAO as follows:

         @SqlUpdate("INSERT INTO largecities (rank, name) values (:rank, :name)")
         void insert(@Bind("rank") int rank, @Bind("name") String name);

We can now use this function to insert data from our main program as follows:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                dbi.registerMapper(new LargeCitiesMapper());
                JdbiDAO dao = dbi.open(JdbiDAO.class);
                
                dao.insertCities(11, "Mumbai");
                List<LargeCities> list =  dao.getCities(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                dao.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

This results in the following output:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 11 Name: Mumbai

Updating Data

Updating data would require yet another function in our DAO as follows:

         @SqlUpdate("UPDATE largecities SET rank = :rank, name = :name WHERE rank = 11")
         void updateCities(@Bind("rank") int rank, @Bind("name") String name);

Note that in order to keep the example simple, we have restricted the parameter count to be the same as the other examples.

This DAO function is used in the main program as:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                dbi.registerMapper(new LargeCitiesMapper());
                JdbiDAO dao = dbi.open(JdbiDAO.class);
                
                dao.updateCities(12, "New York");
                List<LargeCities> list =  dao.getCities(); 
                
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
             
                dao.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

Output of this program is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo
Rank: 12 Name: New York

Deleting Data

This, again, requires a new function in the DAO:

         @SqlUpdate("DELETE FROM largecities WHERE rank = :rank")
         void deleteCities(@Bind("rank") int rank);

Our main program would use this functions as:

        try  {
                
                DBI dbi = new DBI("jdbc:postgresql://localhost:5432/postgres", "postgres", "");
                dbi.registerMapper(new LargeCitiesMapper());
                JdbiDAO dao = dbi.open(JdbiDAO.class);
                
                dao.deleteCities(12);
                List<LargeCities> list =  dao.getCities(); 
               
                for (LargeCities a : list) {
                        System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
                }
                
                dao.close();
        } 
        catch (Exception e) {
            e.printStackTrace();
        }

Resulting in the following output:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Drawbacks of Using jDBI

One of the major reasons of using ORMs is typesafety. jDBI, however, works at a lower level than typical ORMs and hence lacks this key feature. Essentially, this means that even though you will be working at an abstract level from JDBC, chances of runtime errors will remain intact. This also implies that the rather useful feature of auto-complete provided by most IDEs can not be used for database interactions.

jDBI does not provide any means of auto-generating code related to database interactions. This means that you will need to write your object class yourself and that you will also need to ensure that its properties map properly to the table columns you will be querying. This manual creation also means that any changes in the relational objects will need manual changes in the corresponding domain model.