Spring Boot JPA:为同一参数(JPQL)传递多个值 [英] Spring Boot JPA: Passing Multiple Values for the Same Parameter (JPQL)

问题描述

我正在使用JPQL在JPA的CurdRepository接口中编写SQL查询.我能够用命名参数编写多个查询，并且它们就像一个魅力一样工作.

I'm using JPQL to write SQL queries in JPA's CurdRepository interface. I was able to write more than one query with named parameters and they work like a charm.

但是，我的应用程序要求我将未知数目的字符串与一个表中的多个列进行匹配.据我所知，在SQL中看起来像这样:

However, my application requires that I match an unknown number of strings with more than one column in a table. To my knowledge, in SQL it'd look something like this:

SELECT * FROM TABLE WHERE COLUMN_1 LIKE "%string_1%" OR COLUMN_1 LIKE"%string_2%" ... OR COLUMN_2 LIKE "%string_1%" OR COLUMN_2 LIKE "%string_2%" ... ...

我在MySQL中试过了，效果很好.但是，我不知道如何在JPQL中进行描述.通过将命名参数设置为List，我能够为IN子句传递多个值，如下所示:

I tried this in MySQL and it works fine. I couldn't figure out how to depict this in JPQL, however. I was able to pass multiple values for the IN clause by setting the named parameter to a List, as follows:

 @Query("SELECT t FROM Table t WHERE TABLE_ID IN (:table_ids)")
public Page<Table> findByParameters(@Param("table_ids") List<Integer> table_ids, Pageable page)

我尝试对LIKE子句执行相同的操作，但是如果传递的值大于1，最终结果为0.我也遇到LOCATE子句的相同问题.

I tried doing the same thing with the LIKE clause but I end up with 0 results if I pass more than 1 value. I face the same problem with the LOCATE clause as well.

在最坏的情况下，我可以设置一个对一个String起作用的查询，并多次调用它，然后合并所有结果.但是，我宁愿避免这种情况，因为它只不过是一条胶带式解决方案.

Worst case scenario, I can set up one query that works on one String, and call it several times and then merge all the results. I'd very much rather avoid this however, as it'd be nothing more than a duct-tape solution.

我在网上寻找解决方案已有几个小时，但没有找到任何解决我的问题的方法.任何提示都将受到高度赞赏.

I looked online for solutions for hours but haven't found anything that addresses my concern. Any tip is highly appreciated.

谢谢.

推荐答案

因为@Query需要在使用JPQL的编译时进行修复，否则甚至本机查询都将使此类事情难以实现，尤其是以一种类型安全的方式.

Because @Query needs to be fixed at compile time using JPQL or even native query will make this kind of things hard to implement especially in a type safe way.

所以我意识到您正在追求JPQL解决方案，但这是学习和利用Specification界面和JPA的CriteriaQuery的绝佳机会.就是这样.

So I realize you are after JPQL solution but this is an awesome opportunity to learn and take advantage of Specification interface and JPA's CriteriaQuery. This is exactly it is for.

看看以下存储库:

public interface Table1Repository            // to use specifications in queries 
        extends JpaRepository<Table1, Long>, JpaSpecificationExecutor<Table1> {

    @SuppressWarnings("serial")
    public static Specification<Table1> multiLikeColumn1(List<String> likePatterns) {
        return new Specification<Table1>() {
            @Override
            public Predicate toPredicate(Root<Table1> root, CriteriaQuery<?> query,
                                            CriteriaBuilder criteriaBuilder) {
                Path<String> column1 = root.get("column1");
                // create a Predicate for each "column1 like 'xy%az%' you need
                List<Predicate> predicates = likePatterns.stream()
                    .map(likePattern -> criteriaBuilder.like(column1, likePattern))
                    .collect(Collectors.toList());
                // then "concatenate" list of likes with "OR"
                return criteriaBuilder.or(predicates.toArray(new Predicate[]{}));
            }
        };
    }

}

它可能看起来有点复杂，但实际上当您熟悉它时就不是.用法很简单，例如:

It might look a bit complex but actually it is not when you get familiar with it. Usage is simple, like:

@Resource
private Table1Repository repo;
repo.findAll(Table1Repository.multiLikeColumn1(Arrays.asList("%X%","%Z%")))

这篇关于Spring Boot JPA:为同一参数(JPQL)传递多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！