Criteria JPA 2 with 3 tables
Each join takes you from the leftish type parameter to the rightish one. So, the details
join of my code (second line) starts from fromUpdates
, that is a Path<Update>
, and creates something which is behind the scenes also a Path<Detail>
. From that, you can build other joins. Try this (code not tested):
Root<Update> fromUpdates = query.from(Update.class);
Join<Update, Detail> details = fromUpdates.join("details");
Join<Detail, Associate> associate = details.join("associate");
List<Predicate> conditions = new ArrayList();
conditions.add(builder.equal(associate.get("associateId"), associateId));
conditions.add(builder.isNull(details.get("ack_date")));
TypedQuery<Update> typedQuery = em.createQuery(query
.select(fromUpdates)
.where(conditions.toArray(new Predicate[] {}))
.orderBy(builder.asc(fromUpdates.get("updateId")))
.distinct(true)
);
For three tables involved.
CriteriaBuilder builder = theEntityManager.getCriteriaBuilder(); CriteriaQuery query1 = builder.createQuery(BasicMemberInfo.class);
Root<Table1> table1 = query1.from(Table1.class);
Root<Table2> table2 = query1.from(Table2.class);
Root<Table3> table3 = query1.from(Table3.class);
List<Predicate> conditions = new ArrayList();
conditions.add(builder.equal(table3.get("Table1").get("memberId"), table1.get("memberId")));
conditions.add(builder.equal(table2.get("tableid").get("memberId"), table1.get("memberId")));
conditions.add(builder.equal(table2.get("indicator"), 'Y'));
conditions.add(builder.equal(table3.get("StatusCd"), "YES"));
TypedQuery<BasicCustInfo> typedQuery = theEntityManager.createQuery(
query1.multiselect(table1.get("memberId"), table2.get("AcctId"))
.where(conditions.toArray(new Predicate[] {}))
);
List<BasicMemberInfo> custList = typedQuery.getResultList();
public class BasicMemberInfo {
String memberId;
String AcctId;
public BasicCustInfo() {
// TODO Auto-generated constructor stub
}
public BasicMemberInfo( BigDecimal memberId,String AcctId ) {
this.memberId = memberId;
this.AcctId = AcctId;
}
public BigDecimal getmemberId() {
return memberId;
}
public void setmemberId(BigDecimal memberId) {
memberId = memberId;
}
public String getAcctId() {
return AcctId;
}
public void setAcctId(String AcctId) {
AcctId = AcctId;
}
}