Is it possible to implement such SQL using Criteria API?

akorud · **Posted:** Thu Dec 28, 2006 11:16 am

Hi,
I have simple database structure, tables a, b with master-detail relationship a.id->b.master_id
What I have to do is to select from b with some criteria and then left join with a
Straightforward approach:

Code:

Criteria c = new sess.createCriteria(b.class);
c.add(Expression...);
c.setFetchMode("b.master", FetchMode.JOIN);
c.list();

generate following sql:

Code:

SELECT * FROM b LEFT OUTER JOIN a ON b.master_id=a.id WHERE ...criteria...

Problem is that a is small table and b is huge (hundred of thousands records) and in this sql, first join (full, on all records from b) is performed and then result is filtered according to criteria.
What I'd like to implement is something like this:

Code:

SELECT bb.* FROM (SELECT b.* FROM b WHERE ...criteria...) bb LEFT OUTER JOIN a ON bb.master_id = a.id

- first filtering, then join (only on filtered records).
This works fine with native SQL, I wonder if it possible to implement this using Criteria API (for dynamic criteria construction).

Thanks in advance,
Andriy

batmat · **Posted:** Thu Dec 28, 2006 3:43 pm

I don't know if it'd be possible with Criteria or HQL, but at least you can use the SQL directly and say to Hibernate that the result type is a specified entity:

Code:

List<YourBObject> list = session.createSQLQuery("SELECT bb.* FROM (SELECT b.* FROM b WHERE ...criteria...) bb LEFT OUTER JOIN a ON bb.master_id = a.id").addEntity(YourBObject.class).list();

Using named query, it would be even cleaner:
in hbm.xml

Code:

<sql-query name="yourQuery">
    <return class="eg.Person"/>
    SELECT {bb.*} 
    FROM (
             SELECT b.* 
             FROM b 
             WHERE ...criteria... = :yourNamedParam and .. = :otherNamedParam ) bb LEFT OUTER JOIN a ON bb.master_id = a.id</sql-query>

And in the code, call this named query, bind the param, call list on this query and you're done :-). The big point of doing it this way is that you externalize the query type to hbm. It could be HQL, SQL, or even a stored procedure, the hibernate code doesn't have to know, which is great for maintenance and evolutions imo.

http://www.hibernate.org/hib_docs/v3/re ... medqueries

akorud · **Posted:** Thu Dec 28, 2006 5:33 pm

Thanks,
that's exactly what I'm doing. Problem (well, not really a problem) is that criteria is runtime constructed - not value, but logic expression. And I have to write

Code:

if (condition) {
  sql = "WHERE x=y";
} else {
  sql = "WHERE x=y AND a=b";
}

which is not very elegant solution :)