2

to select an overview from a database I have to use a statement like this:

select a1.begin, a1.end, b1.name, c1.name, d1.name, e1.name, f1.name, ...
from a2, b1, b2, c1, c2, a1
left outer join (d1 join d2 on d2.id = d1.parent and d1.user = ?) on
     d1.deleted = 0 and d1.parent = a1.d1_id
left outer join (e1 join e2 on e2.id = e1.parent and e1.user = ?) on
     e1.deleted = 0 and e1.parent = a1.e1_id
left outer join (f1 join f2 on f2.id = f1.parent and f1.user = ?) on
     f1.deleted = 0 and f1.parent = a1.f1_id
where ....  a lot more stuff...

The statement works like a charm and the database has no problem to process it really fast. Believe me, there's definitely no other way to get the required data.

Until now I was pretty proud to say, that I don't have a single word of pure EJB-QL or even SQL in our java project. But now I'm not sure if there is any way to build such a statement by using the CriteriaBuilder. I have absolutely no idea how to create an outer join with more than the single criteria or even with a nested inner join.

Any hints greatly appreciated.

Thanks Hennes

Hennes
  • 1,340
  • 1
  • 10
  • 26

2 Answers2

0

first join:

criteria=session.createCriteria(mappingClass.class).createAlias("propertyName","firstAliasName");

second join:

criteria.createAlias("firstAliasName.propertyName","secondAliasName");

and so on.

That will do OFC if you are joining by mapped relations. If not than read this:Criteria API . The simplest way to do such think it would by by HPQL or JPQL, however you will probably wont get mapped enities as result, only ResultSet with columns (but you won't get it anyway I think)

Antoniossss
  • 31,590
  • 6
  • 57
  • 99
0

It's hard to give specific help without knowing the object model, but I believe you want to use the join method and specify the join type. Using it you can nest joins that will use the relationship defined on the specified attribute mapping. Explain action of the criteria API can be found here: http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Criteria#Join

Eclipselink also supports the 'on' clause in JPQL and criteria queries through feature https://bugs.eclipse.org/bugs/show_bug.cgi?id=367452

Chris
  • 20,138
  • 2
  • 29
  • 43