0

I want to implement a similar query with CriteriaBuilder API:

SELECT *, ROW_NUMBER() OVER( ORDER BY Student_Score) AS RowNumberRank
FROM StudentScore

The main problem is that JPQL doesn't support window functions and I don't know how to replicate their behavior...

Commander Tvis
  • 2,244
  • 2
  • 15
  • 41
  • I know that it is possible with a native query but it is a pretty crooked solution... – Commander Tvis Dec 18 '19 at 14:58
  • You don't. As you stated, a native query is your only solution, as criteria API only provides general functions that all database systems have in common, like joins, where clause, sum, avg, etc. Vendor specific functions are not supported – XtremeBaumer Dec 18 '19 at 15:12
  • I've seen that [some use cases of window functions can be rewritten](https://stackoverflow.com/questions/35961996/retrieving-a-row-number-from-within-a-specific-group-of-rows-using-a-self-join-i), but I'm not sure how I can convert my query. – Commander Tvis Dec 18 '19 at 15:16

1 Answers1

2

Analytical functions are vendor specific. This makes is hard to implement with a CriteriaBuilder API. Using native query ties your java application to a specific database vendor. To avoid that you can create a vendor specific view and create in your java application a select with the criteria api using that view.

Beside the StudentScore entity you already have you had to define a (readonly) StudentScoreView entity with the extra attribute rowNumberRand which you can use in your JPQL query.

FredvN
  • 504
  • 1
  • 3
  • 14