Spring JPA Repository 动态查询 [英] Spring JPA Repository dynamic query

问题描述

目前我一直在使用以下 Spring JPA Repository 基础自定义查询，它工作正常，

Currently I have been using following Spring JPA Repository base custom query and it works fine,

 @Query("SELECT usr FROM User usr  WHERE usr.configurable = TRUE "
              + "AND (" +
                        "lower(usr.name) like lower(:filterText) OR lower(usr.userType.classType.displayName) like lower(:filterText) OR lower(usr.userType.model) like lower(:filterText)"
              +      ")"
              + "")
  public List<User> findByFilterText(@Param("filterText") String filterText, Sort sort);

当过滤文本将是逗号分隔值时，我需要修改此查询.但按照以下方式，它将是一个动态查询，我该如何执行它.

I need to modify this query when filter text going to be a comma separated value. But as following manner it will be a dynamic query and how can I execute it.

我需要构建的动态查询，

Dynamic query I need to build,

String sql = "SELECT usr FROM User usr WHERE usr.configurable = TRUE";

for(String word : filterText.split(",")) {
                sql += " AND (lower(usr.name) like lower(:" + word + ") OR lower(usr.userType.classType.displayName) like lower(:" + word + ") OR lower(usr.userType.model) like lower(:" + word + "))";
}

推荐答案

Per JB Nizet 和 spring-data 文档，您应该使用自定义接口 + 存储库实现.

Per JB Nizet and the spring-data documentation, you should use a custom interface + repository implementation.

使用方法创建接口:

public interface MyEntityRepositoryCustom {
    List<User> findByFilterText(Set<String> words);
}

创建一个实现:

@Repository
public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    public List<User> findByFilterText(Set<String> words) {
        // implementation below
    }
}

在您现有的 Repository 界面中扩展新界面:

Extend the new interface in your existing Repository interface:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyEntityRepositoryCustom {
    // other query methods
}

最后，在别处调用该方法:

Finally, call the method somewhere else:

dao.findByFilterText(new HashSet<String>(Arrays.asList(filterText.split(","))));

查询实现

您生成 sql 变量的方法，即将一些字符串连接到查询中是不好的.不要这样做.

Your method of producing the sql variable, namely by concatenating some strings into the query is bad. Do not do this.

您连接的 word 必须是 有效的 JPQL 标识符，即一个 : 后跟一个 java 标识符开始，可选地后跟一些 java 标识符部分.这意味着如果您的 CSV 包含 foo bar,baz，您将尝试使用 foo bar 作为标识符，并且您将收到异常.

The word which you are concatenating must be a valid JPQL identifier, namely a : followed by a java identifier start, optionally followed by some java identifier part. This means that if your CSV contains foo bar,baz, you will attempt to use foo bar as an identifier and you'll get an exception.

您可以改为使用 CriteriaBuilder以安全的方式构造查询:

You can instead use CriteriaBuilder to construct the query in a safe way:

public List<User> findByFilterText(Set<String> words) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> q = cb.createQuery(User.class);
    Root<User> user = q.from(User.class);

    Path<String> namePath = user.get("name");
    Path<String> userTypeClassTypeDisplayName = 
                     user.get("userType").get("classType").get("displayName");
    Path<String> userTypeModel = user.get("userType").get("model");
    List<Predicate> predicates = new ArrayList<>();
    for(String word : words) {
        Expression<String> wordLiteral = cb.literal(word);
        predicates.add(
                cb.or(
                    cb.like(cb.lower(namePath), cb.lower(wordLiteral)),
                    cb.like(cb.lower(userTypeClassTypeDisplayName),
                            cb.lower(wordLiteral)),
                    cb.like(cb.lower(userTypeModel), cb.lower(wordLiteral))
                )
        );
    }
    q.select(doc).where(
            cb.and(predicates.toArray(new Predicate[predicates.size()]))
    );

    return entityManager.createQuery(q).getResultList();
}

这篇关于Spring JPA Repository 动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！