Spring Data - Multi-column searches

Question

I am using Spring Data for the paging and the sorting. However, I would like to perform multi-columns searches.

Now, I am using the annotation @Query in my repository interface like this:

public interface MyRepository extends PagingAndSortingRepository<Item,Long> {

    @Query(value="select mt from MY_TABLE mt where mt.field1 = %searchtext% or mt.field2 = %searchtext% or mt.field3 = %searchtext%")    
    Page<Item> findByAllColumns(@Param("searchtext") String searchtext, Pageable pageable);

}

I would like to know if there is another way to do because the number of columns in a table can be high.

Thanks for your help.

edit (clarification of the question after the comment of Brandon Oakley): The problem in this solution is in the where clause of the @Query annotation because we have to repeat the exact same searchtext parameter for every column we want to search on

Answer 1

Here is sample of such Specification for User:

public static Specification<User> containsTextInName(String text) {
    if (!text.contains("%")) {
        text = "%" + text + "%";
    }
    String finalText = text;
    return (root, query, builder) -> builder.or(
            builder.like(root.get("lastname"), finalText),
            builder.like(root.get("firstname"), finalText)
    );
}

or even more customizable implementation:

public static Specification<User> containsTextInAttributes(String text, List<String> attributes) {
    if (!text.contains("%")) {
        text = "%" + text + "%";
    }
    String finalText = text;
    return (root, query, builder) -> builder.or(root.getModel().getDeclaredSingularAttributes().stream()
            .filter(a -> attributes.contains(a.getName()))
            .map(a -> builder.like(root.get(a.getName()), finalText))
            .toArray(Predicate[]::new)
    );
}

public static Specification<User> containsTextInName(String text) {
    return containsTextInAttributes(text, Arrays.asList("lastname", "firstname"));
}

Usage:

userRepository.findAll(Specifications.where(UserSpecifications.containsTextInName("irs")))

Answer 2

You could use specifications. That also gives you more flexibility. You can have one method, but use multiple specifications for a query:

Page<Item> findAll(Specification<T> spec, Pageable pageable);

myRepository.findAll(textInAllColumns(searchText), pageable);