Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question on hibernate IN Operator

 
saumil baxi
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
What is the maximum number that I can specify in 'In' operator
Like from ABC where abc in (1000,200.......) ??

And what is the alternate solution if it exceeds the maximum value.

Thanks,
Saumil
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by maximum value? Do you mean how many options can we put in the IN clause?
 
saumil baxi
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I mean to say "The maximnum parameter values can be defined in an IN condition "

Like
From ABC where XYZ IN (10,20....... Limit of this values)..
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No idea. I'd be surprised if Hibernate imposed a limit. Indeed, you can bind a List of values to a query, so the number possible is probably very large.

The driver and database may impose a limit. Under most database you are going to hit a query size problem at some stage or other. Oracle for example used to have a limit of 64Kb per query. That was back in Oracle 9 days, so I'm not sure if it has move on by now.

That being said a 64Kb query is a pretty huge query. So general good software practice rules probably apply: if you find yourself writing code that is hitting the maximum space allowed, stop and have a rethink.
 
saumil baxi
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply.

I am passing the entire list in the query. But the size of the list vary from 1 to 5000 .
so when i say from ABC where xyz in (List) . It works fine for limited size.but for bigger size it didnt work.

Is there any other alternative for IN operator.

Thanks
Saumil
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Is there any other alternative for IN operator.

As a means to query for results where a condition belongs in a set? No. This is true of both SQL as well as HQL. There are different ways of writing queries, but if your query is failing because your are hitting a limit on the size of the SQL itself, and that limit is being hit because of the number of values you have to put in your conditional clause then however you write it you are probably going to have the same problem.

Are you using bind variables?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic