CODEWITHSUNDEEP
javasqljooq
Jef Jedrison
Jef Jedrison

Reputation: 303

does jOOQ support querying on local results?

Say you have fetched some data from the database:

Record r = <DSLContext>.select()...fetch();

Does jOOQ support further local querying on the result?

<DSLContext>.select(...).from(r).where(...)...fetch();

Sometimes, it makes sense to locally query results, based on earlier queries on the database.

I could not find anything useful in the documentation, so I guess the answer is no. However, I am asking it here anyway, for certainty and possibly initiating some thoughts or discussions about this feature (although this isn't the purpose of SO).

Upvotes: 1

Views: 384

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220852

If you really want another round trip to the database, then yes, you can create a new org.jooq.Table from a org.jooq.Result using DSL.table(Result):

select(...)
.from(DSL.table(result))
.where(...)
.fetch();

This will generate a table using a SQL VALUES(...) constructor if your database supports that, or a derived table with UNION ALL, otherwise:

-- If database supports VALUES()
SELECT ...
FROM (VALUES (1, 'a'),
             (2, 'b'),
             (3, 'c')) t(column1, column2)
WHERE ...

-- Otherwise
SELECT ...
FROM (SELECT 1 column1, 'a' column2 UNION ALL
      SELECT 2        , 'b'         UNION ALL
      SELECT 3        , 'c') t
WHERE ...

-- actual SQL may vary, of course

An alternative version that takes Record instead of Result is on the roadmap for jOOQ 3.6: #4009. In the meantime, you'll have to either use the original Result reference, or create a new instance using one of the DSLContext.newResult(...) methods.

Upvotes: 1

Related Questions