从 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

将 CONCAT('%', :searchString, '%') 替换为 %: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 它使用 private final Map<Method, 存储库查询>查询； 来自内部类 public class QueryExecutorMethodInterceptor implements MethodInterceptor(我不确定这个变量是何时/如何创建和填充的)，其中包含所有用 @Query<注释的查询/code> 在我的存储库界面中.

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:

1. 何时以及如何private final Map;查询变量创建和填充?
2. 究竟是什么导致了这个错误?我在升级过程中错过了什么吗?我是否使用/混合了已弃用的逻辑/错误的逻辑，是否应该进一步更改代码?

我们的数据库是 MariaDB 10.1.36

Our DB is MariaDB 10.1.36

在所有发生此行为的地方(在某些地方仍然发生)，未绑定参数始终是最后一个

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.

可能与bug有关?

显然发生了一些奇怪的事情，所以:这种绑定解析究竟是如何发生的?

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屋！