How to use Criteria & Metamodel API with Spring Boot to create dynamic queries

Sometimes you may need to create your sql queries on runtime with some specification given by your client. In these cases, you may create many JPQLs (or native queries) for each specification. Handling with this way could be hard to maintain. In these scenarios, you may use CRITERIA API to create your sql queries in your java code. Let’s quickly learn criteria api and its usages in Spring Boot to create sql query in our Java code

Github Link

If you only need to see the code, here is the github link

Create Spring Boot Project

First create a new spring boot project with the following dependencies:

<dependencies>
    <!--	To generate metamodel 	-->
	<dependency>
		<groupId>org.hibernate.orm</groupId>
		<artifactId>hibernate-jpamodelgen</artifactId>
	</dependency>
	<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-testcontainers</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>junit-jupiter</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>postgresql</artifactId>
      <scope>test</scope>
    </dependency>
</dependencies>

Note: I am going to use testcontainer to run sql queries on the postgresql docker. Make sure that you have installed docker and it is running while running the test cases

Create entity classes

Assume that we have User and UserRole entities:

@Entity
@Table(name = "user")
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Getter
@Setter
public class User {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "email")
    private String email;

    @Column(name = "name")
    private String name;

    @Column(name = "version_id")
    @Version
    private Long versionId;

    // if I update/delete user, hibernate should update/delete related UserRole
    @ManyToMany(fetch = FetchType.LAZY, cascade ={
            CascadeType.PERSIST,
            CascadeType.MERGE
    })
    @JoinTable(name = "user_role_join", // table name
            joinColumns = @JoinColumn(name = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<UserRole> userRoles = new HashSet<>();
}

@Entity
@Table(name = "user_role")
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Getter
@Setter
public class UserRole {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "role")
    private String role;

    @Column(name = "version_id")
    @Version
    private Long versionId;
}

with the following repository classes:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}

@Repository
public interface UserRoleRepository extends JpaRepository<UserRole, Long> {
}

with these setup is set. Let’s start what do we mean by Criteria and Metamodel?

What is Criteria and Metamodel APIs?

The Criteria API is used to define queries for entities by creating queriesh objects. These queriesh objects are written in Java and typesafe.

While we are writing criteria queries we need to reference the entity class and its attributes, we often reference those attributes names as String which is not type-safe and we have to remember all the attributes of the entity. Metamodel generates statilc model(another class) of the entity and we can use generated metamodel (and its attributes) to write type-safe queries.

Steps to create Criteria query

Use an EntityManager to create a CriteriaBuilder object.

import jakarta.persistence.EntityManager;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
// ...

@RequiredArgsConstructor
@Service
public class UserService {

    private final EntityManager entityManager;

    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
    }
}

Create a query object by creating instance of CriteriaQuery

public class UserService {
    private final EntityManager entityManager;

    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
    }
}

Set the query root. (think of that you are creating FROM {Entity} part of the query)

public class UserService {
    private final EntityManager entityManager;
    
    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);

        Root<User> user = query.from(User.class); // FROM {Entity} part of the query
    }
}

Specify the result type of the query by using the select method of CriteriaQuery (think of that you are creating SELECT part of the query):

public class UserService {
    private final EntityManager entityManager;
    
    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
        Root<User> user = query.from(User.class); // Set the 'FROM {Entity}' part of the query

        query.select(user); // Set the `SELECT` part of the query: 
    }
}

Prepare the query for execution by creating TypedQuery<T>, specify the type of the query result:

public class UserService {
    private final EntityManager entityManager;
    
    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
        Root<User> user = query.from(User.class); // Set the 'FROM {Entity}' part of the query
        query.select(user); // Set the `SELECT` part of the query:

        TypedQuery<User> typedQuery = entityManager.createQuery(query); // prepare for execution and specify the result type
    }
}

Execute the query by calling getResultList. We call that method because we know that there are many users in our database. (there is also method called getSingleResult):

public class UserService {
    private final EntityManager entityManager;

    public List<User> getAllUsers() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
        Root<User> user = query.from(User.class); // Set the 'FROM {Entity}' part of the query
        query.select(user); // Set the `SELECT` part of the query:
        TypedQuery<User> typedQuery = entityManager.createQuery(query); // prepare for execution and specify the result type
        
