Share jOOQ on Facebook
Share jOOQ on Twitter

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.

Great Reasons for Using jOOQ

Our customers spend most time on their business-logic.
Because jOOQ takes care of all their Java/SQL infrastructure problems.

Database First

Tired of ORMs driving your database model?

Whether you design a new application or integrate with your legacy, your database holds your most important asset: your data.

jOOQ is SQL-centric. Your database comes "first".

Typesafe SQL

Fed up with detecting SQL syntax errors in production?

SQL is a highly expressive and type safe language with a rich syntax. jOOQ models SQL as an internal DSL and uses the Java compiler to compile your SQL syntax, metadata and data types.

Code Generation

Bored with renaming table and column names in your Java code?

jOOQ generates Java classes from your database metadata. Your Java compiler will tell you when your code is out of sync with your schema.

Active Records

Annoyed by the amount of SQL you write for CRUD?

jOOQ lets you perform CRUD and POJO mapping directly on Active Records, which are also generated from the code generator.

Multi-Tenancy

Worried about multi-schema or shared-schema multi-tenancy?

jOOQ lets you configure database schema and table overrides at runtime and also supports row-level security.

Standardisation

Overwhelmed by the subtle differences in SQL dialects?

jOOQ performs SQL transformation to transform common SQL expressions into your database's closest match. Write SQL that works on all your databases.

Query Lifecycle

Irritated by your ORM's mysterious SQL generation?

jOOQ lets you hook into its SQL generation lifecycle, for logging, transaction handling, ID generation, SQL transformation and much more.

Procedures

Surprised by your ORM's lack of support for stored procedures?

Stored Procedures are an essential feature of modern SQL databases. jOOQ lets you embed stored function calls into your SQL statements.

Database First

Your data is your most important asset.

You have made a well-informed decision, when you chose a relational database management system to store your data. You are used to writing SQL to query and manipulate your data. Now, you're looking for a professional tool to help you write your SQL in Java.

With jOOQ, your database and your data come first. You want to be able to use every feature your database offers, using the language that is used to interact with databases: SQL. jOOQ will let you do precisely that.

More details can be seen in the manual:

Database First

Typesafe SQL

SQL is a very unique and rich language, optimally designed to express complex queries in a simple, declarative form. When writing SQL, you will focus on what data you want to fetch, not how you want to fetch it.

jOOQ treats SQL like what it is in the first place: A language. Through its unique and modern fluent API design techniques, jOOQ embeds SQL as an internal domain-specific language directly in Java, making it easy for developers to write and read code that almost feels like actual SQL.

As an internal domain-specific language, jOOQ can leverage the powerful Java compiler and Java's generics for

  • Column type checks
  • Row value expression type checks
  • SQL syntax checks

More details can be seen in the manual:

Typesafe SQL

Code Generation

SQL meta data is an essential part of your code base. It is where you define table and column types, which can be used in your SQL statements in a type safe manner.

During development, SQL meta data are in constant flux. Developers add, rename, remove tables, columns, procedures, parameters all the time.

Using jOOQ's code generator, your Java code will not only compile against your actual SQL meta data, it will also immediately take notice of the changes performed to SQL meta data. This will help prevent syntax errors due to improperly changed meta data in an early phase of your development or deployment cycle.

No more productive surprises due to changed meta data!

Code Generation

Active Records

Much of your daily work with SQL is repetitive CRUD: Creating, Reading, Updating, Deleting of database records.

jOOQ incorporates the popular Active Record paradigm by modelling each database table as a typesafe record, which is capable of storing, deleting and refreshing itself through an intuitive API.

Apart from the above operations, jOOQ's Active Records are also capable of

  • Optimistic locking
  • Foreign key navigation
  • Mapping themselves from / to your custom POJO types

More details can be seen in the manual:

Active Records

Multi-Tenancy

You have established a well-defined development process. You're probably using

  • Development databases (maybe even one per developer)
  • Test databases
  • Staging databases
  • Production databases

jOOQ lets you configure your environment easily, allowing you to rewrite generated SQL schema and table names.

Multi Tenancy

Standardisation

Each SQL dialect is different in various obvious and subtle ways. These differences include things like:

  • SQL statements (e.g. MERGE vs. ON DUPLICATE KEY UPDATE)
  • SQL clauses (e.g. Oracle CONNECT BY vs. hierarchical CTE)
  • Built-in functions (e.g. NVL, COALESCE, IFNULL, CASE .. END)
  • Pseudo elements (e.g. DUAL, SYS.DUMMY, SYSIBM.DUAL, ...)
  • Syntax elements (e.g. derived column lists)

Popular SQL database vendors have put a lot of effort into their most innovative and useful SQL clauses and functions and with jOOQ, you can use them very easily.

jOOQ produces an AST from your queries, which can be transformed into equivalent SQL expressions, should you chose a syntax element, which is not natively supported by your database.

More details can be seen in the manual:

Standardisation

Query Lifecycle

The Java to SQL integration is at the core of your business. It interfaces your two most important assets:

  • Your data
  • Your business logic

