如何使用JPA标准API过滤postgres数组列？ [英] How to filter postgres array column with the JPA criteria API?

问题描述

我使用的是：

• Hibernate 4.3.5


• Spring JPA 1.6.0


• Javax Persistence API 2.1
  
  
  refcodemailing列定义为一个int数组： int []
  
  
  

  我的实体对象：
  
  

    @Entity 
   @Table 
   public class CalendarEvent implements Serializable {
   
   @Id 
   @GeneratedValue（strategy = GenerationType.AUTO）
   private int id = 0; 
   
   @Convert（converter = IntegerArrayConverter.class）
   @Column（name =refcodemailing）
   private final List< Integer> mailingCodes = new ArrayList<>（）; 
   
   ... .... 
   
  } 
    

  我试图用以下JPA规范方法过滤列数组：
  
  

    private final List< MailingCode> mailingCodes = new ArrayList<>（）; 
   
   @Override 
   public Predicate toPredicate（Root< CalendarEvent> root，CriteriaQuery<？>查询，CriteriaBuilder cb）{
   
   //邮件代码
   if（！mailingCodes.isEmpty（））{
   List< Predicate> mailingCodePred = new ArrayList<>（）; 
   
  （MailingCode mailingCode：mailingCodes）{
   restrictions.add（cb.isMember（mailingCode.getId（），root。< List< Integer>> get（mailingCodes） ））; 
   
   
   restrictions.add（cb.and（cb.isNotNull（root。< List< Integer>> get（mailingCodes）），cb.or（mailingCodePred.toArray （new Predicate [] {}））））; 
   
   
   $ / code> 

  但引发以下异常：

    java.lang.IllegalArgumentException：未知集合表达式类型[org.hibernate.jpa.criteria.path.SingularAttributePath] 
   at org。 hibernate.jpa.criteria.CriteriaBuilderImpl.isMember（CriteriaBuilderImpl.java:1332）
   at com.agenda.CalendarEventQuery.toPredicate（CalendarEventQuery.java:100）
   at org.springframework.data.jpa.repository。 support.SimpleJpaRepository.applySpecificationToCriteria（SimpleJpaRepository.java:521）
   at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery（SimpleJpaRepository.java:472）
    
   
  有没有办法做到这一点？ 
   
  解决方案
  根据JPA 2.0规范：
   
   
   
   
  在
  集合成员表达式中不支持表达式来评估嵌入类型。支持在
  集合成员表达式中使用可嵌入的元素可能会添加到此
  规范的未来版本中。
   
   
  然而，我建立了在GitHub上使用Hibernate的一个工作示例 
   
   
   
  假设我们有这个 CalendarEvent 实体和 MailingCode  DTO对象： 
   
   
    @Entity（name =CalendarEvent）
   @Table 
   public static class CalendarEvent implements Serializable {
   
   @Id 
   @GeneratedValue（strategy = GenerationType.AUTO） 
  私人整数ID; 
   
   @ElementCollection 
   private final List< Integer> mailingCodes = new ArrayList<>（）; 
   
  } 
   
   public static class MailingCode {
   private Integer id; 
   
   public MailingCode（Integer id）{
   this.id = id; 
  } 
   
   public Integer getId（）{
   return id; 
   
   
   
   
   $ b $您可以编写Criteria API代码，如下所示： 
   
   
   
   $ b $ / p> 
   
   
    CriteriaBuilder builder = entityManager.getCriteriaBuilder（）; 
   CriteriaQuery< CalendarEvent> criteria = builder.createQuery（CalendarEvent.class）; 
   Root< CalendarEvent> root = criteria.from（CalendarEvent.class）; 
   
  列表< MailingCode> mailCodes = Arrays.asList（
  新MailingCode（1），
  新MailingCode（2），
  新MailingCode（3）
  ）; 
   
  表达式< List< Integer>> mailingCodesPath = root.get（mailingCodes）; 
   
  谓词谓词= builder.conjunction（）; （MailingCode mailingCode：mailingCodes）{
   predicate = builder.and（predicate，builder.isMember（mailingCode.getId（），mailingCodesPath））; 
   
   
  } 
   
   criteria.where（predicate）; 
  列表< CalendarEvent> events = entityManager.createQuery（criteria）.getResultList（）; 
    
  它的作用： 
   
   
    SELECT criteriaap0_.id AS id1_0_ 
   FROM CalendarEvent criteriaap0_ 
   WHERE 1 = 1 
   AND（1 IN（选择mailingcod1_.mailingCodes 
   FROM CalendarEvent_mailingCodes mailingcod1_ 
   WHERE criteriaap0_.id = mailingcod1_.CalendarEvent_id））
   AND（2 IN（SELECT mailingcod2_.mailingCodes 
   FROM CalendarEvent_mailingCodes mailingcod2_ 
   WHERE criteriaap0_.id = mailingcod2_.CalendarEvent_id））
   AND（3 IN（SELECT mailingcod3_.mailingCodes 
   FROM CalendarEvent_mailingCodes mailingcod3_ 
   WHERE criteriaap0_.id = mailingcod3_.CalendarEvent_id））
    
  但是，IN查询是一个更好的选择，因为上面的SQL查询是不理想的。
   
  I am using:
  
  
  Hibernate 4.3.5
  Spring JPA 1.6.0
  Javax Persistence API 2.1
  
  
  The "refcodemailing" column is defined as an array of int: int[]
  
  My entity object:
  @Entity
  @Table
  public class CalendarEvent implements Serializable {
  
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id = 0;
  
    @Convert(converter = IntegerArrayConverter.class)
    @Column(name = "refcodemailing")
    private final List<Integer> mailingCodes = new ArrayList<>();
  
    // ....
  
  }
  I am trying to filter the column array with the following JPA Specification method:
  private final List<MailingCode> mailingCodes = new ArrayList<>();
  
  @Override
  public Predicate toPredicate(Root<CalendarEvent> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
  
    // Mailing codes
    if(!mailingCodes.isEmpty()){
      List<Predicate> mailingCodePred = new ArrayList<>();
  
      for(MailingCode mailingCode: mailingCodes){
        restrictions.add(cb.isMember(mailingCode.getId(), root.<List<Integer>>get("mailingCodes")));
      }
  
      restrictions.add(cb.and(cb.isNotNull(root.<List<Integer>>get("mailingCodes")),       cb.or(mailingCodePred.toArray(new Predicate[]{}))));
    }
  }
  But the following exception is thrown:
  java.lang.IllegalArgumentException: unknown collection expression type [org.hibernate.jpa.criteria.path.SingularAttributePath]
      at org.hibernate.jpa.criteria.CriteriaBuilderImpl.isMember(CriteriaBuilderImpl.java:1332)
      at com.agenda.CalendarEventQuery.toPredicate(CalendarEventQuery.java:100)
      at org.springframework.data.jpa.repository.support.SimpleJpaRepository.applySpecificationToCriteria(SimpleJpaRepository.java:521)
      at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:472)
  Is there a way to do it?
  解决方案
  According to JPA 2.0 specs:
  
    
  Expressions that evaluate to embeddable types are not supported in
    collection member expressions. Support for use of embeddables in
    collection member expressions may be added in a future release of this
    specification.
  However, I built a working example on GitHub using Hibernate.
  
  Assuming we have this CalendarEvent entity and the MailingCode DTO object:
  @Entity(name = "CalendarEvent")
  @Table
  public static class CalendarEvent implements Serializable {
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Integer id;
  
      @ElementCollection
      private final List<Integer> mailingCodes = new ArrayList<>();
  
  }
  
  public static class MailingCode {
      private Integer id;
  
      public MailingCode(Integer id) {
          this.id = id;
      }
  
      public Integer getId() {
          return id;
      }
  }
  You can write the Criteria API code as follows:
  CriteriaBuilder builder = entityManager.getCriteriaBuilder();
  CriteriaQuery<CalendarEvent> criteria = builder.createQuery(CalendarEvent.class);
  Root<CalendarEvent> root = criteria.from(CalendarEvent.class);
  
  List<MailingCode> mailingCodes = Arrays.asList(
      new MailingCode(1),
      new MailingCode(2),
      new MailingCode(3)
  );
  
  Expression<List<Integer>> mailingCodesPath = root.get("mailingCodes");
  
  Predicate predicate = builder.conjunction();
  
  for(MailingCode mailingCode: mailingCodes){
      predicate = builder.and(predicate, builder.isMember(mailingCode.getId(), mailingCodesPath));
  }
  
  criteria.where(predicate);
  List<CalendarEvent> events = entityManager.createQuery(criteria).getResultList();
  And it works:
  SELECT  criteriaap0_.id AS id1_0_
  FROM    CalendarEvent criteriaap0_
  WHERE   1 = 1
          AND ( 1 IN ( SELECT mailingcod1_.mailingCodes
                       FROM   CalendarEvent_mailingCodes mailingcod1_
                       WHERE  criteriaap0_.id = mailingcod1_.CalendarEvent_id ) )
          AND ( 2 IN ( SELECT mailingcod2_.mailingCodes
                       FROM   CalendarEvent_mailingCodes mailingcod2_
                       WHERE  criteriaap0_.id = mailingcod2_.CalendarEvent_id ) )
          AND ( 3 IN ( SELECT mailingcod3_.mailingCodes
                       FROM   CalendarEvent_mailingCodes mailingcod3_
                       WHERE  criteriaap0_.id = mailingcod3_.CalendarEvent_id ) )
  However, an IN query is a much better choice since the SQL query above is suboptimal.
  
                          
  这篇关于如何使用JPA标准API过滤postgres数组列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！