How to make an advanced search with Spring Data REST?

Question

My task is to make an advanced search with Spring Data REST. How can I implement it?

I managed to make a method to do a simple search, like this one:

public interface ExampleRepository extends CrudRepository<Example, UUID>{

    @RestResource(path="searchByName", rel="searchByName")
    Example findByExampleName(@Param("example") String exampleName);

}

This example works perfectly if I have to go simply to the url:

.../api/examples/search/searchByName?example=myExample

But what I have to do if there are more than one field to search?

For example, if my Example class has 5 fields, what implementation should I have to make an advanced search with all possibiles fileds?

Consider this one:

.../api/examples/search/searchByName?filed1=value1&field2=value2&field4=value4

and this one:

.../api/examples/search/searchByName?filed1=value1&field3=value3

What I have to do to implement this search in appropriate way?

Thanks.

Juanajuanita

Take a look here spring.io/blog/2011/04/26/… – Woods

Found any better way, than the one you started with ? – Buzzer

Take a look to my answer. – Juanajuanita

I believe I accomplished what you're looking for, please check my answer. – Davedaveda

Answer 1

Spring Data Rest has integrated QueryDSL with web support as well which you can use for your advanced search requirement. You need to change your repository to implement QueryDslPredicateExecutor and things will work out of the box.

Here is a sample from the blog article about the feature:

$ http :8080/api/stores?address.city=York    
{
    "_embedded": {
        "stores": [
            {
                "_links": {
                    …
                }, 
                "address": {
                    "city": "New York", 
                    "location": { "x": -73.938421, "y": 40.851 }, 
                    "street": "803 W 181st St", 
                    "zip": "10033-4516"
                }, 
                "name": "Washington Hgts/181st St"
            }, 
            {
                "_links": {
                    …
                }, 
                "address": {
                    "city": "New York", 
                    "location": { "x": -73.939822, "y": 40.84135 }, 
                    "street": "4001 Broadway", 
                    "zip": "10032-1508"
                }, 
                "name": "168th & Broadway"
            }, 
            …
        ]
    }, 
    "_links": {
        …
    }, 
    "page": {
        "number": 0, 
        "size": 20, 
        "totalElements": 209, 
        "totalPages": 11
    }
}

Hephaestus

Answer 2

I managed to implement this using Query by Example.

Let's say you have the following models:

@Entity
public class Company {

  @Id
  @GeneratedValue
  Long id;

  String name;
  String address;

  @ManyToOne
  Department department;

}


@Entity
public class Department {

  @Id
  @GeneratedValue
  Long id;

  String name;

}

And the repository:

@RepositoryRestResource
public interface CompanyRepository extends JpaRepository<Company, Long> {
}

(Note that JpaRepository implements QueryByExampleExecutor).

Now you implement a custom controller:

@RepositoryRestController
@RequiredArgsConstructor
public class CompanyCustomController {

  private final CompanyRepository repository;

  @GetMapping("/companies/filter")
  public ResponseEntity<?> filter(
      Company company,
      Pageable page,
      PagedResourcesAssembler assembler,
      PersistentEntityResourceAssembler entityAssembler
  ){

    ExampleMatcher matcher = ExampleMatcher.matching()
        .withIgnoreCase()
        .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING);

    Example example = Example.of(company, matcher);

    Page<?> result = this.repository.findAll(example, page);

    return ResponseEntity.ok(assembler.toResource(result, entityAssembler));

  }
}

And then you can make queries like:

localhost:8080/companies/filter?name=google&address=NY

You can even query nested entities like:

localhost:8080/companies/filter?name=google&department.name=finances

I omitted some details for brevity, but I created a working example on Github.

Davedaveda

Answer 3

The implementation of query methods is widely documented in Spring reference documentation and tons of technical blogs, though quite a bunch are outdated.

Since your question is probably "How can I perform a multi-parameter search with any combination of fields without declaring an awful lot of findBy* methods?", the answer is Querydsl, which is supported by Spring.

Baptistery

Don't think query dsl can be combined with SDR. – Fromenty

