3

I want to write this query using Hibernate Criteria language. I am pretty new to Hibernate and not able to convert this query into Criteria form. I referred lots of answers available on SO but in my case I am using inner join on different columns rather than primary key/ foreign key column. I referred this but still can't make it right.

select TableA.columnA1, TableA.columnA2, TableA.columnA3, TableB.columnB1, TableC.columnC2 from TableA inner join  TableB 
on 
cast(TableA.columnA3 as Integer) = TableB.columnB2
inner join
TableC
on 
TableB.columnB3 = TableC.columnC1
Community
  • 1
  • 1
work_in_progress
  • 747
  • 1
  • 10
  • 27

1 Answers1

3

To handle the joining logic, you are going to want to use from for each of the tables and include all of your conditions from the on-clauses in the where predicate.

Here is a JPA example that handles a parent-child relationship without having a foreign-key relationship:

EntityManager em = getDb().getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Child> criteria = cb.createQuery(Child.class);
Root<Parent> p = criteria.from(Parent.class);
Root<Child> c = criteria.from(Child.class);
Predicate condition = cb.and(
    cb.equal(c.get(Child_.parentId), p.get(Parent_.id)),
    ...
    );
criteria.where(condition);
criteria.select(c);
criteria.orderBy(cb.asc(c.get(Child_.createDate)));
TypedQuery<Child> q = em.createQuery(criteria).setMaxResults(limit);

A JPA example is provided here, because the Hibernate criteria API is deprecated in favor of the JPA criteria API (see Legacy Hibernate Criteria Queries).

Rob
  • 6,247
  • 2
  • 25
  • 33
  • Thanks for your answer. I am a newbie here. I am not able to get getDb() from an object. I can't find it. – work_in_progress Jun 15 '16 at 17:33
  • That is pseudo code for "whatever is holding your DB-related stuff". Substitute whatever way you are using for getting ahold of your entity manager. – Rob Jun 15 '16 at 18:27