Add limit and offset to query that was created from a String

Question

I have query as String like

select name from employee

and want to limit the number of rows with limit and offset.

Is this possible with jOOQ and how do I do that?

Something like:

dsl.fetch("select name from employee").limit(10).offset(10);

Answer 1

Yes you're close, but you cannot use fetch(sql), because that eagerly executes the query and it will be too late to append LIMIT and OFFSET. I generally don't recommend the approach offered by Sergei Petunin, because that way, you will tell the RDBMS less information about what you're going to do. The execution plan and resource allocations are likely going to be better if you actually use LIMIT and OFFSET.

There are two ways to do what you want to achieve:

Use the parser

You can use DSLContext.parser() to parse your SQL query and then modify the resulting SelectQuery, or create a derived table from that. Creating a derived table is probably a bit cleaner:

dsl.selectFrom(dsl.parser().parse("select name from employee"))
   .limit(10)
   .offset(10)
   .fetch();

The drawback is that the parser will have to understand your SQL string. Some vendor specific features will no longer be available.

The advantage (starting from jOOQ 3.13) is that you will be able to provide your generated code with attached converters and data type bindings this way, as jOOQ will "know" what the columns are.

Use plain SQL

You were already using plain SQL, but the wrong way. Instead of fetching the data eagerly, just wrap your query in DSL.table() and then use the same approach as above.

When using plain SQL, you will have to make sure manually, that the resulting SQL is syntactically correct. This includes wrapping your query in parentheses, and possibly aliasing it, depending on the dialect you're using:

dsl.selectFrom(table("(select name from employee)").as("t"))
   .limit(10)
   .offset(10)
   .fetch();