无法将多个参数传递给带通配符的SQL语句。

huangapple go评论71阅读模式
英文:

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&lt;Account&gt; result = this.jdbcTemplate.query(LIST_OF_ACCOUNT_SQL, new String[]{ids},

When I pass only one argument like

        final String ids= &quot;3213&quot;;

Code is working fine.

But I have problem passing multiple arguments to my wildcard

final String ids= &quot;3213, 2313&quot;;

This is my SQL

&quot;SELECT ID, NAME FROM ACCOUNT WHERE STATUS = &#39;OK&#39; AND ID IN (?) &quot;;

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(&quot;,&quot;, Collections.nCopies(ids.size(), &quot;?&quot;));
 


List&lt;Account&gt; result = jdbcTemplate.query(
      String.format(&quot;SELECT ID, NAME FROM ACCOUNT WHERE STATUS = &#39;OK&#39; AND ID IN  (%s)&quot;, inSql), 
      ids.toArray(), 
      (rs, rowNum) -&gt; new Account(rs.getInt(&quot;ID&quot;), rs.getString(&quot;NAME&quot;)));

答案2

得分: 1

你可以使用NamedParameterJdbcTemplateMapSqlParameterSource来处理数组数据:

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 = &quot;SELECT ID, NAME FROM Accounts WHERE STATUS = &#39;OK&#39; AND ID IN (:ids)&quot;;

private NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

public static List&lt;Account&gt; getAccountsByIds(String[] ids) {
    SqlParameterSource parameters = new MapSqlParameterSource(&quot;ids&quot;, ids);

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

huangapple
  • 本文由 发表于 2020年10月23日 22:07:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/64501560.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定