Hibernate eager loading (fetch all properties does not work)

Question

Basically I want to eager-load properties. I have the following HQL query:

SELECT u.id AS id, u.name AS text, u AS obj FROM User AS u fetch all properties

I would expect this to execute one query only. Instead I got N+1 queries.

The code is the following:

Query q = mySession.createQuery(
    "SELECT u.id AS id, u.name AS text, u AS obj FROM User AS u fetch all properties")
    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

for (Iterator i = q.iterate(); i.hasNext();) {
    Object line = i.next();
    System.out.println(line);
}

The output I get (with hibernate.show_sql set to true) is:

Hibernate: select user0_.id as col_0_0_, user0_.name as col_1_0_, user0_.id as col_2_0_ from user user0_
Hibernate: select user0_.id as id0_0_, user0_.name as name0_0_, user0_.location as location0_0_ from user user0_ where user0_.id=?
{id=1, obj=User@b6548 [id='1' name='John' ], text=John}
Hibernate: select user0_.id as id0_0_, user0_.name as name0_0_, user0_.location as location0_0_ from user user0_ where user0_.id=?
{id=2, obj=User@4865ce [id='2' name='Arnold' ], text=Arnold}

Ps: The situation is just the same without transformers.

Edit:

The file with the entity-mappings:

<hibernate-mapping>
    
    <class name="User" table="user">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        <property name="name"/>
        <property name="location"/>
        <map name="customPrices" table="custprice">
            <key column="user"/>
            <map-key-many-to-many column="product" class="Product"/>
            <element column="price" type="double"/>
        </map>
    </class>
    
    <class name="Product" table="product">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        <property name="name"/>
        <property name="listprice"/>
    </class>
    
</hibernate-mapping>

I tried adding lazy="false" to the class and to the individual properties. No difference.

My configuration file:

<hibernate-configuration>
<session-factory>
    <property name="connection.url">jdbc:mysql://192.168.0.203/hibtest</property>
    <property name="connection.username">hibtest</property>
    <property name="connection.password">hibb345</property>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="current_session_context_class">thread</property>
    <property name="hibernate.show_sql">true</property>
    
    <mapping resource="combined.hbm.xml" />
</session-factory> 
</hibernate-configuration>

Edit2:

Even the following code causes N+1 query. Although I only fetch the ID field, which according to documentation should not cause objects to load.

for (Iterator i = q.iterate(); i.hasNext();) {
    Object line = i.next();
    User u = (User)((Map)line).get("obj");
    System.out.println(u.getId());
}

please add your hibernate mapping. It seems like you location association is lazily loaded. — Eran Harel, Apr 18 '11 at 13:16
What exactly are you trying to achieve with this query? Why do you need to specify `id` and `name` separately? — axtavt, Apr 18 '11 at 13:29
@axtavt The results are exactly what I expect. Only the way Hibernate fetches the result is unrealistic. Even when I explicitly add `fetch all properties`. — vbence, Apr 18 '11 at 13:37
Is there a specific reason why you: 1. use iterate() instead of list()? 2. select individual properties along with the main entity? — Eran Harel, Apr 18 '11 at 13:41
@Eran The results are fed to an other API. But yes: using `.list().interator()` instead of `.interate()`causes eager loading. You might want to post this as an answer too. - Not very intuitive behavior though. Makes me wonder how many landmines such as this lie in Hibernate... — vbence, Apr 18 '11 at 13:58
@Eran Harel Please write a short answer so I can accept it :) — vbence, Apr 18 '11 at 15:10

score 8 · Accepted Answer · edited May 23 '17 at 12:02

The problem was with .iterate(). According to Hibernate API docs:

Entities returned as results are initialized on demand. The first SQL query returns identifiers only.

This is a special function to be used when we expect the resulting objects to be already cached. They will be (lazy) loaded when accessed.

So for general use, to get an iterator on a result of a query you should use .list().iterate().

Thanks for Eran Harel for the help.

score 0 · Answer 2 · answered Apr 18 '11 at 13:46

0

In essense, the cause of your problem is an attempt to incorporate result transformation logic into a query.

I think it would be better to make a simple HQL query and then apply your specific result transformation logic in a custom ResultTransformer, something like this:

Query q = mySession.createQuery("FROM User")
    .setResultTransformer(new ResultTransformer() {
        public List transformList(List l) { return l; }
        public Object transformTuple(Object[] tuple, String[] aliases) {
            Map<String, Object> r = new HashMap<String, Object>();
            User u = (User) tuple[0];
            r.put("obj", u);
            r.put("id", u.getId());
            r.put("text", u.getName());
            return r;
        }
    });

answered Apr 18 '11 at 13:46

axtavt

239,438
41
511
482

Thanks for the idea, but I mentioned in the original question: "Ps: The situation is just the same without transformers." – vbence Apr 18 '11 at 13:54
Yep, the problem was with `.iterate()` - seemingly it forces some *superlazy* mode over all other settings. – vbence Apr 18 '11 at 14:21
@vbence here is why: http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#objectstate-querying-executing-iterate - this is why I asked – Eran Harel Apr 18 '11 at 14:38
@Eran Harel The javadoc also mentions some half a sentence, but it does not emphesize that this is a rarely used function normal cases should use `.list().interator()` instead. - Apart the fact that the function name should suggest this special behaviour in the first place. – vbence Apr 18 '11 at 14:42
@vbence basically you should only use iterate if you're low on system resources (or when the collection you're iterating over is huge), and even then it is usually better to use pagination instead. I agree on the poor documentation - everybody make this mistake... – Eran Harel Apr 18 '11 at 14:52

score 0 · Answer 3 · answered Apr 18 '11 at 14:44

0

Looks like it cannot retrieve the obj in a single query with separate columns.

Since you use the same values from User for the other columns, the example from @axtavt seems your best solution.

answered Apr 18 '11 at 14:44

SPee

656
4
5

1

The problem was that `.list().interator()` has to be used. – vbence Apr 18 '11 at 15:09

Hibernate eager loading (fetch all properties does not work)

Edit:

Edit2:

3 Answers3