Spring boot 2 @Query 命名参数绑定值解析从 1.5 升级后搞砸了 [英] 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, RepositoryQuery>查询； 来自内部类 public class QueryExecutorMethodInterceptor 实现 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.

可能与错误有关?

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

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.

这篇关于Spring boot 2 @Query 命名参数绑定值解析从 1.5 升级后搞砸了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！