JOOQ Object Oriented Querying
| Industry | Software development |
|---|---|
| Products | jOOQ |
jOOQ Object Oriented Querying, commonly known as jOOQ, is a compact Java library created by Data Geekery GmbH. It follows the active record design pattern and facilitates the integration of relational and object-oriented databases by using a domain-specific language that builds SQL queries from classes generated based on database schemas.
Paradigm
The core philosophy of jOOQ centers on using SQL as the primary means of database interaction, deliberately avoiding the introduction of new query languages. Instead, it produces plain SQL statements through jOOQ objects and code derived from database schemas. Execution of these SQL commands is handled via JDBC.
In contrast to full-featured object–relational mapping (ORM) tools like EclipseLink or Hibernate, jOOQ offers a more streamlined abstraction that remains closely aligned with SQL syntax. This strategy mitigates common challenges related to object–relational impedance mismatch by minimizing syntax errors, simplifying type conversions, and managing variable bindings effectively. jOOQ is capable of handling sophisticated queries that include aliasing, unions, nested subqueries, and joins. It also supports advanced database functionalities such as user-defined types (UDTs), enumerated types, stored procedures, and native functions specific to certain databases.
Example
A nested query example retrieving authors who have books marked as sold out:
<syntaxhighlight lang="sql"> -- Retrieve authors with sold-out books SELECT * FROM AUTHOR a
WHERE EXISTS (SELECT 1
FROM BOOK
WHERE BOOK.STATUS = 'SOLD OUT'
AND BOOK.AUTHOR_ID = a.ID);
</syntaxhighlight>
The corresponding jOOQ DSL representation:
<syntaxhighlight lang="java"> // Selecting from an aliased table create.selectFrom(table("AUTHOR").as("a"))
.where(exists(selectOne()
.from(table("BOOK"))
.where(field("BOOK.STATUS").equal(field("BOOK_STATUS.SOLD_OUT")))
.and(field("BOOK.AUTHOR_ID").equal(field("a.ID")))));
</syntaxhighlight>
A more concise version utilizing code generated from database metadata:
<syntaxhighlight lang="java"> // Selecting from an aliased table final Author a = AUTHOR.as("a");
create.selectFrom(a)
.where(exists(selectOne()
.from(BOOK)
.where(BOOK.STATUS.equal(BOOK_STATUS.SOLD_OUT))
.and(BOOK.AUTHOR_ID.equal(a.ID))));
</syntaxhighlight>