        List<User> allUsers = typedQuery.getResultList(); // Execute the query
        return allUsers;
    }
}

method getAllUsers is equal to the following sql query:

select
        u1_0.id,
        u1_0.email,
        u1_0.name,
        u1_0.version_id 
from
        users u1_0

Here is the test method to verify list of users:

public class UserService {
    public void createUserWithRepositoryCall(User user) {
        userRepository.save(user);
    }
}

// ...
class CriteriaApiWithSpringBootApplicationTests {
	@Autowired
	private UserService userService;
	@Test
	void contextLoads() {
        // create users
		createUserWithRepositoryCall();

        // then getAllUsers 
		List<User> allUsers = userService.getAllUsers();
		Assertions.assertEquals(newUsers().count(), allUsers.size());
	}

	static Stream<User> newUsers() {
		// new users
	}
}

Let’s look at the generated metamodels for each entities

Steps to generate metamodel for entities

If you are using the following dependency:

<dependency>
	<groupId>org.hibernate.orm</groupId>
	<artifactId>hibernate-jpamodelgen</artifactId>
</dependency>

While project is compiled, Hibernate will automatically generate metamodel for your @Entity classes. The class name will be {EntityClassName}_. For instance, User.java => User_.java

Details of the Criteria API

In general, query consists of a SELECT clause, a FROM clause and a WHERE clause. Criteria API set these clauses by using Java objects, so the query can be created in a typesafe manner.

CriteriaBuilder is used to construct:

Criteria queries
Selections
Expressions
Predicates
Ordering

CriteriaQuery (which is another java objects) defines a particular query that will use one or more entities in our project. To create CriteriaQuery instances call CriteriaBuilder.createQuery method.

The CriteriaQuery’s type should be set to the expected result type of the query:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// query return the User entity
CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);

// query return the String
CriteriaQuery<String> anotherQuery = criteriaBuilder.createQuery(String.class);

Query roots

This is similar to FROM part of the query. In other words it specifies the search area(table) of the given query.

FROM part can be created by calling criteriaQuery.from(Entity.class) method:

CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);

Root<User> user = query.from(User.class);

Query root can also be set using EntityType (with generated metamodel):

CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);

Metamodel metamodel = entityManager.getMetamodel();
EntityType<User> User_ = metamodel.entity(User.class);
Root<User> pet = query.from(User_);

Note: Criteria queries may have more than one query root.
Root<User> userFrom = query.from(User.class);
Root<User> anotherUserFrom = query.from(User.class);    

Query with joins

If you are going to navigate related entity classes, your query must define a join and join clause can be defined from FROM.join method. This is similar JOIN keyword in sql.

The join method returns object of type Join<X, Y>

X is the source entity
Y is the target entity

Join<User, UserRole> userWithUserRoleJoin = user.join(User_.userRoles);

Navigate to attributes of the entities

Very often, we need to select some attributes (of the entity or entities) to show in “SELECT clause” or to use in “WHERE cluase”. To to that we can use Path object in Criteria API.

Metamodel class(es) provides which attributes can be used in SELECT or WHERE clauses (or other clauses in any related sql part).

The attribute can be SingularAttribute(single value attribute selection), CollectionAttribute, SetAttribute, ListAttribute, or MapAttribute. Here is the example for User_.java

@StaticMetamodel(User.class)
@Generated("org.hibernate.processor.HibernateProcessor")
public abstract class User_ {
	public static final String USER_ROLES = "userRoles";
	public static final String VERSION_ID = "versionId";
	public static final String NAME = "name";
	public static final String ID = "id";
	public static final String EMAIL = "email";


    // Because we define private Set<UserRole> userRoles = new HashSet<>(); in the User entity, attribute's selection must be SetAttribute etc...
	public static volatile SetAttribute<User, UserRole> userRoles;
	
	public static volatile SingularAttribute<User, Long> versionId;
	
	public static volatile SingularAttribute<User, String> name;
	
	public static volatile SingularAttribute<User, Long> id;
}

Here is the query to select user’s email:

public class UserService {
    // ...

