Thanks Lukas,
You are there a ways to do this like the example 'function' method.
The first example looks more clean imo but unfortunately I'm using MySQL so
probably not smart to use that approach.
Regarding composability and the re-usable element it would make sense to
re-use this portion:
from =
.from(
table("driver"))
.where(
field("last_name").equal(someName))
.orderBy(
field("first_name").asc())
String countSql = jooq.selectCount().from(from).getSQL(ParamType.INLINED);
String pageSql = jooq.select(field("a"),
field("b")).from(from).limit(limit).offset(offset).getSQL(ParamType.INLINED);
But I assume this is not possible and should better go for the approach in
your 2nd example?
On Monday, February 1, 2016 at 5:11:07 PM UTC+1, Lukas Eder wrote:
>
> 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]
> <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
