如何使用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 theMailingCode
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屋!