1

Here is my simplified database:

user_post
- id
- post_id (FK)

post
- id
- link_id (FK)

link
- id

I've a list of user_post and I wanna get for each of them the link they are linked to.

Currently, I'm doin it like this:

SELECT userPost.post.link FROM UserPost userPost WHERE userPost IN (:userPosts)

Works great, but maybe later, I'll get a huge number of user_post, so there'll be a lot of values within the IN clause (100 < x < 5000+).

Is IN limited? Is there any other way to do it? Before, I was doin it like this:

for (UserPost userPost : user.getUserPosts()) {
    Link link = userPost.getPost().getLink();
    //
}

But the solution above takes really a lot of time to get executed, so I'm lookin for a more optimized one.

sp00m
  • 47,968
  • 31
  • 142
  • 252

1 Answers1

4

Yes, the IN clause is limited:

The number of values in the IN list is only limited by the max_allowed_packet value.

In Oracle, the limit is set to 1000 elements.

Instead of selecting user posts, and then iterating through them to get their link or issuing another query to get them, you could load the links in the same query as the user posts:

select userPost from UserPost userPost
left join fetch userPost.post post
left join fetch post.link link;

This query will load eveything at once, and iterating theough the user posts and getting their link using userPost.getPost().getLink() won't trigger any additional query.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • I didn't know, many thanks! But what if I've a custom list of `user_post`, i.e. not every `user_post` a user is linked to, but only a sublist of them? What great whould be something like `select userPost from :userPosts userPost ...` but I guess this isn't possible, is it? – sp00m Jun 21 '12 at 09:46
  • Well, if those user posts are loaded by a query, just add the left join fetches to this query, and you'll have the sublist of user posts, with their post and link loaded. – JB Nizet Jun 21 '12 at 09:49
  • Side question: if I run a query with a `JOIN FETCH` clause twice in a same HTTP request execution, will Hibernate fetch the results twice, or will it look it its cache if he already fetched them? – sp00m Jun 21 '12 at 15:11
  • Unless you have a query cache configured, the query will be executed twice, as all the other queries. – JB Nizet Jun 21 '12 at 16:09