is there any limit of statement i can use where in(1,2,3,4,5,6....) in createSQLQuery in spring hibernatetemplate() ?
+2
A:
Yes, but said limit is not caused by Hibernate or Spring but rather underlying database.
For example, Oracle AFAIK allows no more than 1000 items in IN() clause. There are usually also limits on the maximum statement length.
ChssPly76
2009-12-10 01:18:34
what workaround i can use if i need to pass long... where in(..)
cometta
2009-12-10 01:38:21
@cometta can you pass a range where .. between .. ?
prateek urmaliya
2009-12-10 02:19:28
cannot. value in in(..) is unique random and not in range
cometta
2009-12-10 02:23:47
With Oracle, I usually tell my clients to limit it to 20 per statement execution, for performance reasons.Another note, don't use the same sql statements with a varying number of items. i.e. IN(?) then IN (?, ?) and then IN (?, ?, ?). Thats also a performance penalty. All you need is one batch version of the statement with the IN(?, ?, ?) and one non-batch version without the IN, just a "= ?". which you can execute multiple times to finish the remaining items.
DragonFax
2009-12-10 11:29:01
+1
A:
I believe it depends on the underlying database you are using. For example, Oracle used to limit you to 1000 items in an IN clause and I believe it still does, but I'm not certain. What is your database?
Nebakanezer
2009-12-10 01:18:51
i'm using oracle. what workaround i can use if i need to pass long... where in(..)
cometta
2009-12-10 01:35:14
+2
A:
As mentioned, Oracle has a limit. One workaround is to execute multiple queries, each with a subset of the IN parameters. The other is to use an OR in the clause: where column in (:firstsubset) or column in (:secondsubset) or ... Some databases also have a limit on the total SQL string length. I've never run into that with Oracle, but wouldn'be surprised.
Brian Deterling
2009-12-10 01:53:23