java - JPA(2.0) 标准简单连接

Question

我在为以下 sql 创建 criteriaQuery 时遇到问题。任何帮助将不胜感激。假设我有两个表“成员”和“人员”。我加入姓名和年龄，并为这两个表设置 where 子句。我使用的是OpenJPA(2.0)

select * 
from Member
join Person
on Member.name = Person.name 
    and Member.age = Person.age
where Member.name = 'someOne'
and Member.age = '24'
and Person.gender = 'F'
and Person.type = 'employee' 

Answer 1

像这样怎么样:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Member> fromMember = criteria.from(Member.class);
Root<Person> fromPerson = criteria.from(Person.class);
criteria.multiselect(fromMember, fromPerson);
List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(fromMember.get(Member_.name), fromPerson.get(Person_.name)));
predicates.add(builder.equal(fromMember.get(Member_.age), fromPerson.get(Person_.age)));
predicates.add(builder.equal(fromMember.get(Member_.name), "someOne"));
predicates.add(builder.equal(fromMember.get(Member_.age), 24));
predicates.add(builder.equal(fromPerson.get(Person_.gender), "F"));
predicates.add(builder.equal(fromPerson.get(Person_.type), "employee"));
criteria.where(predicates.toArray(new Predicate[predicates.size()]));
List<Tuple> result = em.createQuery(criteria).getResultList();

这将返回一个由 Member 和 Person 组成的 2 元素元组。如果您希望每个元组中都有单独的字段，则可以在多选调用中枚举各个字段。