Java JDBI

last modified July 6, 2020

In this tutorial, we show how to work with data with JDBI. We chose MySQL for our database. ZetCode has a complete e-book for MySQL Java, which contains a JDBI chapter: MySQL Java programming e-book.

JDBI is a convenience library built on top of JDBC. It makes database programming much easier. It manages exceptions. It has tools for automatic resource management and mapping result sets to classes. JDBI is conceptually similar to the Spring's JdbcTemplate, for which ZetCode has a tutorial.

The DBI instance provides connections to the database via Handle instances. Handle represents a connection to the database system; it is a wrapper around a JDBC Connection object.

JDBI provides two different style APIs: fluent style and an object style.

Creating a database in MySQL

In this section, we create a new testdb database in MySQL. We use the mysql monitor to do the job, but we could use the NetBeans database tool as well.

DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars(Id INT PRIMARY KEY AUTO_INCREMENT,
                  Name TEXT, Price INT) ENGINE=InnoDB;

INSERT INTO Cars(Name, Price) VALUES('Audi', 52642);
INSERT INTO Cars(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO Cars(Name, Price) VALUES('Skoda', 9000);
INSERT INTO Cars(Name, Price) VALUES('Volvo', 29000);
INSERT INTO Cars(Name, Price) VALUES('Bentley', 350000);
INSERT INTO Cars(Name, Price) VALUES('Citroen', 21000);
INSERT INTO Cars(Name, Price) VALUES('Hummer', 41400);
INSERT INTO Cars(Name, Price) VALUES('Volkswagen', 21600);

This is the SQL to create the Cars table in MySQL.

To create the database and the table, we use the mysql monitor tool.

$ sudo service mysql start

MySQL is started with sudo service mysql start command.

$ mysql -u testuser -p

We connect to the database with the mysql monitor.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

With the CREATE DATABASE statement, a new database is created.

mysql> USE testdb;
mysql> SOURCE cars_mysql.sql

With the source command, we load and execute the cars_mysql.sql file.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)

We verify the data.

The pom.xml file

The examples will use the following Maven POM file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>JDBIEx</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi</artifactId>
            <version>2.73</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>

    </dependencies>

</project>

We have defined dependencies for the JDBI library and the MySQL driver.

Fluent API

In the following examples, we will use the JDBI Fluent API to work with the MySQL database.

Retrieving all cars

In the first example, we fetch all cars from the Cars table.

package com.zetcode;

import java.util.List;
import java.util.Map;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.Query;

public class JDBIEx {

    public static void main(String[] args) {

        Handle handle = null;
        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        String sql = "SELECT * FROM Cars";

        try {

            handle = dbi.open();
            Query<Map<String, Object>> q = handle.createQuery(sql);
            List<Map<String, Object>> l = q.list();

            for (Map<String, Object> m : l) {

                System.out.printf("%d ", m.get("Id"));
                System.out.printf("%s ", m.get("Name"));
                System.out.println(m.get("Price"));
            }

        } finally {
            if (handle != null) {
                handle.close();
            }
        }
    }
}

The example connects to the testdb database and retrieves all cars from the Cars table.

DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
        "testuser", "test623");

An access point is created to the database with the DBI class.

handle = dbi.open();

A Handle to the database is created with the DBI's open method. It represents a connection to the database. A connection to the database is created with the DriverManager.

Query<Map<String, Object>> q = handle.createQuery(sql);

A Query object is created with the createQuery method.

List<Map<String, Object>> l = q.list();

From the query object, we get a list of key/value pairs.

for (Map<String, Object> m : l) {

    System.out.printf("%d ", m.get("Id"));
    System.out.printf("%s ", m.get("Name"));
    System.out.println(m.get("Price"));
}

We go through the list and print all columns.

} finally {
    if (handle != null) {
        handle.close();
    }
}

In the end, we close the handle.

1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

This is the output of the example.

Retrieving a car by its ID

