CODEWITHSUNDEEP
javasqlspringspring-mvcspring-data
leventunver
leventunver

Reputation: 3399

Dynamic native Sql queries for pagination in Spring Data

I want to get the whole WHEREclause as a REST parameter and supply it to PagingAndSortingRepository. Is it possible in Spring? I know I can do this with native sql but I also want to use the paging capabilities of spring. I think QueryDsl won't work for me because the where clause is simply limitless and I have to parse all the parameters in that case.

Ex request:

localhost:8080/users?page=0&size=5&where=(firstname+eq+john+and+lastname+neq+terry)or([email protected])

I expect it to be sth like:

interface UserDAO extends PagingAndSortingRepository<User, Integer> {   

@Query(value = "SELECT * FROM User ?2", 
        countQuery = "SELECT count(*) FROM User ?2", 
        nativeQuery = true)
List<User> getUserList(Pageable pageable, String filter);  
}

In a nutshell, I need something to change the filter parameter in the above code with the requested WHERE clause. Any kind of ideas would be greatly appreciated.

Thanks..

Upvotes: 1

Views: 1726

Answers (1)

meskobalazs
meskobalazs

Reputation: 16041

You need to define the count query too, as Example 50. shows. From the docs:

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. You can however use native queries for pagination by specifying the count query yourself.

(emphasis mine)

Upvotes: 1

Related Questions