JPA Search Helper

Library for building and running advanced and dynamic queries using JPA in Spring Boot.

TL;DR

The library offers two ways to build queries:

Mode 1: Via Map<String, String>, to support GET endpoints with query params
Mode 2: Via an object (easily serializable/deserializable via JSON), to support POST endpoints that expect query parameters in the body (new from 2.0.0 version)

Spoiler

Through jpa-search-helper your controller will be able to receive requests like this:

Mode 1:

curl -X GET \
  'https://myexampledomain.com/persons?
  firstName=Biagio
  &lastName_startsWith=Toz
  &birthDate_gte=19910101
  &country_in=IT,FR,DE
  &address_eq=Via Roma 1,Via Milano/,1,20 West/,34th Street
  &company.name_in=Microsoft,Apple,Google
  &company.employees_between=500,5000'

Mode 2:

curl -X POST -H "Content-type: application/json" -d '{
  "filter" : {
      "operator": "and", // the first filter must contain a root operator: AND, OR or NOT
      "filters" : [
        {
          "operator": "eq",
          "key": "firstName",
          "value": "Biagio"
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "startsWith",
              "key": "lastName",
              "value": "Toz",
              "options": {
                "ignoreCase": true
              }
            },
            {
              "operator": "endsWith",
              "key": "lastName",
              "value": "ZZI",
              "options": {
                "ignoreCase": true,
                "trim" : true
              }
            }
          ]
        },
        {
          "operator": "in",
          "key": "company.name",
          "values": ["Microsoft", "Apple", "Google"]
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "gte",
              "key": "birthDate",
              "value": "19910101"
            },
            {
              "operator": "lte",
              "key": "birthDate",
              "value": "20010101"
            }
          ]
        },
        {
          "operator": "between",
          "key" : "company.employees",
          "values": [500, 5000],
          "options": {
            "negate": true
          }
        }
      ]
  },
  "options": {
    "pageSize": 10,
    "pageOffset": 0,
    "sortKey": "birthDate",
    "sortDesc": false
  }
  
}' 'https://myexampledomain.com/persons'

.. how you do it? Read this readme!

Compatibility Matrix

Minimum requirements

Java 17 or later
Spring Boot 3.2.x or later

JPA Search Helper	Spring Boot	Java
[v0.0.1 - v2.1.1]	3.2.x	[17 - 22]
[v3.0.0 - latest]	3.3.x	[17 - 22]

Project dependency

Maven

<dependency>
    <groupId>app.tozzi</groupId>
    <artifactId>jpa-search-helper</artifactId>
    <version>3.0.3</version>
</dependency>

Gradle

implementation 'app.tozzi:jpa-search-helper:3.0.3'

Usage

1. `@Searchable` annotation

Start by applying the @Searchable annotation to the fields in your DTO/Domain model, or alternatively your JPA entity, that you want to make available for search. If you have fields that you want to make searchable within other objects then annotate these with @NestedSearchable.

@Data
public class PersonDTO {

  @Searchable
  private String firstName;

  @Searchable
  private String lastName;

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

  @Searchable
  private String country;
  
  @Searchable
  private String fillerOne;
  
  @Searchable
  private String fillerTwo;

  @NestedSearchable
  private Company company;

  @Data
  public static class CompanyDTO {

    @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, ZONEDDATETIME.
Validation properties:
- datePattern: only for DATE, LOCALDATE, LOCALDATETIME, LOCALTIME, OFFSETDATETIME, OFFSETTIME, ZONEDDATETIME target types. 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 #.##
Other:
- sortable: if false, the field can be used by search but cannot be used for sorting. Default: true.
- trim: apply trim.
- tags: useful if the DTO field can correspond to multiple entity fields (the example is available further down).
- allowedFilters: exclusively allowed filters.
- notAllowedFilters: not allowed filters.
- likeFilters: allowed like filters (contains, startsWith, endsWith). Default: true.

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;

  @Column(name = "FIL_ONE")
  private String fillerOne;

  @Column(name = "FIL_TWO")
  private String fillerTwo;

