Introduction

In recent years, I have experienced the same developer's need again and again. The need for improved persistence support. After lots of years of experience with Java, I have grown tired with all the solutions that are "standard", "J2EE compliant", but in the end, just ever so complicated. I don't deny, there are many good ideas around, that have eventually brought up excellent tools, such as Hibernate, JPA/EJB3, iBatis, etc. But all of those tools seem to go to a single direction without giving up any of that thought: Object-relational Mapping. So you end up using a performant database that cost's 100k+$ of license every year just to abstract it with a "standard" persistence layer.

I wanted to go a different direction. And take the best of OR-Mapping (code generation, type safety, object oriented query construction, SQL dialect abstraction, etc) without denying the fact, that beneath, I'm running an RDBMS. That's right. R like Relational. Read on about how jOOQ (Java Object Oriented Querying) succeeds in bringing the "relational to the object"

Abstract

Many companies and software projects seem to implement one of the following two approaches to interfacing Java with SQL

• The very basic approach: Using JDBC directly or adding a home-grown abstraction on top of it. There is a lot of manual work associated with the creation, maintenance, and extension of the data layer code base. Developers can easily use the full functionality of the underlying database, but will always operate on a very low level, concatenating Strings all over the place.

• The very sophisticated approach: There is a lot of configuration and a steep learning curve associated with the introduction of sophisticated database abstraction layers, such as the ones created by Hibernate, JPA, iBatis, or even plain old EJB entity beans. While the generated objects and API's may allow for easy manipulation of data, the setup and maintenance of the abstraction layer may become very complex. Besides, these abstraction layers provide so much abstraction on top of SQL, that SQL-experienced developers have to rethink.

A different paradigm

I tried to find a new solution addressing many issues that I think most developers face every day. With jOOQ - Java Object Oriented Querying, I want to embrace the following paradigm:

• SQL is a good thing. Many things can be expressed quite nicely in SQL.
• The relational data model is a good thing. It should not be abstracted by OR-Mapping
• SQL has a structure and syntax. It should not be expressed using "low-level" String concatenation.
• Variable binding tends to be very complex when dealing with major queries.
• POJO's (or data transfer objects) are great when writing Java code manipulating database data.
• POJO's are a pain to write and maintain manually. Source code generation is the way to go
• The database comes first. Then the code on top of it.
• Yes, you do have stored procedures and user defined types (UDT's) in your legacy database. Your database-tool should support that.

I think that these key ideas are useful for a very specific type of developer. That specific developer

• interfaces Java with huge legacy databases.
• knows SQL well and wants to use it extensively.
• doesn't want to learn any new language (HQL, JPQL, etc)
• doesn't want to spend one minute fine-tuning some sophisticated XML-configuration.
• wants little abstraction over SQL, because his software is tightly coupled with his database. Something that I think the guys at Hibernate or JPA seem to have ignored.
• needs a strong but light-weight library for database access. For instance to develop for mobile devices.

How does jOOQ fit in this paradigm?

Not only does jOOQ completely address the above paradigm, it does so quite elegantly. Let's say you have this database that models your bookstore. And you need to run a query selecting all books by authors born after 1920. You know how to do this in SQL:

-- Select all books by authors born after 1920, named "Paulo" from a catalogue:
SELECT * 
  FROM t_author a 
  JOIN t_book b ON a.id = b.author_id 
 WHERE a.year_of_birth > 1920 
   AND a.first_name = 'Paulo'
 ORDER BY b.title

The same query expressed with jOOQ-Objects

  // Instanciate your factory using a JDBC connection
  // and specify the SQL dialect you're using. Of course you can 
  // have several factories in your application.
  Factory create = new Factory(connection, SQLDialect.MYSQL);

  // Create the query using generated, type-safe objects. You could
  // write even less code than that with static imports!
  SelectQuery q = create.selectQuery();
  q.addFrom(TAuthor.T_AUTHOR);
  q.addJoin(TBook.T_BOOK, TAuthor.ID, TBook.AUTHOR_ID);

  // Note how you do not need to worry about variable binding.
  // jOOQ does that for you, dynamically
  q.addCompareCondition(TAuthor.YEAR_OF_BIRTH, 1920, Comparator.GREATER);
  
  // The AND operator and EQUALS comparator are implicit here
  q.addCompareCondition(TAuthor.FIRST_NAME, "Paulo");
  q.addOrderBy(TBook.TITLE); 

The jOOQ query object model uses generated classes, such as TAuthor or TBook. Like many other code generation tools do, jOOQ will generate static final objects for the fields contained in each table. In this case, TAuthor holds a member called TAuthor.T_AUTHOR to represent the table itself, and members such as TAuthor.ID, TAuthor.YEAR_OF_BIRTH, etc to hold the table's fields. 

But you could also use the jOOQ DSL API to stay closer to SQL

  // Do it all "on one line".
  SelectQuery q = create.select()
    .from(T_AUTHOR)
    .join(T_BOOK).on(TAuthor.ID.equal(TBook.AUTHOR_ID))
    .where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920)
    .and(TAuthor.FIRST_NAME.equal("Paulo")))
    .orderBy(TBook.TITLE).getQuery();