In the next example, we fetch a car name from the Cars table by its ID.

package com.zetcode;

import java.util.Map;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.Query;
import org.skife.jdbi.v2.util.StringColumnMapper;

public class JDBIEx2 {

    public static void main(String[] args) {

        Handle handle = null;
        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        try {

            handle = dbi.open();

            String sql = "SELECT Name FROM Cars WHERE Id = ?";

            Query<Map<String, Object>> q = handle.createQuery(sql);
            q.bind(0, 1);

            String carName = q.map(StringColumnMapper.INSTANCE).first();

            System.out.println(carName);

        } finally {

            if (handle != null) {
                handle.close();
            }
        }
    }
}

In the example, we select a car name from the Cars table. The SQL query takes a parameter which is bound later.

String sql = "SELECT Name FROM Cars WHERE Id = ?";

This is SQL code for selecting a car name from the table. The question mark is a token to be filled later in code.

Query<Map<String, Object>> q = handle.createQuery(sql);

A new Query object is created from the SQL statement.

q.bind(0, 1);

With the bind method, we bind the missing parameter. The parameter is bound positionally.

String carName = q.map(StringColumnMapper.INSTANCE).first();

We map a column of the result set with the StringColumnMapper to the string type. The first method is used for returning one value.

System.out.println(carName);

The name of the car is printed to the console.

Data source

In this example, we connect to the database using a data source. The usage of a data source improves application's performance and scalability.

# mysql properties
mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/testdb
mysql.username=testuser
mysql.password=test623

In the db.properties file, we have the connection properties.

The file is placed in the project's Resources directory.

package com.zetcode;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Map;
import java.util.Properties;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.Query;
import org.skife.jdbi.v2.util.IntegerColumnMapper;

public class JDBIEx3 {

    public static MysqlDataSource getMySQLDataSource() throws
            FileNotFoundException, IOException {

        Properties props = new Properties();
        FileInputStream fis = null;
        MysqlDataSource ds = null;

        fis = new FileInputStream("src/main/Resources/db.properties");
        props.load(fis);

        ds = new MysqlConnectionPoolDataSource();
        ds.setURL(props.getProperty("mysql.url"));
        ds.setUser(props.getProperty("mysql.username"));
        ds.setPassword(props.getProperty("mysql.password"));

        return ds;
    }

    public static void main(String[] args) throws IOException {

        Handle handle = null;
        MysqlDataSource ds = getMySQLDataSource();

        DBI dbi = new DBI(ds);

        try {

            handle = dbi.open();

            String sql = "SELECT Price FROM Cars WHERE Id = ?";

            Query<Map<String, Object>> q = handle.createQuery(sql);
            q.bind(0, 1);

            Integer price = q.map(IntegerColumnMapper.WRAPPER).first();

            System.out.println(price);

        } finally {
            if (handle != null) {
                handle.close();
            }
        }
    }
}

The example selects the price of a car found by its ID.

fis = new FileInputStream("src/main/Resources/db.properties");
props.load(fis);

We load the properties from the Resources directory.

ds = new MysqlConnectionPoolDataSource();
ds.setURL(props.getProperty("mysql.url"));
ds.setUser(props.getProperty("mysql.username"));
ds.setPassword(props.getProperty("mysql.password"));

A MysqlConnectionPoolDataSource is created. We set the parameters from the properties file.

Integer price = q.map(IntegerColumnMapper.WRAPPER).first();

Since the SQL query returns an integer, we use the IntegerColumnMapper class.

The withHandle method

The DBI class has a convenience method called withHandle, which manages the lifecycle of a handle and yields it to a callback for use by clients.

package com.zetcode;

import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.util.IntegerColumnMapper;

public class JDBIEx4 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");
        String sql = "SELECT Price FROM Cars WHERE Id = :id";
        int id = 3;

        Integer price = dbi.withHandle((Handle h) -> {

            return h.createQuery(sql)
                    .map(IntegerColumnMapper.WRAPPER)
                    .bind("id", id)
                    .first();
        });

        System.out.println(price);
    }
}

