Help: Multiple values in where clause using IN (:parameter)

[Francisco J Rosas]

Hi everybody!!!

I am having this problem in a where clause of a SQL using NamedParameterJdbcDaoSupport, NamedParameterJdbcTemplate and MapSqlParameterSource.

My query is something like this
SELECT
<list of fields>
FROM
<table joining other tables>
WHERE
<some conditions>
AND SOME_ATTRIBUTE IN (: parameter)

What I want to accomplish in SQL is something like this:
SELECT
<list of fields>
FROM
<table joining other tables>
WHERE
<some conditions>
AND SOME_ATTRIBUTE IN (value 1, value 2, value 3, ... , value n)

I tried with:
...
WHERE
<some conditions>
AND SOME_ATTRIBUTE IN (: parameter)

In runtime, the value of : parameter changes to a String, with a list of comma separeted values, but does not work.

this is my code:

String parameter = getListofValues(someList);
map.addValue("parameter", parameter, Types.VARCHAR);
...
private String getListofValues(List someList){
		StringBuffer buffer = new StringBuffer();

		Object[] values = someList.toArray();
		int totalValues = values.length;

		if(totalValues > 0){
			for (int i = 0; i < totalValues - 1; i++) {
				buffer.append(values[i]);
				buffer.append(",");
			}

			buffer.append(values[totalValues - 1]);
		}

		return buffer.toString();
}

In the code, I also tried adding the char ' , but it does not work either.
The list of the values added at IN clause should be dynamically generated, so the list of values.

What's wrong with my code or what could be the possible solution to these problem.

Regards and thank you all.

[trisberg]

Try passing in the List itself as the parameter value. NamedParameterJdbcTemplate supports this when you use a named parameter. It will expand the SQL to include the correct number of ? placeholders for the number of items in the List.