jOOQ ships with a DSL (Domain Specific Language) somewhat similar to Linq that facilitates query creation. The strength of DSL becomes obvious when you are using jOOQ constructs such as the decode function:

  // Create a case statement. Unfortunately "case" is a reserved word in Java
  // Hence the method is called DECODE after its related Oracle function
  Field<String> nationality = create.decode()
    .when(TAuthor.FIRST_NAME.equal("Paulo"), "brazilian")
    .when(TAuthor.FIRST_NAME.equal("George"), "english")
    .otherwise("unknown"); // "else" is also a reserved word ;-)

 The above will render this SQL code:

  CASE WHEN T_AUTHOR.FIRST_NAME = 'Paulo'  THEN 'brazilian'
       WHEN T_AUTHOR.FIRST_NAME = 'George' THEN 'english'
                                           ELSE 'unknown'
  END 

Use the DSL API when:

• You want your Java code to look like SQL
• You want your IDE to help you with auto-completion (you will not be able to write select .. order by .. where .. join or any of that stuff)

Use the regular API when:

• You want to create your query step-by-step, creating query parts one-by-one
• You need to assemble your query from various places, passing the query around, adding new conditions and joins on the way

In any case, all API's will construct the same underlying implementation object, and in many cases, you can combine the two approaches

Once you have established the query, execute it and fetch results

  // Execute the query and fetch the results
  q.execute();
  Result<?> result = q.getResult();

  // Result is Iterable, so you can loop over the resulting records like this:
  for (Record record : result) {

    // Type safety assured with generics
    String firstName = record.getValue(TAuthor.FIRST_NAME);
    String lastName = record.getValue(TAuthor.LAST_NAME);
    String title = record.getValue(TBook.TITLE);
    Integer publishedIn = record.getValue(TBook.PUBLISHED_IN);

    System.out.println(title + " (published in " + publishedIn + ") by " + firstName + " " + lastName);
  }

 Or simply write

  for (Record record : q.fetch()) {
    // [...]
  }

Fetch data from a single table and use jOOQ as a simple OR-Mapper

  // Similar query, but don't join books to authors.
  // Note the generic record type that is added to your query:
  SimpleSelectQuery<TAuthorRecord> q = create.select(T_AUTHOR)
            .where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920)
            .and(TAuthor.FIRST_NAME.equal("Paulo")))
            .orderBy(TAuthor.LAST_NAME).getQuery();

  // When executing this query, also Result holds a generic type:
  q.execute();
  Result<TAuthorRecord> result = q.getResult();
  for (TAuthorRecord record : result) {
    
    // With generate record classes, you can use generated getters and setters:
    String firstName = record.getFirstName();
    String lastName = record.getLastName();

    System.out.println("Author : " + firstName + " " + lastName + " wrote : ");

    // Use generated foreign key navigation methods
    for (TBookRecord book : record.getTBooks()) {
      System.out.println("  Book : " + book.getTitle());
    }
  }

