Play 2.4 Ebean pagination issue

Question

I want to use pagination in my application. Here is simple example:

public static List<MyClass> getPage(int page, int size) {
    PagedList<MyClass> findPagedList = Ebean.find(MyClass.class).findPagedList(page,size);
    return findPagedList.getList();
}

When I ask for the first page, I got my result with no problem but when I ask second page (page=1, size=10 for example) I got following error

[PersistenceException: Query threw SQLException:Windowed functions do not support constants as ORDER BY clause expressions.

I'm using MsSQL ad a DB server. How can I fix it?

Thanks

PS here the raw SQL

select * 
from ( 
    select top 30
        row_number() over (order by null) as rn,
        t0.ID c0, t0.update_date c1, t0.create_date c2,
        t0.code c3, t0.is_fulfilled c4, t0.fulfill_date c5,
        t0.fulfill_request_id c6, t0.app_id c7,
        t0.access_code_header_id c8, t0.product_id c9
    from access_code_details t0
) as limitresult where  rn > 20 and  rn <= 30 

My DB configurations:

db.default.url="jdbc:sqlserver://127.0.0.1:3333;databaseName=MyDB"
db.default.user=sa
db.default.password="******"
db.default.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
ebean.default.databasePlatform=com.avaje.ebean.config.dbplatform.MsSqlServer2005Platform
ebean.default="model.*"

Elmaleh

Catch actual SQL code which is sent to DB and post here. – Killing

I've updated the question. Added SQL. It seems there is a real issue with SQL syntax. I got same error when I run in in SQL client. So should I change this syntax somehow? Thanks – Elmaleh

Great paging, TOP 30, no ORDER BY, row_number ordered by NULL. Can you change SQL Dialect for your ORM or sth like this? – Killing

Check persistence.xml and set dialect for MS SQL like org.hibernate.dialect.SQLServer2008Dialect or higher if available. – Killing

lad2025, the closest configuration for dielect in ebean is ebean.default.databasePlatform=com.avaje.ebean.config.dbplatform.MsSqlServer2005Platform which has no effect I dont have persistence.xml since I'm not using hibernate – Elmaleh

As you see your ORM gives incorrect syntax, so maybe you should ask authors of this framework or you configured it incorrectly. – Killing

Can you list the database configuration in your application.conf? – Vitriolic

@Vitriolic , thanks for your answer. I've updated the post. – Elmaleh

Answer 1

Finally I got it. When using pagination, must explicitly define order column in query.

Ebean.find(MyClass.class).order("id").findPagedList(page,size);

however the error message is completely messed up my mind.

Elmaleh

is the static Ebean.find thread safe?? been looking for documentation but its not clear for me. – Selfconfidence

Answer 2

Another way using setFirstRow() and setMaxRows()

Model.find.where().setFirstRow(offset).setMaxRows(limit).findList();

Docs

Nucleoprotein