    /**
     * Sql Query:["
     *     select
     *         u1_0.name 
     *     from
     *         users u1_0"]
     * @return
     */
    public List<String> getAllUsersEmails() {
        // get builder to build query
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        // create query with return type String
        CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);

        // using metamodel,
        // set query's "FROM" clause means that '... FROM User'
        Metamodel metamodel = entityManager.getMetamodel();
        EntityType<User> user_ = metamodel.entity(User.class);
        Root<User> user = query.from(user_);

        // using metamodel,
        // set query's select value(s), this should be matched with return type of the CriteriaQuery
        query.select(user.get(User_.name));

        // Prepare the query for execution, specify the query result type
        TypedQuery<String> typedQuery = entityManager.createQuery(query);

        // execute the query
        List<String> allUsers = typedQuery.getResultList();
        return allUsers;
    }
}

Restricting Criteria Query Results

With CriteriaQuery.where method we can restrict the results of a query on the CriteriaQuery object. This is simlar to the WHERE clause in the sql
CriteriaQuery.where evaluates instances of the Expression interface to restrict the results according to the conditions of the expressions.
An Expression object is used in a query’s SELECT, WHERE, or HAVING clause.

package jakarta.persistence.criteria;


/**
 * Type for query expressions.
 *
 * @param <T> the type of the expression
 *
 * @since 2.0
 */
public interface Expression<T> extends Selection<T> {

    /**
     *  Create a predicate to test whether the expression is null.
     *  @return predicate testing whether the expression is null
     */
    Predicate isNull();
    // ...
}

Let’s find all users where name is not null:

public class UserService {
    /**
     * Sql Query:["
     *     select
     *         u1_0.id,
     *         u1_0.email,
     *         u1_0.name,
     *         u1_0.version_id 
     *     from
     *         users u1_0 
     *     where
     *         u1_0.name is not null"]
     * @return
     */
    public List<User> findAllUsersWhereNameNotNull() {
        // get builder to build query
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
        // using metamodel
        // set query's "FROM" clause e.g ... FROM User
        Metamodel metamodel = entityManager.getMetamodel();
        EntityType<User> user_ = metamodel.entity(User.class);
        Root<User> user = query.from(user_);

        // set query's where clause
        query.where(user.get(User_.name).isNotNull());

        // Prepare the query for execution, specify the query result type
        TypedQuery<User> typedQuery = entityManager.createQuery(query);

        // execute the query
        return typedQuery.getResultList();
    }
}

There also other methods such as: isNull, in.

To use additional methods such as equal, gt (greater than), between, like … you can use CriteriaBuilder interface.

Here is the example to search user’s email with like query:


public class UserService {

    /**
     * Sql Query:["
     *     select
     *         u1_0.id,
     *         u1_0.email,
     *         u1_0.name,
     *         u1_0.version_id 
     *     from
     *         users u1_0 
     *     where
     *         u1_0.email like ? escape ''"]
     * Params:[(%customer%)]
     * @return
     */
    public List<User> getUsersWithEmailLike(String email) {
        // get builder to build query
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
        // using metamodel
        // set query's "FROM" clause e.g ... FROM User
        Metamodel metamodel = entityManager.getMetamodel();
        EntityType<User> user_ = metamodel.entity(User.class);
        Root<User> user = query.from(user_);

        // set query's where clause (use additional expression from CriteriaBuilder
        query.where(criteriaBuilder.like(user.get(User_.email), "%" + email + "%"));

        // Prepare the query for execution, specify the query result type
        TypedQuery<User> typedQuery = entityManager.createQuery(query);

        // execute the query
        return typedQuery.getResultList();
    }
}

Complex queries examples

Enough for the theory part, let’s do some real world examples:

Here is the test scenario:

public class ComplexQueryExamples {

    @Autowired
    private UserService userService;
    @Autowired
    private Flyway flyway;

    private void createUsers() {
        newUsers().forEach(user -> userService.createUserWithRepositoryCall(user));
    }

    // Clear and migrate database for each test
    @BeforeEach
    public void restoreDatabase() {
        flyway.clean();
        flyway.migrate();
    }

    @Test
    void testComplexQueries() {
        // ....    
    }

