java - sprint namedParameterJdbcTemplate batchUpdate with in 子句

Question

我正在使用 spring 将批处理记录插入到数据库中。但我收到此错误:java.sql.SQLException:无法在 java.util.ArrayList 和 JAVA_OBJECT 之间转换。你能帮我看看我做错了什么吗

private static final String INSERT_BATCH_QUERY ="INSERT INTO TEST_TABLE (ID,TEST_ID) select :id,  substring((SELECT ', ' + USERS.COMMON_NAME FROM USERS WHERE USERS.ID IN ( :testIds ) FOR XML PATH('')), 2, 4000);


public class Test{

private int id;
private List<Intenger> testIds;
    //**getters and setters**//
}

public static void Main(String... args){
        List<Test> myTe=new ....
        SqlParameterSource[] sqlParameterSource = SqlParameterSourceUtils.createBatch(myTe.toArray());
        result = namedParameterJdbcTemplate.batchUpdate(INSERT_BATCH_QUERY, sqlParameterSource); 
}

错误:

Caused by: java.sql.SQLException: Unable to convert between java.util.ArrayList and JAVA_OBJECT.
    at net.sourceforge.jtds.jdbc.Support.convert(Support.java:633)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:590)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:907)
    at com.jolbox.bonecp.PreparedStatementHandle.setObject(PreparedStatementHandle.java:909)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:426)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166)
    at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:65)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access$000(NamedParameterBatchUpdateUtils.java:32)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatchUpdateUtils.java:48)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1010)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:989)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
    ... 26 more

如果我使用硬编码值:

private static final String INSERT_BATCH_QUERY ="INSERT INTO TEST_TABLE (ID,TEST_ID) select :id,  substring((SELECT ', ' + USERS.COMMON_NAME FROM USERS WHERE USERS.ID IN ( 1234,654,55 ) FOR XML PATH('')), 2, 4000);

或者当我只传递一个 int 时它起作用了。

请帮忙:)

Answer 1

使用 SqlParameterSource 定义参数，以便查询将替换占位符SqlParameterSource params = new MapSqlParameterSource("testIds", testIds).addValue("id", id);