Spring Boot REST WebService + JPA : pageable and filter

Question

I'm fairly new to these technos and I need a pitch to understand a good way to do things. I've got an Employee entity, and I want to list them by making a GET query to an endpoint.I have to return a Page of Employees, after having applied filters on field(s). Currently, the Pageable on the GET query works, but not my filter.

Here is my REST endpoint :

@RequestMapping(value = "/employees",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
@Timed
@Transactional(readOnly = true)
public ResponseEntity<List<EmployeeDTO>> getAllEmployees(Pageable pageable, String filters) throws URISyntaxException, JSONException {

        JSONObject sfilters = null;
        try {
            sfilters = new JSONObject(filters.trim());
        } catch (JSONException e) {
            e.printStackTrace();
        }

        // I wish this on could works, but still have to update it if we add fields to our Employee entity
        Page<Employee> page = employeeRepository.findAllByCompanyIdAndFirstnameLikeAndLastnameLike(
            userService.getUserWithAuthorities().getCompany().getId(),
            sfilters.get("firstname").toString(),
            sfilters.get("lastname").toString(),
            pageable);

        HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(page, "/api/employees");
        ResponseEntity<List<EmployeeDTO>> result = new ResponseEntity<>(page.getContent().stream()
            .map(employeeMapper::employeeToEmployeeDTO)
            .collect(Collectors.toCollection(LinkedList::new)), headers, HttpStatus.OK);

        return result;
}

NB : I have to filter on more fields than that but I want to keep example as clear as possible.

My repository method :

Page<Employee> findAllByCompanyIdAndFirstnameLikeAndLastnameLike(Long idCompany, String firstname, String lastname, Pageable pageable);

Client side, it works well, I send good params for pageable and for my filter to be converted into JSONObject. But now I need suggestions on how can I properly generate dynamics filters on my query. My JPA repository method doesnt work.

I tried to use predicates, but it does not help as the methods fail when I give it a Predicate arg. This method seems to be good to check, items by items, if one or more match your pred but not sure they are made for retrieve set of items with a dynamic query.

EDIT : I created an EmployeeSpecification class implementing Specification but I want to return a list/array of Predicates not only one. The default method to override thus return a single Predicate. How can I manage to get multiple predicate from this entity ?

Thanks for any hint, helps, past experiences to share.

Answer 1

I found out how to do this with Predicates. Firstly, I must use the JPA method findAll in my repository :

Page<Employee> findAll(Specification<Employee> spec, Pageable pageable);

Then, I've created a custom class which implements the Specification Spring Boot object :

public class EmployeeSpecification implements Specification<Employee> {

    private final JSONObject criteria;
    private List<Predicate> filters;

    public EmployeeSpecification(JSONObject criteria) {
        this.criteria = criteria;
    }

    @Override
    public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Iterator<?> keys = criteria.keys();
        List<Predicate> filters = new ArrayList<>();

        if (criteria.length() != 0) {

            while (keys.hasNext()) {
                String key = (String) keys.next();
                String filterValue = null;

                try {
                    filterValue = criteria.get(key).toString();
                } catch (JSONException e) {
                    e.printStackTrace();
                }

                if (filterValue != null) {
                    filters.add(criteriaBuilder.like(criteriaBuilder.upper(root.<String>get(key)), "%" + filterValue.toUpperCase() + "%"));
                }
            }
        }
        //this is the point : didn't know we could concatenate multiple predicates into one.
        return criteriaBuilder.and(filters.toArray(new Predicate[filters.size()]));
    }
}

After this, in my WS endpoint method, I just have to instanciate EmployeeSpecification and to call the JPA findAll method, passing my filter JSON object and my Pageable object :

@RequestMapping(value = "/employees",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
@Timed
@Transactional(readOnly = true)
public ResponseEntity<List<EmployeeDTO>> getAllEmployees(Pageable pageable, String filters) throws URISyntaxException, JSONException {

    JSONObject sfilters = null;
    try {
        sfilters = new JSONObject(filters.trim());
    } catch (JSONException e) {
        e.printStackTrace();
    }

    EmployeeSpecification spec = new EmployeeSpecification(sfilters);

    Page<Employee> page = employeeRepository.findAll(
        spec,
        pageable);

    HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(page, "/api/employees");
    ResponseEntity<List<EmployeeDTO>> result = new ResponseEntity<>(page.getContent().stream()
        .map(employeeMapper::employeeToEmployeeDTO)
        .collect(Collectors.toCollection(LinkedList::new)), headers, HttpStatus.OK);
    return result;
}

Now I can send Pageable items and multiple fields filters, I'm able to correctly retrieve results depending of the sorting, number per pages, current page and filters on fields. Thanks a lot for your help ;) (lol)