时间戳

首页 > 解决方案 > Spring JPA CriteriaBuilder，连接表的where子句

spring - Spring JPA CriteriaBuilder，连接表的where子句

问题描述

如何在连接表上创建具有多个 where 子句的查询？

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<A> cq = cb.createQuery(A.class);
Root<A> a = cq.from(A.class);
ListJoin<A, B> b = a.joinList("bs");
cq.select(a).where(cb.equal(b.get(B_.usDotNumber), 3), 
cb.equal(a.get(A_.accountNumber), 1));
List<A> as = em.createQuery(cq).getResultList();

我得到的错误是：

java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: accountNumber of: com.foo.B [select generatedAlias0 from com.foo.A as generatedAlias0 inner join generatedAlias0.bs as generatedAlias0 where ( generatedAlias0.accountNumber=1 ) and ( generatedAlias0.usDotNumber=3 )]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133)

...

生成的查询：

select generatedAlias0 from com.foo.A as generatedAlias0 inner join generatedAlias0.bs as generatedAlias0 where ( generatedAlias0.accountNumber=1 ) and ( generatedAlias0.usDotNumber=3 );

为什么两个表都使用“generatedAlias0”，我该如何解决这个问题？查询应该是：

select generatedAlias0 from com.foo.A as generatedAlias0 inner join generatedAlias0.bs as generatedAlias1 where ( generatedAlias0.accountNumber=1 ) and ( generatedAlias1.usDotNumber=3 );

编辑：根据要求添加实体关系。删除了所有无关的东西。

@Entity
public class A {
    @Column(name = "FOO")
    private String accountNumber;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "anId", referencedColumnName = "anotherId", updatable = false)
    @LazyCollection(LazyCollectionOption.FALSE)
    private final List<B> bs = Collections.synchronizedList(new ArrayList<>());
}

@Entity
public class B {
    @Column(name = "BAR")
    private Integer usDotNumber;
}

非常感谢！

标签： springjpa

解决方案

This query worked after removing the select(a). No, I don't understand it, but maybe it will help someone else.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<A> cq = cb.createQuery(A.class);
Root<A> a = cq.from(A.class);
ListJoin<A, B> b = a.joinList("bs");
cq.where(cb.equal(b.get(B_.usDotNumber), 3), cb.equal(a.get(A_.accountNumber), 1));
List<A> as = em.createQuery(cq).getResultList();

推荐阅读