how to use both row num and order by in hibernate sub query?

Question

select e from (select e from table_name e order by e.refid desc) as table_name e where (rownum) between :satrt and :end

i get the error as " org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 15 "

Hibernate query : FROM table_name where rownum between :start and :end order by refid desc

works fine . but the row number order gets varied .

kindly suggest how to write the hibernate query .

Answer 1

This is the MySQL query to get result according to your requirement where row number order will not be varied.

select * from (SELECT round(@row_number:=@row_number+1) AS row_number,csh.* 
FROM table_name csh, (SELECT @row_number:=0) AS t ORDER BY id)t where 
row_number between 1 and 10;

Same be can achieve in hibernate with in easy way without writing such sub queries by using Query defined methods. This hibernate code will produce same result set of above sql query.

Root<table_name_entity> root = criteriaQuery.from(table_name_entity.class);
criteriaQuery.orderBy(builder.asc(root.get("ID")));
Query<table_name_entity> query = getSession().createQuery(criteriaQuery);
query.setFirstResult(0);
query.setMaxResults(9);
List<table_name_entity> list = query.list();