java - 带有in子句的sprint namedParameterJdbcTemplate batchUpdate

我正在使用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时，它就起作用了。

请帮忙 :)

最佳答案

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