JPA Search offers a solution based on JPA that simplify database search from an API with pagination and sorting.
Description 🔍
Jpa-Search simplifies specific entity searches with JPA, including :
Modular filters
Multiple filters
Modular sorts
Multiple sorts (following each other)
Various operations (you can add yours)
Complete pagination
This search is designed to be efficient, since it operates at a low level, as close as possible to SQL, thanks to the CriteriaBuilder provided by jakarta.persistence.
A search works with a configuration file where you define each possible filter, its associated operation (e.g.: name equals value) and where to fetch the field in DB. Once a filter has been defined, it is possible to filter by this ascending or descending field.
The search function takes as input a list of query params in the form of a Map<String, String> (also containing a page and size field for pagination if it's not a key filter), a List<String> sort list, the type of entity searched for Class<T> and the type of search configuration enumeration Class<E>. In return, this function returns a Page<T> containing the number of elements requested and the number of total elements.
How to use it ? 🤔
To configure a search, you need to create a configuration criteria enum described like below:
The idea here is to associate in FilterConfig.of(...) an operation, a query param name and an entity field name.
Taking ACCOUNT_TYPE as an example, we're looking for an ApiKey that is associated with a particular account type, in this case equality.
FilterOperation
The FilterOperation is used to describe the operation to be performed on the filter, many of which are already available and applicable to different types:
ObjectFilterOperation
| Operator Name | Description | Filter Type |
|---------------|--------------------------------------------------|-------------|
| `EQUAL` | Checks for equality between `field` and `filter` | `Object` |
StringFilterOperation
| Operator Name | Description | Filter Type |
|----------------------------------------|----------------------------------------------------------------------------|-------------|
| `CONTAIN` | Checks that `field` contains part of the `filter` | `String` |
| `CONTAIN_IGNORE_CASE` | Checks that the `field` contains part of the `filter`, case ignored | `String` |
| `CONTAIN_IGNORE_CASE_IGNORE_ACCENT` | Checks for equality between `field` and `filter`, case and accents ignored | `String` |
| `START_WITH` | Checks that the `field` begins with the `filter` | `String` |
| `START_WITH_IGNORE_CASE` | Check that the `field` begins with the `filter`, case ignored | `String` |
| `START_WITH_IGNORE_CASE_IGNORE_ACCENT` | Check that the `field` starts with the `filter`, case and accents ignored | `String` |
| `EQUAL_IGNORE_CASE` | Checks for equality between `field` and `filter`, case | `String` |
| `EQUAL_IGNORE_CASE_IGNORE_ACCENT` | Checks for equality between `field` and `filter`, case and accents ignored | `String` |
ListComparableFilterOperation
| Operator Name | Description | Filter Type |
|---------------|--------------------------------------------------------|-----------------|
| `BETWEEN` | Checks that `field` is between the two `filter` fields | `Comparable[2]` |
ComparableFilterOperation
| Operator Name | Description | Filter Type |
|---------------------------------|------------------------------------------------------------------|--------------|
| `GREATER_THAN_OR_EQUAL` | Checks that `field` is greater than or equal to `filter` | `Comparable` |
| `GREATER_THAN_OR_EQUAL_OR_NULL` | Checks that `field` is greater than or equal to `filter` or null | `Comparable` |
| `LESS_THAN_OR_EQUAL` | Checks that `field` is smaller than or equal to `filter` | `Comparable` |
ListObjectFilterOperation
| Operator Name | Description | Filter Type |
|---------------|----------------------------------------------------------------------------|-------------|
| `IN_EQUAL` | Checks the equality of `field` with one of the `fields` in the filter list | `Object[]` |
ListStringFilterOperation
| Operator Name | Description | Filter Type |
|--------------------------------------|--------------------------------------------------------------------------------------------------|-------------|
| `IN_CONTAIN` | Checks that `field` contains one of the fields in the `filter` list | `String[]` |
| `IN_EQUAL_IGNORE_CASE_IGNORE_ACCENT` | Checks that `field` is equal to one of the fields in the `filter` list, case and accents ignored | `String[]` |
VoidFilterOperation
| Operator Name | Description | Filter Type |
|-----------------------|----------------------------|-------------|
| `NOT_NULL` | Checks for `field` nullity | `Void` |
| `NULL` | Checks for `field` nullity | `Void` |
| `COLLECTION_IS_EMPTY` | Checks for `field` nullity | `Void` |
You can easily create an other enum which inherits from IFilterOperation, like :
CustomFilterOperation
This example shows you how to create an operation filter that can be reused.
Example:
```java
public enum CustomFilterOperation implements IFilterOperation
Config
FilterConfig
Configures the filter in relation to a field.
Parameters:
| Key | Filter | pathFirst | paths (Optional) |
|--------------------|-------------------------------|------------------------------------------------------------|-------------------------------------------------------------|
| Name of the filter | Filter that you want to apply | First path to the field where you want to apply the filter | Same function as for the path, but for the remaining fields |
Example:
```java
SEARCH(FilterConfig.of("search", StringFilterOperation.CONTAIN_IGNORE_CASE, "firstName", "lastName"));
```
SorterConfig
You can add additional sorter like `id` in the example to sort your entities.
In this case if we set `sorts = {id,asc}`, the response will be the sort by id in ascending order.
Parameters:
| Key | path |
|--------------------|------------------------------------------------------|
| Name of the sorter | Path to the field where you want to apply the sorter |
Example:
```java
ID(FilterSorterConfig.of("id", ObjectFilterOperation.EQUAL, "id"));
```
FilterSorterConfig
This filter groups FilterConfig and SorterConfig. So you can use this config like a sort or like a filter.
Parameters:
| Key | Filter | pathFirst | paths (Optional) |
|--------------------|-------------------------------|------------------------------------------------------------|-------------------------------------------------------------|
| Name of the sorter | Filter that you want to apply | First path to the field where you want to apply the filter | Same function as for the path, but for the remaining fields |
Example:
```java
ID(FilterSorterConfig.of("id", ObjectFilterOperation.EQUAL, "id"));
```
GroupFilterConfig
The purpose of this filter is to group some FilterConfig.
Parameters:
| Key | Filter | Filters (Optional) |
|-------------------|-------------------------------|--------------------|
| Name of the Group | Filter that you want to apply | Other filters |
Example:
```java
PEOPLE(GroupFilterConfig.of("searchpeople", FIRSTNAME.getFilterConfig(), LASTNAME.getFilterConfig()));
```
MultiFilterConfig
To understand this filter, let's take the example of a company: Avisto. Avisto has a `List`.
And if you want to get the employees whose names start with "M" or "N", you can use this filter to apply the name filter twice.
Parameters:
| Key | Filter | joinPath |
|------------------------|-------------------------------|------------------------------------------------------|
| Name of the Multiplier | Filter that you want to apply | path to the field where you want to apply the filter |
Example:
```java
PEOPLE(MultiFilterConfig.of("searchpeople", FIRSTNAME.getFilterConfig(), "employees"));
```
Parameters example:
`searchpeople="M","N"`
The library also supports EntityGraphs, so you can use the search function with an EntityGraph.
Test
If you want to test your ISearchCriteriaConfig, you can create tests like this:
```java
@Test
& ISearchCriteriaConfig> void ValidateCriteriaConfigurationsTest() {
Reflections reflections = new Reflections("com.jpasearchimplem");
Set> classes = reflections.getSubTypesOf(ISearchCriteriaConfig.class);
classes.forEach(clazz -> {
System.out.println("Checking for criteria : " + clazz.getSimpleName());
assertTrue(clazz.isEnum());
try {
SearchUtils.checkCriteriaConfig((Class) clazz);
} catch (JpaSearchException e) {
fail(e.getMessage());
}
});
}
```
Benchmark 📈
If you've ever done research with CriteriaBuilder, you probably know that it's very tedious and time-consuming.
Example of 1 filter and 1 sorter without JpaSearch
```java
public class EmployeeSpecification implements Specification {
private final EmployeeCompleteDTO filter;
private final String scope;
private final String order;
public EmployeeSpecification(
EmployeeCompleteDTO filter,
String scope,
String order
) {
this.filter = filter;
this.scope = scope;
this.order = order;
}
@Override
public Predicate toPredicate(Root root, CriteriaQuery> query,
CriteriaBuilder criteriaBuilder) {
List predicates = new ArrayList<>();
if (filter.getFirstName() != null) {
predicates.add(criteriaBuilder.like(criteriaBuilder.function("unaccent",
String.class,
criteriaBuilder.lower(root.get("firstName"))),
"%" + StringUtils.stripAccents(filter.getFirstName()
.toLowerCase()) + "%"));
}
Order sortOrder;
sortOrder = order.equals("asc")
? criteriaBuilder.asc(root.get(scope))
: criteriaBuilder.desc(
criteriaBuilder.coalesce(root.get(scope), 0));
query.orderBy(sortOrder);
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
}
}
```
Example of 13 filters and 2 sorters with JpaSearch
```java
public enum EmployeeCriteria implements ISearchCriteriaConfig {
ID(FilterSorterConfig.of("id", ObjectFilterOperation.EQUAL, "id")),
FIRSTNAME(FilterSorterConfig.of("firstname", StringFilterOperation.CONTAIN_IGNORE_CASE, "firstName")),
LASTNAME(FilterConfig.of("lastname", StringFilterOperation.CONTAIN_IGNORE_CASE, "lastName")),
LASTNAME_IGNORE_ACCENT(FilterConfig.of("lastnameIgnoreAccent", StringFilterOperation.CONTAIN_IGNORE_CASE_IGNORE_ACCENT, "lastName")),
BIRTHDATE(FilterConfig.of("birthdate", ListComparableFilterOperation.BETWEEN, "birthDate")),
MARRYING(FilterConfig.of("marrying", ObjectFilterOperation.EQUAL, "marriedEmployee.id")),
COMPANY(FilterConfig.of("company", StringFilterOperation.START_WITH, "company.name")),
FIRSTNAME_OR_LASTNAME(FilterConfig.of("firstnameLastname", StringFilterOperation.CONTAIN_IGNORE_CASE, "firstName", "lastName")),
FIRSTNAME_AND_LASTNAME(GroupFilterConfig.of("searchName", FIRSTNAME.getFilterConfig(), LASTNAME.getFilterConfig())),
SEARCH_MARRIED(MultiFilterConfig.of("search_married", FIRSTNAME_AND_LASTNAME.getFilterConfig(), "marriedEmployee.id")),
PET_NAME(FilterConfig.of("petName", StringFilterOperation.CONTAIN_IGNORE_CASE, "pets[name]")),
PET_SPECIES(FilterConfig.of("petSpecies", ObjectFilterOperation.EQUAL, "pets[species]")),
PETS(GroupFilterConfig.of("searchPets", PET_NAME.getFilterConfig(), PET_SPECIES.getFilterConfig())),
MULTI_PETS(MultiFilterConfig.of("multiPets", PET_SPECIES.getFilterConfig(), "pets"));
final ISearchConfig searchConfig;
@Override
public OrderCriteria getDefaultOrderCriteria() {
return new OrderCriteria(ID.getKey(), SortDirection.ASC);
}
@Override
public Class getRootClass() {
return Employee.class;
}
}
```
Known limitations 🐧
Jpa Search doesn't provide the option to filter through several *ToMany relations, for example: Companies → Employees → Pets (the generic search doesn't allow you to filter on the pets owned by the employees of each company).
Sorting on elements requiring a join is not possible (How to sort on a list of linked elements)
Autogenerated swaggers can't identify fields in queryparams, since a Map<String, String> is passed.
To use the IGNORE_ACCENT operation you must install the function according to your database manager. After that you can specify the name of the function or the name of the schema where the function is located.
MySQL
```sql
DROP FUNCTION IF EXISTS unaccent;
DELIMITER |
CREATE FUNCTION unaccent( textvalue VARCHAR(10000) ) RETURNS VARCHAR(10000)
DETERMINISTIC
NO SQL
BEGIN
SET @textvalue = textvalue COLLATE utf8mb4_general_ci;
-- ACCENTS
SET @withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
SET @withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
SET @count = LENGTH(@withaccents);
WHILE @count > 0 DO
SET @textvalue = REPLACE(@textvalue, SUBSTRING(@withaccents, @count, 1), SUBSTRING(@withoutaccents, @count, 1));
SET @count = @count - 1;
END WHILE;
-- SPECIAL CHARS
SET @special = '«»’”“!@#$%¨&()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+|\';
SET @count = LENGTH(@special);
WHILE @count > 0 DO
SET @textvalue = REPLACE(@textvalue, SUBSTRING(@special, @count, 1), '');
SET @count = @count - 1;
END WHILE;
RETURN @textvalue;
END
|
DELIMITER ;
```
PostgreSQL
```sql
CREATE EXTENSION IF NOT EXISTS "unaccent";
```
Microsoft SQL Server
```sql
IF OBJECT_ID('dbo.unaccent', 'FN') IS NOT NULL
DROP FUNCTION dbo.unaccent;
GO
CREATE FUNCTION dbo.unaccent (@textvalue NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @withaccents NVARCHAR(MAX), @withoutaccents NVARCHAR(MAX), @special NVARCHAR(MAX);
DECLARE @count INT;
SET @textvalue = @textvalue COLLATE Latin1_General_BIN;
-- ACCENTS
SET @withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿƒ';
SET @withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYaaaaaaaceeeeiiiinoooooouuuuyyf';
SET @count = LEN(@withaccents);
WHILE @count > 0
BEGIN
SET @textvalue = REPLACE(@textvalue, SUBSTRING(@withaccents, @count, 1), SUBSTRING(@withoutaccents, @count, 1));
SET @count = @count - 1;
END;
-- SPECIAL CHARS
SET @special = '«»’”“!@#$%¨&()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+|';
SET @count = LEN(@special);
WHILE @count > 0
BEGIN
SET @textvalue = REPLACE(@textvalue, SUBSTRING(@special, @count, 1), '');
SET @count = @count - 1;
END;
RETURN @textvalue;
END;
```
Requirements ⬇️
This library is compatible with Java17+, so you can install it simply by adding its dependencies to your build.gradle / pom.xml.
Getting started ✋
1 - Configure your SpringBootApplication
Firstly, you need to specify that you want Spring to scan your package and the library package to allow Spring bean detectiona and injection, so in your SpringBootApplication add this line :
```java
@SpringBootApplication(scanBasePackages = {"your_package", "com.avisto.jpasearch"})
```
2 - Configure your Entity
For the library to correctly analyze your entity, you must add SearchableEntity to your Entity :
```java
public class Entity implements SearchableEntity {}
```
3 - Create Criteria Enum
To specify a filter for your search, you need to create an enum as described in [How to use it ? 🤔](#how-to-use-it).
4 - Configure your Service
After applying the previous steps, you can inject a SearchCriteriaRepository with the criteria and the entity you want to search with.
example with constructor injection :
```java
private final SearchCriteriaRepository searchCriteriaRepository;
```
example with field injection :
```java
@Autowired private SearchCriteriaRepository searchCriteriaRepository;
```
5 - Example of use
This example shows you how to search your entity with EntityCriteria Enum :
```java
public Page getEntities(Map params, List sorts) {
return searchCriteriaRepository.search(EntityCriteria.class, params, sorts, EntityDTO.EntityInList::new);
}
```
The result: Page contains the total number of elements and a list of elements.