SQLFunctionTemplate不应用查询参数的顺序

问题描述

我将问题简化为这个简单的模板:

I reduced the problem to this simple template :

//I extend the PostgreSQL dialect because I need a non-standard feature.
public class MyDialect extends PostgreSQL82Dialect {

  public MyDialect() {
    //With PostgreSQL, "date2 - date1" returns the number of days between the 2 given dates.
    registerFunction("date_diff", new SQLFunctionTemplate(StandardBasicTypes.LONG, " ((?2) - (?1)) "));
  }
}

在此请求中，我注意到Hibernate并不关心((?2) - (?1))

In this request, I noticed that Hibernate does not care about the number after ? in ((?2) - (?1))

因此，如果我使用:

Expression<Date> date1 = ...
Expression<Date> date2 = ...
em.getCriteriaBuilder().function("date_diff", Integer.class, date1, date2);

该调用将返回(date1 - date2)的结果，但我曾期望(date2 - date1).

the call will return the result of (date1 - date2) but I had expected (date2 - date1).

是错误还是功能?给参数加数字有什么意义?

Is it a bug or a feature? What is the point of giving a number to the parameters?

推荐答案

我认为问题出在参数绑定上.尝试向SQL Server注册DATE_ADD的函数时遇到类似的问题.这是我的方法调用:

I think the problem lies with parameter binding. I had a similar issue when trying to register a function for DATE_ADD with SQL Server. Here was my method call:

registerFunction("addminutes", new TestSqlFunctionTemplate(TimeMillisType.INSTANCE, "DATEADD(MINUTE, ?2, ?1)"));

浏览 TemplateRenderer的源代码我发现问题在于如何呈现SQL字符串.向render函数传递了要发送的参数列表，但是由于绑定了参数，因此向其发送了?"列表指示绑定参数的字符串.在我的示例中使用带有绑定参数的查询时，render函数的输出为:

After digging through the source code for TemplateRenderer I found the issue lies with how the SQL string is rendered. The render function is passed a list of the arguments to send, but since the parameters are bound it is sent a List of "?" Strings that indicate the bound parameter. The output for the render function when using a query with bound parameters in my example was:

DATEADD(MINUTE, ?, ?)

没有给出顺序的指示.我正在寻找替代解决方案，但到目前为止还没有发现任何问题.

Which gives no indication of the order. I'm looking for an alternate solution, but I haven't come across anything as of yet.

这篇关于SQLFunctionTemplate不应用查询参数的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！