Spring Data - Multi-column searches

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

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

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