Support list in IN clause in NamedParameterJdbcTemplate.batchUpdate [SPR-17402]

[spring-projects-issues]

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:

• NamedParameterJdbcTemplate batchUpdate returns an array of size 1 when the batchArgs passed is an empty array [SPR-17476] #22008 NamedParameterJdbcTemplate batchUpdate returns an array of size 1 when the batchArgs passed is an empty array

Referenced from: pull request #1997, and commits a3d763d

2 votes, 4 watchers

[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 {

[spring-projects-issues]

Fedor Bobin commented

#1997

[spring-projects-issues]

Fedor Bobin commented

Juergen Hoeller any updates?

[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.

[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.

[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])".

 

[spring-projects-issues]

Fedor Bobin commented

Yes. It will work (if your batches has same size)

[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.