    static Stream<User> newUsers() {
        // Four users:
        // one is admin, one has two roles, one has two addresses, one has no address, one has no address and role
        UserRole customer = UserRole.builder()
                .role("customer")
                .build();
        UserRole admin = UserRole.builder()
                .role("admin")
                .build();
        Address address = Address.builder()
                .address("Dummy Address İstanbul")
                .city("İstanbul")
                .build();
        Address anotherAddress = Address.builder()
                .address("Dummy Address New York")
                .city("New York")
                .build();
        return Stream.of(
                User.builder()
                        .email("[email protected]")
                        .name("test2")
                        .userRoles(Set.of(
                                customer,
                                admin
                        ))
                        .build(),
                User.builder()
                        .email("[email protected]")
                        .name("test1")
                        .userRoles(Set.of(
                                customer
                        ))
                        .userAddresses(Set.of(address))
                        .build(),
                User.builder()
                        .email("[email protected]")
                        .name("test3")
                        .userRoles(Set.of(
                                customer
                        ))
                        .userAddresses(Set.of(address, anotherAddress))
                        .build(),
                User.builder()
                        .email("[email protected]")
                        .name("test4")
                        .build()
        );
    }
}

Find all users without join in paginated way

public Page < User > getAllUsersWithJoin(PageRequest pageRequest) {
  // create builder
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  // set up query result's type
  CriteriaQuery < User > query = criteriaBuilder.createQuery(User.class);

  Metamodel metamodel = entityManager.getMetamodel();
  EntityType < User > userEntityType = metamodel.entity(User.class);

  // set up query's from clause
  Root < User > fromUserEntity = query.from(userEntityType);

  // create Select clause
  query.select(fromUserEntity);
  // convert pageRequest into jpa order by
  query.orderBy(QueryUtils.toOrders(pageRequest.getSort(), fromUserEntity, criteriaBuilder));

  // Prepare the query for execution, specify the query result type
  TypedQuery < User > typedQuery = entityManager.createQuery(query);

  // set related fields for pagination
  typedQuery.setFirstResult((int) pageRequest.getOffset());
  typedQuery.setMaxResults(pageRequest.getPageSize());

  // execute query for content (list of users)
  List < User > content = typedQuery.getResultList();

  // Create "Count query" for the count part of pagination
  CriteriaQuery < Long > countQuery = criteriaBuilder.createQuery(Long.class);

  Root < User > countRoot = countQuery.from(userEntityType);
  countQuery.select(criteriaBuilder.count(countRoot));

  TypedQuery < Long > countQueryType = entityManager.createQuery(countQuery);
  Long totalElementsInPagination = countQueryType.getSingleResult();

  // create pagination
  return new PageImpl < > (content, pageRequest, totalElementsInPagination);
}

Find all users joined with UserRole and Address in paginated way

public Page < User > getAllUsersWithJoin(PageRequest pageRequest) {
  // create builder
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  // set up query result's type
  CriteriaQuery < User > query = criteriaBuilder.createQuery(User.class);

  Metamodel metamodel = entityManager.getMetamodel();
  EntityType < User > userEntityType = metamodel.entity(User.class);

  // set up query's from clause
  Root < User > fromUserEntity = query.from(userEntityType);

  // JOIN FETCH all related entities for User to include entities in User-entity
  fromUserEntity.fetch(User_.userRoles, JoinType.LEFT);
  fromUserEntity.fetch(User_.userAddresses, JoinType.LEFT);

  // create Select clause
  query.select(fromUserEntity);

  // convert pageRequest into jpa order by
  query.orderBy(QueryUtils.toOrders(pageRequest.getSort(), fromUserEntity, criteriaBuilder));

  TypedQuery < User > typedQuery = entityManager.createQuery(query);
  typedQuery.setFirstResult((int) pageRequest.getOffset());
  typedQuery.setMaxResults(pageRequest.getPageSize());
  List < User > content = typedQuery.getResultList();

  // Create "Count query" for the count part of pagination
  CriteriaQuery < Long > countQuery = criteriaBuilder.createQuery(Long.class);

  Root < User > countRoot = countQuery.from(userEntityType);
  countQuery.select(criteriaBuilder.count(countRoot));

  TypedQuery < Long > countQueryType = entityManager.createQuery(countQuery);
  Long totalElementsInPagination = countQueryType.getSingleResult();

  return new PageImpl < >(content, pageRequest, totalElementsInPagination);
}

