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.

我需要构建动态查询，

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

推荐答案

按JB Nizet和< a href =http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behaviour =nofollow noreferrer> spring-data documentation ，你应该使用自定义接口+存储库实现。

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

使用方法创建一个接口：

Create an interface with the method:

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.

您连接的字必须是< a href =http://openjpa.apache.org/builds/1.2.3/apache-openjpa/docs/jpa_langref.html#jpa_langref_named_pa​​rams =nofollow noreferrer>有效的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屋！