从1.5升级后，Spring Boot 2 @Query命名参数绑定值解析混乱 [英] Spring boot 2 @Query named parameter binding value resolution messes up after upgrade from 1.5
问题描述
我们使用SpringBoot 1.5进行以下工作查询:
We have the following working query using SpringBoot 1.5:
@Query(value = "SELECT DISTINCT c FROM Customer c INNER JOIN c.industry i WHERE " +
"c.role IN :roleFilter " +
"AND (:#{#industryFilter.size()} = 1 OR i.id IN :industryFilter) " +
"AND (:searchString IS NULL " +
"OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') " +
"OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) " +
"AND (:includeDeleted = true OR c.deletedDate is NULL)",
countQuery = "SELECT COUNT(DISTINCT c) FROM Customer c INNER JOIN c.industry i WHERE " +
"c.role IN :roleFilter AND " +
"(:#{#industryFilter.size()} = 1 OR i.id IN :industryFilter) " +
"AND (:searchString IS NULL " +
"OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') " +
"OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) " +
"AND (:includeDeleted = true OR c.deletedDate is NULL)")
Page<Customer> findCustomers(@Param("roleFilter") Set<Role> roleFilter,
@Param("industryFilter") Set<String> industryFilter,
@Param("searchString") String searchString,
@Param("includeDeleted") boolean includeDeleted, Pageable pageable);
请注意我们如何将输入传递给LIKE:CONCAT('%', :searchString, '%')
Please note how we pass the input to the LIKE: CONCAT('%', :searchString, '%')
从springBootVersion = '1.5.17.RELEASE'升级到springBootVersion = '2.1.3.RELEASE'(我们使用Gradle)后，该查询将在运行时失败，并发生以下异常:
After upgrading from springBootVersion = '1.5.17.RELEASE' to springBootVersion = '2.1.3.RELEASE' (we use Gradle) that query will fail at runtime with an exception:
org.hibernate.QueryException:未绑定命名参数:includeDeleted
用%:searchString%替换CONCAT('%', :searchString, '%')可以解决此问题.
Replacing CONCAT('%', :searchString, '%') with %:searchString% fixes the problem.
我的问题是:为什么?
通过进入调试模式并遵循完整的调用栈，我可以看到正确地从方法调用正确检索到的参数，如在第205行的JdkDynamicAopProxy中观察到的那样，进行了调用Object[] argsToUse = AopProxyUtils.adaptArgumentsIfNecessary(method, args);，结果是:
By going into debug mode and following the full callstack, I could see the parameters being correctly retrieved from the method invocation as observed in JdkDynamicAopProxy at line 205 makes a call Object[] argsToUse = AopProxyUtils.adaptArgumentsIfNecessary(method, args); that results in:
argsToUse = {Object[5]@15562}
0 = {HashSet@15491} size = 4
1 = {HashSet@15628} size = 1
2 = null
3 = {Boolean@15629} false
4 = {PageRequest@15490} "Page request [number: 0, size 20, sort: name: ASC,name2: ASC]"
到目前为止，一切都很好.然后，我们继续操作，调用方法也已正确解析:
So far so good. Then, we keep going and the method to call is also correctly resolved:
parameterTypes = {Class[5]@15802}
0 = {Class@198} "interface java.util.Set"
1 = {Class@198} "interface java.util.Set"
2 = {Class@311} "class java.lang.String"
3 = {Class@15811} "boolean"
4 = {Class@9875} "interface org.springframework.data.domain.Pageable"
然后，我们走得更远，然后到达RepositoryFactorySupport行599，调用private Object doInvoke(MethodInvocation invocation) throws Throwable，该代码使用内部类public class QueryExecutorMethodInterceptor implements MethodInterceptor中的private final Map<Method, RepositoryQuery> queries;(我不确定何时/如何创建和填充此变量)，包含我的存储库界面中所有用@Query注释的查询.
Then we go a bit further and we get to RepositoryFactorySupport line 599 calling private Object doInvoke(MethodInvocation invocation) throws Throwable which uses private final Map<Method, RepositoryQuery> queries; from the inner class public class QueryExecutorMethodInterceptor implements MethodInterceptor (I am unsure when/how was this variable created and populated), which contains all the queries annotated with @Query in my repository interface.
对于我们的特定情况，它包含一个与我正在调用的查询( findCustomers )匹配的条目(最后一个):
For our specific case, it contains an entry (last one) that matches the query I am invoking (findCustomers):
queries = {HashMap@16041} size = 3
0 = {HashMap$Node@16052} "public abstract com.swisscom.psp.domain.Customer com.swisscom.psp.repository.CustomerRepository.getOne(java.lang.String)" ->
1 = {HashMap$Node@16055} "public abstract boolean com.swisscom.psp.repository.CustomerRepository.existsWithRole(java.lang.String,java.util.Set)" ->
2 = {HashMap$Node@16058} "public abstract org.springframework.data.domain.Page com.swisscom.psp.repository.CustomerRepository.findCustomers(java.util.Set,java.util.Set,java.lang.String,boolean,org.springframework.data.domain.Pageable)" ->
展开该条目，我可以看到错误的出处，:includeDeleted命名参数的绑定根本不存在:
And expanding that entry I can see where the error comes from, the binding for the :includeDeleted named parameter is simply not there:
value = {SimpleJpaQuery@16060}
query = {ExpressionBasedStringQuery@16069}
query = "SELECT DISTINCT c FROM Customer c INNER JOIN c.industry i WHERE c.role IN :roleFilter AND (:__$synthetic$__1 = 1 OR i.id IN :industryFilter) AND (:searchString IS NULL OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) AND (:includeDeleted = true OR c.deletedDate is NULL)"
bindings = {ArrayList@16089} size = 6
0 = {StringQuery$InParameterBinding@16092} "ParameterBinding [name: roleFilter, position: null, expression: null]"
1 = {StringQuery$ParameterBinding@16093} "ParameterBinding [name: __$synthetic$__1, position: null, expression: #industryFilter.size()]"
2 = {StringQuery$InParameterBinding@16094} "ParameterBinding [name: industryFilter, position: null, expression: null]"
3 = {StringQuery$ParameterBinding@16095} "ParameterBinding [name: searchString, position: null, expression: null]"
4 = {StringQuery$ParameterBinding@16096} "ParameterBinding [name: searchString, position: null, expression: null]"
5 = {StringQuery$ParameterBinding@16097} "ParameterBinding [name: searchString, position: null, expression: null]"
现在，我已经有了前面提到的修复程序，但是我仍然非常想知道以下内容，以供将来参考:
Now, I have the fix as mentioned earlier, but I would still very much like to know the following for future reference:
- 何时以及如何创建和填充
private final Map<Method, RepositoryQuery> queries变量? - 究竟是什么导致此错误?我在升级过程中错过了什么吗?我是否正在使用/混合已弃用的逻辑/错误的逻辑，并且应该进一步更改代码?
- when and how is the
private final Map<Method, RepositoryQuery> queriesvariable created and populated? - what exactly is causing this error? Did I miss something in the upgrade process? Am I using/mixing deprecated logic/wrong logic and should change the code further?
我们的数据库是MariaDB 10.1.36
Our DB is MariaDB 10.1.36
在所有发生此行为的地方(在某些地方仍然发生)，unbound参数始终是最后一个
In all the places where this behaviour occurred (in some it still occurs), the unbound parameter is always the last one
升级后其他人也有类似的行为，为什么会发生这种情况? 参考
Someone else also has a similar behaviour after the upgrade, why does this happen? reference
参考而且这种怪异的行为也有报道.足够有趣的是，如果我将已经连接的输入传递给:searchString(例如:％SOMETHING％)，并且如果我留下％:searchString％，我确实会得到异常，但是我没有得到异常.是的，最后移动这些参数可以解决我在绑定时遇到的一些错误.
reference and also this weird behaviour has been reported. Interesting enough, I do not get the exception IF I pass already concatenated input to :searchString (eg: %SOMETHING%) and I do get the exception if I leave %:searchString% instead. And yes, moving those parameters in the end solves some errors I had with binding.
也许与错误相关?
显然发生了一些奇怪的事情，因此:此绑定解析是如何发生的?
Clearly there is something strange going on, so: how does this binding resolution happen exactly?
提前感谢，祝您有愉快的一天
Thanks in advance and have a nice day
推荐答案
实际上，据我所知，您的两种方法都不是在此处使用通配符占位符处理LIKE的正确方法.相反，LIKE表达式应为:
Actually, as far as I know, neither of your two approaches is the correct one to use here for handling LIKE with a wildcard placeholder. Instead, the LIKE expression should be:
LIKE :searchString
对于此参数:searchString，您应该进行绑定:
To this parameter :searchString you should be binding:
String searchString = "bananas";
String param = "%" + searchString + "%";
// then bind param to :searchString
也就是说，将整个字符串与%通配符绑定在一起.然后，让数据库担心如何对其进行转义.
That is, you bind the entire string, with the % wildcard, together. Then, let the database worry about how to escape it.
这篇关于从1.5升级后，Spring Boot 2 @Query命名参数绑定值解析混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
