Optional param with query Spring-Boot jpa 1.5

Question

I want to execute query for filter list with spring boot 1.5, I have param required (username) and optional param (email), How I can assign optional for param in query ? @Query("SELECT u FROM User u WHERE u.username LIKE :username AND u.email=:email") public Page getUsers(@Param("username") String username, @Param("email") String email); http://localhost:8080/users?username=&email=test : work fine http://localhost:8080/users?username= : Exception

Create two methods, and call one or the other depending on the presence of the email. — JB Nizet
I really have many params for filter but for this question I simply for two params, How I can execute query with required and optional param ? — Taki Rahal
Use the JPA criteria API to dynamically create the query, or QueryDSL. — JB Nizet

ygor ygor · Accepted Answer · 2018-11-19T22:55:12

Use JPA criteria API like this:

First, create a Specification object:

private Specification<Item> createSpecification(ItemSearch itemSearch) {
    return (root, query, criteriaBuilder) -> criteriaBuilder.and(
            Stream.of(
                    itemSearch.getUsername() == null ? null : criteriaBuilder.like(root.get("username"), itemSearch.getUsername()),
                    itemSearch.getEmail() == null ? null : criteriaBuilder.equal(root.get("email"), itemSearch.getEmail())
            ).filter(Objects::nonNull).toArray(Predicate[]::new)
    );
}

Extend your repository from JpaSpecificationExecutor:

public interface ItemRepository extends CrudRepository<Item, Long>, JpaSpecificationExecutor<Item> {

}

Select all items using the specification:

List<Item> items = itemRepository.findAll(createSpecification(itemSearch))

Optional param with query Spring-Boot jpa 1.5

1 Answers