  @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;

}

2. `JPASearchRepository` interface

Your Spring JPA repository must extend JPASearchRepository< YourEntityClass >.

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

}

3. Search implementation

In your manager, or in your service, or wherever you want to use the repository:

Mode 1: define a map <filter_key#options, value>:

// ...

  @Autowired
  private PersonRepository personRepository;

    // ...

    Map<String, String> filters = new HashMap<>();
    filters.put("firstName_eq", "Biagio");
    filters.put("lastName_startsWith#i", "Toz"); // ignore case
    filters.put("birthDate_gte", "19910101"); 
    filters.put("country_in", "IT,FR,DE");
    filters.put("company.name_eq#n", "Bad Company"); // negation
    filters.put("company.employees_between", "500,5000");
    filters.put("fillerOne_null#n", "true"); // not null
    filters.put("fillerTwo_empty", "true"); // empty
    
    // Without pagination
    List<PersonEntity> fullSearch = personRepository.findAll(filters, PersonDTO.class);
  
    filters.put("birthDate_sort" : "ASC"); // sorting key and sorting order
    filters.put("_limit", "10"); // page size
    filters.put("_offset", "0"); // page offset
    
    // With pagination
    Page<PersonEntity> sortedAndPaginatedSearch = personRepository.findAllWithPaginationAndSorting(filters, PersonDTO.class);
    
    // ...

// ...

Mode 2: instead of a map, you will need to use JPASearchInput, which I will show here, for simplicity, in JSON format.

Through mode 2 it is possible to manage complex filters with AND, OR and NOT (see later).

{
  "filter" : {
      "operator": "and", // the first filter must contain a root operator: AND, OR or NOT
      "filters" : [
        {
          "operator": "eq",
          "key": "firstName",
          "value": "Biagio"
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "startsWith",
              "key": "lastName",
              "value": "Toz",
              "options": {
                "ignoreCase": true
              }
            },
            {
              "operator": "endsWith",
              "key": "lastName",
              "value": "ZZI",
              "options": {
                "ignoreCase": true,
                "trim" : true
              }
            }
          ]
        },
        {
          "operator": "in",
          "key": "company.name",
          "values": ["Microsoft", "Apple", "Google"]
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "gte",
              "key": "birthDate",
              "value": "19910101"
            },
            {
              "operator": "lte",
              "key": "birthDate",
              "value": "20010101"
            }
          ]
        },
        {
          "operator": "empty",
          "key": "fillerOne",
          "options": {
            "negate": true
          }
        },
        {
          "operator": "between",
          "key" : "company.employees",
          "values": [500, 5000],
          "options": {
            "negate": true
          }
        }
      ]
  },
  "options": {
    "pageSize": 10,
    "pageOffset": 0,
    "sortKey": "birthDate",
    "sortDesc": false
  }
  
}

Exceptions:

If a field does not exist, is not searchable or is not sortable, you will receive an InvalidFieldException.
If the value of a field does not meet the requirements you will receive an InvalidValueException.
Other cases: JPASearchException

Docs

Managed root operators

Filter name	Library Key	Supported modes
AND	and	1, 2
OR	or	2
NOT	not	2

Through Mode 1, all filters compose exclusively an AND search.

To use the other operators, OR and NOT, you must use Mode 2

Managed search filter operators

Filter name	Library Key	SQL	Supported modes	Value required
Equals	eq	sql_col = val	1,2	yes
Contains	contains	sql_col LIKE '%val%'	1,2	yes
In	in	sql_col IN (val1, val2, ..., valN)	1,2	yes
Starts With	startsWith	sql_col LIKE 'val%'	1,2	yes
Ends With	endsWith	sql_col LIKE '%val'	1,2	yes
Greater Than	gt	sql_col > val	1,2	yes
Greater Than or Equal	gte	sql_col >= val	1,2	yes
Less Than	lt	sql_col < val	1,2	yes
Less Than or Equal	lte	sql_col <= val	1,2	yes
Between	between	sql_col BETWEEN val1 AND val2	1,2	yes
Null	null	sql_col IS NULL	1,2	no
Empty	empty	sql_coll_col IS NULL	1,2	no

