2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / Using Java Database Interface (jDBI) with PostgreSQL
Umair Shahid

Using Java Database Interface (jDBI) with PostgreSQL

April 10, 2018/0 Comments/in 2ndQuadrant, Umair's PlanetPostgreSQL /by Umair Shahid

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.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Power of Indexing in PostgreSQL [Webinar Follow-up] Partition Elimination in PostgreSQL 11
Scroll to top
×