Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

jOOQ Tip of the Day: Reuse Bind Values

DZone's Guide to

jOOQ Tip of the Day: Reuse Bind Values

· Java Zone
Free Resource

Bitbucket is for the code that takes us to Mars, decodes the human genome, or drives your next car. What will your code do? Get started with Bitbucket today, it's free.

jOOQ implements your SQL statements as AST (Abstract Syntax Tree). This means that your SQL statement is modelled in a non-text form prior to serialising it as a textual SQL statement to your JDBC driver.

One advantage of this is that you can freely manipulate this AST any way you want. This can be done using jOOQ’s SQL transformation capabilities, or in some cases much more simply directly in your client code.

Imagine, for instance, that you have a SQL statement where several bind values should be identical. This is a frequent problem in SQL, as SQL is verbose and repetitive. For instance:

-- Both "1" should in fact be the same value
SELECT 1
FROM   TABLE
WHERE  TABLE.COL < 1
 
-- Both "2" should in fact be the same value
SELECT 2
FROM   TABLE
WHERE  TABLE.COL < 2

With jOOQ, what you can do is this:

// Create a single bind value reference
Param<Integer> value = val(1);
 
// And use that reference several times in your query:
Select<?> query =
DSL.using(configuration)
   .select(value.as("a"))
   .from(TABLE)
   .where(TABLE.COL.lt(value));
 
assertEquals(1, (int) query.fetchOne("a"));
 
// Now, for the second query, simply change the value
value.setValue(2);
 
// ... and execute the query again
assertEquals(2, (int) query.fetchOne("a"));

As a jOOQ developer, you’re directly manipulating your SQL statement’s AST. Nothing keeps you from turning that AST into a directed graph (beware of cycles, of course), to improve your SQL expressiveness.


Bitbucket is the Git solution for professional teams who code with a purpose, not just as a hobby. Get started today, it's free.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}