Using jOOQ with Spring: CRUD

Petri KainulainenApril 17th, 2014Last Updated: April 17th, 2014

2 185 13 minutes read

jOOQ is a library which helps us to get back in control of our SQL. It can generate code from our database and lets us build typesafe database queries by using its fluent API.

The earlier parts of this tutorial have taught us how we can configure the application context of our example application and generate code from our database.

We are now ready to take one step forward and learn how we can create typesafe queries with jOOQ. This blog post describes how we can add CRUD operations to a simple application which manages todo entries.

Let’s get started.

Additional Reading:

Using jOOQ with Spring: Configuration is the first part of this tutorial, and it describes you can configure the application context of a Spring application which uses jOOQ. You can understand this blog post without reading the first part of this tutorial, but if you want to really use jOOQ in a Spring powered application, I recommend that you read the first part of this tutorial as well.
Using jOOQ with Spring: Code Generation is the second part of this tutorial, and it describes how we can reverse-engineer our database and create the jOOQ query classes which represents different database tables, records, and so on. Because these classes are the building blocks of typesafe SQL queries, I recommend that you read the second part of this tutorial before reading this blog post.

Creating the Todo Class

Let’s start by creating a class which contains the information of a single todo entry. This class has the following fields:

The id field contains the id of the todo entry.
The creationTime field contains a timestamp which describes when the todo entry was persisted for the first time.
The description field contains the description of the todo entry.
The modificationTime field contains a timestamp which describes when the todo entry was updated.
The title field contains the title of the todo entry.

The name of this relatively simple class is Todo, and it follows three principles which are described in the following:

We can create new Todo objects by using the builder pattern described in Effective Java by Joshua Bloch. If you are not familiar with this pattern, you should read an article titled Item 2: Consider a builder when faced with many constructor parameters.
The title field is mandatory, and we cannot create a new Todo object which has either null or empty title. If we try to create a Todo object with an invalid title, an IllegalStateException is thrown.
This class is immutable. In other words, all its field are declared final.

The source code of the Todo class looks as follows:

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.joda.time.LocalDateTime;

import java.sql.Timestamp;

public class Todo {

    private final Long id;

    private final LocalDateTime creationTime;

    private final String description;

    private final LocalDateTime modificationTime;

    private final String title;

    private Todo(Builder builder) {
        this.id = builder.id;

        LocalDateTime creationTime = null;
        if (builder.creationTime != null) {
            creationTime = new LocalDateTime(builder.creationTime);
        }
        this.creationTime = creationTime;

        this.description = builder.description;

        LocalDateTime modificationTime = null;
        if (builder.modificationTime != null) {
            modificationTime = new LocalDateTime(builder.modificationTime);
        }
        this.modificationTime = modificationTime;

        this.title = builder.title;
    }

    public static Builder getBuilder(String title) {
        return new Builder(title);
    }

    //Getters are omitted for the sake of clarity.

    public static class Builder {

        private Long id;

        private Timestamp creationTime;

        private String description;

        private Timestamp modificationTime;

        private String title;

        public Builder(String title) {
            this.title = title;
        }

        public Builder description(String description) {
            this.description = description;
            return this;
        }

        public Builder creationTime(Timestamp creationTime) {
            this.creationTime = creationTime;
            return this;
        }

        public Builder id(Long id) {
            this.id = id;
            return this;
        }

        public Builder modificationTime(Timestamp modificationTime) {
            this.modificationTime = modificationTime;
            return this;
        }

        public Todo build() {
            Todo created = new Todo(this);

            String title = created.getTitle();

            if (title == null || title.length() == 0) {
                throw new IllegalStateException("title cannot be null or empty");
            }

            return created;
        }
    }
}

Let’s find out why we need to get the current date and time, and more importantly, what is the best way to do it.

Getting the Current Date and Time

Because the creation time and modification time of each todo entry are stored to the database, we need a way to obtain the current date and time. Of course could we simply create this information in our repository. The problem is that if we would do this, we wouldn’t be able to write automated tests which ensure that the creation time and the modification time are set correctly (we cannot write assertions for these fields because their values depends from the current time).

That is why we need to create a separate component which is responsible for returning the current date and time. The DateTimeService interface declares two methods which are described in the following:

The getCurrentDateTime() method returns the current date and time as a LocalDateTime object.
The getCurrentTimestamp() method returns the current date and time as a Timestamp object.

The source code of the DateTimeService interface looks as follows:

import org.joda.time.LocalDateTime;
import java.sql.Timestamp;

public interface DateTimeService {

    public LocalDateTime getCurrentDateTime();

    public Timestamp getCurrentTimestamp();
}

Because our application is interested in the “real” time, we have to implement this interface and create a component which returns the real date and time. We can do this by following these steps:

