CODEWITHSUNDEEP
javasqlspringspring-jdbcjdbctemplate
Michael Biniashvili
Michael Biniashvili

Reputation: 610

sprint namedParameterJdbcTemplate batchUpdate with in clause

I am using spring to insert batch records into the DB. But I am getting this error: java.sql.SQLException: Unable to convert between java.util.ArrayList and JAVA_OBJECT. can you please help what am I doing wrong

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); 
}

Error:

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

If I use hard-coded values:

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);

or when I pass only one int it's working.

Please help :)

Upvotes: 3

Views: 1753

Answers (1)

asfmlr
asfmlr

Reputation: 199

Use the SqlParameterSource to define the params so that the query will replace the placeholders SqlParameterSource params = new MapSqlParameterSource("testIds", testIds).addValue("id", id);

Upvotes: 1

Related Questions