@matr0s take a look at the QueryDslPredicateExecutor interface. Querydsl support in Spring has been out since the Gosling release of Spring. I for one have been using it for months, I can humbly vouch for it ;) – Baptistery

Never knew query dsl has been integrated to SDR. Thank a lot for this link. – Fromenty

Answer 4

I have found a working solution for this task.

@RepositoryRestResource(excerptProjection=MyProjection.class)
public interface MyRepository extends Repository<Entity, UUID> {

    @Query("select e from Entity e "
          + "where (:field1='' or e.field1=:field1) "
          + "and (:field2='' or e.field2=:field2) "
          // ...
          + "and (:fieldN='' or e.fieldN=:fieldN)"
    Page<Entity> advancedSearch(@Param("field1") String field1,
                               @Param("field2") String field2,
                               @Param("fieldN") String fieldN,
                               Pageable page);

}

With this solution, using this base url:

http://localhost:8080/api/examples/search/advancedSearch

We can make advanced searches with all the fields that we need.

Some examples:

http://localhost:8080/api/examples/search/advancedSearch?field1=example
    // filters only for the field1 valorized to "example"

http://localhost:8080/api/examples/search/advancedSearch?field1=name&field2=surname
    // filters for all records with field1 valorized to "name" and with field2 valorized to "surname"

Juanajuanita

Got a compilation error and modified as below. @Query("select e from Entity e " +"where (:field1='' or e.field1=:field1) " +"and (:field2='' or e.field2=:field2) " //... +"and (:fieldN='' or e.fieldN=:fieldN) ") Page<Entity> advancedSearch(@Param("field1") String field1, @Param("field2") String field2, @Param("fieldN") String fieldN, Pageable page); – Macias

Answer 5

I guess You can try following:

List<Person> findDistinctPeopleByLastnameOrFirstname(@Param("lastName")String lastname, @Param("firstName")String firstname);

and examples/search/searchByLastnameOrFirstname?firstName=value1&lastName=value2

Check out: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation

Fromenty

Yup, that is one of the link I pointed to in my earlier answer. Up to two parameters, this is OK, but with three or more, It becomes a bit awkward to define all the findBy methods. If your search fields are "A", "B" and "C", you quickly end up with : findByA, findByB, findByC, findByAAndBAndC, findByAAndB, findByAAndC, findByBAndC... now imagine A, B, C to be meaningful variable names, such as "name, description, createDate". – Baptistery

for now I'm trying something like this: personRepo.findAll(new PersonAdvancedSearch(name, surname, age, etc...)), where PersonAdvancedSearch implements Specification<Person>. PersonAdvancedSearch overrides the method toPredicate. It seems to work, but i still have problems if the attribute is a complex type. – Juanajuanita

@AlessandroC maybe crafting your method manually with the @Query annotation could help you better handle search on your domain object. – Baptistery

well even if it where like this. your resource and repository are in one class. you dont need to write a single line of sql, besides defining a method in a repository interface and ... – Buzzer

Answer 6

You can use CriteriaOperator to create advanced search with Spring Data.

<dependency>
    <groupId>com.apulbere</groupId>
    <artifactId>crop</artifactId>
    <version>0.1.1</version>
</dependency>

For example you have a pet shop and want to search pets by name, birthdate, type etc. using different operators: like, equal, between etc.

The REST endpoint will look like:

http://localhost:64503/pets?birthdate.gte=2010-01-11&nickname.like=Ba

Where the query params have the following format: fieldName.operatorName=value

In controller you map your search criteria with the entity metamodel:

@GetMapping("/pets")
List<PetRecord> search(PetSearchCriteria petSearchCriteria) {
    return cropService.create(Pet.class, petSearchCriteria)
            .match(PetSearchCriteria::getNickname, Pet_.name)
            .match(PetSearchCriteria::getBirthdate, Pet_.birthdate)
            .getResultList()
            .stream()
            .map(petMapper::map)
            .toList();
}

Where the PetSearchCriteria is a simple DTO defined by you using data types form the library:

public class PetSearchCriteria {
    private StringCriteriaOperator nickname;
    private LocalDateCriteriaOperator birthdate;
}

Check out the repository for full example.

Fire