SpEL support for @Query annotation [DATAJDBC-397]

[spring-projects-issues]

sopeLi opened DATAJDBC-397 and commented

 

@Modifying
@Query(value = "update orders o set o.status = #{#order.status}")
Integer updateOrder(@Param("order") Order order);

 

I want to get the parameters of the order by means of order entity then update.
 

---

Reference URL: https://stackoverflow.com/questions/57040030/spring-data-jdbc-use-query-comments-how-to-match-the-parameters-registered-for

Issue Links:

• DATAJDBC-468 Supporting SQL Function around inserting value

• DATAJDBC-526 Ability to change default sql type mappings

• DATAJDBC-605 Parameter concatenation in queries using named parameters

5 votes, 4 watchers

[spring-projects-issues]

Mauro Molinari commented

It was a surprise to discover that, contrary to for instance Spring Data JPA, Spring Data JDBC does not support SpEL in @Query annotation value.

In particular, this doesn't work, even if Spring Security is configured correctly:

@Query("SELECT * FROM Credit WHERE customer = ?#{ principal.id }")
Optional<Credit> findByCurrentCustomer();

Documentation at https://docs.spring.io/spring-security/site/docs/5.3.2.RELEASE/reference/html5/#data
is not very clear, because it talks about "Spring Data" generally.
However if I try to execute the above query the following exception is thrown:

org.springframework.dao.InvalidDataAccessApiUsageException: SQL [SELECT * FROM Credit WHERE customer = ?#{ principal.id }]: given 1 parameters but expected 0
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.<init>(PreparedStatementCreatorFactory.java:220) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.<init>(PreparedStatementCreatorFactory.java:198) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory.newPreparedStatementCreator(PreparedStatementCreatorFactory.java:172) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:419) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:392) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:236) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$1(AbstractJdbcQuery.java:115) ~[spring-data-jdbc-2.0.1.RELEASE.jar:2.0.1.RELEASE]
	at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.execute(StringBasedJdbcQuery.java:85) ~[spring-data-jdbc-2.0.1.RELEASE.jar:2.0.1.RELEASE]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195) ~[spring-data-commons-2.3.1.RELEASE.jar:2.3.1.RELEASE]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152) ~[spring-data-commons-2.3.1.RELEASE.jar:2.3.1.RELEASE]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130) ~[spring-data-commons-2.3.1.RELEASE.jar:2.3.1.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367) ~[spring-tx-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.7.RELEASE.jar:5.2.7.RELEASE]
	at com.sun.proxy.$Proxy115.findByCurrentCustomer(Unknown Source) ~[?:?]
	at it.dcssrl.credito.controller.CreditController.getBalance(CreditController.java:28) ~[main/:?]
[...]

suggesting Spring Data JDBC is not recognizing the SpEL expression as a valid resolver for the parameter