SCJP, SCJD, SCWCD
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by Dan Murphy:
Hi,
I want to use a prepared statement to execute some SQL like
The actual list of ages will vary from one execution to the next, so the SQL string contains a parameter:
However there doesn't appear to be any method defined on PreparedStatement for setting a parameter to a list of values. Although there is a setArray() method, the array in question is of type java.sql.Array, and I've no idea how to create one of these from a "regular" Java array or Collection.
Any suggestions?
Cheers,
Dan
[ June 26, 2008: Message edited by: Dan Murphy ]
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by amit soni:
DO THIS :
--------
int iVariable1=10;
int iVariable1=20;
int iVariable1=30;
select * from person where age in (?,?,?);
ps.setInt(1,iVariable1); //where 'ps' is ref varible of PreparedStatement
ps.setInt(1,iVariable2);
ps.setInt(1,iVariable3);
SCJP, SCJD, SCWCD
Originally posted by Dan Murphy:
That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by Wei Dai:
Maybe you can try
ResultSet rs1=stmt.executeQuery("select 10 union select 20 union select 30");
PreparedStatiment prestmt=con.prepareStatment("select * from person where age in ?");
prestmt.setParameter(1,rs1);
If your jdbc driver doesn't support that feature, you can try to create a temporary table in memory too.
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by Dan Murphy:
That won't work because each time I execute the PreparedStatement the list could have a different number of elements, so I can't simply define a '?' parameter for each item in the SQL string.
[ June 27, 2008: Message edited by: Dan Murphy ]
[OCP 11 Complete Study Guide] [OCP 11 Programmer I Book] [OCP 11 Programmer II Book] [OCP 11 Practice Tests Book] [OCA 8 Book] [OCP 8 Book] [OCP 8 Practice Tests Book] [Blog]
Consider Paul's rocket mass heater. |