The JOOQ technology for writing SQL query in Java.

Introduction :

The first question you have in your mind what is JOOQ ( Java Object-Oriented Querying ) :

JOOQ is a very latest and powerful technology for providing the easiest way to write SQL query in java. It generates Java code from your database and gives you a way to build your type-safe SQL queries through its fluent API.

Java Object-Oriented Querying, we can be called as JOOQ,

is a light database-mapping software library in Java. Its purpose is to be both relational and object-oriented by providing a domain-specific language(DSL) to construct queries from classes generated from a database schema.

Why we use JOOQ :

If we talk about JPA and Hibernate are a great fit to implement persist and update use cases, and simple queries. But most applications required a lot more than that. You have to required the use of a full feature set of SQL to implement your queries. That’s why JPA supports native queries. 

But the other libraries are much best fitted to implement complex SQL queries. One of them is jOOQ. It comes with a new feature called Java DSL that enables you to build SQL queries in better comfortable and type-safe way.

I> JOOQ is a dual-licensed Open Source product.

II> JOOQ implements the SQL internally as a domain-specific language in Java, for providing the typesafe construction and execution of SQL statements of high complexity.

III> And if we talk about extensions that JOOQ includes i.e nested semi-joins, anti-joins, self-joins, aliasing, selects, derived tables, joins,  as well as many vendor-specific extensions such as stored procedures, etc.

How to implement JOOQ :

Before using the jOOQ in your project, First, you need to add a few dependencies into it. 

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>${version.jooq}</version>
</dependency>

Generating JOOQ classes based on a database : 

We are using a PostgreSQL with a simple test database containing the tables author, book, book_author, and publisher.

So now let's start to review the example of a Maven build configuration that calls the code generator within Maven’s generate goal. The very first generator connects to the public schema of the JOOQ database on any SQL server on localhost. Then It writes the generated classes in the folder target/generated-sources/JOOQ.

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>${version.jooq}</version>
  
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>
  
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4.1208</version>
    </dependency>
  </dependencies>
  
  <configuration>
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql:jOOQ</url>
      <user>postgres</user>
      <password>postgres</password>
    </jdbc>
 
    <generator>
      <database>
        <name>org.jooq.util.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <inputSchema>public</inputSchema>
      </database>
      <target>
        <packageName>org.thoughts.on.java.db</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>

When you run the Maven build, and after a successful build, you can find a set of classes in the packages :

I> org.ava.db. 

II> org.java.db.tables.  

III> org.java.db.tables.records.

Implementing Queries with jOOQ :

The JOOQ comes with the great thing that is the DSL, it is very similar to SQL’s syntax. So, if you are so familiar with SQL, you will not have any problems to write your queries with jOOQ.

So the things are started with the creation of a DSLContext which you need to initialize with a JDBC Connection and the SQLDialect you want to use. Here I am using a PostgreSQL 9.4 database on localhost.

String user = "postgres";
String pass = "postgres";
String url = "jdbc:postgresql:jOOQ";
 
// Create a JDBC Connection
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    // Create a context for your database
    DSLContext ctx = DSL.using(conn, SQLDialect.POSTGRES_9_4);
     
    // Do something useful ...
 
} catch (Exception e) {
    e.printStackTrace();
}

You can then use the DSLContext to create your queries.

So here I am starting with a simple query that retrieves all records from the book table.

Result result = ctx.select().from(BOOK).fetch();
for (Record r : result) {
    Long id = r.get(BOOK.ID);
    String title = r.get(BOOK.TITLE);
    Date publishingDate = r.get(BOOK.PUBLISHINGDATE);
    log.info("Book: id="+id+" title="+title+ " publishingDate="+publishingDate);
}

In the above code, you can see an advantage of the code generation. In the place of providing the name of the book table as a String, you can simply use a static attribute of the generated Book class. 

After that when you have defined your query, Then you need to execute it and retrieve the result as you want. In the above example, I am doing that by calling the fetch method.

and in the final step, you can simply process the result. Here the query simply returned a collection of Record interfaces. And each of them represents a record of the query result.

A Slightly Less Simple Query :

Result> result = 
        ctx.select(
                AUTHOR.FIRSTNAME, 
                AUTHOR.LASTNAME, 
                DSL.count(BOOK_AUTHOR.BOOKID).as("bookCount"))
            .from(AUTHOR)
                .leftJoin(BOOK_AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
            .where(AUTHOR.LASTNAME.like("Jan%en"))
            .groupBy(AUTHOR.FIRSTNAME, AUTHOR.LASTNAME)
            .fetch();
for (Record r : result) {
    String fName = r.get(AUTHOR.FIRSTNAME);
    String lName = r.get(AUTHOR.LASTNAME);
    Integer _bookCount = r.get("bookCount", int.class);
    System.out.println(fName + " " + lName + " wrote " + _bookCount + " books.");
}

Finally, the jOOQ is a very important concept and enables you to implement your queries with a type-safe DSL that looks pretty similar to SQL.