如何使用谓词在spring数据jpa @Query中检查集合是否为空 [英] How to check collection for null in spring data jpa @Query with in predicate

问题描述

我的spring数据jpa存储库中有以下查询:

  @Query(" SELECT table1 FROM Table1 table1"+" INNER JOIN FETCH table1.error错误"+" WHERE table1.date =?1"+" AND(COALESCE(?2)为NULL或(table1.code IN?2)).+" AND(COALESCE(?3)为NULL或(error.errorCode IN?3)).)列表<表1>findByFilter(日期，List< String>代码，List< String> errorCodes); 

当我运行此查询时，它通过控制台向我显示此错误:

  org.postgresql.util.PSQLException:错误:不存在运算符:字符变化= bytea提示:没有运算符匹配给定的名称和参数类型.您可能需要添加显式类型转换.位置:1642 

但是，如果我运行查询时没有(COALESCE(?2)IS NULL OR 部分，只是 table1.code IN?2 ，它确实可以工作

有人知道这个错误可能是由于什么造成的吗?

解决方案

具有一个参数的

1. COALESCE 没有任何意义.这是一个缩写的CASE表达式，它返回第一个非空操作数.(请参见此)

2. 我建议您使用

3. 我建议您也避免使用过时的 Date ，而改用java 8 Date/Time API.

因此，考虑到上述所有因素，您应该使用动态查询，因为@SimonMartinelli的注释中也建议使用动态查询.特别是，您可以查看规范.

假设您具有以下映射:

  @Entity公共类错误{@ID私人Long ID；私有字符串errorCode;//...}@实体公共课表1{@ID私人Long ID；私有LocalDateTime日期；私有字符串代码；@多多私人错误错误；//...} 

您可以编写以下规范:

  import javax.persistence.criteria.JoinType;导入javax.persistence.criteria.Predicate;导入org.springframework.data.jpa.domain.Specification;导入org.springframework.util.CollectionUtils;公共类TableSpecs{公共静态规范<表1>findByFilter(LocalDateTime日期，List< String>代码，List< String> errorCodes){返回(根，查询，构建器)->{root.fetch(错误"，JoinType.LEFT)；谓词结果= builder.equal(root.get("date")，date);如果(！CollectionUtils.isEmpty(codes)){结果＝ builder.and(结果，root.get(代码").in(代码)))；}如果(！CollectionUtils.isEmpty(errorCodes)){结果＝ builder.and(结果，root.get(错误").get(错误代码").in(错误代码))；}返回结果；};}}公共接口TableRepository扩展了CrudRepository< Table1，Long>，JpaSpecificationExecutor< Table1>.{默认List< Table1>findByFilter(LocalDateTime日期，List< String>代码，List< String> errorCodes){返回findAll(TableSpecs.findByFilter(date，codes，errorCodes));}} 

然后使用它:

  List< Table1>结果= tableRepository.findByFilter(date，Arrays.asList("TBL1")，Arrays.asList("ERCODE2"))))； 

I have this query in my spring data jpa repository:

@Query("SELECT table1 FROM Table1 table1 "
   + "INNER JOIN FETCH table1.error error"
   + "WHERE table1.date = ?1 "
   + "AND (COALESCE(?2) IS NULL OR (table1.code IN ?2)) "
   + "AND (COALESCE(?3) IS NULL OR (error.errorCode IN ?3)) ")
List<Table1> findByFilter(Date date, List<String> codes, List<String> errorCodes);

When I run this query, it shows me this error by console:

    org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
      Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
      Position: 1642

However if I run the query without the (COALESCE (?2) IS NULL OR part, just the table1.code IN ?2, it does work

Does anyone know what this error could be due to?

解决方案

1. COALESCE with one parameter does not make sense. This is an abbreviated CASE expression that returns the first non-null operand. (See this)

2. I would suggest you to use named parameters instead of position-based parameters. As it's stated in the documentation this makes query methods a little error-prone when refactoring regarding the parameter position.

3. As it's stated in documentation related to the IN predicate:

The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter, the list of values must not be empty; it must contain at least one value.

4. I would suggest you also avoid to use outdated Date and use instead java 8 Date/Time API.

So, taken into account all above, you should use a dynamic query as it was suggested also in comments by @SimonMartinelli. Particularly you can have a look at the specifications.

Assuming that you have the following mapping:

@Entity
public class Error
{
   @Id
   private Long id;
   private String errorCode;

   // ...
}

@Entity
public class Table1
{
   @Id
   private Long id;
   private LocalDateTime date;
   private String code;

   @ManyToOne
   private Error error;

   // ...
}

you can write the following specification:

import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.CollectionUtils;

public class TableSpecs
{

   public static Specification<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return (root, query, builder) -> {
         root.fetch("error", JoinType.LEFT);
         Predicate result = builder.equal(root.get("date"), date);
         
         if (!CollectionUtils.isEmpty(codes)) {
            result = builder.and(result, root.get("code").in(codes));
         }
         if (!CollectionUtils.isEmpty(errorCodes)) {
            result = builder.and(result, root.get("error").get("errorCode").in(errorCodes));
         }
         return result;
      };
   }
}

public interface TableRepository extends CrudRepository<Table1, Long>, JpaSpecificationExecutor<Table1>
{
   default List<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return findAll(TableSpecs.findByFilter(date, codes, errorCodes));
   }
}

and then use it:

List<Table1> results = tableRepository.findByFilter(date, Arrays.asList("TBL1"), Arrays.asList("ERCODE2")));

这篇关于如何使用谓词在spring数据jpa @Query中检查集合是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！