Java Object Oriented Querying - AbsoluteAstronomy.com

jOOQ stands for Java Object Oriented Querying. It is a very light database mapping library in Java

Java (programming language)

Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...

. Its purpose is to be both relational

Relational model

The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...

and object oriented by providing a Domain-specific language to construct queries from classes generated

Code generation

In computer science, code generation is the process by which a compiler's code generator converts some intermediate representation of source code into a form that can be readily executed by a machine ....

from a database schema

Database schema

A database schema of a database system is its structure described in a formal language supported by the database management system and refers to the organization of data to create a blueprint of how a database will be constructed...

. Instead of providing object-relational mapping

Object-relational mapping

Object-relational mapping in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language...

, it implements an Active record pattern

Active record pattern

In software engineering, the active record pattern is an architectural pattern found in software that stores its data in relational databases. It was named by Martin Fowler in his 2003 book Patterns of Enterprise Application Architecture...

Paradigm

jOOQ claims that SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

should come first in any database integration. While it provides a lot of abstraction

Abstraction principle (programming)

In software engineering and programming language theory, the abstraction principle is a basic dictum that aims to reduce duplication of information in a program whenever practical by making use of abstractions provided by the programming language or software libraries...

on top of JDBC, it does not have as much functionality and complexity as Hibernate

Hibernate (Java)

Hibernate is an object-relational mapping library for the Java language, providing a framework for mapping an object-oriented domain model to a traditional relational database...

or JPA

Java Persistence API

The Java Persistence API, sometimes referred to as JPA, is a Java programming language framework managing relational data in applications using Java Platform, Standard Edition and Java Platform, Enterprise Edition....

or similar products as far as OR-mapping

Object-relational mapping

is concerned. It is essentially a compromise between the two. SQL has many features that cannot be used in an object oriented programming paradigm

Programming paradigm

A programming paradigm is a fundamental style of computer programming. Paradigms differ in the concepts and abstractions used to represent the elements of a program and the steps that compose a computation A programming paradigm is a fundamental style of computer programming. (Compare with a...

. This is a major drawback of the object-relational model

Object-Relational impedance mismatch

The object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system is being used by a program written in an object-oriented programming language or style; particularly when objects or class definitions...

.

With jOOQ, no new textual query language

Query language

Query languages are computer languages used to make queries into databases and information systems.Broadly, query languages can be classified according to whether they are database query languages or information retrieval query languages...

is introduced, but plain SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

can be constructed using jOOQ objects and code generated

Code generation

from a schema

Database schema

. This prevents syntax errors and type mapping problems. Also, variable binding is taken care of. Without the OR-mapping overhead, however, it is also possible to create very complex queries, with aliasing, unions

Union (SQL)

-UNION operator:In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite...

, nested selects, complex joins. Besides, jOOQ also supports database-specific features, such as UDTs, enum types

Enumerated type

In computer programming, an enumerated type is a data type consisting of a set of named values called elements, members or enumerators of the type. The enumerator names are usually identifiers that behave as constants in the language...

, stored procedures and native functions.

Example

A nested query selecting from an aliased table

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

And its equivalent in jOOQ DSL:

// Alias the author table
Table a = T_AUTHOR.as("a");

// Use the aliased table in the select statement
create.selectFrom(a)
.where(create.exists(create.select(create.constant(1))
.from(T_BOOK)
.where(TBook.STATUS.equal(TBookStatus.SOLD_OUT)
.and(TBook.AUTHOR_ID.equal(a.getField(TAuthor.ID))))));

See jOOQ Manual: DSL Support for many more examples

Paradigm

Example

See also