The example selects a price of a car identified by its ID.

String sql = "SELECT Price FROM Cars WHERE Id = :id";

This SQL query uses a named parameter.

Integer price = dbi.withHandle((Handle h) -> {

    return h.createQuery(sql)
            .map(IntegerColumnMapper.WRAPPER)
            .bind("id", id)
            .first();
});

A query is created and executed without us having to worry about closing the handle.

Mapping custom classes

It is possible to map custom classes to the result sets. The mapping class must implement the ResultSetMapper<T> interface.

package com.zetcode;

public class Car {

    private Long Id;
    private String Name;
    private int Price;

    public Car(Long Id, String Name, int Price) {
        this.Id = Id;
        this.Name = Name;
        this.Price = Price;
    }

    public Long getId() {
        return Id;
    }

    public void setId(Long Id) {
        this.Id = Id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String Name) {
        this.Name = Name;
    }

    public int getPrice() {
        return Price;
    }

    public void setPrice(int Price) {
        this.Price = Price;
    }

    @Override
    public String toString() {
        return "Car{" + "Id=" + Id + ", Name=" + Name + ", Price=" + Price + '}';
    }
}

This is a custom Car class to which we are going to map the result set.

package com.zetcode;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;


public class CarMapper implements ResultSetMapper<Car> {

    @Override
    public Car map(int idx, ResultSet rs, StatementContext ctx) throws SQLException {
        return new Car(rs.getLong("Id"), rs.getString("Name"), rs.getInt("Price"));
    }
}

We provide the mapping class. It returns a new Car object filled with the data from the result set.

package com.zetcode;

import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;

public class JDBIEx5 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        String sql = "SELECT * FROM Cars WHERE Id = :id";
        int id = 3;

        Car car = dbi.withHandle((Handle h) -> {

            return h.createQuery(sql)
                    .map(new CarMapper())
                    .bind("id", id)
                    .first();
        });

        System.out.println(car);
    }
}

The example selects a Car object from the table identified by its ID.

Car car = dbi.withHandle((Handle h) -> {

    return h.createQuery(sql)
            .map(new CarMapper())
            .bind("id", id)
            .first();
});

A custom CarMapper object is passed to the map method.

Batch operations

Batch processing allows us to group related SQL statements into a batch and submit them with one call to the database. This can significantly improve the performance of our application.

Batch operations are not atomic; they do not provide an all or nothing solution. For instance, if we create an incorrect INSERT statement, it fails, but other INSERT statements are executed.

package com.zetcode;

import org.skife.jdbi.v2.Batch;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;

public class JDBIEx6 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        Handle handle = dbi.open();
        Batch batch = handle.createBatch();

        batch.add("DROP TABLE IF EXISTS Friends");
        batch.add("CREATE TABLE Friends(Id INT AUTO_INCREMENT PRIMARY KEY, Name TEXT)");
        batch.add("INSERT INTO Friends(Name) VALUES ('Monika')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Tom')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Jane')");
        batch.add("INSERT INTO Friends(Name) VALUES ('Robert')");

        batch.execute();
    }
}

The example creates a new Friends table. The SQL commands are grouped into one batch operation.

Batch batch = handle.createBatch();

A Batch represents a group of non-prepared statements; it is created with the createBatch method.

batch.add("DROP TABLE IF EXISTS Friends");

The add method adds a statement to the batch.

batch.execute();

The batch is executed with the execute method.

Transactions

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

Also note that in MySQL, DDL statements such as DROP TABLE and CREATE TABLE cause an implicit commit to a transaction.

package com.zetcode;

import org.skife.jdbi.v2.Batch;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.TransactionStatus;
import org.skife.jdbi.v2.VoidTransactionCallback;

