Using jOOQ With Spring: Sorting and Pagination

JOOQ is a library which helps us to get in control of our SQL. It can generate code from our database and help us to 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 application, generate code from our database, and add CRUD operations to our jOOQ repository.

This time we will learn how we can implement a simple search function which supports sorting and pagination.

Let’s get started.

Adding Pagination and Sorting Support to the Web Layer

When we implement a search function which must support both pagination and sorting, we have to figure out a way to provide the page number, page size, name of the sort field, and the sort order to our backend.

We could of course implement a component which supports this but it isn’t so simple than it sounds. It is pretty easy to create a HandlerMethodArgumentResolver which extracts this information from a HTTP request and converts it to a object which is then passed forward to our controller method as a method argument. The problem is that there are many "exceptional" situations which makes this task pretty tricky. For example,

• If this information is not found from the HTTP request, we must fallback to default values.
• If the required information is missing (e.g. page number is given without specifying page size), we must either fallback to default values or return an error to the user of our REST API.

Luckily, we don’t have to implement this component. The Spring Data Commons project has a component which extracts paging and sorting information from HTTP requests and allows us to inject this information into controller methods.

Let’s find out we can get the Spring Data Commons binaries with Maven.

Getting the Required Dependencies with Maven

We can get the required binaries with Maven by adding the following dependency declaration to the dependencies section of our POM file:

<dependency>
	<groupId>org.springframework.data</groupId>
	<artifactId>spring-data-commons</artifactId>
	<version>1.7.1.RELEASE</version>
</dependency>

Our next step is to make some changes to the application context configuration of our example application. Let’s move on and find out what kind of changes we must do.

Configuring the Application Context

We can enable the web pagination support of Spring Data by making a one simple change to the application context configuration class that configures the web layer of our example application. We have to annotate the configuration class with the @EnableSpringDataWebSupport annotation. This ensures that the required beans are registered automatically.

The relevant part of the WebAppContext class looks as follows:

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.web.config.EnableSpringDataWebSupport;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

@Configuration
@ComponentScan({
        "net.petrikainulainen.spring.jooq.common.controller",
        "net.petrikainulainen.spring.jooq.todo.controller"
})
@EnableWebMvc
@EnableSpringDataWebSupport
public class WebAppContext extends WebMvcConfigurerAdapter {
	//Other methods are omitted for the sake of clarity
}

That is it. We have now made the required changes to the application context configuration of our example application. Let’s find out how we can use the web pagination support in our application.

Using Web Pagination

When we want to sort and paginate our query results, we have to follow these steps:

1. Add the paging and sorting configuration to the HTTP request.
2. Add a Pageable method parameter to the controller method.

First, we can add the paging and sorting configuration to the HTTP request by using the following request parameters:

• The page request parameter specifies the requested page number.
• The size request parameter specifies the size of the requested page.
• The sort request parameter specifies the properties which are used to sort the query results. This value of this request parameter must follow this syntax: property,property(,ASC|DESC). If the sort direction isn’t given, the results are sorted in ascending order. If you want to switch the sort order, you have to use multiple sort parameters (e.g. ?sort=title&sort=id,desc).

Second, we have to add a Pageable method parameter to our controller method. The relevant part of the TodoController class looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.validation.Valid;
import java.util.List;


@RestController
@RequestMapping("/api/todo")
public class TodoController {

    private final TodoCrudService crudService;

    private final TodoSearchService searchService;

    @Autowired
    public TodoController(TodoCrudService crudService, TodoSearchService searchService) {
        this.crudService = crudService;
        this.searchService = searchService;
    }

    @RequestMapping(value = "/search", method = RequestMethod.GET)
    public Page<TodoDTO> findBySearchTerm(@RequestParam("searchTerm") String searchTerm, Pageable pageable) {
        return searchService.findBySearchTerm(searchTerm, pageable);
    }
}

We can now add the search function to our jOOQ repository. Let’s find out how this is done.

Implementing the Repository Layer

The first thing that we have to do is to add a new public method to the TodoRepository interface. The findBySearchTerm(String searchTerm, Pageable pageable) method finds the todo entries whose title or description contains the given search term and returns the query results by following the paging and sorting configuration given as a method parameter.

The relevant part of the TodoRepository interface looks as follows:

