1

I have a sql query:

select * from A
INNER JOIN B
ON A.id = B.id
INNER JOIN C
ON B.id = C.id
INNER JOIN D
ON C.id = D.id
where D.name = 'XYZ'
   and D.Sex = 'M'

I have been trying to come with hibernate query criteria for the above sql, but having problems. Could anybody help out.

Mikko Maunu
  • 38,833
  • 8
  • 124
  • 129
user1502377
  • 73
  • 1
  • 2
  • 10

2 Answers2

5
Criteria c = session.createCriteria(A.class, "a");
                    .createAlias("a.b", "b")
                    .createAlias("b.c", "c")
                    .createAlias("c.d", "d")
                    .add(Restrictions.eq("d.sex", "M"))
                    .add(Restrictions.eq("d.name", "XYZ"));
JB Nizet
  • 633,450
  • 80
  • 1,108
  • 1,174
  • When we say session.createAlias("a.b",b)..would it create an inner join between A and B? – user1502377 Jul 04 '12 at 22:26
  • And how different is criteria.createAlias("a.b","b") is to just saying criteria.createAlias("b","b") – user1502377 Jul 04 '12 at 22:27
  • It wouldn't be different, however, the intial call to createCriteria gave an alias: createCriteria(A.class, "a"). As a result and further reference to fields from the A instance need to use the "a." prefix. – Matt Jul 04 '12 at 23:48
  • Actually, I don't think they *must*, but at least they *can*. I find it clearer to asign an alias to each entity, even the root one. And yes, as the javadoc indicates, it creates an inner join. – JB Nizet Jul 05 '12 at 05:43
1

On your question you want to perform a Cartesian Join, and this is not supported by Criteria, although you can do it with HQL as show below. There is a similar question here

With HQL query you could do something like:

select a from 
   A a, 
   B b, 
   C c 
where 
   a.id = b.id and 
   c.id = b.id and 
   d.id = c.id and 
   d.name = 'XYZ' and 
   d.sex = 'M'

The query is used in a regular hibernate query:

Query query = session.createQuery(query); // <-- here you use the query above
List results = query.list();
Community
  • 1
  • 1
Francisco Spaeth
  • 22,073
  • 6
  • 60
  • 101
  • 1
    I have to use hibernate's query criteria to do this. For ex- Criteria c = session.createCriteria(Dokument.class, "dokument"); c.createAlias("dokument.role", "role"); // inner join by default c.createAlias("role.contact", "contact"); c.add(Restrictions.eq("contact.lastName", "Test")); return c.list(); – user1502377 Jul 04 '12 at 19:58