Open fmisir opened 1 year ago
Please provide a Minimimal Reproducable Example, preferable as a Github repository. Make sure to include the database, either as an in memory database or if that is not possible using Testcontainers.
If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.
@schauder here is the Minimal Reproducable Example: https://github.com/bci-oss/spring-data-jdbc-1323 Thanks!
Any update on this issue?
It's still not possible to use spring-data-jdbc query with a "IN" containing tuple (spring-data-jdbc version 2.4.8)
The exemple provided can be simplified with simple IN criteria
@Query(""" select p.lastname from person p WHERE (lastname, firstname) IN (:combinations) """ ) List<String> findLastnameWithTupleIn(@Param("combinations") List<String[]> combinations);
Error with postgresql:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select p.lastname from person p WHERE (lastname, firstname) IN (?, ?) ]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY
Errro with H2:
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select p.lastname from person p WHERE (lastname, firstname) IN (?, ?) ]; Serialization failed, cause: "java.io.NotSerializableException: org.h2.jdbc.JdbcArray" [90026-214]; nested exception is org.h2.jdbc.JdbcSQLDataException: Serialization failed, cause: "java.io.NotSerializableException: org.h2.jdbc.JdbcArray" [90026-214]
The workaround is to use directly NamedParameterJdbcTemplate :
namedParameterJdbcTemplate.query("select p.lastname from person p WHERE (lastname, firstname) IN (:combinations)", Map.of("combinations", combinations), myRowMapper))
But we need to implement a custom rowMapper and we can't use the auto mapping providing by spring data jdbc. That is painful for complex aggregate.
@schauder Is there an update to this issue? The problem is still there.
Thanks for your feedback
This issue is still present in Spring Data JDBC 2.4.18.
In my case in conjunction with MariaDB:
org.springframework.dao.InvalidDataAccessApiUsageException: ConnectionCallback; (conn=48692) Array type is not supported; nested exception is java.sql.SQLFeatureNotSupportedException: (conn=48692) Array type is not supported
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96) ~[spring-jdbc-5.3.31.jar:5.3.31]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-5.3.31.jar:5.3.31]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-5.3.31.jar:5.3.31]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1577) ~[spring-jdbc-5.3.31.jar:5.3.31]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:344) ~[spring-jdbc-5.3.31.jar:5.3.31]
at org.springframework.data.jdbc.core.convert.DefaultJdbcTypeFactory.createArray(DefaultJdbcTypeFactory.java:75) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.data.jdbc.core.convert.BasicJdbcConverter.writeJdbcValue(BasicJdbcConverter.java:313) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.convertAndAddParameter(StringBasedJdbcQuery.java:181) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.bindParameters(StringBasedJdbcQuery.java:155) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.execute(StringBasedJdbcQuery.java:136) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.7.18.jar:2.7.18]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.7.18.jar:2.7.18]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:160) ~[spring-data-commons-2.7.18.jar:2.7.18]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139) ~[spring-data-commons-2.7.18.jar:2.7.18]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.31.jar:5.3.31]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.31.jar:5.3.31]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.31.jar:5.3.31]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.31.jar:5.3.31]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.31.jar:5.3.31]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) ~[spring-aop-5.3.31.jar:5.3.31]
at jdk.proxy4/jdk.proxy4.$Proxy86.findAllLeikaleistungVerfahren(Unknown Source) ~[na:na]
at de.chamaeleon.didaexporter.service.VerfahrenExportService.exportVerfahren(VerfahrenExportService.java:73) ~[classes/:na]
at de.chamaeleon.didaexporter.service.DidaExportService.export(DidaExportService.java:67) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.context.event.ApplicationListenerMethodAdapter.doInvoke(ApplicationListenerMethodAdapter.java:344) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.event.ApplicationListenerMethodAdapter.processEvent(ApplicationListenerMethodAdapter.java:229) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.event.ApplicationListenerMethodAdapter.onApplicationEvent(ApplicationListenerMethodAdapter.java:166) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:178) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:171) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:145) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:429) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:386) ~[spring-context-5.3.31.jar:5.3.31]
at org.springframework.boot.context.event.EventPublishingRunListener.ready(EventPublishingRunListener.java:114) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplicationRunListeners.lambda$ready$6(SpringApplicationRunListeners.java:82) ~[spring-boot-2.7.18.jar:2.7.18]
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:120) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:114) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplicationRunListeners.ready(SpringApplicationRunListeners.java:82) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:323) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) ~[spring-boot-2.7.18.jar:2.7.18]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1289) ~[spring-boot-2.7.18.jar:2.7.18]
at de.chamaeleon.didaexporter.DidaExporterApplication.main(DidaExporterApplication.java:10) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:50) ~[spring-boot-devtools-2.7.18.jar:2.7.18]
Caused by: java.sql.SQLFeatureNotSupportedException: (conn=48692) Array type is not supported
at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:281) ~[mariadb-java-client-3.4.0.jar:na]
at org.mariadb.jdbc.export.ExceptionFactory.notSupported(ExceptionFactory.java:333) ~[mariadb-java-client-3.4.0.jar:na]
at org.mariadb.jdbc.Connection.createArrayOf(Connection.java:743) ~[mariadb-java-client-3.4.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.createArrayOf(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.jdbc.core.JdbcTemplate$CloseSuppressingInvocationHandler.invoke(JdbcTemplate.java:1650) ~[spring-jdbc-5.3.31.jar:5.3.31]
at jdk.proxy2/jdk.proxy2.$Proxy52.createArrayOf(Unknown Source) ~[na:na]
at org.springframework.data.jdbc.core.convert.DefaultJdbcTypeFactory.lambda$createArray$1(DefaultJdbcTypeFactory.java:75) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:336) ~[spring-jdbc-5.3.31.jar:5.3.31]
... 49 common frames omitted
The following query doesn't work any more with spring-data-jdbc versions 2.4.1 & 2.4.2. It works in version 2.4.0.
The invocation of the method is like this:
I tested it with PostgreSQL and H2.
PostgreSQL Version: postgres:13.6-alpine Driver-Version: 42.5.0 Error:
H2 Version: 2.1.214 Error:
The issue seams to be here: https://github.com/spring-projects/spring-data-relational/compare/2.4.0...2.4.2#diff-b8453546f8ae7cc224005f536bb3be8a0c341186f2cf38b2b34d958cc769ac4cR171
The
if
condition should evaluate tofalse
forIterable
s containing array objects.