Hi Marcel,
There are different ways to tackle this problem. The most robust and
straight-forward solution would involve putting your query in a derived
table, and operating on that to get the count:
Select<?> select = jooq.select(...) // Your existing SELECT
int count = jooq.fetchCount(select);
Result<?> page =
jooq.selectFrom(table(select)).limit(...).offset(...).fetch();
This will work for (almost) any type of query. Unfortunately, some
databases (specifically MySQL) are known to be bad at SQL transformation,
so you probably want to avoid unnecessary derived tables in MySQL.
Another option is to see queries not as static objects, but as functions in
your application. Specifically:
public Select<?> drivers(Integer limit, Integer offset, Field<?>...
selectColumns) {
return jooq
.select(selectColumns)
.from(
table("driver"))
.where(
field("last_name").equal(someName))
.orderBy(
field("first_name").asc())
.limit(limit == null ? Integer.MAX_VALUE : limit)
.offset(offset == null ? 0 : offset);
}
There are of course other ways to achieve the same. The interesting lesson
here, however, is to see jOOQ statements as composable, re-usable elements
that can be nested into each other, or created using functions / methods.
I hope this helps,
Lukas
2016-02-01 15:02 GMT+01:00 Marcel Overdijk <[email protected]>:
> Imagine I have a query like:
>
> String someName = ".."
>
> String sql = jooq
> .select(
> field("first_name"),
> field("last_name"))
> .from(
> table("driver"))
> .where(
> field("last_name").equal(someName))
> .orderBy(
> field("first_name").asc())
> .getSQL(ParamType.INLINED);
>
>
>
> I want to basically reuse this base query to actually:
>
> 1) select the count for the given base from/where.
> 2) extend the query to limit the result set to given page size/offset.
>
> Would it be possible to create some base part query and then change the
> select cause (count vs. actual columns) and the where (add pagesize/offset)
> and perform the 2 queries?
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