public class JDBIEx7 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        dbi.inTransaction(new VoidTransactionCallback() {
            @Override
            protected void execute(Handle handle, TransactionStatus status)
                    throws Exception {

                Batch batch = handle.createBatch();

                batch.add("DROP TABLE IF EXISTS Friends");
                batch.add("CREATE TABLE Friends(Id INT AUTO_INCREMENT PRIMARY KEY, Name TEXT)");
                batch.add("INSERT INTO Friends(Name) VALUES ('Monika')");
                batch.add("INSERT INTO Friends(Name) VALUES ('Tom')");
                batch.add("INSERT INTO Friends(Name) VALUES ('Jane')");
                batch.add("INSERT INTO Friends(Name) VALUES ('Robert')");

                batch.execute();
            }
        });
    }
}

The example places a batch operation in a transaction. Due to the implicit commits of DDL statements in MYSQL, only the INSERT statements are in a all or nothing mode.

dbi.inTransaction(new VoidTransactionCallback() {
    @Override
    protected void execute(Handle handle, TransactionStatus status)
            throws Exception {

        ...
    }
});

A transaction is created with the inTransaction method. The VoidTransactionCallback is a transaction callback that does not return a value.

SQL Object API

The SQL Object API provides a declarative mechanism for common JDBI operations. To use the SQL Object API, we create an interface or an abstract class with annotations such as @SqlQuery or @SqlUpdate.

Simple example

We create an example where we will create simple queries utilizing the SQL Object API.

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.8</version>
</dependency>

In the example, we also use the lombok library, which reduces some boilerplate code.

package com.zetcode;

import lombok.Data;

@Data
public class Car {

    private final Long Id;
    private final String Name;
    private final int Price;
}

The Car class is decorated with the lombok's @Data annotation. It will automatically create the getter and setter methods, equals method, toString method, hashCode method, and an arguments constructor.

package com.zetcode;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

public class CarMapper implements ResultSetMapper<Car> {

    @Override
    public Car map(int idx, ResultSet rs, StatementContext ctx) throws SQLException {
        return new Car(rs.getLong("Id"), rs.getString("Name"), rs.getInt("Price"));
    }
}

The CarMapper maps the result set to the Car class.

package com.zetcode;

import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.customizers.Mapper;

public interface MyDAO {

    @SqlQuery("SELECT * FROM Cars WHERE Id = :id")
    @Mapper(CarMapper.class)
    Car findById(@Bind("id") int id);

    @SqlQuery("SELECT COUNT(Id) FROM Cars")
    int countCars();
}

Here we have a MyDAO interface decorated with two @SqlQuery annotations. The methods find a car by its ID and count all cars in the table.

@SqlQuery("SELECT * FROM Cars WHERE Id = :id")

The @SqlQuery annotation indicates that the method executes the specified query.

@Mapper(CarMapper.class)

The @Mapper specifies the result set mapper on a query method.

Car findById(@Bind("id") int id);

The @Bind annotation binds the method's argument to the SQL query parameter.

package com.zetcode;

import org.skife.jdbi.v2.DBI;

public class JDBIEx8 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        int id = 3;

        MyDAO dao = dbi.onDemand(MyDAO.class);
        Car car = dao.findById(id);

        System.out.println(car);

        int nCars = dao.countCars();

        System.out.printf("There are %d cars in the table", nCars);
    }
}

In this client application, we find a car with ID equal to 3 and count all cars in the table.

MyDAO dao = dbi.onDemand(MyDAO.class);

The onDemand method creates a new sql object which will obtain and release connections from this dbi instance, as it needs to, and can, respectively. We should not explicitly close this sql object.

Car car = dao.findById(id);

We get a car with the specified ID.

int nCars = dao.countCars();

We count the number of cars in the database table.

Transactions

In SQL Object API we can use the @Transaction annotation to create a transaction.

