I recently had to try to make an endpoint with Spring Boot that would allow for very flexible querying. This would mean including filtering, sorting, and paging. Most REST endpoints are set up to deal with a single entity so I struggled to find an example/tutorial that included all three. Hopefully, you can benefit from the research that I have done. What I ended up with was a modified version of this tutorial
If you came here for the code then you can just skip down below, or you can jump to the code in Github

Overview

There’s a number of things that have to come together for all of this to work. I’ve broken this down into 5 different sections. I do make some assumptions; however, I will not be going into depth about Spring or JPA. I also assume your data source is SQL based. I know that this example will work for MySQL and SQL Server. However, your mileage may vary as you try different data sources.

Formatting the URL

Normally when making REST endpoints, this is the easiest step. However, due to the complexity of the queries, we have to get a bit creative. Eventually, I ended up with this format:

1
/?search=columnName:value

In this example, I expect the endpoint to search my table for where the given column name is equal to the given value.
For my first iteration I included the following operations:
• : (Equality, =)
• > (Greater than)
• < (Less than)
• ! (Negation, not equal)
• - (Sort descending)
• + (Sort ascending)
This would mean that a more complex query might look something like:

1
/?search=col1:val1,col2<val2,col3>val3,col4!val4,col5-,col6+

This method worked for me but if you need more functionality you will want to figure that out at this stage as it will define what choices you make with the rest of the service.

Note!: When you’re formatting your URL, match the Java class attributes rather than the table column names! This means that if your column name is first_name but your entity object uses firstName, use firstName in the URL!

Parsing

Next, now that we have a URL defined, we need a way to parse out the search string.

First, we need to define our operations, pretty straight forward enum here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public enum SearchOperation {
    EQUALITY, NEGATION, GREATER_THAN, LESS_THAN, SORT_ASC, SORT_DESC;

    public static final String[] SIMPLE_OPERATION_SET = {":", "!", ">", "<", "+", "-"};

    public static SearchOperation getSimpleOperation(String input) {
        switch (input) {
            case ":":
                return EQUALITY;
            case "!":
                return NEGATION;
            case ">":
                return GREATER_THAN;
            case "<":
                return LESS_THAN;
            case "+":
            case " ": // + is encoded in query strings as a space
                return SORT_ASC;
            case "-":
                return SORT_DESC;
            default:
                return null;
        }
    }
}

Next, we need a simple class to house each search criteria:

1
2
3
4
5
public class SearchCriteria {
    private String key;
    private SearchOperation operation;
    private Object value;
}

Then we need to write our parser. Just create a new class. I called mine: SearchCriteriaParser
At the top of that class, I defined the regex that will do most of the parsing.

1
2
3
4
5
6
7
private static String wordRegex = "[a-zA-Z]\\w*";
private static String valueRegex = "\\w+";
private static String operatorRegex = "(:|<|>|!|\\+|-|\\s)";
private static String timestampRegex = "[0-9]{4}-[0-9]{2}-[0-9]{2}T[0 -9]{2}:[0-9]{2}:[0-9]{2}-[0-9]{2}:[0-9]{2}";
private static String idRegex = "\\w{8}-\\w{4}-\\w{4}-\\w{4}-\\w{12}";
private static String fullRegex = "(" + wordRegex + ")" + operatorRegex + "(" + timestampRegex + "|" + idRegex + "|" + valueRegex + ")?,";
private static final Pattern searchPattern = Pattern.compile(fullRegex);

This looks complex, but it could be simplified for your needs. My input could include things like UUIDs and time stamps which could potentially conflict with recognition of operations. In the end we are looking for a repeating pattern: (word)(operation)(word?). However, that ‘word’ might take different shapes.
Finally, we need to use the regex to parse the search string:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public List<SearchCriteria> parse(String searchString) {
    List<SearchCriteria> searchCriterias = new ArrayList<>();
    if (searchString != null) {
        Matcher matcher = searchPattern.matcher(searchString + ",");
        while (matcher.find()) {
            SearchCriteria searchCriteria = new SearchCriteria();
            searchCriteria.setKey(matcher.group(1));
            searchCriteria.setOperation(SearchOperation.getSimpleOperation(matcher.group(2)));
            searchCriteria.setValue(matcher.group(3));
            if ((searchCriteria.getOperation() != SearchOperation.SORT_DESC && searchCriteria.getOperation() != SearchOperation.SORT_ASC) || searchCriteria.getValue() == null) {
                searchCriterias.add(searchCriteria);
            }
        }
    }
    return searchCriterias;
}

If we consider our simplified pattern of (word)(operation)(word?) we should only match on strings that match that pattern. Meaning anything that doesn’t match is thrown out.

Note!: Also, you might notice the grouping. In the regex, each group is defined by (). This makes it easier for us when we parse it out. Group 1 will always be the column name, group 2 will always be the operation and group 3 is the optional value. (Sorting doesn’t have a value!)

Building Specifications (Filtering)

Next, we need a way to use this nice list of search criteria. To do this, we need to generate some Specification objects which are part of javax.persistance

