Java Spring JPA Repository 动态查询

Spring JPA Repository dynamic query

提问by Channa

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

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

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

回答by beerbajay

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

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

Create an interface with the method:

使用该方法创建一个接口：

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

Create an implementation:

创建一个实现：

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

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

Extend the new interface in your existing Repository interface:

在现有的 Repository 界面中扩展新界面：

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(","))));

Query implementation

查询实现

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

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

The wordwhich 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 baras an identifier and you'll get an exception.

将word你所串联必须是一个有效的JPQL标识符，即一个:接着一个标识启动Java，后面可以跟一些Java标识符的一部分。这意味着，如果您的 CSV 包含foo bar,baz，您将尝试foo bar用作标识符并且您将收到异常。

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

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

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

回答by Barium Scoorge

I've been looking for the solution myself : The naming of the "Custom" repository interface and implentation is very strict (as said there How to add custom method to Spring Data JPA)

我自己一直在寻找解决方案：“自定义”存储库接口和实现的命名非常严格（正如那里所说的如何向 Spring Data JPA 添加自定义方法）

So, to be clear, the whole code : (But @beerbajay was right)

所以，要清楚的是，整个代码：（但@beerbajay 是对的）

The custom method interface

自定义方法接口

public interface MyEntityRepositoryCustom {
    List<MyEntity> findSpecial();
}

The custom method implementation

自定义方法实现

public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom {
    @PersistenceContext
    private EntityManager em;

    //custom method implementation
    public List<Object> findSpecial() {
        List<Object> list = em.createNativeQuery("select name, value from T_MY_ENTITY").getResultList();
        return list;
    }
}

The "original" repository

“原始”存储库

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity,Long>, MyEntityRepositoryCustom {
    //original methods here... do not redefine findSpecial()...
}

You can now use the "original" repository with the new custom methods

您现在可以将“原始”存储库与新的自定义方法一起使用

@Service
public class MyService {
    @Autowired
    private DataRepository r;

    public void doStuff() {
        List<Object> list = r.findSpecial();
    }
}

回答by Tom Elias

Spring Data JPA has a way to create Custom and Dynamic queries with "Specifications": Spring Data - Specifications

Spring Data JPA 有一种使用“规范”创建自定义和动态查询的方法： Spring Data - Specifications

First, your interface which extends JPARepositoryor CRUDRepositoryshould also implement JpaSpecificationExecutor<...>and that's all you need. Your repository now has a new method findAllwhich accepts a Specification<...>object, and your can use the method Beerbajay used to create Criteria Queries by overriding the method toPredicate(...)and there you are free to build (almost) any query you want like so:

首先，您的接口扩展JPARepository或CRUDRepository也应该实现JpaSpecificationExecutor<...>，这就是您所需要的。您的存储库现在有一个findAll接受Specification<...>对象的新方法，您可以使用 Beerbajay 用于通过覆盖该方法来创建 Criteria Queries 的方法，toPredicate(...)并且您可以自由地构建（几乎）任何您想要的查询，如下所示：

Specification<...> spec = new Specification<...>() {
    @Override
    public Predicate toPredicate(Root<...> entity, CriteriaQuery<?> query, CriteriaBuilder cb) {       
        List<Predicate> conditions = buildManyPredicates(cb, entity);
        return cb.and(conditions.toArray(new Predicate[conditions.size()]));
    }
 };

repository.findAll(spec, PageRequest.of(0, 10));

This solves the problem of Spring Data trying to parse the methods you added in the custom interface (because there is no custom interface)

这就解决了Spring Data试图解析你在自定义接口中添加的方法的问题（因为没有自定义接口）