import org.springframework.data.domain.Pageable;

import java.util.List;

public interface TodoRepository {

    public Page<Todo> findBySearchTerm(String searchTerm, Pageable pageable);

	//Other methods are omitted for the sake of clarity
}

The implementation of this method has three responsibilities:

1. It must find the todo entries whose title or description contains the given search term.
2. It must process the sorting and paging options found from the Pageable object and transform them into a form which is understood by jOOQ.
3. It must create the returned Page object. This object contains information about the returned page and the actual search results.

Let’s move on and find out how we can find todo entries whose title or description contains the given search term.

Implementing the Search Query

We can implement the search query by following these steps:

1. Add the findBySearchTerm(String searchTerm, Pageable pageable) method to the JOOQTodoRepository class.
2. Annotate the method with the @Transactional annotation and set the value of its readOnly attribute to true. This ensures that the SELECT statement is executed inside a read-only transaction.
3. Implement the findBySearchTerm() method by following these steps:
   1. Create the like expression that is used in our database query.
   2. 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.
   3. Specify the WHERE clause of the SELECT statement by calling the where(Condition... conditions) method of the SelectWhereStep interface. Create the method parameter of this method by following these steps:
      1. Create the like conditions for the description and title columns of the todos table by calling the likeIgnoreCase(String value) method of the Field interface. Pass the created like expression as a method parameter.
      2. Combine the created like conditions by using the or(Condition other) method of the Condition interface.
   4. Get a list of TodosRecord objects by calling the fetchInto(Class type) method of the ResultQuery interface. Pass a TodosRecord.class object as a method parameter.
   5. Transform the list of TodosRecord objects into a list of Todo objects by calling the private convertQueryResultsToModelObjects() method. This method iterates the list of TodosRecord objects and converts each TodosRecord object into a Todo object by calling the convertQueryResultToModelObject() method. Each Todo object is added to a list which is returned when all TodosRecord objects have been processed.
   6. Create a new PageImpl object and pass the list of Todo objects as a constructor argument.
   7. Return the created PageImpl object.

The source code of our implementation looks as follows:

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
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 DateTimeService dateTimeService;

    private final DSLContext jooq;

    //The constructor is omitted for the sake of clarity

    @Transactional(readOnly = true)
    @Override
    public Page<Todo> findBySearchTerm(String searchTerm, Pageable pageable) {
        String likeExpression = "%" + searchTerm + "%";

        List<TodosRecord> queryResults = jooq.selectFrom(TODOS)
                .where(
                        TODOS.DESCRIPTION.likeIgnoreCase(likeExpression)
                                .or(TODOS.TITLE.likeIgnoreCase(likeExpression))
                )
                .fetchInto(TodosRecord.class);

        List<Todo> todoEntries = convertQueryResultsToModelObjects(queryResults);
		return new PageImpl<>(todoEntries);
    }

    private List<Todo> convertQueryResultsToModelObjects(List<TodosRecord> queryResults) {
        List<Todo> todoEntries = new ArrayList<>();

        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();
    }
	
	//Other methods are omitted for the sake of clarity
}

We have now created a repository method which searches todo entries from the database. Our next step is to sort the query results of this database query.

Sorting the Query Results

Before we can sort the query results of our search query, we must understand how we can get the sorting options of our database query from the Pageable object.

• We can get a reference to a Sort object by calling the getSort() method of the Pageable interface. This object contains the sorting options found from the HTTP request.
• The Sort object can contain zero or more sorting options. The iterator() method of the Sort class returns an Iterator<Sort.Order> object which we can use when we want to process each sorting option of our database query.
• The Sort.Order class contains the property name and the sort direction.

In other words, we have to fulfil the following requirements:

• We must support a situation where no sort options are specified.
• We must support a situation where our query results are sorted by using multiple columns.
• We must assume that each column has its own sort order.

We can fulfil these requirements by making the following changes to the JOOQTodoRepository class:

1. Add a private getTableField(String sortFieldName) method to the repository class and implement this method by following these steps:
   1. Use reflection to get the Field object which provides information about the requested field of the TODOS class.
   2. If the field isn't found or we cannot access it, throw a new InvalidDataAccessApiUsageException.
   3. If the field is found, cast the returned Field object into a TableField object and return it.
