Fedor Bobin opened SPR-17402 and commented
It will be nice to allow do batch queries with list in IN clause. Now it is not supported because query is expanded but NamedParameterBatchUpdateUtils does not aware of it.
Issue Links: - #22008 NamedParameterJdbcTemplate batchUpdate returns an array of size 1 when the batchArgs passed is an empty array
Referenced from: pull request https://github.com/spring-projects/spring-framework/pull/1997, and commits https://github.com/spring-projects/spring-framework/commit/a3d763d137f954e86378dc2fdce31f06a8cfc0e3
2 votes, 4 watchers
Comment From: spring-projects-issues
Fedor Bobin commented
I have a fix. I will not have access to GitHub till Monday. Then I will make a PR with this patch:
Index: spring-jdbc/src/main/java/org/springframework/jdbc/core/PreparedStatementCreatorFactory.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- spring-jdbc/src/main/java/org/springframework/jdbc/core/PreparedStatementCreatorFactory.java (revision aa656c47b87b75f5bf28f864c28d8a220ac25fbc)
+++ spring-jdbc/src/main/java/org/springframework/jdbc/core/PreparedStatementCreatorFactory.java (revision 475e623883a6f07419242b54d61a8f6758b9fe7a)
@@ -176,6 +176,9 @@
sqlToUse, params != null ? Arrays.asList(params) : Collections.emptyList());
}
+ public String getSql() {
+ return sql;
+ }
/**
* PreparedStatementCreator implementation returned by this class.
Index: spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterBatchUpdateUtils.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterBatchUpdateUtils.java (revision aa656c47b87b75f5bf28f864c28d8a220ac25fbc)
+++ spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterBatchUpdateUtils.java (revision 475e623883a6f07419242b54d61a8f6758b9fe7a)
@@ -22,6 +22,8 @@
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BatchUpdateUtils;
import org.springframework.jdbc.core.JdbcOperations;
+import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
+import org.springframework.jdbc.core.PreparedStatementSetter;
/**
* Generic utility methods for working with JDBC batch statements using named parameters.
@@ -32,22 +34,17 @@
*/
public class NamedParameterBatchUpdateUtils extends BatchUpdateUtils {
- public static int[] executeBatchUpdateWithNamedParameters(final ParsedSql parsedSql,
- final SqlParameterSource[] batchArgs, JdbcOperations jdbcOperations) {
+ public static int[] executeBatchUpdateWithNamedParameters(ParsedSql parsedSql, final PreparedStatementCreatorFactory pscf,
+ final SqlParameterSource[] batchArgs, JdbcOperations jdbcOperations) {
- if (batchArgs.length <= 0) {
- return new int[] {0};
- }
-
- String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, batchArgs[0]);
return jdbcOperations.batchUpdate(
- sqlToUse,
+ pscf.getSql(),
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null);
- int[] columnTypes = NamedParameterUtils.buildSqlTypeArray(parsedSql, batchArgs[i]);
- setStatementParameters(values, ps, columnTypes);
+ PreparedStatementSetter preparedStatementSetter = pscf.newPreparedStatementSetter(values);
+ preparedStatementSetter.setValues(ps);
}
@Override
public int getBatchSize() {
Index: spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.java (revision aa656c47b87b75f5bf28f864c28d8a220ac25fbc)
+++ spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.java (revision 475e623883a6f07419242b54d61a8f6758b9fe7a)
@@ -352,8 +352,13 @@
@Override
public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
+ if (batchArgs.length <= 0) {
+ return new int[] {0};
+ }
+ ParsedSql parsedSql = getParsedSql(sql);
+ PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, batchArgs[0], null);
return NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(
- getParsedSql(sql), batchArgs, getJdbcOperations());
+ parsedSql, pscf, batchArgs, getJdbcOperations());
}
@@ -389,14 +394,20 @@
@Nullable Consumer<PreparedStatementCreatorFactory> customizer) {
ParsedSql parsedSql = getParsedSql(sql);
+ PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, paramSource, customizer);
+ Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
+ return pscf.newPreparedStatementCreator(params);
+ }
+
+ protected PreparedStatementCreatorFactory getPreparedStatementCreatorFactory(ParsedSql parsedSql, SqlParameterSource paramSource,
+ @Nullable Consumer<PreparedStatementCreatorFactory> customizer){
String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
if (customizer != null) {
customizer.accept(pscf);
}
- Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
- return pscf.newPreparedStatementCreator(params);
+ return pscf;
}
/**
Index: spring-jdbc/src/test/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplateTests.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- spring-jdbc/src/test/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplateTests.java (revision aa656c47b87b75f5bf28f864c28d8a220ac25fbc)
+++ spring-jdbc/src/test/java/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplateTests.java (revision 475e623883a6f07419242b54d61a8f6758b9fe7a)
@@ -36,6 +36,7 @@
import org.junit.Test;
import org.junit.rules.ExpectedException;
+import org.mockito.InOrder;
import org.springframework.jdbc.Customer;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
@@ -460,6 +461,41 @@
verify(preparedStatement, atLeastOnce()).close();
verify(connection, atLeastOnce()).close();
}
+
+ @Test
+ public void testBatchUpdateWithInClause() throws Exception {
+ @SuppressWarnings("unchecked")
+ Map<String, Object>[] parameters = new Map[2];
+ parameters[0] = Collections.singletonMap("ids", Arrays.asList(1, 2));
+ parameters[1] = Collections.singletonMap("ids", Arrays.asList(3, 4));
+
+ final int[] rowsAffected = new int[] {1, 2};
+ given(preparedStatement.executeBatch()).willReturn(rowsAffected);
+ given(connection.getMetaData()).willReturn(databaseMetaData);
+
+ JdbcTemplate template = new JdbcTemplate(dataSource, false);
+ namedParameterTemplate = new NamedParameterJdbcTemplate(template);
+
+ int[] actualRowsAffected = namedParameterTemplate.batchUpdate(
+ "delete sometable where id in (:ids)",
+ parameters
+ );
+
+ assertEquals("executed 2 updates", 2, actualRowsAffected.length);
+
+ InOrder inOrder = inOrder(preparedStatement);
+
+ inOrder.verify(preparedStatement).setObject(1, 1);
+ inOrder.verify(preparedStatement).setObject(2, 2);
+ inOrder.verify(preparedStatement).addBatch();
+
+ inOrder.verify(preparedStatement).setObject(1, 3);
+ inOrder.verify(preparedStatement).setObject(2, 4);
+ inOrder.verify(preparedStatement).addBatch();
+
+ inOrder.verify(preparedStatement, atLeastOnce()).close();
+ verify(connection, atLeastOnce()).close();
+ }
@Test
public void testBatchUpdateWithSqlParameterSourcePlusTypeInfo() throws Exception {
Comment From: spring-projects-issues
Fedor Bobin commented
https://github.com/spring-projects/spring-framework/pull/1997
Comment From: spring-projects-issues
Fedor Bobin commented
Juergen Hoeller any updates?
Comment From: spring-projects-issues
Juergen Hoeller commented
I'll consider this for 5.1.3 still. That said, if there is a risk for subtle side effects, we might have to turn this into a 5.2 topic instead.
Comment From: spring-projects-issues
Juergen Hoeller commented
I went with a more extensive refactoring, inlining the batch update code into (NamedParameter)JdbcTemplate itself and deprecating (NamedParameter)BatchUpdateUtils accordingly.
Comment From: spring-projects-issues
Yanming Zhou commented
Does NamedParameterJdbcTemplate supports Array/Collection with IN clause like this now?
String sql = "select * from users where id in (:ids)";
Map<String,Object> sqlParameterSource = Collections.singletonMap("ids", new Integer[]{1, 2, 3});
namedParameterJdbcTemplate.query(sql, sqlParameterSource, rowCallbackHandler);
currently my workaround is expand sql to "select * from users where id in (:ids[0],:ids[1],:ids[2])".
Comment From: spring-projects-issues
Fedor Bobin commented
Yes. It will work (if your batches has same size)
Comment From: spring-projects-issues
Yanming Zhou commented
Fedor Bobin It's seems not working, It's querying not updating, It's one single sql not batch update.
Comment From: niloay
If the Array/Collection size is different, It's seems not working.
Comment From: sbrannen
@niloay, this issue was closed 2.5 years ago.
If you feel you have discovered a bug, please open a new issue.