2

I have a user and a message table. User to Message are one-to-many relationships and Message to User are many-to-one relationships. I've marked one of the many-to-one as fetch join. When I 'get' a single Message, Hibernate runs a join query, but when I fetch all the messages, Hibernate runs select queries instead of a join. What could be the reason? Following are the details:

Relationship between them:

User

<set name="messagesForFromUserUid" lazy="true" table="message" inverse="true" cascade="save-update"> <key> <column name="from_user_uid" not-null="true" /> </key> <one-to-many class="repository.Message" /> </set> <set name="messagesForToUserUid" lazy="true" table="message" fetch="select"> <key> <column name="to_user_uid" not-null="true" /> </key> <one-to-many class="repository.Message" /> </set>

Message

<many-to-one name="userByFromUserUid" class="repository.User" fetch="join" lazy="false"> <column name="from_user_uid" not-null="true" /> </many-to-one> <many-to-one name="userByToUserUid" class="repository.User" fetch="select" lazy="proxy"> <column name="to_user_uid" not-null="true" /> </many-to-one>

When I fetch a single message object, Hibernate runs one join query as expected:

Message m = (Message) s.get(Message.class, 2); Hibernate: select message0_.message_uid as message1_1_1_, message0_.from_user_uid as from2_1_1_, message0_.to_user_uid as to3_1_1_, message0_.message_text as message4_1_1_, message0_.created_dt as created5_1_1_, user1_.user_uid as user1_0_0_, user1_.user_name as user2_0_0_, user1_.user_password as user3_0_0_, user1_.email as email0_0_, user1_.first_name as first5_0_0_, user1_.last_name as last6_0_0_, user1_.created_dt as created7_0_0_ from hello.message message0_ inner join hello.user user1_ on message0_.from_user_uid=user1_.user_uid where message0_.message_uid=?

But when I fetch all the messages in one go, Hibernate runs select queries instead:

List<Message> l = s.createQuery("from Message").list(); Hibernate: select message0_.message_uid as message1_1_, message0_.from_user_uid as from2_1_, message0_.to_user_uid as to3_1_, message0_.message_text as message4_1_, message0_.created_dt as created5_1_ from hello.message message0_ Hibernate: select user0_.user_uid as user1_0_0_, user0_.user_name as user2_0_0_, user0_.user_password as user3_0_0_, user0_.email as email0_0_, user0_.first_name as first5_0_0_, user0_.last_name as last6_0_0_, user0_.created_dt as created7_0_0_ from hello.user user0_ where user0_.user_uid=? Hibernate: select user0_.user_uid as user1_0_0_, user0_.user_name as user2_0_0_, user0_.user_password as user3_0_0_, user0_.email as email0_0_, user0_.first_name as first5_0_0_, user0_.last_name as last6_0_0_, user0_.created_dt as created7_0_0_ from hello.user user0_ where user0_.user_uid=?
Improve this question

2 Answers 2

Reset to default
2

Looks like Hibernate does not always use fetch strategies defined in the mapping, for HQL or Criteria queries. They are typically used for get/load. Found a reference here: https://forum.hibernate.org/viewtopic.php?f=1&t=957561

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

"They are typically used for get/load." <= Correction: They are used for load(). Also, you can solve the N+1 SELECTS problem in a different way: turn on lazy select and enable a batch size on the entity.
0

I am not sure but this might be the reason. If you run single query which is join or subquery. It is ok, there may not be any performance difference for single query. But if you are running multiple queries(in your case multiple messages), There may raise Performance issue. I would definitely choose simple select queries instead of join/subqueries. This definitely makes sense if we consider performance. This is what Hibernate does.

Improve this answer

7 Comments

I don't think this answers my question. My question is about the unexpected behaviour that Hibernate shows in that second query. Why does it behave that way?
It is because of Performance. To increase performance, hibernate may be using select queries instead of join. I already given answer to your question.
Why do you think running multiple select queries is more performant than running a single join query? In fact the point of join fetch is to eliminate the 'n + 1 select' problem!
Very good question Shrini! Please check how does single select query executes and how does single join query executes(not sure but I think it uses subqueries internally).
I'm not comparing a single select against a single join. I'm comparing a bunch of selects against a single join.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.