How to use SQL - ROW_NUMBER() function in Java Spring Boot JPA SQL-Query?-CodePudding

I want to write a query in Java Spring Boot persistence API, the SQL query would look like this:

select useruuid, score, timeneeded, ROW_NUMBER() OVER 
(order by score desc, timeneeded) AS new_column 
from scores order by score desc, timeneeded;

I know how to write the Basic JPA Queries, but I haven't find the function row_number, is there any ?

My question would be- How to correctly use the row_number function in spring boot to set an Index over a sorted result list?

CodePudding user response：

You can do it directly in JPQL when you start using Hibernate 6.

=> Hibernate 6 with JPQL Window functions

CodePudding user response：

You can't do it directly in JPQL. However, JPA 2.1 added the function

function(function_name {, function_arg})

to JPQL to provide a way to call user-defined and database-specific functions. For example, in your case try:

select c.useruuid, c.score, c.desc , function('ROW_NUMBER() OVER', 'order by score desc, timeneeded') 
 AS new_column from Score c
order by c.score desc, c.timeneeded;