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
– Jesper, Commented Oct 26, 2016 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
– Dlorite, Commented Oct 26, 2016 at 13:29

Lucas Oliveira · Accepted Answer · 2016-10-26 12:11:35Z

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, 2016 at 12:11

Lucas Oliveira

8931 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dlorite Over a year ago

According to this docs.spring.io/spring-data/jpa/docs/current/reference/html/… you can use a native query and use a Sort like this guys stackoverflow.com/questions/38349930/… are saying.

skyho · Accepted Answer · 2021-03-02 17:08:03Z

-1

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

answered Mar 2, 2021 at 17:08

skyho

1,9637 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Spring Data and Native Query with Sorting

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related