Inner joins using Hibernate Criteria on non-primary key column

Question

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

Answer 1

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).