CODEWITHSUNDEEP
javasqlhibernate
Incredible
Incredible

Reputation: 11

Criteria query for outer join to subquery

I have two tables, tableA and tableB:

tableA: int id, String val
tableB: int id, String logs, int a_ref.

I need to create a query such as:

SELECT tabB.* FROM tableB tabB
LEFT OUTER JOIN tableA tabA ON tabA.id = tabB.a_ref;

But I can't change the metadata (modify the Hibernate mapping files (.hbm) for the respective tables).

How to create a criteria query in hibernate for this?

Thanks in advance.

Upvotes: 1

Views: 877

Answers (1)

JB Nizet
JB Nizet

Reputation: 691755

A HQL or Criteria query is based on entities and relationships between entities, and you didn't show us you entities, so we can only speculate. If TABLEA is mapped to TableA and TABLEB is mapped to TableB, with a many-to-one association between TableB and TableA, then the query is obvious:

select b from TableB b left join b.tableA

But note that the left join is completely useless here, since it doesn't add any restriction to the query, and the query doesn't select anything from TABLEA. Your initial SQL query should very well be written as select tabB.* from TABLEB tabB. And the criteria query would thus just be

Criteria c = session.createCriteria(TableB.class)

Upvotes: 1

Related Questions