Find all users with projection DTO in paginated way

Here is projection class (I am going to use Record):

public record UserDTO(
        Long id,
        String email,
        String name,
        OffsetDateTime createDate
) {
}

public Page < UserDTO > getAllUserWithDTOProjection(PageRequest pageRequest) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery < UserDTO > query = criteriaBuilder.createQuery(UserDTO.class);

  Metamodel metamodel = entityManager.getMetamodel();
  EntityType < User > userEntityType = metamodel.entity(User.class);
  Root < User > fromUserEntity = query.from(userEntityType);

  query.select(
    criteriaBuilder.construct(UserDTO.class,
      fromUserEntity.get(User_.id).alias("id"),
      fromUserEntity.get(User_.email).alias("email"),
      fromUserEntity.get(User_.name).alias("name"),
      fromUserEntity.get(BaseEntity_.createTime).alias("create_time")
    ));

  query.orderBy(QueryUtils.toOrders(pageRequest.getSort(), fromUserEntity, criteriaBuilder));

  TypedQuery < UserDTO > typedQuery = entityManager.createQuery(query);
  typedQuery.setFirstResult((int) pageRequest.getOffset());
  typedQuery.setMaxResults(pageRequest.getPageSize());
  List < UserDTO > content = typedQuery.getResultList();

  CriteriaQuery < Long > countQuery = criteriaBuilder.createQuery(Long.class);

  Root < User > countRoot = countQuery.from(userEntityType);
  countQuery.select(criteriaBuilder.count(countRoot));

  TypedQuery < Long > countQueryType = entityManager.createQuery(countQuery);
  Long totalElementsInPagination = countQueryType.getSingleResult();
  return new PageImpl < > (content, pageRequest, totalElementsInPagination);
}

Count distinct users for each role using group by

public record UserCountPerRole(
        String role,
        Long count
) {
}

Count using User as root:

public Page < UserCountPerRole > countDistinctUsersPerRole(PageRequest pageRequest) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery < UserCountPerRole > query = criteriaBuilder.createQuery(UserCountPerRole.class);

  Metamodel metamodel = entityManager.getMetamodel();
  EntityType < User > userEntityType = metamodel.entity(User.class);
  Root < User > fromUserEntity = query.from(userEntityType);

  // join with userRole to create groupby statement
  Join < User, UserRole > userRoleJoin = fromUserEntity.join(User_.userRoles, JoinType.LEFT);
  
  // query by query with roles
  query.groupBy(userRoleJoin.get(UserRole_.role));

  // construct select statement with DTO projection  
  query.select(
    criteriaBuilder.construct(UserCountPerRole.class,
      userRoleJoin.get(UserRole_.role),
      criteriaBuilder.countDistinct(fromUserEntity.get(User_.id)))
  );

  // only grouping by not null roles  
  query.where(userRoleJoin.get(UserRole_.role).isNotNull());
  // add order by statement
  query.orderBy(criteriaBuilder.asc(userRoleJoin.get(UserRole_.role)));

  TypedQuery < UserCountPerRole > typedQuery = entityManager.createQuery(query);
  typedQuery.setFirstResult((int) pageRequest.getOffset());
  typedQuery.setMaxResults(pageRequest.getPageSize());
  List < UserCountPerRole > content = typedQuery.getResultList();

  // Count query for pagination
  CriteriaQuery < Long > countQuery = criteriaBuilder.createQuery(Long.class);
  Root < User > countRoot = countQuery.from(User.class);
  Join < User, UserRole > countRoleJoin = countRoot.join(User_.userRoles);
  countQuery.select(criteriaBuilder.countDistinct(countRoleJoin.get(UserRole_.role)));
  countQuery.where(countRoleJoin.get(UserRole_.role).isNotNull());
  TypedQuery < Long > countQueryType = entityManager.createQuery(countQuery);
  Long totalElementsInPagination = countQueryType.getSingleResult();

  return new PageImpl < > (content, pageRequest, totalElementsInPagination);
}