2. Add a private convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) method to the repository class and implement the method by following these steps:
   1. If the sort order of this field is ascending, call the asc() method of the Field interface and return the returned object.
   2. Otherwise call the desc() method of the Field interface and return the returned object.
3. Add a private getSortFields(Sort sortSpecification) method to the repository class and implement it by following these steps:
   1. Create a new Collection which contains SortField<?> objects.
   2. If the sort options are not found, return an empty Collection object.
   3. Iterate the Sort.Order objects found from the Sort object given as a method parameter, and process each Sort.Order object by following these steps:
      1. Convert each Sort.Order object into a SortField<?> object by using the getTableField() and convertTableFieldToSortField() methods.
      2. Add each SortField<?> object to the Collection which was created at step one.
   4. Return the Collection of SortField<?> objects.
4. Sort the query results by following these steps:
   1. Get the Sort object by calling the getSort() method of the Pageable interface.
   2. Get the Collection<SortField<?>> object by calling the getSortFields() method. Pass the Sort object as a method parameter.
   3. Create an ORDER BY clause by calling the orderBy(Collection<? extends SortField<?>> fields) method of the SelectSeekStepN interface and pass the Collection<SortField<?>> object as a method parameter.

The source code of our implementation looks as follows (the relevant part are highlighted):

import org.jooq.DSLContext;
import org.jooq.SortField;
import org.jooq.TableField;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

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(readOnly = true)
    @Override
    public Page<Todo> findBySearchTerm(String searchTerm, Pageable pageable) {
        String likeExpression = "%" + searchTerm + "%";

        List<TodosRecord> queryResults = jooq.selectFrom(TODOS)
                .where(
                        TODOS.DESCRIPTION.likeIgnoreCase(likeExpression)
                                .or(TODOS.TITLE.likeIgnoreCase(likeExpression))
                )
                .orderBy(getSortFields(pageable.getSort()))
                .fetchInto(TodosRecord.class);

        List<Todo> todoEntries = convertQueryResultsToModelObjects(queryResults);
		return new PageImpl<>(todoEntries);
    }

    private Collection<SortField<?>> getSortFields(Sort sortSpecification) {
        Collection<SortField<?>> querySortFields = new ArrayList<>();

        if (sortSpecification == null) {
            return querySortFields;
        }

        Iterator<Sort.Order> specifiedFields = sortSpecification.iterator();

        while (specifiedFields.hasNext()) {
            Sort.Order specifiedField = specifiedFields.next();

            String sortFieldName = specifiedField.getProperty();
            Sort.Direction sortDirection = specifiedField.getDirection();

            TableField tableField = getTableField(sortFieldName);
            SortField<?> querySortField = convertTableFieldToSortField(tableField, sortDirection);
            querySortFields.add(querySortField);
        }

        return querySortFields;
    }

    private TableField getTableField(String sortFieldName) {
        TableField sortField = null;
        try {
            Field tableField = TODOS.getClass().getField(sortFieldName);
            sortField = (TableField) tableField.get(TODOS);
        } catch (NoSuchFieldException | IllegalAccessException ex) {
            String errorMessage = String.format("Could not find table field: {}", sortFieldName);
            throw new InvalidDataAccessApiUsageException(errorMessage, ex);
        }

        return sortField;
    }

    private SortField<?> convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) {
        if (sortDirection == Sort.Direction.ASC) {
            return tableField.asc();
        }
        else {
            return tableField.desc();
        }
    }

    private List<Todo> convertQueryResultsToModelObjects(List<TodosRecord> queryResults) {
        List<Todo> todoEntries = new ArrayList<>();

        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 other methods are omitted for the sake of clarity
}

We have now added sorting support to our search query. Let’s move on and finish our search function by adding pagination support to the findBySearchTerm() method.

Paginating the Query Results

We can paginate the query results of our search query by adding the LIMIT .. OFFSET clause to our database query. We can do this by making the following changes to the implementation of our database query:

1. Specify the number of returned rows by calling the limit(int NumberOfRows) method of the SelectLimitStep interface and pass the page size a method parameter (You can get the page size by calling the getPageSize() method of the Pageable interface).
2. Specify the offset by calling the offset(int offset) method of the SelectOffsetStep interface and pass the offset as a method parameter (You can get the offset by calling the getOffset() method of the Pageable interface).
3. Add a private long findCountByLikeExpression(String likeExpression) method to the repository class. Implement this method by following these steps:
   1. Find the number of todo entries by calling the fetchCount(Select<?> query) method of the DSLContext class and pass the used SELECT query as method parameter.
   2. Return the number of todo entries.
