Dynamic native Sql queries for pagination in Spring Data

Question

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..

I should be able to do this by looking at this reference: docs.spring.io/spring-data/jpa/docs/current/reference/html/… But I'm still getting "InvalidJpaQueryMethodException:Cannot use native queries with dynamic sorting and/or pagination in" — leventunver
– leventunver, Commented Dec 19, 2016 at 14:58

meskobalazs · Accepted Answer · 2016-12-19 15:10:26Z

1

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)

answered Dec 19, 2016 at 15:10

meskobalazs

16.1k2 gold badges44 silver badges63 bronze badges

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

3 Comments

leventunver Over a year ago

Actually i did that but didn't work either. Let me edit the question.

meskobalazs Over a year ago

Okay, I get it now, but that won't work repositories. You need to build the whole query yourself.

leventunver Over a year ago

Well then, I guess I need to implement it by myself. But anyways, I couldn't make the pageable native query work. So if someone does that, it's still quite good information.

Collectives™ on Stack Overflow

Dynamic native Sql queries for pagination in Spring Data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related