You want to stay in full control of this interface, influencing SQL rendering, variable binding, query execution and other query operation lifecycles.

jOOQ provides you with a rich SPI to inject custom behaviour, in order to manage:

  • Custom logging
  • Transaction management
  • Event triggers
  • SQL transformation

Query Lifecycle

Procedures

More and more SQL databases implement some sort of procedural language for in-database data processing and bulk operations. You may even chose to move critical business logic into the database for performance, security or other reasons.

With jOOQ, stored procedures and stored functions are first-class citizens, if you chose them to be. The jOOQ code generator will generate a callable method for every routine. Stored functions can be embedded typesafely into your SQL statements.

Procedures

Even Greater Reasons for Using jOOQ

Our customers don't just use jOOQ. They love jOOQ.
Because with jOOQ, writing SQL in Java is fun and productive.

Java Database Framework Comparison

 

jOOQ

Hibernate

MyBatis

SpringData

JDBC

SQL related

         
SQL centric
SQL dialect abstraction
SQL dialect emulation

Database related

         
Stored procedures
User-defined types

Java API related

         
Native SQL support
Typesafe query DSL
Metadata code generation
Active records
Query lifecycle SPI
Schema, table multitenancy
SQL transformation SPI
SQL templating
POJO mapping
Row mapping

Remarks

  1. Hibernate evolves around HQL and/or JPQL. While it does not hide SQL entirely, it does not really help writing SQL.
  2. Spring Data is not focused on SQL, which means that you load lots of non-related dependencies and complexity from other types of data stores.
  3. You can make stored procedures work with all frameworks. But it is a pain, compared to jOOQ.
  4. You can make user-defined types work with JDBC. But it is a pain, compared to jOOQ.
  5. Hibernate inherits JPA's Criteria API, which is a typesafe query DSL, although not for SQL.
  6. jOOQ has a simple templating engine that can be used with plain SQL.
  7. jOOQ's and MyBatis' have simple POJO mapping capabilities.

Examples

With the jOOQ DSL, SQL looks almost as if it were natively supported by Java. For instance, get all books published in 2011, ordered by title

  SELECT * FROM BOOK
   WHERE BOOK.PUBLISHED_IN = 2011
ORDER BY BOOK.TITLE
create.selectFrom(BOOK)
      .where(BOOK.PUBLISHED_IN.eq(2011))
      .orderBy(BOOK.TITLE)

jOOQ also supports more complex SQL statements. get all authors' first and last names, and the number of books they've written in German, if they have written more than five books in German in the last three years (from 2011), and sort those authors by last names limiting results to the second and third row, then lock first and last names columns for update

  SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
    FROM AUTHOR
    JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
   WHERE BOOK.LANGUAGE = 'DE'
     AND BOOK.PUBLISHED > DATE '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
   LIMIT 2
  OFFSET 1
     FOR UPDATE
      OF AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED.gt(date("2008-01-01")))
      .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .having(count().gt(5))
      .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
      .limit(2)
      .offset(1)
      .forUpdate()
      .of(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

Typesafety Examples

SQL is a very type safe language. So is jOOQ. jOOQ uniquely respects SQL's row value expression typesafety. jOOQ will use your Java compiler to type-check the following:

Predicates

jOOQ type-checks simple comparison predicates and predicates with subqueries.

select().from(t).where(t.a.eq(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^
select().from(t).where(t.a.eq(any(select(t2.x).from(t2)));
// Type-check here: -------------------> ^^^^
select().from(t).where(t.a.in(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^

 

Set Operations

jOOQ type-checks degree and data types of union subselects.

select(t1.a).from(t1).unionAll(select(t2.a).from(t2));
// Type-check here: ----------------> ^^^^
select(t1.a, t1.b).from(t1).union(select(t2.a, t2.b).from(t2));
// Type-check here: -------------------> ^^^^^^^^^^

Some more sophisticated examples show type-checks on row value expressions:

SELECT * FROM t WHERE (t.a, t.b) = (1, 2)

SELECT * FROM t WHERE (t.a, t.b) OVERLAPS (date1, date2)

SELECT * FROM t WHERE (t.a, t.b) IN (SELECT x, y)

UPDATE t SET (a, b) = (SELECT x, y FROM t2 WHERE ...)

INSERT INTO t (a, b) VALUES (1, 2)
 
select().from(t).where(row(t.a, t.b).eq(1, 2));
// Type-check here: ----------------->  ^^^^
select().from(t).where(row(t.a, t.b).overlaps(date1, date2));
// Type-check here: ------------------------> ^^^^^^^^^^^^
select().from(t).where(row(t.a, t.b).in(select(t2.x, t2.y)));
// Type-check here: -------------------------> ^^^^^^^^^^
update(t).set(row(t.a, t.b), select(t2.x, t2.y).where(...));
// Type-check here: --------------> ^^^^^^^^^^
insertInto(t, t.a, t.b).values(1, 2);
// Type-check here: ---------> ^^^^

DOWNLOAD

The jOOQ Logo