Because we are generating the Specifications dynamically, it’s easier to implement Specifications and override the toPredicate function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class SearchSpecification implements Specification<MainObj> {

    private SearchCriteria criteria;

    @Override
    public Predicate toPredicate(Root<MainObj> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        switch (criteria.getOperation()) {
            case EQUALITY:
                return criteriaBuilder.equal(root.get(criteria.getKey()), criteria.getValue());
            case NEGATION:
                return criteriaBuilder.notEqual(root.get(criteria.getKey()), criteria.getValue());
            case LESS_THAN:
                return criteriaBuilder.lessThan(root.get(criteria.getKey()), criteria.getValue().toString());
            case GREATER_THAN:
                return criteriaBuilder.greaterThan(root.get(criteria.getKey()), criteria.getValue().toString());
            default:
                return null;
        }
    }
}

After creating a SearchSpecification with a criterion, the Specification will be able do all the filtering.
What this does behind the scenes is that it actually builds out the where clause of the SQL.
You will also need to make sure that your Repository can use Specifications. Just add a extends JpaSpecificationExecutor<>.

1
2
3
public interface MainRepository extends JpaRepository<MainObj, UUID>, JpaSpecificationExecutor<MainObj> {

}

Ok, so far, we have a way to generate a list of Specifications but the methods on the Repository class will only accept one at a time. So, we need a function to and all the Specifications together:

1
2
3
4
5
6
7
8
9
10
11
private <T, V extends Specification<T>> Optional<Specification<T>> andSpecification(List<V> criteria) {
    Iterator<V> itr = criteria.iterator();
    if (itr.hasNext()) {
        Specification<T> spec = Specification.where(itr.next());
        while (itr.hasNext()) {
            spec = spec.and(itr.next());
        }
        return Optional.of(spec);
    }
    return Optional.empty();
}

Finally, we have a single Specification. The logic for executing all of that will look like this:

1
2
3
List<SearchCriteria> searchCriteria = searchCriteriaParser.parse(searchString);
List<SearchSpecification> specList = searchCriteria.stream().map(criterion -> new SearchSpecification(criterion)).collect(Collectors.toList());
Specification<MainObj> specs = andSpecification(specList).orElseThrow(() -> new IllegalArgumentException("No criteria provided"));

Building Sorts (With Paging)

To use sorting and paging, we need to use a Pageable. A pageable has three parts: a page size, a page index, and a sort direction.
But first we need to convert our list of search criteria into Sort.

1
2
3
4
5
6
7
8
9
10
11
12
private List<Sort> generateSortList(List<SearchCriteria> criteria) {
    return criteria.stream().map((criterion) -> {
        switch (criterion.getOperation()) {
            case SORT_ASC:
                return Sort.by(Order.asc(criterion.getKey()));
            case SORT_DESC:
                return Sort.by(Order.desc(criterion.getKey()));
            default:
                return null;
        }
    }).filter((sort) -> sort != null).collect(Collectors.toList());
}

Then we need to combine all the Sorts. For this, I wrote a simple function that ands all the Sorts together:

1
2
3
4
5
6
7
8
9
10
11
12
private <T, V extends Sort> Optional<Sort> andSort(List<V> criteria) {

    Iterator<V> itr = criteria.iterator();
    if (itr.hasNext()) {
        Sort sort = (itr.next());
        while (itr.hasNext()) {
            sort = sort.and(itr.next());
        }
        return Optional.of(sort);
    }
    return Optional.empty();
}

In the end the sorting logic will look like this:

1
2
3
List<Sort> sortList = generateSortList(searchCriteria);
Sort sort = andSort(sortList).orElse(Sort.unsorted());
Pageable pageable = PageRequest.of(pageIndex, pageSize, sort);

Combining It All Together

Let’s look at the core service function:

1
2
3
4
5
6
7
8
9
10
public List<MainObj> search(String searchString, Integer pageSize, Integer pageIndex) {
    List<SearchCriteria> searchCriteria = searchCriteriaParser.parse(searchString);
    List<SearchSpecification> specList = searchCriteria.stream().map(criterion -> new SearchSpecification(criterion)).collect(Collectors.toList());
    Specification<MainObj> specs = andSpecification(specList).orElseThrow(() -> new IllegalArgumentException("No criteria provided"));
    List<Sort> sortList = generateSortList(searchCriteria);
    Sort sort = andSort(sortList).orElse(Sort.unsorted());
    Pageable pageable = PageRequest.of(pageIndex, pageSize, sort);
    Page page = mainRepository.findAll(specs, pageable);
    return page.getContent();
}

Let’s review the steps:

  1. Parse the search string into SearchCriteria
  2. Create Specifications for each SearchCriteria
  3. And all of the Specifications together
  4. Create Sorts for each qualifying criterion
  5. And all the sorts together
  6. Create Pageable
  7. Pass the Specifications and the Pageable to the Repository
  8. Get the content of the page

Still with me? Need to look at the source?

While this service doesn’t have every feature possible, the setup makes it so that you could easily expand on this. Adding more operations would be one simple example. Overall, I’m happy with the way this endpoint turned out.