CODEWITHSUNDEEP
javaspringspring-data
Dlorite
Dlorite

Reputation: 61

Spring Data and Native Query with Sorting

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!

Upvotes: 6

Views: 13642

Answers (2)

skyho
skyho

Reputation: 1903

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

Upvotes: -1

Lucas Oliveira
Lucas Oliveira

Reputation: 883

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/

Upvotes: 4

Related Questions