Iterable argument for NamedParameterJdbcTemplate not correctly processed

[Serranya]

Affects: spring-jdbc 5.3.3

---

We have the following code:

 return jdbcTemplate.query("SELECT whatever FROM table WHERE id IN (:ids)",
                new MapSqlParameterSource("ids",
                        new SqlParameterValue(BIGINT, orders.stream().map(Order::getOrderId)
                                                            .collect(Collectors.toSet()))),
                rs -> {
                   // elided...
                });

... Spring correctly identifies the parameter value as Iterable and replaces the :ids with the correct number of question marks.

But here

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/PreparedStatementCreatorFactory.java

Line 268 in d7e05aa

if (in instanceof Iterable && declaredParameter.getSqlType() != Types.ARRAY) {

Spring does not correctly identify the value as an Iterable. This is because a few lines earlier

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/PreparedStatementCreatorFactory.java

Lines 254 to 258 in d7e05aa

	if (in instanceof SqlParameterValue) {
	SqlParameterValue paramValue = (SqlParameterValue) in;
	in = paramValue.getValue();
	declaredParameter = paramValue;
	}

... Spring unwraps the SqlParameterValue, but its content is just another SqlParameterValue which contains the actual Iterable.

We can use the following code to circumvent the problem:

 return jdbcTemplate.query("SELECT whatever FROM table WHERE id IN (:ids)",
                new MapSqlParameterSource()
                    .addValue("ids", orders.stream().map(Order::getOrderId)
                                                            .collect(Collectors.toSet())),
                rs -> {
                   // elided...
                });

[sbrannen]

Spring unwraps the SqlParameterValue, but its content is just another SqlParameterValue which contains the actual Iterable.

Are you encountering an exception?

If so, can you please provide a copy of the stack trace?

If you're not seeing an exception, what type of error or failure are you experiencing?

[Serranya]

The following exception is throw:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "whatever",               FROM "table"        WHERE  "id" IN (?, ?, ?, ?)        GROUP BY "orderId"]; nested exception is org.postgresql.util.PSQLException: Die Typwandlung für eine Instanz von java.util.HashSet nach long ist nicht möglich.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:738)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:169)
    at de.solutionsdirekt.api.ordertaking.order.OrderDao.getLastRenderPassForOrders(OrderDao.java:389)
    at de.solutionsdirekt.api.ordertaking.order.OrderDao$$FastClassBySpringCGLIB$$42a5ff74.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    at de.solutionsdirekt.api.ordertaking.order.OrderDao$$EnhancerBySpringCGLIB$$8fdffaa4.getLastRenderPassForOrders(<generated>)
    at de.solutionsdirekt.api.ordertaking.pipeline.renderering.OrderRenderingScheduler.renderOrders(OrderRenderingScheduler.java:45)
    at de.solutionsdirekt.api.ordertaking.pipeline.renderering.OrderRenderingScheduler$$FastClassBySpringCGLIB$$9ad68cb2.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    at de.solutionsdirekt.api.ordertaking.pipeline.renderering.OrderRenderingScheduler$$EnhancerBySpringCGLIB$$1181f783.renderOrders(<generated>)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.runAndReset$$$capture(FutureTask.java:305)
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java)
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: Die Typwandlung für eine Instanz von java.util.HashSet nach long ist nicht möglich.
    at org.postgresql.jdbc.PgPreparedStatement.cannotCastException(PgPreparedStatement.java:929)
    at org.postgresql.jdbc.PgPreparedStatement.cannotCastException(PgPreparedStatement.java:923)
    at org.postgresql.jdbc.PgPreparedStatement.castToLong(PgPreparedStatement.java:816)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:557)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:935)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:414)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:231)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:146)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:283)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:241)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
    ... 43 common frames omitted

In short: postgres is complaining that it can't convert a HashSet to a long.

[sbrannen]

Thanks for the prompt feedback. That certainly sheds more light on the subject.

[quaff]

@Serranya Would you verify my PR #26471 ?

[Serranya]

@quaff I ran into the following problem while trying to build your branch:

serra@serra-vbox ~/d/spring-framework (GH-26467) [1]> LANG=c gradle build
Starting a Gradle Daemon, 2 incompatible Daemons could not be reused, use --status for details

> Task :spring-oxm:genJaxb
[ant:javac] : warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
[ant:javac] warning: [options] bootstrap class path not set in conjunction with -source 8
[ant:javac] 1 warning

> Task :spring-jdbc:checkstyleMain
[ant:checkstyle] [ERROR] /home/serra/dev/spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterUtils.java:353:33: '}' at column 5 should be alone on a line. [RightCurly]

> Task :spring-jdbc:checkstyleMain FAILED

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':spring-jdbc:checkstyleMain'.
> Checkstyle rule violations were found. See the report at: file:///home/serra/dev/spring-framework/spring-jdbc/build/reports/checkstyle/main.html
  Checkstyle files with violations: 1
  Checkstyle violations by severity: [error:1]


* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

* Get more help at https://help.gradle.org

Deprecated Gradle features were used in this build, making it incompatible with Gradle 7.0.
Use '--warning-mode all' to show the individual deprecation warnings.
See https://docs.gradle.org/6.8.1/userguide/command_line_interface.html#sec:command_line_warnings

BUILD FAILED in 1m 50s
199 actionable tasks: 88 executed, 30 from cache, 81 up-to-date

A build scan was not published as you have not authenticated with server 'ge.spring.io'.

A coworker on windows machine has the same problem.

Im trying with -x checkstyleMain for now.

[quaff]

checkstyle fixed now.

[jhoeller]

Superseded by #26471.