Create a CurrentTimeDateTimeService class which implements the DateTimeService interface.
Annotate the class with the @Profile annotation and set the name of the profile to ‘application’. This means that the component can be registered to the Spring container when the active Spring profile is ‘application’.
Annotate the class with the @Component annotation. This ensures that the class is found during classpath scanning.
Implement the methods declared in the DateTimeService interface. Each method must return the current date and time.

The source code of the CurrentTimeDateTimeService looks as follows:

import org.joda.time.LocalDateTime;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;

@Profile("application")
@Component
public class CurrentTimeDateTimeService implements DateTimeService {

    @Override
    public LocalDateTime getCurrentDateTime() {
        return LocalDateTime.now();
    }

    @Override
    public Timestamp getCurrentTimestamp() {
        return new Timestamp(System.currentTimeMillis());
    }
}

Let’s move on and start implementing the repository layer of our example application.

Implementing the Repository Layer

First we have create a repository interface which provides CRUD operations for todo entries. This interface declares five methods which are described in the following:

The Todo add(Todo todoEntry) method saves a new todo entry to the database and returns the information of the saved todo entry.
The Todo delete(Long id) method deletes a todo entry and returns the deleted todo entry.
The ListfindAll()method returns all todo entries which are found from the database.
The Todo findById(Long id) returns the information of a single todo entry.
The Todo update(Todo todoEntry) updates the information of a todo entry and returns the updated todo entry.

The source code of the TodoRepository interface looks as follows:

import java.util.List;

public interface TodoRepository {

    public Todo add(Todo todoEntry);

    public Todo delete(Long id);

    public List<Todo> findAll();

    public Todo findById(Long id);

    public Todo update(Todo todoEntry);
}

Next we have to implement the TodoRepository interface. When we do that, we must follow the following rule:

All database queries created by jOOQ must be executed inside a transaction. The reason for this is that our application uses the TransactionAwareDataSourceProxy class, and if we execute database queries without a transaction, jOOQ will use a different connection for each operation. This can lead into race condition bugs.

Typically the service layer acts as a transaction boundary, and each call to a jOOQ repository should be made inside a transaction. However, because programmers make mistakes too, we cannot trust that this is the case. That is why we must annotate the repository class or its methods with the @Transactional annotation.

Now that we have got that covered, we are ready to create our repository class.

Creating the Repository Class

We can create the “skeleton” of our repository class by following these steps:

Create a JOOQTodoRepository class and implement the TodoRepository interface.
Annotate the class with the @Repository annotation. This ensures that the class is found during the classpath scan.
Add a DateTimeService field to the created class. As we remember, the DateTimeService interface declares the methods which are used to get the current date and time.
Add a DSLContext field to the created class. This interface acts as an entry point to the jOOQ API and we can build our SQL queries by using it.
Add a public constructor to the created class and annotate the constructor with the @Autowired annotation. This ensures that the dependencies of our repository are injected by using constructor injection.
Add a private Todo convertQueryResultToModelObject(TodosRecord queryResult) method to the repository class. This utility method is used by the public methods of our repository class. Implement this method by following these steps:
1. Create a new Todo object by using the information of the TodosRecord object given as a method parameter.
2. Return the created object.

The relevant part of the JOOQTodoRepository class looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;


@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DateTimeService dateTimeService;

    private final DSLContext jooq;

    @Autowired
    public JOOQTodoRepository(DateTimeService dateTimeService, DSLContext jooq) {
        this.dateTimeService = dateTimeService;
        this.jooq = jooq;
    }

    private Todo convertQueryResultToModelObject(TodosRecord queryResult) {
        return Todo.getBuilder(queryResult.getTitle())
                .creationTime(queryResult.getCreationTime())
                .description(queryResult.getDescription())
                .id(queryResult.getId())
                .modificationTime(queryResult.getModificationTime())
                .build();
    }
}

Let’s move on and implement the methods which provide CRUD operations for todo entries.

Adding a New Todo Entry

The public Todo add(Todo todoEntry) method of the TodoRepository interface is used to add a new todo entries to the database. We can implement this method by following these steps:

Add a private TodosRecord createRecord(Todo todoEntry) method to the repository class and implement this method following these steps:
1. Get the current date and time by calling the getCurrentTimestamp() method of the DateTimeService interface.
2. Create a new TodosRecord object and set its field values by using the information of the Todo object given as a method parameter.
3. Return the created TodosRecord object.
Add the add() method to the JOOQTodoRepository class and annotate the method with the @Transactional annotation. This ensures that the INSERT statement is executed inside a read-write transaction.
Implement the add() method by following these steps:
1. Add a new todo entry to the database by following these steps:
  1. Create a new INSERT statement by calling the insertInto(Table table) method of the DSLContext interface and specify that you want to insert information to the todos table.
  2. Create a new TodosRecord object by calling the createRecord() method. Pass the Todo object as a method parameter.
  3. Set the inserted information by calling the set(Record record) method of the InsertSetStep interface. Pass the created TodosRecord object as a method parameter.
  4. Ensure that the INSERT query returns all inserted fields by calling the returning() method of the InsertReturningStep interface.
  5. Get the TodosRecord object which contains the values of all inserted fields by calling the fetchOne() method of the InsertResultStep interface.
