Reading Time: 2 minutes

Writing dynamic queries using JPA Specification could be a hard to understand and implement but lets make it simple and easy to understand!

To query array of string in postgreSQL , we might use a query like this :

select * from employee where ARRAY_TO_STRING(keywords, ‘,’) LIKE ‘%lunch%’;

To use specification for querying array string datatype ,we will have to

1. Define a custom dialect to register the sql function in JPA

public class CustomDialect extends PostgreSQL94Dialect {

public CustomDialect() {
        super();
        registerFunction("array_to_string", new StandardSQLFunction("array_to_string"));
    }
}

2. We’ll create a EmployeeSpecification which implements the Specification interface and we’re going to pass in our own constraint to construct the actual query:

Added a special case for querying array of string field in Spring boot

Root is your table.
CriteriaQuery is your query, good for applying distinct, subqueries, order by, etc.
CriteriaBuilder is your conditions, good for creating your where clauses

public class EmployeeSpecification implements Specification<Employee> {
    private List<SearchCriteria> searchCriteria = new ArrayList<>();

public void add(SearchCriteria criteria) {
        this.searchCriteria.add(criteria);
    }

@Override
    public Predicate toPredicate
            (Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        List<Predicate> predicates = new ArrayList<>();

for (SearchCriteria myCriteria : searchCriteria) {
            for (Object vals : myCriteria.getValue()) {
                // for array of string datatype "keywords" column in employee table
                if (myCriteria.getOperation().equalsIgnoreCase("ARRAY_STRING_EQUALITY")) {

Expression<String> delimiter = builder.<String>literal(",");

predicates.add(builder.like(
                            builder.function(
                                    "array_to_string", String.class, root.get(myCriteria.getKey()), delimiter),
                            "%" + vals.toString().toLowerCase() + "%"));
                }

}
        }
        return builder.and(predicates.toArray(new Predicate[0]));

}
}

3. We create a Specification based on operations, which we represent in the following “SearchCriteria” class:

public class SearchCriteria {
    private String key;
    private String operation;
    private List<String> value;
}

4. For defining EmployeeRepository; we’re simply extending the JpaSpecificationExecutor to get the new Specification APIs :

public interface EmployeeRepository extends JpaRepository<Employee,Long>, JpaSpecificationExecutor<Employee> {}

Find the working example on https://techhub.knoldus.com/dashboard/projects/java/5fe1f6f6f257a8b7e41f2c08

References :
https://www.baeldung.com/rest-api-search-language-spring-data-specifications