CODEWITHSUNDEEP
javamysqlhibernate
armnotstrong
armnotstrong

Reputation: 9065

What's the difference between these two ways of queries in hibernate?

I recently dig into Hibernate and found two ways to do an inner join query using HQL, but I don't know what's the difference between them and which one to use.

Let's assume that we have two tables:

Table user
+-------+-----------+-----------+
|userId |userName   | userLevel |
+-------+-----------+-----------+
|1001   |James      | 0         |
+-------+-----------+-----------+
|1002   |Tom        | 2         |
+-------+-----------+-----------+

Table order
+----------+-----------+-----------+----------+
|orderId   |userId     |productId  |fee       |
+----------+-----------+-----------+----------+
|1001      |1002       |1003       | 5        |
+----------+-----------+-----------+----------+
|1002      |1002       |1005       | 50       |
+----------+-----------+-----------+----------+
|1003      |1001       |1010       | 30       |
+----------+-----------+-----------+----------+

User Tom could have multi order records in the order table. And here comes a demand that we want to find some the order information of Tom alone with his name, if it is raw sql we could do this:

select user.userId, user.userName, order.orderId, order.fee from user join order where user.userId=order.userId and user.userId=1002;

But on coming to hibernate I found two ways that could achieve this:

  1. using the one-to-many mapping to connect the relation between user and order and make a orderEntities<Set> in the userEntity definition then make a HQL query like this:

    FROM UserEntity as U INNER JOIN U.orderEntities as O WHERE U.userId=1002;

Or

  1. omit the one-to-many definition and just make a HQL query like this:

    FROM UserEntity U, OrderEntity O WHERE U.userId = O.userId AND U.userId=1002;

What's the different between these two ways of querying? And which one should I use?

Upvotes: 0

Views: 209

Answers (1)

JB Nizet
JB Nizet

Reputation: 692073

Both are wrong, and BTW, the SQL query is wrong as well.

The first one has an inner join that is useless. The only effect of the join is that it will cause the query to not return anything if the user doesn't have any order. If the goal is to retrieve the user by ID, and then to access its orders, then you shouldn't even use a query:

UserEntity u = em.find(UserEntity.class, 1002);
Set<OrderEntity> orders = u.getOrderEntities();

That will however execute two queries: one to load the user, and a second one to load the orders if you actually do something with them (like iterate on them, getting the size of the set, or whatever)

If the goal is to load the user and its orders in a single query, then the query should be

select distinct u from UserEntity u 
left join fetch u.orderEntities 
where u.id = 1002

Note the usage of a left join, and the usage of fetch.

The second one is wrong because it uses O.userId, which should not exist. If the order entity needs to know its user, then you should have a ManyToOne association from Order to User. Never a field holding the ID of another entity.

Upvotes: 1

Related Questions