2. Convert the TodosRecord object returned by the INSERT statement into a Todo object by calling the convertQueryResultToModelObject() method.
3. Return the created the Todo object.

The relevant part of the JOOQTodoRepository class looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DateTimeService dateTimeService;

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity

    @Transactional
    @Override
    public Todo add(Todo todoEntry) {
        TodosRecord persisted = jooq.insertInto(TODOS)
                .set(createRecord(todoEntry))
                .returning()
                .fetchOne();

        return convertQueryResultToModelObject(persisted);
    }

    private TodosRecord createRecord(Todo todoEntry) {
        Timestamp currentTime = dateTimeService.getCurrentTimestamp();

        TodosRecord record = new TodosRecord();
        record.setCreationTime(currentTime);
        record.setDescription(todoEntry.getDescription());
        record.setModificationTime(currentTime);
        record.setTitle(todoEntry.getTitle());

        return record;
    }

    private Todo convertQueryResultToModelObject(TodosRecord queryResult) {
        return Todo.getBuilder(queryResult.getTitle())
                .creationTime(queryResult.getCreationTime())
                .description(queryResult.getDescription())
                .id(queryResult.getId())
                .modificationTime(queryResult.getModificationTime())
                .build();
    }
}

The section 4.3.3. The INSERT statement of the jOOQ reference manual provides additional information about inserting data to the database.

Let’s move on and find out how we can find all entries which are stored to the database.

Finding All Todo Entries

The public List findAll() method of the TodoRepository interface returns all todo entries which are stored to the database. We can implement this method by following these steps:

Add the findAll() method to the repository class and annotate the method with the @Transactional annotation. Set the value of its readOnly attribute to true. This ensures that the SELECT statement is executed inside a read-only transaction.
Get all todo entries from the database by following these steps:
1. Create a new SELECT statement by calling the selectFrom(Table table) method of the DSLContext interface and specify that you want to select information from the todos table.
2. Get a list of TodosRecord objects by calling the fetchInto(Class type) method of the ResultQuery interface.
Iterate the returned list of TodosRecord objects and convert each TodosRecord object into a Todo object by calling the convertQueryResultToModelObject() method. Add each Todo object to the list of Todo objects.
Return the List which contains the found Todo objects.

The relevant part of the JOOQTodoRepository class looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity

    @Transactional(readOnly = true)
    @Override
    public List<Todo> findAll() {
        List<Todo> todoEntries = new ArrayList<>();

        List<TodosRecord> queryResults = jooq.selectFrom(TODOS).fetchInto(TodosRecord.class);

        for (TodosRecord queryResult: queryResults) {
            Todo todoEntry = convertQueryResultToModelObject(queryResult);
            todoEntries.add(todoEntry);
        }

        return todoEntries;
    }

    private Todo convertQueryResultToModelObject(TodosRecord queryResult) {
        return Todo.getBuilder(queryResult.getTitle())
                .creationTime(queryResult.getCreationTime())
                .description(queryResult.getDescription())
                .id(queryResult.getId())
                .modificationTime(queryResult.getModificationTime())
                .build();
    }
}

The section 4.3.2. The SELECT Statement of the jOOQ reference manual provides more information about selecting information from the database.

Next we will find out how we can get a single todo entry from the database.

Finding a Single Todo Entry

The public Todo findById(Long id) method of the TodoRepository interface returns the information of a single todo entry. We can implement this method by following these steps:

Add the findById() method the repository class and annotate the method with the @Transactional annotation. Set the value of its readOnly attribute to true. This ensures that the SELECT statement is executed inside a read-only transaction.
Get the information of a single todo entry from the database by following these steps:
1. Create a new SELECT statement by calling the selectFrom(Table table) method of the DSLContext interface and specify that you want to select information from the todos table.
2. Specify the WHERE clause of the SELECT statement by calling the where(Collection conditions) method of the SelectWhereStep interface. Ensure that the SELECT statement returns only the todo entry which id was given as a method parameter.
3. Get the TodosRecord object by calling the fetchOne() method of the ResultQuery interface.
If the returned TodosRecord object is null, it means that no todo entry was found with the given id. If this is the case, throw a new TodoNotFoundException.
Convert TodosRecord object returned by the SELECT statement into a Todo object by calling the convertQueryResultToModelObject() method.
Return the created Todo object.

