JOOQ is a library which helps us to get 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 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.
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.
- Using jOOQ with Spring: CRUD describes how we can add CRUD operations for a simple application which manages todo entries. Because it covers the information needed to create jOOQ repositories with Spring, I recommend that you read it before reading this blog post.
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 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 finds 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 which 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 API documation of the @EnableSpringDataWebSupport annotation provides more information about the beans which are registered when this annotation is used.
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:
- Add the paging and sorting configuration to the HTTP request.
- 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 List<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 TodoService 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 List<Todo> findBySearchTerm(String searchTerm, Pageable pageable);
//Other methods are omitted for the sake of clarity
}
The implementation of this method has two responsibilities:
- It must find the todo entries whose title or description contains the given search term.
- It must process the sorting and paging options found from the Pageable object and transform them into a form which is understood by jOOQ.
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:
- Add the findBySearchTerm(String searchTerm, Pageable pageable) method to the JOOQTodoRepository class.
- Annotate the method with the @Transactional annotation and set the value of its readOnly attribute to true.
- Implement the findBySearchTerm() method by following these steps:
- Create the like expression which is used in our database query.
- 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.
- Specify the WHERE clause of the SELECT statement by calling the where(Collection conditions) method of the SelectWhereStep interface. Create the method parameter of this method by following these steps:
- 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 like expression as a method parameter.
- Combine the created like conditions by using the or(Condition other) method of the Condition interface.
- 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.
- 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.
- Return the list of Todo objects.
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 List<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);
return convertQueryResultsToModelObjects(queryResults);
}
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
}
The database query of this example is very simple. If you need to create more complex database queries, you should read the section 4.6. Conditional expressions of the jOOQ reference manual. It describes how you can use conditional expressions in your database queries.
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:
- Add a private getTableField(String sortFieldName) method to the repository class and implement this method by following these steps:
- Use reflection to get a Field object which provides information about the requested field of the Todos object.
- If the field isn’t found or we cannot access it, throw a new InvalidDataAccessApiUsageException.
- If the field is found, cast the returned Field object into a TableField object and return it.
- Add a private convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) method to the repository class and implement the method by following these steps:
- If the sort order of this field is ascending, call the asc() method of the Field interface and return the returned object.
- Otherwise call the desc() method of the Field interface and return the returned object.
- Add a private getSortFields(Sort sortSpecification) method to the repository class and implement it by following these steps:
- Create a new Collection which contains SortField<?> objects.
- If the sort options are not found, return an empty Collection object.
- 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:
- Convert each Sort.Order object into a SortField<?> object by using the getTableField() and convertTableFieldToSortField() methods.
- Add each SortField<?> object to the Collection which was created at step one.
- Return the Collection of SortField<?> objects.
- Sort the query results by following these steps:
- Get the Sort object by calling the getSort() method of the Pageable interface.
- Get the Collection<SortField<?>> object by calling the getSortFields() method. Pass the Sort object as a method parameter.
- 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 List<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);
return convertQueryResultsToModelObjects(queryResults);
}
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
}
This solution works but it leaks the implementation details of our repository layer (and database) to the clients of our REST API. We could avoid this by specifying a group of allowed aliases for the column names and implement a translation component which converts these aliases into the field names of the Todos class.
However, because it would increase the complexity of our repository class, we will not do it.
This is actually a great example of a leaky abstraction. This term was originally popularized by Joel Spolsky. He “invented” the law of leaky abstractions which states that:
All non-trivial abstractions, to some degree, are leaky.
You can get more information about the ORDER BY clause by reading the section 4.3.2.9 The ORDER BY clause of the jOOQ reference manual.
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:
- 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).
- 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).
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 List<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);
return convertQueryResultsToModelObjects(queryResults);
}
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
}
You can the more information about the LIMIT .. OFFSET clause by reading the section 4.3.2.10 The LIMIT .. OFFSET clause of the jOOQ reference manual.
If you need to implement “an eternal scroll” (like the one Facebook has in the timeline), you should consider using the seek method. You can get more information about this from the jOOQ website:
- Faster SQL Paging with jOOQ Using the Seek Method
- Faster SQL Pagination with Keysets, Continued
- The SEEK clause @ jOOQ Reference Manual
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 which supports sorting and pagination. This tutorial has taught us three 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 a database query.
The next part of this tutorial describes how we can integrate Spring Data JPA and jOOQ, and more importantly, why we should do it.
- The example application of this blog post is available at Github.
| Reference: | Using jOOQ with Spring: Sorting and Pagination from our JCG partner Petri Kainulainen at the Petri Kainulainen blog. |
