File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Only for JDBC tallents Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Only for JDBC tallents" Watch "Only for JDBC tallents" New topic

Only for JDBC tallents

Sasi Kumar

Joined: Jan 18, 2002
Posts: 15
If you give a easy solution for this problem, then you are very good in JDBC.
The following SQL query should be executed only
through PreparedStatement. You should not use
Query is
select employees from employeemast where
employee_id in (1,2,3,4,5)
This you have to give in prepared statement as
select employees from employeemast where
employee_id in (?)
The values (1,2,3,4,5) will change dynamically in
a for loop.
if you say
here stringValue = "1,2,3,4,5"
This setSting will wrap a single quotes in
the query ie, query will build by java like following.
select employees from employeemast where
employee_id in ('1,2,3,4,5')
which will cause invalid number error. since
employee_id is a number in database.
You should not dynamically build ? in the query
like following
select employees from employeemast where
employee_id in (?,?,?,?,?)
There should be only one Question mark.
If you find a solution then u are great.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
well I guess you could send an array to a proc, thus:
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TESTARRAY", conn);

ARRAY newArray = new ARRAY(desc, conn, s);
CallableStatement cstmt = conn.prepareCall("{ call test.test (?) }");
cstmt.setArray(1, newArray);

Personally I think it's ok to build the query dynamically (so you have a variable number of '?' in the query). In fact thats exactly what I use in my app

You might try asking on there are plenty of smart(er) people there who can help you out.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

unfortunately the PreparedStatement was not made to be used in this way. Even if you find a workaround to make it execute, you will most likely lose all advantages of using PreparedStatements( statement caching cannot be done as the number of parameters is different on each execution, type checking has to be done on every execution, etc. ) So even though you may accomplish fooling the db into using a PreparedStatement in this way, it accomplishes nothing. Your better choice for dynamic queries is a Statement which loops to append the IN values.
and just because you happen to slip it by one driver, does not mean that it will work for another.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
I guess it depends on the application.
The benefit is that strings don't need to be escaped, which is a big prob with statements. I'm happy to take a performance hit.
I agree. Here's the link:
subject: Only for JDBC tallents
It's not a secret anymore!