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

All above the solutions are great, but we can also use Example and ExampleMatcher for multi column search

1. First define search object with search parameters
2. Second, define Custom Example Matcher using ExampleMatcher and Example
3. Third, use customExampleMatcher in findAll() method

/* Build Search object */
Employee employee=new Employee();
        employee.setFirstName(requestDTO.getFilterText());
        employee.setLastName(requestDTO.getFilterText());
        employee.setEmail(requestDTO.getFilterText());

/* Build Example and ExampleMatcher object */
ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny()
                .withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("email", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());

Example<Employee> employeeExample= Example.of(employee, customExampleMatcher);

/* Get employees based on search criteria*/
employeetRepository.findAll(employeeExample, PageRequest.of(requestDTO.getCurrentPageNumber(), requestDTO.getPageSize(), Sort.by(requestDTO.getSortingOrderColumnName()).descending()));

Answer 2

If you want to achieve,

1. Pagination,

2. Search in all String columns,

3. Sort By,

4. Sorting order

in same service/request then this is for you!

I am really impressed with Michail Michailidis' answer and I did update it in my way so that it can be used for any Entity with Pagination (with page number and page size dynamic), sort by, sort order etc.

First of all copy this class at your end:

    public class EntitySpecification {

    public static <T> Specification<T> textInAllColumns(String text) {
        if (!text.contains("%")) {
            text = "%" + text + "%";
        }
        final String finalText = text;

        return (Specification<T>) (root, cq, builder) ->
                builder.or(root.getModel()
                        .getDeclaredSingularAttributes()
                        .stream()
                        .filter(a -> a.getJavaType()
                                .getSimpleName().equalsIgnoreCase("string"))
                        .map(a -> builder.like(root.get(a.getName()), finalText)
                        ).toArray(Predicate[]::new)
                );
    }
}

Now, in your service class, for example in your UserService class if you want to achieve something like users list along with search, sort, pagination etc, then use this only

Pageable paging;
    if (paginationRequest.getSortOrder().matches("ASC")) {
        paging = PageRequest.of(paginationRequest.getPageNo(),
                paginationRequest.getPageSize(), Sort.by(
                        paginationRequest.getSortBy()).ascending());
    } else {
        paging = PageRequest.of(paginationRequest.getPageNo(),
                paginationRequest.getPageSize(), Sort.by(paginationRequest.getSortBy()).descending());
    }

    List<User> userList = userRepository.findAll(
            EntitySpecification.textInAllColumns(paginationRequest.getSearch())
            , paging).getContent();

Now don't get confused here,

PaginationRequest is request POJO class with getters and setters having following initially,

Integer pageNo = 0;
Integer pageSize = 10;
String sortBy = "createdTimeStamp";
String sortOrder;
String search = "";

Answer 3

Combining previous two answers: if you don't want to couple your API and your database schema or in other words you don't want the user to provide a string column name - you can filter out those attributes that are not strings and apply like to all those that are. In the following example it will try to search text in values of columns: name,field1, field2 and field3.

Entity Example:

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public int id;
    public String name;
    public String field2;
    public String field3;
    public String field4;
}

Specification Example:

public class EntitySpecification {

    public static Specification<MyEntity> textInAllColumns(String text) {

        if (!text.contains("%")) {
            text = "%"+text+"%";
        }
        final String finalText = text;

        return new Specification<MyEntity>() {
            @Override
            public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> cq, CriteriaBuilder builder) {
                return builder.or(root.getModel().getDeclaredSingularAttributes().stream().filter(a-> {
                    if (a.getJavaType().getSimpleName().equalsIgnoreCase("string")) {
                        return true;
                    }
                    else {
                        return false;
                }}).map(a -> builder.like(root.get(a.getName()), finalText)
                    ).toArray(Predicate[]::new)
                );
            }
        };
    }

 }

Repository Example:

public interface MyEntityRepository extends PagingAndSortingRepository<MyEntity, Integer> {
    List<MyEntity> findAll(Specification<MyEntity> spec);
}

Usage example:

List<MyEntity> res = failureRepository.findAll(Specifications.where(FailureSpecification.textInAllColumns(text)));

---

another update (search in all types of columns with white-listing of fields with lambdas - code is not checked)

public class EmployeeSpecification {
    public static Specification<Employee> textInAllColumns(String text, Set<String> fields) {
        if (!text.contains("%")) {
            text = "%" + text + "%";
        }
        final String finalText = text;

        return  (Specification<Employee>) (root, query, builder) -> 
                builder.or(root.getModel().getDeclaredSingularAttributes().stream().filter(a -> {
                return fields.contains(a.getName());
            }).map(a -> builder.like(root.get(a.getName()), finalText)).toArray(Predicate[]::new));
    }
} 

Answer 4

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

Answer 5

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")))