Can't pass multiple arguments to SQL statement into wildcard

Question

I have this fragment of code

 final List<Account> result = this.jdbcTemplate.query(LIST_OF_ACCOUNT_SQL, new String[]{ids},

When I pass only one argument like

        final String ids= "3213";

Code is working fine.

But I have problem passing multiple arguments to my wildcard

final String ids= "3213, 2313";

This is my SQL

"SELECT ID, NAME FROM ACCOUNT WHERE STATUS = 'OK' AND ID IN (?) ";

I am using Oracle Database.

score 5 · Accepted Answer · edited Oct 23 '20 at 14:23

5

You can use the following way

String inSql = String.join(",", Collections.nCopies(ids.size(), "?"));
 


List<Account> result = jdbcTemplate.query(
      String.format("SELECT ID, NAME FROM ACCOUNT WHERE STATUS = 'OK' AND ID IN  (%s)", inSql), 
      ids.toArray(), 
      (rs, rowNum) -> new Account(rs.getInt("ID"), rs.getString("NAME")));

edited Oct 23 '20 at 14:23

jarlh

42,561
8
45
63

answered Oct 23 '20 at 14:21

John

74
2

Nowhere Man · Answer 2 · 2020-10-23T15:29:10.513

You may want to use a NamedParameterJdbcTemplate and MapSqlParameterSource which takes care of the array data:

static String LIST_OF_ACCOUNT_SQL = "SELECT ID, NAME FROM Accounts WHERE STATUS = 'OK' AND ID IN (:ids)";

private NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

public static List<Account> getAccountsByIds(String[] ids) {
    SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);

    return this.namedJdbcTemplate.query(
            LIST_OF_ACCOUNT_SQL, 
            parameters,
            (rs, rowNum) -> new Account(rs.getInt("ID"), rs.getString("NAME"))
    );
}

Can't pass multiple arguments to SQL statement into wildcard

2 Answers2