Safely prepare comma delimited list for query

Question

Possible Duplicate:
How to generate a dynamic “in (…)” sql list through Spring JdbcTemplate?

I'm trying to prepare a list of IDs for a IN() clause in MsSQL in java. I've got the below code which looks like it should work, but it's throwing the error: java.sql.SQLException: Conversion failed when converting the nvarchar value '1,2' to data type int.

I'm kind of at a total loss as to why it's trying it like an integer when I'm passing a string. Any insight would be great.

I've also tried changing template.getId() to template.getId().toString() with no luck

    //JOINs
    Collection<Template> templates = search.getTemplateCollection();
    if (templates != null && templates.size() > 0) {
        searchQuery.append("INNER JOIN dbo.content_to_template ON content_to_template.template_id IN (:templateIds) AND content_to_template.content_id = content.id ");

        StringBuilder templateIds = new StringBuilder();
        for (Template template : templates) {
            if (templateIds.length() == 0) {
                templateIds.append(template.getId());
            } else {
                templateIds.append(",").append(template.getId());
            }
        }
        queryMap.put("templateIds", templateIds.toString());
    }



return this.namedjdbcTemplate.query(searchQuery.toString(), new MapSqlParameterSource(queryMap), this.contentRowMapper);

Shouldn't `templateIds` be a collection of ints rather than a string? Your solution wouldn't be very safe even if the string solution worked, as any commas in `template.getId()` will introduce spurious values. — biziclop, Apr 13 '12 at 13:07
Yeah, as @biziclop mentioned - try using int[] as a parameter, if it is possible ofcourse. — d1e, Apr 13 '12 at 13:08
Take a look at this: http://stackoverflow.com/questions/2810418/how-to-execute-query-with-in-clause-in-spring — Vadzim, Apr 13 '12 at 13:09
Or this one [http://stackoverflow.com/questions/1981683/how-to-generate-a-dynamic-in-sql-list-through-spring-jdbctemplate](http://stackoverflow.com/questions/1981683/how-to-generate-a-dynamic-in-sql-list-through-spring-jdbctemplate) — Artem Oboturov, Apr 13 '12 at 13:35

score 2 · Accepted Answer · edited Apr 13 '12 at 14:47

2

You need to prepare a collection of template IDs and pass it to the map. String delimited data may not work as it expects a collection.

=======================

List templateIds = new ArrayList(); 
templateIds.add(template.getId()); 

----
---

 queryMap.put("templateIds", templateIds);

edited Apr 13 '12 at 14:47

mazaneicha

8,794
4
33
52

answered Apr 13 '12 at 13:25

Ram Reddy

123
6

Safely prepare comma delimited list for query

1 Answers1