Handle URL query parameters in Play! SQL statement?

Question

I am testing a Ext JS application (Client Side) and Play Framework (Service Side). I am using a grid in Ext JS with pagination.

The pagination part requires to send URL Query Parameters to my Play! server. This is no big deal, but how to process these parameters in the SQL Statement??

Example:

First request:

http://myDomain:9000/GetUsers?_dc=123456789&page=1&start=0&limit=25

Second reqeust:

http://myDomain:9000/GetUsers?_dc=123456789&page=2&start=25&limit=25

My thoughts: Normally in SQL you can set the TOP results:

SELECT TOP 25 FROM USERS

But how to translate the second request into a Sql query?

Thank you for taking time to help me out!

======>> EDIT: I am developing on SQL Server 2008, but I want this working on Sql Server 2005 or higher and Oracle 9 and higher :-)

It sounds like you are using SQL Server: this might be a dupe of http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — Femi, Aug 31 '11 at 21:36
Yes Femi! That is correct! That is the same question. I'am developing on SQL Server but I will test it also on Oracle as well. Thank you, I will update my question once i get this working. — adis, Aug 31 '11 at 21:44
If you're using Play, then why are you using SQL directly? Shouldn't you be working with JPA? — itsadok, Sep 03 '11 at 06:07
@itsadok; I tried JPA but just could not work with it. Mainly because I need to spent some time to learn it :-). I have just ordered the Play! book so it is going the right direction. — adis, Sep 14 '11 at 17:25

score 0 · Answer 1 · answered Aug 31 '11 at 21:47

You could try something like:

WITH Query_1 AS (
    SELECT
        Field1, Field2, etc
        ROW_NUMBER() OVER (ORDER BY Field1, Field2, etc) AS RowID
    FROM Table
    WHERE x=y
)
SELECT * FROM Query_1 WHERE RowID >= @start
AND RowID < @start + @limit

Of course ROW_NUMBER didn't exist back in SQL 2000 but since you've not told us which SQL you're working with I'm assuming something newer.

score 0 · Accepted Answer · answered Sep 03 '11 at 06:17

Since you're using the Play! framework, what you should do is have a proper model, with entities representing your SQL tables. Then receiving a range of results is built in:

// 25 max users start at 25
List<User> users = User.all().from(25).fetch(25);

You should also look at the pagination module. I haven't tested it, but it looks like exactly what you want.

Handle URL query parameters in Play! SQL statement?

2 Answers2