Repository - order by in native query not working

Question

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.

Answer 1

You can use pageable with the SpEL langage. The Sort object in Pageable will be used to append " order by " in the end of the request. Here is an example.

Answer 2

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.