Java Spring Data - 多列搜索

Spring Data - Multi-column searches

提问by jplandrain

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

我正在使用 Spring Data 进行分页和排序。但是，我想执行多列搜索。

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

现在，我在我的存储库界面中使用@Query注释，如下所示：

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

}

edit: 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(clarification of the question after the comment of Brandon Oakley)

编辑：此解决方案中的问题出在 @Query 注释的 where 子句中，因为我们必须为要搜索的每一列重复完全相同的 searchtext 参数（在 Brandon Oakley 评论后澄清问题）

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.

谢谢你的帮助。

采纳答案by a better oliver

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

回答by Mykhailo Lytvyn

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

回答by Michail Michailidis

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 liketo all those that are. In the following example it will try to search textin values of columns: name,field1, field2and field3.

结合前面的两个答案：如果你不想你的夫妇API和数据库架构或者换句话说，你不希望用户提供一个字符串列名-你可以过滤掉那些不是字符串属性和适用like于所有那些。在下面的例子中，它会尝试搜索text列的值：name，field1，field2和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)

另一个更新（在所有类型的列中搜索带有 lambdas 的字段白名单 - 不检查代码）

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