Options

Mode 1: the option keys must be appended to the filter; e.g. ?firstName_eq#i=Biagio or ?firstName_eq#i#n=Biagio

Option description	Library Key
Ignore case	#i
Negation	#n
Trim	#t

Mode 2:

Option description	Library Key (Java attributes)
Ignore case	ignoreCase
Negation	negate
Trim	trim

For each filter, value the JPASearchFilterOptions attributes

@Data
public static class JPASearchFilterOptions {
    private boolean ignoreCase;
    private boolean trim;
    private boolean negate;
}

or if you prefer to manage a JSON:

{
  // ...
      {
         "operator": "eq",
         "key": "firstName",
         "value": "Biagio",
         "options": {
           "ignoreCase": true,
           "trim": false,
           "negate": true
         }
      }
  // ...
}

Pagination

Filter name	Key	Fixed values
Limit (page size)	limit
Offset (page number)	offset
Sort	sort	ASC, DESC

Mode 1: e.g. ?firstName_sort=DESC&_limit=10&_offset=1

Mode 2: value the JPASearchOptions attributes

@Data
public static class JPASearchOptions {
    private String sortKey;
    private Boolean sortDesc = false;
    private Integer pageSize;
    private Integer pageOffset;
}

or if you prefer to manage a JSON:

{
  "filter" : {
    // ...
  },
  "options" : {
    "sortKey": "firstName",
    "sortDesc": true,
    "pageSize": 10,
    "pageOffset": 1
  }
}

Other (only for Mode 1)

Separator for array values: ,; e.g. ?myField_in=test1,test2 --> values: ["test1", "test2"]
To escape separator: /,; e.g. ?myField_in=test1,test2/,test3 --> values: ["test1", "test2,test3"]

Spring Boot Project example with HTTP Endpoint

Note: This library does not expose any endpoints and therefore no controllers.

An example project is available here.

Mode 1

Controller:

@RestController
public class MyController {

  @Autowired
  private PersonManager personManager;

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

Service/Manager bean:

@Service
public class PersonManager {

  @Autowired
  private PersonRepository personRepository;

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

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

}

Curl:

curl - X GET 
'http://localhost:8080/persons?
firstName=Biagio
&lastName_startsWith=Toz
&birthDate_gte=19910101
&country_in=IT,FR,DE
&company.name_in=Microsoft,Apple
&company.employees_between=500,5000'

Mode 2

Controller:

@RestController
public class MyController {

  @Autowired
  private PersonManager personManager;

  @PostMapping(path="/persons", produces = MediaType.APPLICATION_JSON_VALUE, consumes = MediaType.APPLICATION_JSON_VALUE)
  public List<PersonDTO> findPersons(@RequestBody JPASearchInput input) {
    return personManager.find(input);
  }
}

Service/Manager bean:

@Service
public class PersonManager {

  @Autowired
  private PersonRepository personRepository;

  public List<PersonDTO> find(JPASearchInput input) {
    return personRepository.findAllWithPaginationAndSorting(input, PersonDTO.class).stream().map(this::toDTO).toList();
  }

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

}

Curl:

curl -X POST -H "Content-type: application/json" -d '{
  "filter" : {
      "operator": "and", // the first filter must contain a root operator: AND, OR or NOT
      "filters" : [
        {
          "operator": "eq",
          "key": "firstName",
          "value": "Biagio"
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "startsWith",
              "key": "lastName",
              "value": "Toz",
              "options": {
                "ignoreCase": true
              }
            },
            {
              "operator": "endsWith",
              "key": "lastName",
              "value": "ZZI",
              "options": {
                "ignoreCase": true,
                "trim" : true
              }
            }
          ]
        },
        {
          "operator": "in",
          "key": "company.name",
          "values": ["Microsoft", "Apple", "Google"]
        },
        {
          "operator": "or",
          "filters": [
            {
              "operator": "gte",
              "key": "birthDate",
              "value": "19910101"
            },
            {
              "operator": "lte",
              "key": "birthDate",
              "value": "20010101"
            }
          ]
        },
        {
          "operator": "between",
          "key" : "company.employees",
          "values": [500, 5000],
          "options": {
            "negate": true
          }
        }
      ]
  },
  "options": {
    "pageSize": 10,
    "pageOffset": 0,
    "sortKey": "birthDate",
    "sortDesc": false
  }
  
}' 'http://localhost:8080/persons'