jOOQ not only generates code to model your schema, but it also generates domain model classes to represent tuples in your schema. In the above example, you can see how selecting from the TAuthor.T_AUTHOR table will produce results containing well-defined TAuthorRecord types. These types hold getters and setters like any POJO, but also some more advanced OR-code, such as foreign key navigator methods like 

  // Return all books for an author that are obtained through the
  // T_AUTHOR.ID = T_BOOK.AUTHOR_ID foreign key relationship
  public List<TBookRecord> getTBooks()

Now, for true OR-mapping, you would probably prefer mature and established frameworks such as Hibernate or iBATIS. Don't panic. Better integration with Hibernate and JPA is on the feature roadmap. The goals of jOOQ should not be to reimplement things that are already well-done, but to bring true SQL to Java 

Execute CRUD operations with jOOQ as an OR-mapper

  // Create a new record and insert it into the database
  TBookRecord book = create.newRecord(T_BOOK);
  book.setTitle("My first book");
  book.store();

  // Update it with new values
  book.setPublishedIn(2010);
  book.store();

  // Delete it
  book.delete();

Nothing new in the OR-mapping world. These ideas have been around since EJB entity beans or even before. It's still quite useful for simple purposes.

Execute CRUD operations the way you're used to

You don't need to go into that OR-mapping business. You can create your own INSERT, "INSERT SELECT", UPDATE, DELETE queries. Some examples: 

  InsertQuery<TAuthorRecord> i = create.insertQuery(T_AUTHOR);
  i.addValue(TAuthor.FIRST_NAME, "Hermann");
  i.addValue(TAuthor.LAST_NAME, "Hesse");
  i.execute();

  UpdateQuery<TAuthorRecord> u = create.updateQuery(T_AUTHOR);
  u.addValue(TAuthor.FIRST_NAME, "Hermie");
  u.addCompareCondition(TAuthor.LAST_NAME.equal("Hesse"));
  u.execute();

  // etc...

Now for the advanced stuff

Many tools can do similar stuff as what we have seen before. Especially Hibernate and JPA have a feature called criteria query, that provides all of the type-safety and query object building using DSL's while being based on a solid (but blown-up) underlying architecture.

An important goal for jOOQ is to provide you with all (or at least: most) SQL features that you are missing in other frameworks but that you would like to use because you think SQL is a great thing but JDBC is too primitive for the year 2010, 2011, or whatever year we're in, when you're reading this. So, jOOQ comes along with aliasing, nested selects, unions and many other SQL features. Check out the following sections:

Aliasing

That's a very important feature. How could you have self-joins or in/exists clauses without aliasing? Let's say we have a "T_TREE" table with fields "ID", "PARENT_ID", and "NAME". If we want to find all parent/child NAME couples, we will need to execute a self-join on T_TREE. In SQL, this reads:

  SELECT parent.NAME parent_name, 
         child.NAME child_name
    FROM T_TREE parent
    JOIN T_TREE child ON (parent.ID = child.PARENT_ID)

No problem for jOOQ. We'll write:

  // Create table aliases
  Table<TTreeRecord> parent = TTree.T_TREE.as("parent");
  Table<TTreeRecord> child  = TTree.T_TREE.as("child");

  // Create field aliases from aliased table
  Field<String> parentName  = parent.getField(TTree.NAME).as("parent_name");
  Field<String> childName   = child.getField(TTree.NAME).as("child_name");

  // Execute the above select
  Record record = create.select(parentName, childName)
          .from(parent)
          .join(child).on(parent.getField(TTree.ID).equal(child.getField(TTree.PARENT_ID)))
          .fetchAny();

  // The aliased fields can be read from the record as in the simpler examples:
  record.getValue(parentName);