CREATE TABLE IF NOT EXISTS Authors(Id BIGINT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(25)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Books(Id BIGINT PRIMARY KEY AUTO_INCREMENT,
    AuthorId BIGINT, Title VARCHAR(100),
    FOREIGN KEY(AuthorId) REFERENCES Authors(Id) ON DELETE CASCADE)
    ENGINE=InnoDB;

For this example, we create two tables: Authors and Books.

package com.zetcode;

import java.util.List;
import org.skife.jdbi.v2.exceptions.TransactionFailedException;
import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.SqlUpdate;
import org.skife.jdbi.v2.sqlobject.Transaction;

public abstract class MyDAO {

    @SqlUpdate("INSERT INTO Authors(Name) VALUES(:author)")
    public abstract void createAuthor(@Bind("author") String author);

    @SqlQuery("SELECT Id FROM Authors WHERE Name = :name")
    abstract long getAuthorId(@Bind("name") String name);

    @SqlUpdate("INSERT INTO Books(AuthorId, Title) VALUES(:authorId, :title)")
    abstract void insertBook(@Bind("authorId") Long authorId, @Bind("title") String title);

    @Transaction
    public void insertBooksForAuthor(String author, List<String> titles) {

        Long authorId = getAuthorId(author);

        if (authorId == null) {
            throw new TransactionFailedException("No author found");
        }

        for (String title : titles) {

            insertBook(authorId, title);
        }
    }
}

We have an abstract MyDAO class, where we utilize the @SqlUpdate, @SqlQuery, and @Transaction annotations.

@SqlUpdate("INSERT INTO Authors(Name) VALUES(:author)")
public abstract void createAuthor(@Bind("author") String author);

This method adds a new author.

@SqlQuery("SELECT Id FROM Authors WHERE Name = :name")
abstract long getAuthorId(@Bind("name") String name);

The getAuthorId is used to get the ID of an author. The ID is needed when we insert new books into the Books table.

@SqlUpdate("INSERT INTO Books(AuthorId, Title) VALUES(:authorId, :title)")
abstract void insertBook(@Bind("authorId") Long authorId, @Bind("title") String title);

The insertBook methods inserts a single book into the Books table.

@Transaction
public void insertBooksForAuthor(String author, List<String> titles) {

    Long authorId = getAuthorId(author);

    if (authorId == null) {
        throw new TransactionFailedException("No author found");
    }

    for (String title : titles) {

        insertBook(authorId, title);
    }
}

The @Transaction annotation causes the insertBooksForAuthor to be run within a transaction. So either all books are inserted or none.

package com.zetcode;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.skife.jdbi.v2.DBI;

public class JDBIEx9 {

    public static void main(String[] args) {

        DBI dbi = new DBI("jdbc:mysql://localhost:3306/testdb",
                "testuser", "test623");

        List<Map<String, List<String>>> authorsBooks = new ArrayList<>();

        Map<String, List<String>> autMap1 = new HashMap<>();

        List<String> books1 = new ArrayList<>();
        books1.add("Call of the Wild");
        books1.add("Martin Eden");
        books1.add("The Iron Heel");
        books1.add("White Fang");

        autMap1.put("Jack London", books1);

        Map<String, List<String>> autMap2 = new HashMap<>();

        List<String> books2 = new ArrayList<>();
        books2.add("Father Goriot");
        books2.add("Colonel Chabert");
        books2.add("Cousing Pons");

        autMap2.put("Honore de Balzac", books2);

        authorsBooks.add(autMap1);
        authorsBooks.add(autMap2);

        MyDAO dao = dbi.onDemand(MyDAO.class);

        for (Map<String, List<String>> map : authorsBooks) {

            Set<String> ks = map.keySet();

            for (String author : ks) {

                dao.createAuthor(author);

                List<String> titles = map.get(author);

                dao.insertBooksForAuthor(author, titles);
            }
        }
    }
}

The example inserts two authors and their books into the database.

In this tutorial, we have presented the JDBI library. ZetCode has the following related tutorials: Java tutorial, MySQL Java tutorial, and MySQL tutorial.