Join the JUGS in Lucerne, Switzerland on June 5, 2013 for an introductory session on jOOQ!
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:
- Code Generation: jOOQ generates a simple Java representation of your database schema. Every table, view, stored procedure, enum, UDT is a class.
- Active records: jOOQ implements an easy-to-use active record pattern. It is NOT an OR-mapper, but provides a 1:1 mapping between tables/views and classes. Between columns and members.
- Typesafe SQL: jOOQ allows for writing compile-time typesafe querying using its built-in fluent API.
- SQL standard: jOOQ supports all standard SQL language features including the more complex UNION's, nested SELECTs, joins, aliasing
- Vendor-specific feature support: jOOQ encourages the use of vendor-specific extensions such as stored procedures, UDT's and ARRAY's, recursive queries, and many more.
# How does jOOQ help you?
- Your database always comes FIRST! That's where the schema is, not in your Java code or some XML mapping file.
- Your schema is generated in Java. You can use auto-completion in your IDE!
- Your "value objects" or "data transfer objects" are generated too. This keeps things DRY
- Your Java code won't compile anymore when you modify your schema. That means less runtime errors.
- You and your DBA can be friends again because you have full control over your SQL.
- You can port your SQL to a new database. jOOQ will generate SQL that works on any database.
- You won't have syntax errors in your query.
- You won't forget to bind variables correctly. No SQL injection, either.
- You can forget about JDBC's verbosity (especially useful when dealing with UDTs, ARRAYs and stored procedures).
Or in short:
- You can be productive again!
# When to use jOOQ
- When you love your RDBMS of choice, including all its vendor-specific features.
- When you love control over your code.
- When you love the relational data model.
- When you love SQL.
- When you love stored procedures.
- When you love both OLAP and OLTP
# 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...
- When you want to map your object-oriented domain model to a database and not vice versa.
- When your schema changes more frequently than you can re-deploy jOOQ-generated source code.
- When you need to write DDL statements. jOOQ only supports DML statements.
- When you don't really need SQL, only "persistence".
# 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:
- CUBRID 8.4.1 and 9.0.0
- DB2 9.7
- Derby 10.8
- Firebird 2.5.1
- H2 1.3.161
- HSQLDB 2.2.5
- Ingres 10.1.0
- MySQL 5.1.41 and 5.5.8
- Oracle XE 10.2.0.1.0 and 11g
- PostgreSQL 9.0
- SQLite with inofficial JDBC driver v056
- SQL Server 2008 R8 and 2012
- Sybase Adaptive Server Enterprise 15.5
- Sybase SQL Anywhere 12
These platforms have been observed to work as well, but are not integration-tested
- Google Cloud SQL (MySQL)
Planned (Contributions and suggestions are very welcome!)
- SQL Azure
- Sybase SQL Anywhere OnDemand
# Other requirements
jOOQ runs with Java 1.6+
jOOQ is licensed under the Apache Software License 2.0
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.