KOS Developer's Portal

Database DAOs

Introduction

The previous page explained how to set up a database and how to change its structure as needed. In this page, we focus on how to interact with the database: adding and retrieving data from the tables. To do this, we will create DAO classes, one for each table, that enable us to perform the CRUD operations: create, read, update, and delete of one or more rows.

Overview

DAO: Data Access Object

A DAO is a software pattern that provides an interface to some type of database object, typically a table with all of its rows and columns. Read more about it at Wikipedia.

Using JDBI, there are two primary ways of creating a DAO:

Fluent
Declarative

Using the fluent style, you create a DAO class and then write explicit methods to fetch and store data.

Using the declarative style, you create a DAO interface and then annotate methods and/or write default implementations.

Source code for this and most other Java reference pages is available on GitHub.

Setup

In these examples, we will write a DAO for our "users" table. As you recall, it has three fields:

id (integer)
firstName (text)
lastName (text)

Whether using the fluent or declarative style, the first thing to do is create an object to hold these field values. Ours is named, unsurprisingly, UserInfo:

UserInfo data bean

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserInfo {
    private Integer id;
    private String firstName;
    private String lastName;
}

Entity / Domain Classes

The UserInfo class is an example of an "entity" or "domain" class. This type of class has data fields that represent the information from a single row in a database table. The field names and types must be in agreement with the table’s columns.

As you can see, this data bean has exactly one Java field for each database field. Each of these is named identically to the corresponding database column. That’s all we need to have an object that mirrors our table’s data.

Fluent DAO

This section describes the fluent style, while the next page describes the declarative style.

UserDao1

With all of that in mind, let’s create our data access object.

The following code shows a fluent-style DAO for our "users" table (which was created in the Database Migrator pages).

UserDao1 class

public class UserDao1 {

    private Jdbi jdbi;

    public void setDataSource(DataSource dataSource) {
        jdbi = Jdbi.create(dataSource);
    }

    public UserInfo getById(int userId) {
        return jdbi.withHandle(handle ->
                handle.select("SELECT * FROM users WHERE [id] = ?", userId)
                      .map(new UserMapper1())
                      .findOne()
        ).orElse(null);
    }

    public void insert(UserInfo userInfo) {
        jdbi.useHandle(handle ->
                handle.createUpdate("""
                              INSERT INTO users
                              ([id], [firstName], [lastName]) VALUES (:id,  :firstName,  :lastName)""")
                      .bindBean(userInfo)
                      .execute()
        );
    }

    public void update(UserInfo userInfo) {
        jdbi.useHandle(handle ->
                handle.createUpdate("""
                              UPDATE users
                              SET [firstName] = :firstName, [lastName] = :lastName
                              WHERE [id]=:id""")
                      .bindBean(userInfo)
                      .execute()
        );
    }

    public void delete(int id) {
        jdbi.useHandle(handle ->
                handle.createUpdate("DELETE FROM users WHERE [id] = :id")
                      .bind("id", id)
                      .execute()
        );
    }

    public void save(UserInfo userInfo) {
        jdbi.useHandle(handle ->
                handle.createUpdate("""
                              INSERT INTO users
                              ([id], [firstName], [lastName]) VALUES (:id, :firstName, :lastName)
                              ON CONFLICT([id]) DO
                              UPDATE SET [firstName] = :firstName, [lastName] = :lastName""")
                      .bindBean(userInfo)
                      .execute()
        );
    }

    public int numRows() {
        return jdbi.withHandle(handle ->
                handle.createQuery("SELECT COUNT(*) FROM users")
                      .mapTo(Integer.class)
                      .one());
    }

    @SuppressWarnings("SqlWithoutWhere")
    public void truncate() {
        jdbi.useHandle(handle ->
                handle.execute("DELETE FROM users"));
    }

    public List<UserInfo> fetchByLastName(String lastName) {
        return jdbi.withHandle(handle ->
                handle.select("SELECT * FROM users WHERE [lastName] = ?", lastName)
                      .map(new UserMapper1())
                      .list()
        );
    }

    private static class UserMapper1 implements RowMapper<UserInfo> {
        @Override
        public UserInfo map(ResultSet rs, StatementContext ctx) throws SQLException {
            return new UserInfo(rs.getInt("id"), rs.getString("firstName"), rs.getString("lastName"));
        }
    }
}

As you can see, many of these methods deal with fetching and storing a UserInfo object:

getById()
insert()
update()
delete()
save() (a combination of both an INSERT and an UPDATE)

The other three methods:

Counts the total number of rows in the table: numRows()
Removes all rows in the table: truncate()
Retrieves a list of rows that share the same last name: fetchByLastName()

As you can see, every method contains a call to the JDBI object’s useHandle() method, passing in some sort of lambda expression. In the lambda code there’s a SQL statement, which optional parameters, indicating what operation should be performed.

It is beyond the scope of these examples to go into detail concerning the JDBI library. For that, please refer to their excellent documentation at http://jdbi.org/.

Declarative DAO

UserDao2

This second version of our DAO uses annotations to indicate to the code what corresponding SQL statement to execute. As you can see, it is quite a bit shorter than the previous version:

UserDao2 interface

@RegisterRowMapper(UserMapper2.class)
public interface UserDao2 {

    @SqlQuery("SELECT * FROM users WHERE [id] = :id")
    UserInfo getById(@Bind("id") Integer id);

    @SqlUpdate("INSERT INTO users ([id], [firstName], [lastName]) VALUES (:id, :firstName, :lastName)")
    void insert(@BindBean UserInfo userInfo);

    @SqlUpdate("UPDATE users SET [firstName]=:firstName, [lastName]=:lastName WHERE [id] = :id")
    void update(@BindBean UserInfo userInfo);

    @SqlUpdate("DELETE FROM users WHERE [id] = :id")
    void delete(@Bind("id") Integer id);

    @SqlUpdate("""
            INSERT INTO users ([id], [firstName], [lastName]) VALUES (:id, :firstName, :lastName)
            ON CONFLICT([id]) DO UPDATE SET [firstName]=:firstName, [lastName]=:lastName""")
    void save(@BindBean UserInfo userInfo);

    @SqlQuery("SELECT COUNT(*) FROM users")
    int numRows();

    @SqlUpdate("DELETE FROM users")
    @SuppressWarnings("SqlWithoutWhere")
    void truncate();

    @SqlQuery("SELECT * FROM users WHERE [lastName] = :lastName")
    List<UserInfo> fetchByLastName(@Bind("lastName") String lastName);

    class UserMapper2 implements RowMapper<UserInfo> {
        @Override
        public UserInfo map(ResultSet rs, StatementContext ctx) throws SQLException {
            return new UserInfo(rs.getInt("id"), rs.getString("firstName"), rs.getString("lastName"));
        }
    }
}

UserDao2 has the same eight methods that UserDao1 has. The difference is that @SqlQuery and @SqlUpdate annotations to indicate the SQL statement to execute, and the @Bind and @BindBean annotations to indicate how variables and objects are used in those statements.

The JDBI documentation is found on their sql_objects page.

Summary

This page demonstrated how to create both a fluent-style and declarative-style DAOs for our sample "users" database table.

This concludes the database access set of pages.