How to do a left outer join using criteria query on unassociated entities?-CodePudding

StringBuffer buffer = new StringBuffer();
buffer.setLength(0);
buffer.append("select phone.name,phone.id,object.name,object.id  from Phone as phone");
if(isA){
  buffer.append("left outer join A as object where object.id=phone.id")
}
else{
  buffer.append("left outer join B as object where object.id=phone.id")
}

I have an SQL command like this in an old code. I have to covert it into JPA.

If I do not have any relation between Phone and A and B, how can this be implemented using criteria query?

CodePudding user response：

If Phone is an entity managed by JPA but not A and B, you could use a native query instead of the Criteria API. This way you can take the SQL query from your old code as-is and use it with JPA.

String nativeSql = buffer.toString();
List<Phone> phones = entityManager.createNativeQuery(nativeSql, Phone.class).getResultList();

CodePudding user response：

That's not possible with JPA Criteria unfortunately because there is no API for this yet. In Hibernate 6.0 there will be an extension though that supports this.

For now, you can either continue concatenating JPQL fragments like you seem to do, or use a query builder like Blaze-Persistence which supports this:

CriteriaBuilder<Tuple> cb = criteriaBuilderFactory.create(entityManager, Tuple.class);
cb.from(Phone.class, "p");
cb.select("p.name").select("p.id").select("o.name").select("o.id");
if(isA) {
  cb.leftJoin(A.class, "o").on("o.id").eqExpression("p.id").end();
}
else{
  cb.leftJoin(B.class, "o").on("o.id").eqExpression("p.id").end();
}
List<Tuple> result = cb.getResultList();