wood burning stoves*
The moose likes JDBC and the fly likes Oracle IN operator with PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle IN operator with PreparedStatement" Watch "Oracle IN operator with PreparedStatement" New topic
Author

Oracle IN operator with PreparedStatement

Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Hello,

How can I go about using a PreparedStatement for a SQL query that uses the Oracle IN operator.
I have something like
SELECT name FROM emp WHERE id IN ('A11', 'B12', 'C13')
The number of values specified in the IN-list is only known at run time.

Is there any way of using a PreparedStatement other than calculate the number of values at run-time and create a String with that many '?'s, and set them individually, in which case I might as well use a Statement, since the values to be put into IN-list are retrieved directly from the database, and will be genuine String values.

Cheers
Sonny
satish sathineni
Ranch Hand

Joined: May 03, 2004
Posts: 46
for IN operator u have to use statement only becoz as it doesnt make any difference as every time it has to compile the statement with the changing values...

For using IN operator the best idea would be Statement rather than Prepared Statement...

I also faced the problem as urs but i opted for Statement rather than Prepared Statement for reasons above mentioned....

cheers...
satish....
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Sonny,

since the values to be put into IN-list are retrieved directly
from the database

Then you may probably be able to rewrite your query so that it doesn't need to use a variable-length "IN" list.

In any case, yours is an ultra FAQ. As far as I know, the only way is the one you have described. If you haven't already done so, I suggest going to the Ask Tom Web site and doing a search for the terms "variable in list".

Good Luck,
Avi.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

Sonny,
I've used a "batching" solution to use prepared statements with an in clause. I pick some predefined batch sizes (like 1, 4, 11 and 51.) I then fill up the largest batch size and submit it. Repeat for the remaining data.

The idea is that the database can truly prepare my prepared statement (since there are only four of them.)


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Avi, I think I will rewrite it to use a subquery as the condition.
And thanks for the link, I did not know of that site, it seems really useful.

Jeanne, How exactly do you use the 'batching' solution? If you have a PreparedStatement with say 10 parameters, and at runtime you have 14 values that you need to use, do you execute it once with the first 10 values, and on the second pass, you set the 10 parameters by repeating the 4 values that are left?
or Have I got it all wrong?

Thanks guys

Cheers
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

Sonny,
Suppose your batch sizes are 1, 4 and 10. For 14, you would do one query with ten parameters. Then, you would do a query with four parameters. This would be a separate SQL string with only four parameters. You wouldn't use the full one with 10 parameters, because those would be wasted. The idea is to have a finite number of distinct sql queries in your prepared statements so they stay in the cache.
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Thanks Jeanne.

Incidently, I happened to come accross this article that goes into some performance issues with using IN-lists with Oracle 7, in case anybody finds that useful.
Tuning Oracle for IN-lists
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle IN operator with PreparedStatement
 
Similar Threads
java.sql.SQLException: ORA-00903: invalid table name
Batching Select Statements in JDBC
Error Numeric Overflow-oracle
How prepared statements shall be used
PreparedStatement's - how do you use them CORRECTLY?