Advanced Settings

Join Fetch

It is possible to force joins with fetch to allow Hibernate to execute a single query for the relationships defined on the entity. This is only possible without pagination:

// ...

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

// ...

Multiple entities for the same DTO

If you have a DTO that is the result of the conversion of multiple entities, it is possible to explicitly specify a map (string, string) whose key represents the name of the DTO field and the value is the name of the field of the entity to be searched for:

// ...

Map<String, String> entityFieldMap = Map.of("company", "companyEntity.name");

// Without pagination
personRepository.findAll(filters, PersonDTO.class, fetches, entityFieldMap);

// With pagination
personRepository.findAllWithPaginationAndSorting(filters, PersonDTO.class, entityFieldMap);

// ...

Multiple object for the same entity

Another special case could be where an object can be repeated within the DTO to represent multiple pieces of the entity. The solution for the search:

@Entity
public class CoupleEntity {

  @Id
  private Long id;

  @Column(name = "p1_fn")
  private String p1FirstName;

  @Column(name = "p1_ln")
  private String p1LastName;

  @Column(name = "p2_fn")
  private String p2FirstName;

  @Column(name = "p2_ln")
  private String p2LastName;
}

@Data
public class CoupleDTO {

  @NestedSearchable
  private PersonDTO p1;

  @NestedSearchable
  private PersonDTO p2;

  @Data
  public static class PersonDTO {

    @Searchable(tags = {
            @Tag(fieldKey = "p1.firstName", entityFieldKey = "p1FirstName"),
            @Tag(fieldKey = "p2.firstName", entityFieldKey = "p2FirstName"),
    })
    private String firstName;

    @Searchable(tags = {
            @Tag(fieldKey = "p1.lastName", entityFieldKey = "p1LastName"),
            @Tag(fieldKey = "p2.lastName", entityFieldKey = "p2LastName"),
    })
    private String lastName;
  }
}

curl - request GET 
 - url 'https://www.myexampledomain.com/couples?
p1.firstName_iEq=Romeo
&p2.firstName_iEq=Giulietta'

Name		Name	Last commit message	Last commit date
Latest commit History 91 Commits
.github/workflows		.github/workflows
gradle/wrapper		gradle/wrapper
src		src
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
build.gradle		build.gradle
gradlew		gradlew
gradlew.bat		gradlew.bat
settings.gradle		settings.gradle

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

JPA Search Helper

TL;DR

Spoiler

Compatibility Matrix

Minimum requirements

Project dependency

Maven

Gradle

Usage

1. `@Searchable` annotation

2. `JPASearchRepository` interface

3. Search implementation

Exceptions:

Docs

Managed root operators

Managed search filter operators

Options

Pagination

Other (only for Mode 1)

Spring Boot Project example with HTTP Endpoint

Mode 1

Mode 2

Advanced Settings

Join Fetch

Multiple entities for the same DTO

Multiple object for the same entity

About

Releases 24

Packages

Contributors 2

Languages

License

biagioT/jpa-search-helper

Folders and files

Latest commit

History

Repository files navigation

JPA Search Helper

TL;DR

Spoiler

Compatibility Matrix

Minimum requirements

Project dependency

Maven

Gradle

Usage

1. @Searchable annotation

2. JPASearchRepository interface

3. Search implementation

Exceptions:

Docs

Managed root operators

Managed search filter operators

Options

Pagination

Other (only for Mode 1)

Spring Boot Project example with HTTP Endpoint

Mode 1

Mode 2

Advanced Settings

Join Fetch

Multiple entities for the same DTO

Multiple object for the same entity

About

Topics

Resources

License

Stars

Watchers

Forks

Releases 24

Packages 0

Contributors 2

Languages

1. `@Searchable` annotation

2. `JPASearchRepository` interface

Packages