Functionally, it is easy to see how this works. Look out for future releases of jOOQ for improvements in the DSL support of field and table aliasing

IN clause

The org.jooq.Field class provides many methods to construct conditions. In previous examples, we have seen how to create regular compare conditions with = < <= >= > != operators. Now Field also has a couple of methods to create IN conditions: 

  // Create IN conditions with constant values that are bound to the
  // query via JDBC's '?' bind variable placeholders
  Condition    in(T... values);
  Condition    in(Collection<T> values);
  Condition notIn(T... values);
  Condition notIn(Collection<T> values);

  // Create IN conditions with a sub-select
  Condition    in(QueryProvider<?> query)
  Condition notIn(QueryProvider<?> query)

The constant set of values for IN conditions is an obvious feature. But the sub-select is quite nice:

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

In jOOQ, this translates to

  create.select(T_AUTHOR)
        .where (TAuthor.ID.in(create.selectDistinct(TBook.AUTHOR_ID)
                                    .from(T_BOOK)
                                    .where(TBook.STATUS.equal(TBookStatus.SOLD_OUT))));

EXISTS clause

Very similar statements can be expressed with the EXISTS clause. The above set of authors could also be obtained with this statement:

  -- 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);

In jOOQ (as of version 1.5.0), this translates to

  // Alias the author table
  Table<TAuthorRecord> 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))))));

UNION clauses

SQL knows of four types of "UNION operators":

• UNION
• UNION ALL
• EXCEPT
• INTERSECT

All of these operators are supported by all types of select queries. So in order to write things like:

  SELECT TITLE FROM T_BOOK WHERE PUBLISHED_IN > 1945
  UNION
  SELECT TITLE FROM T_BOOK WHERE AUTHOR_ID = 1

You can write the following jOOQ logic:

  create.select(TBook.TITLE).from(T_BOOK).where(TBook.PUBLISHED_IN.greaterThan(1945)).union(
  create.select(TBook.TITLE).from(T_BOOK).where(TBook.AUTHOR_ID.equal(1)));

Of course, you can then again nest the union query in another one (but be careful to correctly use aliases):

  -- alias_38173 is an example of a generated alias, 
  -- generated by jOOQ for union queries
  SELECT alias_38173.TITLE FROM (
    SELECT T_BOOK.TITLE, T_BOOK.AUTHOR_ID FROM T_BOOK WHERE T_BOOK.PUBLISHED_IN > 1945
    UNION
    SELECT T_BOOK.TITLE, T_BOOK.AUTHOR_ID FROM T_BOOK WHERE T_BOOK.AUTHOR_ID = 1
  ) alias_38173
  ORDER BY alias_38173.AUTHOR_ID DESC

In jOOQ:

  Select<?> union = 
    create.select(TBook.TITLE, TBook.AUTHOR_ID).from(T_BOOK).where(TBook.PUBLISHED_IN.greaterThan(1945)).union(
    create.select(TBook.TITLE, TBook.AUTHOR_ID).from(T_BOOK).where(TBook.AUTHOR_ID.equal(1)));

  create.select(union.getField(TBook.TITLE))
        .from(union)
        .orderBy(union.getField(TBook.AUTHOR_ID).descending());

Note that a UNION query will automatically generate an alias if you use it as a nested table. In order to nest this query correctly, you need to get the aliased field from the query as seen in the example abov.

Other, non-standard SQL features

See more examples about stored procedures, UDT's, enums, etc on https://sourceforge.net/apps/trac/jooq/wiki/Examples

Summary

jOOQ brings the relational world to Java without trying to cover up its origins. jOOQ is relational. And object oriented. Just in a different way. Try it for yourself and I would be very glad for any feedback you may have. Find jOOQ on http://jooq.sourceforge.net

Cheers
Lukas Eder