0

Possible Duplicate:
What is the best approach using JDBC for parameterizing an IN clause?

I was wondering if it's possible to pass a list to an SQL statement. I've been Google-ing and most of the answers I found did not make use of a separate .xml file where all the SQL queries were saved.

My sql.xml file contains:

<sql name="getResultSet"><![CDATA[
SELECT ColumnName FROM TableName 
WHERE OtherColumnName IN(?) 
WITH UR;  ]]>
</sql>

In my action.java:

String fileName = "sql.xml";
ListQuery query = new ListQuery(getDefaultsDataSource(), SQLParser.getSql(fileName, "getResultSet"), new int[] {Types.ARRAY});
List rows = (List) query.execute(new Object[] { values });

'values' would be the user input, for example:

values = Name1, Name2, Name3, ...

'values' will be the one in IN(?)

How do I go about this? Is there a better way to do it? Thanks!

Community
  • 1
  • 1
Nicole
  • 39
  • 1
  • 9
  • What library/framework is this? Where does `ListQuery` come from? – theon Nov 28 '12 at 08:21
  • ListQuery is basically a created method that gets the datasource, the sql file name and the parameter. This method gets the parameter from the user and substitutes it to the '?' in the sql.xml file. – Nicole Nov 28 '12 at 08:28
  • I'm actually more concerned with the SQL part of the code, can you pass a List to the query? Even if it's in a totally different manner of coding, I am open to suggestions. :) – Nicole Nov 28 '12 at 08:30
  • @JBNizet I will try that link you gave me. Thanks! – Nicole Nov 28 '12 at 08:31
  • Assuming you are talking to SQL Server, then another good resource is [Erland Sommarskog's Arrays and Lists in Sql Server](http://www.sommarskog.se/arrays-in-sql.html). More specifcally, I like his approach using [XML as a parameter](http://www.sommarskog.se/arrays-in-sql-2005.html#XML). – Mr Moose Nov 28 '12 at 08:53

0 Answers0