Hi,

I am using Hibernate 3.1. I got a criteria query that joins multiple tables. When I execute the query, the SQL generated has all columns from all tables in FROM clause. Is there a way, I can restrict hibernate to select cloumns from main criteria only?

Below is the example.

Criteria query:
criteria = getSession().createCriteria(JournalLine.class);
criteria.createAlias("journal", "jou").createAlias("jou.journalType", "jty").add(Restrictions.ne("jty.txnNature", JournalType.TxnNature.CASH));
criteria.list();

JournalLine has many-to-one relationship with Journal and Journal has one-to-one relationship with Journal Type.
Above code generates SQL that selectes columns from JournalLine, Journal and JournalType while I want it to select only JournalLine.

I have gone through the projection topic. Adding projection is one option but then I need to add projection for all columns in JournalLine table and also have to construct JournalLine object manually which is very ugly.

Any help will be very much appreciated.

Many Thanks
Shantanu

Have you tried setting distinct root entity option on result transformer as shown above?

Thanks for helping but this does not work. As far as I understand, the DISTINCT_ROOT_ENTITY is used for selecting distinct rows of root entity and does not affect columns in SELECT clause in the query.

Thanks
Shantanu

I haven't tried this out myself, but isn't that the intention of setting lazy loading at the class level?

I'm not sure what your goal is - in your example would you want Hibernate to only select the columns in the JournalLine class, and not in the JournalType class?

No lazy loading will happen in case of any collections that are set for it but when I join multiple tables in WHERE clause, hibernate selects ALL columns from ALL tables i.e. in my case it is selecting all columns from JournaLine, JournalType and Journal while I wanted to select only JournalLine columns.

I was wondering whether there is any way to tell hibernate to select columns from Root entity only and not other entities.

Thanks
Shantanu

To give you hint you can use DetachedCriteria which will atleast result in columns of only JournalLine.class but with subqueries.

Something like



The snippet may not be correct, atleast it will give you idea how to extract only columns from one persistent class.

Thanks for help. The technique seems to be working. There is a issue though. I wonder whether this is hibernate bug. Here is working code.


DetachedCriteria jtyCriteria = DetachedCriteria.forClass(JournalType.class, "jty");
jtyCriteria.add(Restrictions.ne("jty.txnNature", JournalType.TxnNature.CASH));
jtyCriteria.setProjection(Projections.property("jty.code"));

DetachedCriteria jouCriteria = DetachedCriteria.forClass(Journal.class, "jou");
jouCriteria.setProjection(Projections.property("jou.SID"));
jouCriteria.add(Property.forName("jou.journalType").in(jtyCriteria));
jouCriteria.getExecutableCriteria(getSession()).list();

DetachedCriteria criteria = DetachedCriteria.forClass(JournalLine.class, "jli");

criteria.add(Property.forName("jli.journal").in(jouCriteria));
criteria.getExecutableCriteria(getSession()).list();


This code is creating subquery in subquery.

If I do not put the line in bold then I get following exception. Any idea?

java.lang.NullPointerException
at org.hibernate.criterion.SubqueryExpression.getTypedValues(SubqueryExpression.java:80)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getQueryParameters(CriteriaQueryTranslator.java:251)
at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:55)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:333)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1514)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy24.list(Unknown Source)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at ......

[quote="shantanu"]jouCriteria.getExecutableCriteria(getSession()).list();[quote]

Thanks for posting the solution. It helps.

Similar to you I wasnt using the above quoted line and getting the same exception. Having ran out of ideas, I thought maybe Hibernate Criteria doesnt allow subqueries in a subquery.
I still dont know why that exception is coming.

Is this a bug in hibernate? How can we do subquery in subquery?

I wouldnt call it as bug just because I dont know how to work with it. Its true there is very less documentation about it.

Agreed. It may not be a bug but can someone from Hibernate team let me know how can I do subquery within a subquery?

Thanks
Shantanu

One solution to the original problem of Hibernate selecting all columns is to use projections.

Please look at

http://forum.hibernate.org/viewtopic.ph ... projection

This shows how to populate objects directly from projections. Ony disadvantage is that the objects created are transient objects.