Dynamic DB Queries with Spring Boot RESTful service
In this section we will dynamically write queries for a Spring Boot RESTful app.
Dynamic queries are important for searching and filtering data from the database where the search conditions are not constant.
Our project consists simple blog application where users can post comments and list comments.
In a previous post, we created a Spring Boot RESTful service for our blog with Pagination.
We implemented how to filter users comments for a particular user, retrieve all user comments and save new comments.
We used Spring Data’s JpaRepository and created simple queries using Spring Data JPA query methods.
Using Spring Data JPA query methods
Let’s assume we have a domain entity class called Comments.java which has an attribute userId. This attribute stores the id of the user who has made the comment.
To find all comments made by a particular user, we extend Spring Data’s JpaRepository and use query methods. Spring JPA will create queries from method names that we define.
For example, the below method will find all comments for a particular userId.
Optional<Page<Comment>> findAllByUserId(String userId, Pageable page);Limitations of Spring Data JPA query methods
However, what if we want to filter comments based on not only the userId, but also the comment description, and a time period when the comment was posted? We also want to know if the user is a long term user who has been reading our blog for at least two years.
All of these filter conditions are optional, which means they may or may not be present in the API request. So, how do we add dynamic conditions to our database query based on the API request?
Dynamic Queries with Specifications
There are many ways to create dynamic queries in Spring JPA. One of these methods is using Specifications.
The Specification interface lets us dynamically build queries using the CriteriaBuilder interface.
The Specification interface is defined as below. More details can be found at the official Spring JPA documentation.
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
CriteriaBuilder builder);
}We will use this interface to define our dynamic queries for filtering user comments.
Let us first create the domain class and repository interface.
Comments.java
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.time.Instant;
import java.util.UUID;
@Entity
@Table(name="comment")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Comment {
@Id
@GeneratedValue()
private UUID id;
@Column(nullable = false)
private String userId;
@Column(nullable = false)
private String description;
private Instant createdDate;
@Column(nullable = false)
private String createdBy;
private Instant updatedDate;
}CommentRespository.java
The respository interface now extends not only JpaRespository, but also the JpaSpecificationExecutor interface.
We overload the findAll() method that accepts a parameter of type Specification. We have the option to query with and without pagination .
We also have the default findAll() query method without using Specification.
import com.pal.vidisha.springreact.myblog.domain.Comment;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
import java.util.UUID;
@Repository
public interface CommentRepository extends
JpaRepository <Comment, UUID>,
JpaSpecificationExecutor<Comment> {
Optional<Page<Comment>> findAllByUserId(String userId, Pageable page);
Page<Comment> findAll(Specification<Comment> specification, Pageable page);
List<Comment> findAll(Specification<Comment> specification);
Page<Comment> findAll( Pageable page);
List<Comment> findAll();
}Creating a custom Specification class
Now let us create a custom specification class called CommentSpecification.java
We define methods that returns a Specification for each of our criteria.
- Check if user is a long term user — The condition for this is to check if the createdDate attribute is less than 01/01/2020.
public Specification<Comment> isLongTermUser() {
return (root, query, cb) ->
cb.lessThan(
root.get("createdDate"),
LocalDate.of(2020, 01, 01)
.atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant()
);
}2. Check if comment description contains a text — We check if the description attribute contains our search text.
public Specification<Comment> commentLike (String searchText) {
return (root, query, cb) ->
cb.like(
root.get("description"),
"%"+ searchText + "%"
);
}3. Check if comment was posted in a certain timeframe — We check if the the createdDate attribute’s value is between the values of fromDate and toDate.
public Specification<Comment> createdBetween (
Instant fromDate,
Instant toDate) {
return (root, query, cb) ->
cb.between(
root.get("createdDate"),
fromDate, toDate
);
}4. Check if comment is made by a particular user — We check if the user’s userId is equal to the attribute userId.
public Specification<Comment> byUser ( String userId) {
return (root, query, cb) ->
cb.equal(
root.get("userId"),
userId
);
}Finally, we define a method for dynamically adding our various conditions with a AND clause.
Only if the parameter is not empty, we add the parameter to the criteria builder to dynamically build our query.
public Specification<Comment> conditionalSearchForUser(
String searchText,
Instant fromDate,
Instant toDate,
String userId,
boolean checkIsLongTermUser
) {Specification spec = null;spec = where(byUser(userId));if(checkIsLongTermUser == true)
spec = spec.and(isLongTermUser());
if(searchText!=null && !searchText.isBlank())
spec = spec.and(commentLike(searchText));if(fromDate!= null && !fromDate.isBlank()
&& toDate!= null && !toDate.isBlank()) {
spec = spec.and(
createdBetween(
LocalDate.parse(fromDate).atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant(),
LocalDate.parse(toDate).atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant()
)
);
}return spec;
}
The final specification class is as below:
CommentSpecification.java
import com.pal.vidisha.springreact.myblog.domain.Comment;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import java.time.Instant;
import java.time.LocalDate;
import static org.springframework.data.jpa.domain.Specification.where;
@Component
public class CommentSpecification {
public Specification<Comment> isLongTermUser() {
return (root, query, cb) ->
cb.lessThan(
root.get("createdDate"),
LocalDate.of(2020, 01, 01)
.atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant()
);
}
public Specification<Comment> commentLike (String searchText) {
return (root, query, cb) ->
cb.like(
root.get("description"),
"%"+ searchText + "%"
);
}
public Specification<Comment> createdBetween (
Instant fromDate,
Instant toDate) {
return (root, query, cb) ->
cb.between(
root.get("createdDate"),
fromDate, toDate
);
} public Specification<Comment> byUser ( String userId) {
return (root, query, cb) ->
cb.equal(
root.get("userId"),
userId
);
} public Specification<Comment> conditionalSearchForUser(
String searchText,
Instant fromDate,
Instant toDate,
String userId,
boolean checkIsLongTermUser
) { Specification spec = null; spec = where(byUser(userId)); if(checkIsLongTermUser == true)
spec = spec.and(isLongTermUser());
if(searchText!=null && !searchText.isBlank())
spec = spec.and(commentLike(searchText)); if(fromDate!= null && !fromDate.isBlank()
&& toDate!= null && !toDate.isBlank()) {
spec = spec.and(
createdBetween(
LocalDate.parse(fromDate).atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant(),
LocalDate.parse(toDate).atStartOfDay(
ZoneId.of("Australia/Sydney")
)
.toInstant()
)
);
} return spec;
}}
Calling the specification from the service class
We create a method called searchUserCommentsByFilters() where we pass in our conditional search parameters.
To call the findAll method defined in commentRepository and pass our commentSpecification method for creating our conditional criteria builder.
commentRepository.findAll(
commentSpecification.conditionalSearchForUser(
searchText,
fromDate,
toDate,
userId,
checkIsLongTermUser),
page);searchUserCommentsByFilters() method in CommentService.java class
public CommentsDTO searchUserCommentsByFilters(
String userId,
String searchText,
String fromDate,
String toDate,
boolean checkIsLongTermUser,
Pageable page) {
Page<Comment> filteredComments =
commentRepository.findAll(
commentSpecification.conditionalSearchForUser(
searchText,
fromDate,
toDate,
userId,
checkIsLongTermUser),
page); long totalElements = filteredComments.getTotalElements();
int totalPages = filteredComments.getTotalPages();
return CommentsDTO.builder()
.comments(filteredComments.get()
.map(comment ->
CommentDTO.builder()
.userId(comment.getUserId())
.description(comment.getDescription())
.createdDate(comment.getCreatedDate())
.createdBy(comment.getCreatedBy())
.id(comment.getId())
.userId(comment.getUserId())
.updatedDate(comment.getUpdatedDate())
.build())
.collect(Collectors.toList()))
.pages(totalPages)
.totalCount(totalElements)
.build();
}
Controller method
@GetMapping(
path = "/search",
consumes = {
MediaType.APPLICATION_JSON_VALUE ,
MediaType.APPLICATION_XML_VALUE
},
produces = {
MediaType.APPLICATION_JSON_VALUE,
MediaType.APPLICATION_XML_VALUE
})
public ResponseEntity< CommentsDTO> searchUserCommentsByFilters(
@RequestParam(defaultValue= "0", required = false)
Integer page ,
@RequestParam(defaultValue= "5", required = false)
Integer pageSize,
@RequestParam( required = true)
String userId,
@RequestParam( defaultValue= "", required = false)
String searchText,
@RequestParam( defaultValue= "", required = false)
String fromDate,
@RequestParam( defaultValue= "", required = false)
String toDate,
@RequestParam( defaultValue= "false", required = false)
String checkIsLongTermUser
) {
Pageable paging = PageRequest.of(page, pageSize);
CommentsDTO commentsDTOs =
commentService.searchUserCommentsByFilters(
userId,
searchText,
fromDate,
toDate,
Boolean.valueOf(checkIsLongTermUser),
paging); return new ResponseEntity<>( commentsDTOs, HttpStatus.ACCEPTED);
}Testing our code with conditional queries
First let us retrieve all comments. There are three comments in total.
Now, let us filter our results by userId = 2. As expected, the results are the same as before.
What if we change the userId = 1? We do not have a user with this id. We can see that the response is empty.
Let us now add a date range from 01/01/2022 to 31/12/2022. As expected, we again receive all three comments as the response.
When we change the date range to 01/01/2021 to 31/12/2021, we get an empty response.
and searching by comment description 1 test, returns the correct comment.
and finally checking if the user is a long term user returns an empty response as all comments are made in 2022.
As we can see we can add any combination of search conditions and get back the correct response.
Spring Data JPA Specifications help us to easily add dynamic conditions to our search criteria.
Thank you for reading!