4. Get the number of todo entries, which matches with the given like expression, by calling the private findCountByLikeExpression() method in the findBySearchTerm() method. Pass the used like expression as a method paramer.
5. Create a new PageImpl object and pass the following information as constructor arguments:
   1. The list which contains the found todo entries.
   2. The Pageable object given as a method parameter.
   3. The number of todo entries which matches with the given like expression.
6. Return the created PageImpl object.

After we have made these changes to our repository method, the source code of our repository method looks as follows (the changes are highlighted):

import org.jooq.DSLContext;
import org.jooq.SortField;
import org.jooq.TableField;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

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(readOnly = true)
    @Override
    public Page<Todo> findBySearchTerm(String searchTerm, Pageable pageable) {
        String likeExpression = "%" + searchTerm + "%";

        List<TodosRecord> queryResults = jooq.selectFrom(TODOS)
                .where(
                        TODOS.DESCRIPTION.likeIgnoreCase(likeExpression)
                                .or(TODOS.TITLE.likeIgnoreCase(likeExpression))
                )
                .orderBy(getSortFields(pageable.getSort()))
                .limit(pageable.getPageSize()).offset(pageable.getOffset())
                .fetchInto(TodosRecord.class);

	    List<Todo> todoEntries = convertQueryResultsToModelObjects(queryResults);
        long totalCount = findCountByLikeExpression(likeExpression);

		return new PageImpl<>(todoEntries, pageable, totalCount);
    }
	
    private long findCountByLikeExpression(String likeExpression) {
           return jooq.fetchCount(jooq.select()
		   			.from(TODOS)
					.where(
							TODOS.DESCRIPTION.likeIgnoreCase(likeExpression)
									.or(TODOS.TITLE.likeIgnoreCase(likeExpression))
					)
           );
	}
	
    private Collection<SortField<?>> getSortFields(Sort sortSpecification) {
        Collection<SortField<?>> querySortFields = new ArrayList<>();

        if (sortSpecification == null) {
            return querySortFields;
        }

        Iterator<Sort.Order> specifiedFields = sortSpecification.iterator();

        while (specifiedFields.hasNext()) {
            Sort.Order specifiedField = specifiedFields.next();

            String sortFieldName = specifiedField.getProperty();
            Sort.Direction sortDirection = specifiedField.getDirection();

            TableField tableField = getTableField(sortFieldName);
            SortField<?> querySortField = convertTableFieldToSortField(tableField, sortDirection);
            querySortFields.add(querySortField);
        }

        return querySortFields;
    }

    private TableField getTableField(String sortFieldName) {
        TableField sortField = null;
        try {
            Field tableField = TODOS.getClass().getField(sortFieldName);
            sortField = (TableField) tableField.get(TODOS);
        } catch (NoSuchFieldException | IllegalAccessException ex) {
            String errorMessage = String.format("Could not find table field: {}", sortFieldName);
            throw new InvalidDataAccessApiUsageException(errorMessage, ex);
        }

        return sortField;
    }

    private SortField<?> convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) {
        if (sortDirection == Sort.Direction.ASC) {
            return tableField.asc();
        }
        else {
            return tableField.desc();
        }
    }

    private List<Todo> convertQueryResultsToModelObjects(List<TodosRecord> queryResults) {
        List<Todo> todoEntries = new ArrayList<>();

        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();
    }
	
	//Other methods are omitted for the sake of clarity
}

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

Summary

We have now implemented a search function that supports sorting and pagination. This tutorial has taught us four things:

• We learned how we can use the web pagination support of the Spring Data Commons project.
• We learned how we can add the ORDER BY clause to a database query.
• We learned how we can add the LIMIT .. OFFSET clause to a database query.
• We learned how we can create new Page objects that contain our query results and useful pagination metadata.

The next part of this tutorial describes how we can integrate Spring Data JPA and jOOQ, and more importantly, why we should do it.

P.S. You can get the example application of this blog post from Github.