# Philosophy

SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL!

# What does jOOQ code look like?

It's simple. 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 PUBLISHED_IN = 2011
ORDER BY TITLE
 
create.selectFrom(BOOK)
      .where(PUBLISHED_IN.equal(2011))
      .orderBy(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)

# What does type safety mean to jOOQ?

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:

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: ---------------> ^^^^

And also set operations:

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

And even 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 FROM t2)

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).from(t2)));
// 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: ---------> ^^^^

# What is jOOQ?

jOOQ stands for Java Object Oriented Querying. It combines these essential features:

# How does jOOQ help you?

Or in short:

# When to use jOOQ

# When not to use jOOQ

On the other hand, many people like the ease of use of Hibernate or other products, when it comes to simply persisting any domain model in any database. You should not use jOOQ...

# What databases are supported

Every RDMBS out there has its own little specialties. jOOQ considers those specialties as much as possible, while trying to standardise the behaviour in jOOQ. In order to increase the quality of jOOQ, some 70 unit tests are run for syntax and variable binding verification, as well as some 180 integration tests with an overall of around 1200 queries for any of these databases:

These platforms have been observed to work as well, but are not integration-tested

Planned (Contributions and suggestions are very welcome!)

# Other requirements

jOOQ runs with Java 1.6+

License

jOOQ is licensed under the Apache Software License 2.0

Thanks

YourKit is kindly supporting open source projects with its full-featured Java Profiler. YourKit, LLC is the creator of innovative and intelligent tools for profiling Java and .NET applications. Take a look at YourKit's leading software products: YourKit Java Profiler and YourKit .NET Profiler.


The jOOQ Logo