When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: SELECT first_name, last_name FROM actor ORDER BY actor_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY As always, we're using the Sakila database for this example. This is rather … Continue reading Calculating Pagination Metadata Without Extra Roundtrips in SQL
Tag: dynamic SQL
Create Empty Optional SQL Clauses with jOOQ
When using jOOQ to create dynamic SQL statements (one of jOOQ's core value propositions), it is often necessary to add query elements conditionally, with a default "No-op" behaviour. For first time users, this default "no-op" behaviour is not always obvious as the jOOQ API is vast, and as with any vast API, there are many … Continue reading Create Empty Optional SQL Clauses with jOOQ
Never Concatenate Strings With jOOQ
jOOQ supports a vast amount of SQL syntax out of the box. As such, most users will not think of resorting to string concatenation like in the old days when writing dynamic SQL with JDBC. But every now and then, a vendor specific feature is not supported by jOOQ (yes, it happens). In that case, … Continue reading Never Concatenate Strings With jOOQ
When Using Bind Variables is not Enough: Dynamic IN Lists
In a previous blog post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective. In this article, I will show an … Continue reading When Using Bind Variables is not Enough: Dynamic IN Lists
Map Reducing a Set of Values Into a Dynamic SQL UNION Query
Sounds fancy, right? But it's a really nice and reasonable approach to doing dynamic SQL with jOOQ. This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this: SELECT T.COL1 FROM T WHERE T.COL2 = 'V1' UNION SELECT T.COL1 … Continue reading Map Reducing a Set of Values Into a Dynamic SQL UNION Query
5 Things You May Not Have Known About jOOQ
jOOQ has been around for a while now (since 2009!) and by now we can say we've seen quite a bit of things about the SQL and Java languages. Some of our design decisions are particular in the way jOOQ thinks about programming with SQL. These include: Nullability (let's stop fighting it) Value types (let's … Continue reading 5 Things You May Not Have Known About jOOQ
Using Kotlin’s Apply Function for Dynamic SQL with jOOQ
It was hard to limit ourselves to 10 Nice Examples of Writing SQL in Kotlin With jOOQ, recently, because the Kotlin language has many nice little features that really help a lot when working with Java libraries. We've talked about the nice with() stdlib function, which allows to "import" a namespace for a local scope … Continue reading Using Kotlin’s Apply Function for Dynamic SQL with jOOQ
A Functional Programming Approach to Dynamic SQL with jOOQ
Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you're constructing with the jOOQ DSL is a dynamic query by nature. You're constructing a query expression tree using a convenient API (the "DSL"), even if you think your SQL statement is static. For instance: for (Record rec : ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME) … Continue reading A Functional Programming Approach to Dynamic SQL with jOOQ