CODEWITHSUNDEEP
javamysqlspringjdbctemplate
Aaron Silverman
Aaron Silverman

Reputation: 22635

Java Spring: What is best way to do a mysql query using an IN clause using JdbcTemplates?

If I have a bunch of stuff in quotes I find myself doing something like:

String sql = "SELECT col1, col2, col3 "
           + "FROM my_awesome_table "
           + "WHERE strValue IN (%s)";

sql = String.format(sql, COMMA_JOINER.join(Iterables.transform(strValues,
                                                               new Function<String, String>() {
                                                                   @Override
                                                                   public String apply(String input) {
                                                                       return "'" + input.replaceAll("'", "''") + "'";
                                                                   }
                                                               })));

List<ResultObject> resultObjects =
        getSimpleJdbcTemplate().query(sql, resultObjectRowMapper());

But it seems odd I have to build my own IN clause, surround the strings with single quotes, and escape them myself. There has got to be better way! Some additional context: I don't expect to be querying for much (few dozen or few hundred rows max) and optimization isn't too much of a concern as long as we don't query the rows one at a time!

Upvotes: 1

Views: 1634

Answers (2)

JB Nizet
JB Nizet

Reputation: 691715

First of all, even without JdbcTemplate, you should use prepared statements, and thus let the JDBC driver escape the quotes for you. The SQL query would be something like

select ... where strValue in (?, ?, ?, ?)

and you would bind each parameter using PreparedStatement.setParameter(), without any need to escape anything.

With NamedParameterJdbcTemplate, it's even easier, because Spring allows using a single parameter for the whole collection of Strings. Its usage is explained in the Spring documentation:

String sql = "select ... from ... where strValue in :listOfValues";
Map namedParameters = Collections.singletonMap("listOfValues", strValues);
...

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

According to this thread on springsource, the following should work:

String sql = "SELECT col1, col2, col3 "
       + "FROM my_awesome_table "
       + "WHERE strValue IN (:items)";

getSimpleJdbcTemplate().query(
    sql
,   Collections.singletonMap("items", strValues)
,   resultObjectRowMapper()
);

Upvotes: 2

Related Questions