The relevant part of the JOOQTodoRepository looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity.

    @Transactional(readOnly = true)
    @Override
    public Todo findById(Long id) {
        TodosRecord queryResult = jooq.selectFrom(TODOS)
                .where(TODOS.ID.equal(id))
                .fetchOne();

        if (queryResult == null) {
            throw new TodoNotFoundException("No todo entry found with id: " + id);
        }

        return convertQueryResultToModelObject(queryResult);
    }

    private Todo convertQueryResultToModelObject(TodosRecord queryResult) {
        return Todo.getBuilder(queryResult.getTitle())
                .creationTime(queryResult.getCreationTime())
                .description(queryResult.getDescription())
                .id(queryResult.getId())
                .modificationTime(queryResult.getModificationTime())
                .build();
    }
}

The section 4.3.2. The SELECT Statement of the jOOQ reference manual provides more information about selecting information from the database.

Let’s find out how we can delete a todo entry from the database.

Deleting a Todo Entry

The public Todo delete(Long id) method of the TodoRepository interface is used to delete a todo entry from the database. We can implement this method by following these steps:

Add the delete() method to the repository class and annotate the method with the @Transactional annotation. This ensures that the DELETE statement is executed inside a read-write transaction.
Implement this method by following these steps:
1. Find the deleted Todo object by calling the findById(Long id) method. Pass the id of the deleted todo entry as a method parameter.
2. Delete the todo entry from the database by following these steps:
  1. Create a new DELETE statement by calling the delete(Table table) method of the DSLContext interface and specify that you want to delete information from the todos table.
  2. Specify the WHERE clause of the DELETE statement by calling the where(Collection conditions) method of the DeleteWhereStep interface. Ensure that the DELETE statement deletes the todo entry which id was given as a method parameter.
  3. Execute the the DELETE statement by calling the execute() method of the Query interface.
3. Return the information of the deleted todo entry.

The relevant part of the JOOQTodoRepository class looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity

    @Transactional
    @Override
    public Todo delete(Long id) {
        Todo deleted = findById(id);

        int deletedRecordCount = jooq.delete(TODOS)
                .where(TODOS.ID.equal(id))
                .execute();

        return deleted;
    }
}

The section 4.3.5. The DELETE Statement of the jOOQ reference manual provides additional information about deleting data from the database.

Let’s move on and find out how we can update the information of an existing todo entry.

Updating an Existing Todo Entry

The public Todo update(Todo todoEntry) method of the TodoRepository interface updates the information of an existing todo entry. We can implement this method by following these steps:

Add the update() method to the repository class and annotate the method with the @Transactional annotation. This ensures that the UPDATE statement is executed inside a read-write transaction.
Get the current date and time by calling the getCurrentTimestamp() method of the DateTimeService interface.
Update the information of the todo entry by following these steps:
1. Create a new UPDATE statement by calling the update(Table table) method of the DSLContext interface and specify that you want to update information found from the todos table.
2. Set the new description, modification time, and title by calling the set(Field field, T value) method of the UpdateSetStep interface.
3. Specify the WHERE clause of the UPDATE statement by calling the where(Collection conditions) method of the UpdateWhereStep interface. Ensure that the UPDATE statement updates the todo entry which id is found from the Todo object given as a method parameter.
4. Execute the UPDATE statement by calling the execute() method of the Query interface.
Get the information of the updated todo entry by calling the findById() method. Pass the id of the updated todo entry as a method parameter.
Return the information of the updated todo entry.

The relevant part of the JOOQTodoRepository class looks as follows:

import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;

import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private final DateTimeService dateTimeService;

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity.

    @Transactional
    @Override
    public Todo update(Todo todoEntry) {
        Timestamp currentTime = dateTimeService.getCurrentTimestamp();
       
        int updatedRecordCount = jooq.update(TODOS)
                .set(TODOS.DESCRIPTION, todoEntry.getDescription())
                .set(TODOS.MODIFICATION_TIME, currentTime)
                .set(TODOS.TITLE, todoEntry.getTitle())
                .where(TODOS.ID.equal(todoEntry.getId()))
                .execute();

        return findById(todoEntry.getId());
    }
}

The section 4.3.4. The UPDATE Statement of the jOOQ reference manual provides additional information about updating the information which is stored to the database.
If you are using Firebird or PostgreSQL databases, you can use the RETURNING clause in the update statement (and avoid the extra select clause).

That is all folks. Let’s summarize what we learned from this blog post.

Summary

We have now implemented CRUD operations for todo entries. This tutorial has taught us three things:

We learned how we can get the current date and time in a way which doesn’t prevent us from writing automated tests for our example application.
We learned how we can ensure that all database queries executed by jOOQ are executed inside a transaction.
We learned how we can create INSERT, SELECT, DELETE, and UPDATE statements by using the jOOQ API.

The next part of this tutorial describes how we can add a search function, which supports sorting and pagination, to our example application.