英文:
Can't pass multiple arguments to SQL statement into wildcard
问题
我有这段代码片段
final List<Account> result = this.jdbcTemplate.query(LIST_OF_ACCOUNT_SQL, new String[]{ids},
当我只传递一个参数时,像这样
final String ids= "3213";
代码能正常工作。
但是我在传递多个参数给我的通配符时遇到问题
final String ids= "3213, 2313";
这是我的SQL
"SELECT ID, NAME FROM ACCOUNT WHERE STATUS = 'OK' AND ID IN (?) ";
我正在使用Oracle数据库。
英文:
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.
答案1
得分: 5
你可以使用以下方式:
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")));
英文:
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")));
答案2
得分: 1
你可以使用NamedParameterJdbcTemplate
和MapSqlParameterSource
来处理数组数据:
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"))
);
}
英文:
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"))
);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论