Implement paging using JDBI

[Mark Wolfe]

Gday All

I am looking for ideas on how best to implement paging using JDBI. So I am currently testing with h2 and planning to deploy to PostgreSQL.

Building a simple REST API and would like to implement an page option which would return a n rows starting at n * page value, all pretty simple.

It is my understanding I have two options:

1. Use a query which uses SELECT * FROM TABLE_NAME LIMIT n OFFSET (n * page)
2. Open a forward only result set, move to offset (n * page) retrieve rows = page size then close the result.

Does anyone have any suggestions or recommendations on how this is best implemented using JDBI?

Regards,

Mark Wolfe

[Brian McCallister]

Putting aside JDBI, paging is generally better done by finding the
first element in the page and fetching the next $page-size elements
after it. As long as you have an ordered index on your sort field,
this is usually more efficient than limit/offset.

Example:
https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/docs/TestPaging.java

If you really want limit/offset, just bind the limit and offset as
params and you should be good to go.

-Brian

>
> Regards,
>
> Mark Wolfe

[Mark Wolfe]

The main reason I suggested the limit offset was to remove the dependency on the where in your example.

SELECT ID, NAME, DATEADDED FROM PERSON WHERE ID > :start_id ORDER BY ID LIMIT :page_size

vs

SELECT ID, NAME, DATEADDED FROM PERSON ORDER BY ID LIMIT :page_size OFFSET :start_row_number

This enables similar queries to be used.

SELECT ID, NAME, DATEADDED FROM PERSON ORDER BY NAME LIMIT :page_size OFFSET :start_row_number

SELECT ID, NAME, DATEADDED FROM PERSON DATEADDED BY NAME LIMIT :page_size OFFSET :start_row_number

I absolutely agree that IF all I wanted to do return a set based on id field your version is simplest.

Or am I missing something fundamental here?

Thanks for your help so far it is much appreciated.

--
Regards,

Mark Wolfe

--
I am not young enough to know everything.
--

[Brian McCallister]

On Wed, Feb 8, 2012 at 5:52 PM, Mark Wolfe <ma...@wolfe.id.au> wrote:
> The main reason I suggested the limit offset was to remove the dependency on
> the where in your example.
>
> SELECT ID, NAME, DATEADDED FROM PERSON WHERE ID > :start_id ORDER BY ID
> LIMIT :page_size
>
> vs
>
> SELECT ID, NAME, DATEADDED FROM PERSON ORDER BY ID LIMIT :page_size OFFSET
> :start_row_number
>
> This enables similar queries to be used.
>
> SELECT ID, NAME, DATEADDED FROM PERSON ORDER BY NAME LIMIT :page_size OFFSET
> :start_row_number
>
> SELECT ID, NAME, DATEADDED FROM PERSON DATEADDED BY NAME LIMIT :page_size
> OFFSET :start_row_number
>
> I absolutely agree that IF all I wanted to do return a set based on id field
> your version is simplest.
>
> Or am I missing something fundamental here?

Any time you are paging you need an order by clause if you want
consistent ordering. If you are ordering by something, it should have
an ordered index. Whatever this field you are ordering by is, you can
use it to do the id + offset approach.

Using an offset requires the database to count forward to the offset.
Some times this is reasonably efficient, sometimes it is not -- and it
is very database dependent unfortunately. On the other hand an
(ordered) index search to a key is always log N and the sequential
read for the next set of ids (or even entries if scanning on primary
key) is going to be about as efficient as it can be made. In general,
trying to do large offsets is pretty expensive.

-Brian

[Mark Wolfe]

Gday Brian

Thanks a lot for the explanation, I had not considered those flaws in my proposed query structure.

I will certainly take this into account as I develop my API.

Cheers

[Sebastian Clausen]

Hello Brian,

I totally agree with your point, but what if I really want to do a "SELECT * FROM table ORDER BY column LIMIT n,m" query?
If you normally bind something to a variable with JDBI, it would be evaluated as a string, not as the fieldname.
I found posts where something similar has been discussed and "@UseStringTemplate3StatementLocator" has beend mentioned, but I'm afraid, I don't know how to implement this.

I would be very glad, if you could help :)

[Brian McCallister]

On Tue, May 28, 2013 at 11:35 PM, Sebastian Clausen <phosph...@googlemail.com> wrote:

Hello Brian,

I totally agree with your point, but what if I really want to do a "SELECT * FROM table ORDER BY column LIMIT n,m" query?
If you normally bind something to a variable with JDBI, it would be evaluated as a string, not as the fieldname.
I found posts where something similar has been discussed and "@UseStringTemplate3StatementLocator" has beend mentioned, but I'm afraid, I don't know how to implement this.

I would be very glad, if you could help :)

The behavior of binding things to a prepared statement is going to be database/driver dependent. The named parameter binding in jdbi is parameter binding to prepared statements. JDBI converts it to positional and just binds them.

If you want to template the sql before the prepared statement is created, the string template locator is really useful.

-Brian

 

Am Donnerstag, 9. Februar 2012 03:49:57 UTC+1 schrieb Brian McCallister:

Any time you are paging you need an order by clause if you want

consistent ordering. If you are ordering by something, it should have
an ordered index. Whatever this field you are ordering by is, you can
use it to do the id + offset approach.

Using an offset requires the database to count forward to the offset.
Some times this is reasonably efficient, sometimes it is not -- and it
is very database dependent unfortunately. On the other hand an
(ordered) index search to a key is always log N and the sequential
read for the next set of ids (or even entries if scanning on primary
key) is going to be about as efficient as it can be made. In general,
trying to do large offsets is pretty expensive.

-Brian

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.