CODEWITHSUNDEEP
javaspringspring-data-jpaspring-data
Akmenrha
Akmenrha

Reputation: 21

Spring Data - Multi-column search by only one value

I am using Spring Data for adding into my grid a list of Customer.

In my repository, I have the following method :

public List<CustomerDto> findAllByFirstNameContainingOrLastNameContainingAllIgnoreCase(String firstName, String lastName);

It works, but in reality, firstName and lastName parameters are the same value.

Is there by any chance the possibility to achieve, by only using the method's keyword functionnality (I don't wanna add complexity by using the @Query annotation and writing my own query), to have only one parameter for filtering my two columns and avoiding me to provide the same parameter twice in the method like :

repository.myNewAwesomeMethodForFilteringTwoColumnsWithOneValue(filterValue);

Instead of :

repository.findAllByFirstNameContainingOrLastNameContainingAllIgnoreCase(filterValue, filterValue);

Thanks for your help, if you have any ideas or suggestions !

Upvotes: 2

Views: 1383

Answers (1)

emyasa
emyasa

Reputation: 86

A generic solution that I can think of is using JPA Specification. This might be an overkill solution but if you have a lot of use cases for multi-column search then I think it's worth it.

I'll demonstrate it first how I use it.

    List<String> bookColumns = Arrays.asList("title", "genre", "author.name");

    // Search "Spring": matches 2 Books - Spring in Action and Spring Integration
    GenericSearchSpecification<Book> searchSpecification =  new GenericSearchSpecification<>(bookColumns, "Spring");
    List<Book> books = bookRepository.findAll(searchSpecification);
    Assert.assertEquals(2, books.size());

GenericSearchSpecification is a class that I've created which has a constructor accepting 2 parameters

  • searchableColumns
  • searchString

Now for the class implementation

public class GenericSearchSpecification<T> implements Specification<T> {

    private List<String> columns;
    private String search;

    public GenericSearchSpecification(List<String> columns, String search) {
        this.columns = columns;
        this.search = search;
    }

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        List<Predicate> predicates = new ArrayList<>();
        for (String column: columns) {
            Path path = buildPath(column, root);
            Predicate predicate = criteriaBuilder.like(criteriaBuilder.upper(path), "%" + search.toUpperCase() + "%");
            predicates.add(predicate);
        }

        return criteriaBuilder.or(predicates.stream().toArray(Predicate[]::new));
    }

    private Path buildPath(String column, Path path) {

        if (!column.contains(".")) return path.get(column);

        String[] parts = column.split("\\.");
        for (String part: parts) {
            path = path.get(part);
        }

        return path;
    }

}

If you're interested feel free to visit the article that I created specifically for this: https://medium.com/javarevisited/jpa-specification-a-generic-search-e8695b1d19ec

Upvotes: 1

Related Questions