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.

Sublett

Answer 1

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.

Aficionado

Ill give it a go, but what about the limit and offset params ? what can I do there – Sublett

great, it works, no issues with limit or offset either. Thanks dude – Sublett

I have use ` ORDER BY CASE WHEN :sort_param = 'name ASC' THEN activation_name END ASC` but i got activation_name column not found. do you have any idea? – Airframe

in this answer activation_name was a reference to an expression in the SELECT list, actually, the alias assigned to the first expression SELECT a.name AS activation_name, .... MySQL allows the column alias to be referenced in an ORDER BY clause. In databases that don't allow that, we could use a.name instead. The exact expressions in the ORDER BY clause are going to depend on what we are attempting to achive, what column we want to order by. – Aficionado

This literally saved me. I have been trying to figure out the solution for this, and this answer clarified the issue that I was facing. Thank you @spencer7593. – Falciform

Answer 2

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.

Shattuck

Answer 3

Use createNativeQuery and directly append the order by value as string into query rather than using setParameter(). It worked fine for me.

Gerfen

Answer 4

I had the same problem using native query in Spring Boot and the way that i found was:

1- Create a pageable:

Pageable pageable = PageRequest.of(numPage, sizePage, Sort.by(direction , nameField));

2- Add "ORDER BY true" into the query, for example:

@Query(value = " SELECT  * " +
            "FROM articulos a   " +
            "WHERE " +            
            "AND a.id = :id" +           
            "ORDER BY TRUE",
        countQuery = " SELECT  * " +
            "FROM articulos a   " +
            "WHERE " +            
            "AND a.id = :id" +           
            "ORDER BY TRUE"
        , nativeQuery = true)
    Page<PrecioArticuloVO> obtenerPreciosPorCategoriaProveedor(@Param("id")Long id,Pageable pagina);

Neediness

Answer 5

After so many attempts i gave a try with below one it worked for me.

1. First need to remove native Query = true
2. As JPQL doesn't allow limit and offset in jpa query just remove from query.
3. Next remove order by :orderClause offset :offset limit :limit from query itself
4. re write the jpa repo method as below

List<Object[]> findSirenAlarmTimeActivation(@Param("activationUUIDs") List<UUID> activationUUIDs, Pageable pageable)

4. Call the above jpa method like below pass PageRequest.of(offset, pageSize, Sort.by(Sort.Order.asc(sortBy))))

@Query("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"
)
List<Object[]> findSirenAlarmTimeActivation(@Param("activationUUIDs") List<UUID> activationUUIDs, Pageable pageable)

findSirenAlarmTimeActivation(UUIDList, PageRequest.of(offset, pageSize, Sort.by(Sort.Order.asc(sortBy))));

That's it, it worked for me, pagination, sorting

Lemnos