Pagination with offset, limit queries?

[kloudsamurai]

Hi Andrew,

Do you ever use pagination in your queries using offset, limit? I try to avoid large offsets but I continue to find examples where it's helpful. Currently I'm implementing it within a DAO with something like this:

val name = "findPaginated"
val sql = sql(name) {
    """
        SELECT
            $columns
        FROM
            ${table.name}
        LIMIT :offset, :limit
    """
}
return session.select(
        sql,
        mapOf("offset" to offset, "limit" to limit),
        options(name),
        table.rowMapper()
);

I'm wondering if this would be something worthy of adding to AbstractDao. Your thoughts?

[andrewoma]

Hi Dave,

Yes, I do use offset and limit, but never on a query that selects all entities. Usually I have some admin style search that accepts criteria and does paging via limit and offset.

So I doubt I'd use a generic findPaginated like above.

I have thought seriously about adding it to StatementOptions so that it can be used on any statement (in fact, I think I had it in the class in the early days).

However, limit and offset aren't universally supported across the major vendors, so I've been a little reluctant to implement it. This blog shows some of the trickiness: https://blog.jooq.org/tag/limit/

Given I only support mysql and postgres at present, I guess I could pretend that Oracle and pals don't exist and do the simple variant for now.

[andrewoma]

Hi Dave,

I decided to implement limit and offset in the core via StatementOptions.

See d877062

I think it should cover your use case as well as the general case.

Cheers,
Andrew

[andrewoma]

Implemented in d877062