JOOQ Object Oriented Querying

jOOQ Object Oriented Querying, commonly known as jOOQ, is a light database-mapping software library in Java that implements the active record pattern. Its purpose is to be both relational and object oriented by providing a domain-specific language to construct queries from classes generated from a database schema.^{[citation needed]}

Paradigm

jOOQ claims that SQL should come first in any database integration. Thus, it does not introduce a new textual query language, but rather allows for constructing plain SQL from jOOQ objects and code generated from a database schema. jOOQ uses JDBC to call the underlying SQL queries.^{[citation needed]}

While it provides abstraction on top of JDBC, jOOQ does not have as much functionality and complexity as standard object–relational mapping libraries such as EclipseLink or Hibernate.^{[citation needed]}

jOOQ's closeness to SQL has advantages over typical object–relational mapping libraries.^{[citation needed]} SQL has many features that cannot be used in an object oriented programming paradigm; this set of differences is referred to as the object–relational impedance mismatch. By being close to SQL, jOOQ helps to prevent syntax errors and type mapping problems.^{[citation needed]} Also, variable binding is taken care of. It is also possible in jOOQ to create very complex queries, that involve aliasing, unions, nested selects and complex joins. jOOQ also supports database-specific features, such as UDTs, enum types, stored procedures and native functions.^{[citation needed]}

Example

A nested query selecting from an aliased table

  -- Select authors with books that are sold out
  SELECT * FROM AUTHOR a
        WHERE EXISTS (SELECT 1
                   FROM BOOK
                  WHERE BOOK.STATUS = 'SOLD OUT'
                    AND BOOK.AUTHOR_ID = a.ID);

And its equivalent in jOOQ DSL:

  // Use the aliased table in the select statement
  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")))));

Or more simply, using code generation from the database metadata to generate constants:

  // Use the aliased table in the select statement
  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))));

See also

• Apache Calcite
• List of object–relational mapping software
• SQL/OLB

References

External links

• jOOQ Home
• Using Spring Boot with jOOQ
• JSR-341
• JaQu Archived 2021-05-05 at the Wayback Machine
• Linq4j
• Quaere
• QueryDSL

This page was last edited on 12 January 2024, at 16:59