存储库-在本机查询中按订单排序不起作用 [英] Repository - order by in native query not working

问题描述

我有一个spring数据JPA存储库(位于postgres db上)，我有时需要使用nativeQuery = true选项使用本机查询.

但是，在当前情况下，我需要在订单字段中进行传递，并且这样做是这样的:

通话.

targetStatusHistoryRepository.findSirenAlarmTimeActivation([uuid,uuid2],"activation_name DESC", 0, 10)

..回购方法

@Query(
        nativeQuery = true,
        value = """select
                     a.name as activation_name,
                     min(transition_from_active_in_millis),
                     max(transition_from_active_in_millis),
                     avg(transition_from_active_in_millis) from target_status_history t, activation_scenario a
                     where t.activation_uuid=a.activation_scenario_id and t.transition_from_active_in_millis > 0 and t.activation_uuid in (:activationUUIDs) group by a.name,t.activation_uuid
                     order by :orderClause offset :offset limit :limit """
)
List<Object[]> findSirenAlarmTimeActivation(@Param("activationUUIDs") List<UUID> activationUUIDs,
                                                              @Param("orderClause") String orderClause, @Param("offset") int offset, @Param("limit") int limit )

我用DESC写了一个单元测试，然后用ASC调用，反之亦然，看来第一个调用是什么，第二个给出了相同的结果.

解决方案

如果这是一条准备好的语句，并且在ORDER BY子句中提供了绑定值，则该值有效，但是...

提供的绑定值不会解释为SQL文本.也就是说，该值将被视为只是一个值(如文字字符串).它不会被视为列名或ASC或DESC关键字.

在您的语句上下文中，为:orderClause绑定占位符提供一个值，其作用将与您编写ORDER BY 'some literal'相同.

那实际上根本没有对行进行任何排序.

(至少在我用于DB2，Teradata，Oracle，SQL Server，MySQL和MariaDB(JDBC，Perl DBI，ODBC，Pro/C等)的每个SQL客户端库中都是如此

(MyBatis确实提供了一种方便的机制来在SQL文本中执行变量替换，在准备文本之前动态更改SQL文本，但是这些替换是在准备语句之前处理的，并且不会在语句中变成绑定占位符)

在ORDER BY子句中使用一些精心设计的表达式可以得到一些动态"排序.例如，我们可以使静态SQL文本如下所示:

  ORDER BY CASE WHEN :sort_param = 'name ASC'  THEN activation_name END ASC
         , CASE WHEN :sort_param = 'name DESC' THEN activation_name END DESC

(这里的SQL文本不是动态的，实际上是静态的，就像我们写的一样.

 ORDER BY expr1 ASC
        , expr1 DESC

窍门"是ORDER BY子句中的表达式有条件地返回每一行中某些列的值，或者它们正在根据值返回文字(在上面的示例中为文字NULL).绑定值的值，在执行时评估.

最终的效果是我们可以动态"获得以下任一效果:

 ORDER BY activation_name ASC, NULL DESC

或

 ORDER BY NULL ASC, activation_name DESC

或

 ORDER BY NULL ASC, NULL DESC

取决于我们为:sort_param占位符提供什么值.

I have a spring data JPA repository (on a postgres db) and from time to time I need to use native queries using the nativeQuery = true option.

However in my current situation I need to pass in an order field and am doing so like this:

the call..

targetStatusHistoryRepository.findSirenAlarmTimeActivation([uuid,uuid2],"activation_name DESC", 0, 10)

.. the repo method

@Query(
        nativeQuery = true,
        value = """select
                     a.name as activation_name,
                     min(transition_from_active_in_millis),
                     max(transition_from_active_in_millis),
                     avg(transition_from_active_in_millis) from target_status_history t, activation_scenario a
                     where t.activation_uuid=a.activation_scenario_id and t.transition_from_active_in_millis > 0 and t.activation_uuid in (:activationUUIDs) group by a.name,t.activation_uuid
                     order by :orderClause offset :offset limit :limit """
)
List<Object[]> findSirenAlarmTimeActivation(@Param("activationUUIDs") List<UUID> activationUUIDs,
                                                              @Param("orderClause") String orderClause, @Param("offset") int offset, @Param("limit") int limit )

I wrote a unit test with a DESC and then a ASC call and vice versa, and it seems what ever the first call is, the second gives the same result.

解决方案

If that's a prepared statement, and that's a bind value being supplied in the ORDER BY clause, that is valid, BUT...

The bind value supplied won't be interpreted as SQL text. That is, the value will be seen as just a value (like a literal string). It won't be seen as a column name, or an ASC or DESC keyword.

In the context of your statement, supplying a value for the :orderClause bind placeholder, that's going to have the same effect as if you had written ORDER BY 'some literal'.

And that's not really doing any ordering of the rows at all.

(This is true at least in every SQL client library I've used with DB2, Teradata, Oracle, SQL Server, MySQL, and MariaDB (JDBC, Perl DBI, ODBC, Pro/C, et al.)

(MyBatis does provide a convenient mechanism for doing variable substitution within the SQL text, dynamically changing the SQL text before it's prepared, but those substitutions are handled BEFORE the statement is prepared, and don't turn into bind placeholders in the statement.)

It is possible to get some modicum of "dynamic" ordering with some carefully crafted expressions in the ORDER BY clause. For example, we can have our static SQL text be something like this:

  ORDER BY CASE WHEN :sort_param = 'name ASC'  THEN activation_name END ASC
         , CASE WHEN :sort_param = 'name DESC' THEN activation_name END DESC

(The SQL text here isn't dynamic, it's actually static, it's as if we had written.

 ORDER BY expr1 ASC
        , expr1 DESC

The "trick" is that the expressions in the ORDER BY clause are conditionally returning either the value of some column from each row, or they are returning a literal (in the example above, the literal NULL), depending on the value of a bind value, evaluated at execution time.

The net effect is that we can "dynamically" get the effect of either:

 ORDER BY activation_name ASC, NULL DESC

or

 ORDER BY NULL ASC, activation_name DESC

or

 ORDER BY NULL ASC, NULL DESC

depending on what value we supply for the :sort_param placeholder.

这篇关于存储库-在本机查询中按订单排序不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！