Advanced and dynamic searching with Spring Data JPA

I have often been asked to develop advanced search services. By advanced search I mean searches in which it’s possible to apply multiple filters on all (or almost all) fields such as: like, between, in, greater than, etc.

So imagine having to build a service, based on one or more entities, capable of offering an endpoint that can be called like this (start keeping an eye out for special suffixes < propertyName >< _suffix >):

curl - request GET \
 - url 'http://www.myexampledomain.com/persons?
firstName=Biagio
&lastName_startsWith=Toz
&birthDate_gte=19910101
&country_in=IT,FR,DE
&company.name_in=Microsoft,Apple
&company.employees_between=500,5000'

or

curl --request GET \
--url 'http://www.myexampledomain.com/persons?
firstName_endsWith=gio
&lastName_in=Tozzi,Totti
&birthDate_lt=19980101
&_offset=0
&_limit=100
&birthDate_sort=ASC'

If you are using JPA in a Spring Boot project you can now develop this search service with just a few lines of code thanks to JPA Search Helper! Let me explain what it is.

JPA Search Helper

First step: @Searchable annotation

Start by applying the @Searchable annotation to the fields in your DTO, or alternatively your JPA entity, that you want to make available for search.

public class Person {

    @Searchable
    private String firstName;

    @Searchable
    private String lastName;

    @Searchable(entityFieldKey = "dateOfBirth")
    private Date birthDate;

    @Searchable
    private String country;

    private Company company;

    @Data
    public static class Company {

        @Searchable(entityFieldKey=companyEntity.name)
        private String name;

        @Searchable(entityFieldKey=companyEntity.employeesCount)
        private int employees;

    }

}

The annotation allows you to specify:
Core properties:

• entityFieldKey: the name of the field defined on the entity bean (not to be specified if using the annotation on the entity bean). If not specified the key will be the field name.
• targetType: the managed object type by entity. If not specified the librariy tries to obtain it based on field type (es. Integer field without target type definition will be INTEGER). If there is no type compatible with those managed, it will be managed as a string. Managed types: STRING, INTEGER, DOUBLE, FLOAT, LONG, BIGDECIMAL, BOOLEAN, DATE, LOCALDATE, LOCALDATETIME, LOCALTIME, OFFSETDATETIME, OFFSETTIME. Validation properties:
• datePattern: only for DATE target type. Defines the date pattern to use.
• maxSize, minSize: maximum/minimum length of the value
• maxDigits, minDigits: only for numeric types. Maximum/minimum number of digits.
• regexPattern: regex pattern.
• decimalFormat: only for decimal numeric types. Default #.## Continuing the example, our entity classes:

@Entity
@Data
public class PersonEntity {

    @Id
    private Long id;

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

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

    @Column(name = "BIRTH_DATE")
    private Date dateOfBirth;

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

    @OneToOne
    private CompanyEntity companyEntity;

}

@Entity
@Data
public class CompanyEntity {

    @Id
    private Long id;

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

    @Column(name = "COUNT")
    private Integer employeesCount;

}

Second and last step: JPASearchRepository<?>

Your Spring JPA repository must extend JPASearchRepository<?>:

@Repository
public interface PersonRepository extends JpaRepository<PersonEntity, Long>, JPASearchRepository<PersonEntity> {

}

Well, let’s build the filters and feed them to the repository:

// ...

Map<String, String> filters = new HashMap<>();
filters.put("firstName_eq", "Biagio");
filters.put("lastName_startsWith", "Toz");
filters.put("birthDate_gte", "19910101"); 
filters.put("country_in", "IT,FR,DE");
filters.put("company.name_in", "Microsoft,Apple");
filters.put("company.employees_between", "500,5000");

// Without pagination
List<PersonEntity> fullSearch = personRepository.findAll(filters, Person.class);

filters.put("birthDate_sort" : "ASC");
filters.put("_limit", "10");
filters.put("_offset", "0");

// With pagination
Page<PersonEntity> sortedAndPaginatedSearch = personRepository.findAllWithPaginationAndSorting(filters, Person.class);

// ...

Basically you just need to define a map whose key is made up of < fieldName >< _suffix > and search value. The complete list of suffixes, i.e. available filters, is here.

Note 1: if no suffix is ​​specified the search is done in equal (_eq)

Note 2: In the example I applied the @Searchable annotation on the DTO fields. Alternatively, it’s possible to apply them directly on the entity.

A pseudo-real implementation in a Spring Boot project

Service/Manager bean:

@Service 
public class PersonManager {    

    @Autowired      
    private PersonRepository personRepository;

    public List<Person> find(Map<String, String> filters) {
      return personRepository.findAllWithPaginationAndSorting(filters, Person.class).stream().map(this::toDTO).toList(); 
    } 

    private static Person toDTO(PersonEntity personEntity) {
        // ...
    }

}

Controller:

@RestController
public class MyController {

    @Autowired      
    private PersonManager personManager;

    @GetMapping(path="/persons", produces = MediaType.APPLICATION_JSON_VALUE)  
    public List<Person> findPersons(@RequestParam Map<String, String> requestParams) {  
        return personManager.find(requestParams);  
    }
}

..et voilà les jeux sont faits

Extra

The library allows you to force join fetch.

A “fetch” join allows associations or collections of values to be initialized along with their parent objects using a single select.

That’s how:

// ...

Map<String, JoinFetch> fetches = Map.of("companyEntity", JoinFetch.LEFT);
personRepository.findAll(filters, Person.class, fetches);

// ...

That’s all.. for now!