seal seal - 5 months ago 31
SQL Question

How to join Multiple table using hibernate criteria where entity relationship is not direct?

I have three entity. those are -

@Entity
public class Organization {
@Id
private long id;
@Column
private String name;
}


====

@Entity
public class Book {
@Id
private Long id;
@Column
private String name;
@ManyToOne
private Organization organization;
}


======

@Entity
public class Account {
@Id
private Long id;
@Column
private String name;
@ManyToOne
private Book book;
}


In these three entity I would like to perform following sql-

SELECT acc.name, acc.id
FROM account acc
JOIN book b on acc.book_id = b.id
JOIN organization org on b.organization_id = org.id
WHERE org.name = 'XYZ'


In this case
Account
entity has no relation with the
Organization
entity directly.
Account
entity has the relation via
Book
. How can I achieve this using hibernate criteria dynamic query ?

Answer

you can do like this :

Criteria accountCriteria = getCurrentSession().createCriteria(Account.class,"acc");
Criteria bookCriteria =  accountCriteria .createCriteria("book","b");
Criteria orgCriteria =  bookCriteria.createCriteria("organization","org");
orgCriteria.add(Restrictions.eq("name", "XYZ"));

ProjectionList properties = Projections.projectionList();
properties.add(Projections.property("name"));
properties.add(Projections.property("id"));

criteria.setProjection(properties);
criteria.list();