Spring Data and Native Query with Sorting

Question

In a web project, using spring-data(1.10.4.RELEASE) with a Oracle database, i am trying use a native query with a Sort variable.

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(nativeQuery = true,value = "SELECT * FROM USERS WHERE LASTNAME = :lastname #sort")
  List<User> findByLastname(@Param("lastname") String lastname, Sort sort);
}

The query launched is:

SELECT * FROM USERS WHERE LASTNAME = 'Lorite' #sort ORDER BY LASTNAME

Like you can see the annotation "#sort" is still there.

I have tried Spring Data and Native Query with pagination but the annotation is there yet and using another syntax like ?#{#sort} or {#sort} the problem persist.

Anything is welcome.

Thanks!

Is there a reason why you are using a native query here? Spring Data JPA can do all of this automatically for you, no native query (or even a JPA query) is necessary. — Jesper, Oct 26 '16 at 11:41
I know but the query is more complicated, this is only an example and the only way is to use a native query. Thanks anyway. — Dlorite, Oct 26 '16 at 13:29

score 4 · Answer 1 · answered Oct 26 '16 at 12:11

4

The documentation says:

Note, that we currently don’t support execution of dynamic sorting for native queries as we’d have to manipulate the actual query declared and we cannot do this reliably for native SQL.

Furthermore, this #sort interpolation does not exist

[1] http://docs.spring.io/spring-data/jpa/docs/current/reference/html/

answered Oct 26 '16 at 12:11

Lucas Oliveira

833
1
10
24

1

According to this http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query you can use a native query and use a Sort like this guys http://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination are saying. – Dlorite Oct 26 '16 at 13:30

score -1 · Answer 2 · answered Mar 02 '21 at 17:08

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

Example 64. Declare native count queries for pagination at the query method by using @Query

Native Queries with Spring Data JPA

Spring Data and Native Query with Sorting

2 Answers2