Here is the generated query

    select
        ur1_1.role,
        count(distinct u1_0.id) 
    from
        users u1_0 
    left join
        users_roles_join ur1_0 
            on u1_0.id=ur1_0.id 
    left join
        users_roles ur1_1 
            on ur1_1.id=ur1_0.role_id 
    where
        ur1_1.role is not null 
    group by
        1 
    order by
        1 
    offset
        ? rows 
    fetch
        first ? rows only

Simple dynamic filtering with Specification

To create dynamic queries in spring boot application we can use JpaSpecificationExecutor.
First, extends repository interface with JpaSpecificationExecutor:

@Repository
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

Second, create model object to filter User’s attribute(s)

@Builder
@Getter
public class UserDynamicSearchSpecification {
    @Singular
    private Set<String> searchEmailLikes;
    @Singular
    private Set<String> searchRoles;
    @Singular
    private Set<String> searchCities;
}

Third, create util class to combine list of search specification:

// class to combine search specification in UserDynamicSearchSpecification object
public class UserSpecification {
    private UserSpecification() {}

    // combine specifications with and filter (you may also use "or" statement)
    public static Specification<User> createSpecification(UserDynamicSearchSpecification dynamicSearchSpecification) {
        return Specification.where(searchByEmail(dynamicSearchSpecification.getEmailLikes()))
                .and(searchByRoles(dynamicSearchSpecification.getRoles()))
                .and(searchByCities(dynamicSearchSpecification.getCities()));
    }

    public static Specification<User> searchByEmail(Set<String> emails) {
        if (CollectionUtils.isEmpty(emails)) {
            return null;
        }

        return (root, query, criteriaBuilder) -> {

            List<Predicate> predicates = new ArrayList<>();
            emails.forEach(email -> {
                Predicate predicate = criteriaBuilder.like(root.get(User_.email), "%" + email + "%");
                predicates.add(predicate);
            });

            return criteriaBuilder.or(predicates.toArray(new Predicate[0]));
        };
    }

    public static Specification<User> searchByRoles(Set<String> roles) {
        // ... implementation for searchByRoles
        
    }


    public static Specification<User> searchByCities(Set<String> cities) {
        // ... implementation for searchByCities
    }
}

Finally, define a method in the service class

@Service
public class UserService {
    private final UserRepository userRepository;

    public Page<User> findUserDynamically(UserDynamicSearchSpecification dynamicSearchSpecification, PageRequest pageRequest) {
        Specification<User> specification = UserSpecification.createSpecification(dynamicSearchSpecification);
        return userRepository.findAll(specification, pageRequest);
    }
}

Here are the examples:


@EnableTestcontainers
@SpringBootTest
public class ComplexQueryTest {

    @Autowired
    private UserService userService;

    private UserDynamicSearchSpecification buildSpecification(Consumer<UserDynamicSearchSpecification.UserDynamicSearchSpecificationBuilder> customizer) {
        var builder = UserDynamicSearchSpecification.builder();
        customizer.accept(builder);
        return builder.build();
    }

    @Test
    void dynamicUserSearch() {
        // search by email like and role
        var searchSpecification = buildSpecification((builder) -> builder
                .emailLike("[email protected]")
                .emailLike("[email protected]")
                .role("customer")
        );
        Page<User> users = userService.findUserDynamically(searchSpecification, PageRequest.of(0, 1));
        Assertions.assertEquals(2, users.getTotalElements());
        User user = users.toList().getFirst();
        User finalUser = user;
        Assertions.assertAll(
                () -> Assertions.assertNotNull(finalUser.getEmail()),
                () -> Assertions.assertNotNull(finalUser.getUserRoles()),
                () -> Assertions.assertNotNull(finalUser.getUserAddresses())
        );

        // search only with roles
        searchSpecification = buildSpecification((builder) -> builder
                .role("customer")
        );
        users = userService.findUserDynamically(searchSpecification, PageRequest.of(0, 1));
        Assertions.assertEquals(3, users.getTotalElements());
        user = users.toList().getFirst();
        User finalUser1 = user;
        Assertions.assertAll(
                () -> Assertions.assertNotNull(finalUser1.getEmail()),
                () -> Assertions.assertNotNull(finalUser1.getUserRoles()),
                () -> Assertions.assertNotNull(finalUser1.getUserAddresses())
        );
    }
}