JOOQ Object Oriented Querying (jOOQ)

jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects.

The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java.

Why Use jOOQ?

While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for cases where the developer doesn’t really require all the intricacies and fine tuning tools available with JPA.

Because of the way jOOQ is designed, it becomes very easy to write Java applications on top of an existing database. jOOQ helps you generate all the required classes and object automatically and you are all set go (as demonstrated in the ‘Prominent Features’ section below).

Like Hibernate, database portability is a huge advantage of jOOQ. Again, like Hibernate, typesafety ensures you get to know about errors at compile time rather than at runtime (which is one of the main irritants of JDBC). As opposed to writing SQL in JDBC, you can also enjoy the auto-complete features of your favorite IDE.

And of course, jOOQ is free to use with PostgreSQL (and all other open source databases)!

Prominent Features

Comprehensive documentation around the feature set available with jOOQ is listed on their website. To illustrate a few prominent features here, let’s use the same ‘largecities’ table that we used for HQL in the previous section.

Before starting off, please make sure you have downloaded jOOQ from http://www.jooq.org/download. Also, please ensure that you have the table ‘largecities’ available and data loaded in it.  

Generating the Classes

In order to generate the required classes using jOOQ’s command line tools automatically, you will first need to write an XML file that chalks out all the details required by jOOQ. Template for the XML file given below was taken from jOOQ’s website and then filled in for the example we are going to use. Notice the very helpful explanation in comments for each of the required fields.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd">

  <!-- Configure the database connection here -->

  <jdbc>

    <driver>org.postgresql.Driver</driver>

    <url>jdbc:postgresql://localhost:5432/postgres</url>

    <user>postgres</user>

    <password></password>

  </jdbc>

  <generator>

    <!-- The default code generator. You can override this one, to generate your own code style

         Defaults to org.jooq.util.DefaultGenerator -->

    <name>org.jooq.util.DefaultGenerator</name>

    <database>

      <!-- The database type. The format here is:

           org.util.[database].[database]Database -->

      <name>org.jooq.util.postgres.PostgresDatabase</name>

      <!-- The database schema (or in the absence of schema support, in your RDBMS this

           can be the owner, user, database name) to be generated -->

      <inputSchema>public</inputSchema>

      <!-- All elements that are generated from your schema

           (A Java regular expression. Use the pipe to separate several expressions)

           Watch out for case-sensitivity. Depending on your database, this might be important! -->

      <includes>.*</includes>

      <!-- All elements that are excluded from your schema

           (A Java regular expression. Use the pipe to separate several expressions).

           Excludes match before includes -->

      <excludes></excludes>

    </database>

    <target>

      <!-- The destination package of your generated classes (within the destination directory) -->

      <packageName>test.generated</packageName>

      <!-- The destination directory of your generated classes -->

      <directory>/Users/Shared/workspace</directory>

    </target>

  </generator>

</configuration>

In order to generate the required classes, you will need to run the org.jooq.util.GenerationTool against this XML file. In order to run this tool, you should have the following jar files in your classpath:

• jooq
• jooq-meta
• jooq-codegen
• PostgreSQL JDBC

The first three should be a part of the jOOQ download package and you can download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/download.html.

Assuming jOOQ version 3.6.2 and PostgreSQL JDBC version 9.4, and assuming all jar files are available in the current directory with the XML file named largecities.xml, the command to run the tool should look like the following on Linux / OSX:

java -classpath 

jooq-3.6.2.jar:jooq-meta-3.6.2.jar:jooq-codegen-3.6.2.jar:postgresql-9.4-1201.jdbc4.jar:. org.jooq.util.GenerationTool largecities.xml

and the following on Windows:

java -classpath

jooq-3.6.2.jar;jooq-meta-3.6.2.jar;jooq-codegen-3.6.2.jar;postgresql-9.4-1201.jdbc4.jar:. org.jooq.util.GenerationTool largecities.xml

The only difference between the two being usage of ‘:’ on Linux/OSX as opposed to using ‘;’ on Windows.

Note that, rather than doing this manually like described above, you can also integrate class generation to your IDE or to your Ant / Maven build configuration.

Once successful, the tool will generate all the required files to the target directory specified in your XML file. You can then include these files to your project.

Establishing the Connection

Establishing a connection to the database is exactly the way you would for standard JDBC connectivity. For our purposes, the following basic class serves the purpose:

import java.sql.*;

public class Main {

    public static void main(String[] args) {

        String userName = "postgres";

        String password = "Password123";

        String url = "jdbc:postgresql://localhost:5432/postgres";

        try  {

            Connection conn = DriverManager.getConnection(url, userName, password);        

        } 

        catch (Exception e) {

            e.printStackTrace();

        }

    }

}

Reading from the Database

jOOQ has functions available for all basic SQL constructs. This means that a jOOQ read operations looks a lot like the standard SQL query execution. In order to read from the database, all you need to do is get an instance of DSLContext and write a ‘select’ query. Enhancing the code above, we end up with:

import java.sql.*;

import org.jooq.*;

import org.jooq.impl.DSL;

import static test.generated.Tables.LARGECITIES;

public class Main {

    public static void main(String[] args) {

        String userName = "postgres";

        String password = "Password123";

        String url = "jdbc:postgresql://localhost:5432/postgres";

        try  {

            Connection conn = DriverManager.getConnection(url, userName, password);        

            DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);

            Result<Record> result = create.select().from(LARGECITIES).fetch();

        } 

        catch (Exception e) {

            e.printStackTrace();

        }

    }

}

Note, that in order to use the table ‘largecities’ by its name, you will need to statically import the files that were auto-generated by jOOQ’s tool. The import in case of the example above is import static test.generated.Tables.LARGECITIES;

Using the Retrieved Data

Using the actual data is quite similar to the way JDBC handles it, with the added ease of being able to specify table names and columns with type-safety. The following code simply prints out the data to stdout.

import java.sql.*;

import org.jooq.*;

import org.jooq.impl.DSL;

import static test.generated.Tables.*;

public class Main {

    public static void main(String[] args) {

        String userName = "postgres";

        String password = "Password123";

        String url = "jdbc:postgresql://localhost:5432/postgres";

        try  {

            Connection conn = DriverManager.getConnection(url, userName, password);        

            DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);

            Result<Record> result = create.select().from(LARGECITIES).fetch();

            

            for (Record r : result) {

                Integer rank = r.getValue(LARGECITIES.RANK);

                String name = r.getValue(LARGECITIES.NAME);

                System.out.println("Rank: " + rank + " Name: " + name );

            }

        } 

        catch (Exception e) {

            e.printStackTrace();

        }

    }

}

Output of this program is as follows:

Rank: 1 Name: Tokyo

Rank: 2 Name: Seoul

Rank: 3 Name: Shanghai

Rank: 4 Name: Guangzhou

Rank: 5 Name: Karachi

Rank: 6 Name: Delhi

Rank: 7 Name: Mexico City

Rank: 8 Name: Beijing

Rank: 9 Name: Lagos

Rank: 10 Name: Sao Paulo

Specifying the WHERE Clause

In order to create a filter on the result set, you can specify a WHERE clause in much the same way as other SQL constructs were used in the previous section. To get the top 5 cities, the code becomes:

             Result<Record> result =
create.select().from(LARGECITIES).where(LARGECITIES.RANK.lessThan(6)).fetch();

After this change, the output of the program is:

Rank: 1 Name: Tokyo

Rank: 2 Name: Seoul

Rank: 3 Name: Shanghai

Rank: 4 Name: Guangzhou

Rank: 5 Name: Karachi

Fetching Selected Columns Only

By default, select() captures all the columns from the table. You can pass the columns that you want to retrieve in the select() function in order to limit the scope of your query. As an example, the code below fetches and prints only the names of the cities:

        try  {

            Connection conn = DriverManager.getConnection(url, userName, password);        

            DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);

            Result<Record1<String>> result = create.select(LARGECITIES.NAME).from(LARGECITIES).fetch();

            

            for (Record r : result) {

                String name = r.getValue(LARGECITIES.NAME);

                System.out.println(" Name: " + name );

            }

        } 

        catch (Exception e) {

            e.printStackTrace();

        }

Output of this code is:

 Name: Tokyo

 Name: Seoul

 Name: Shanghai

 Name: Guangzhou

 Name: Karachi

 Name: Delhi

 Name: Mexico City

 Name: Beijing

 Name: Lagos

 Name: Sao Paulo

Drawbacks of Using jOOQ

jOOQ is a nice, lightweight, and easy to use framework when your approach is relational model centric. There are some things to watch out for, however:

• Because jOOQ is designed keeping in mind the relational model centric approach, if your database design is driven by your Java code, jOOQ is probably not the right tool for you. This is especially true if you are writing a new application and are yet to design the database.

• jOOQ expects you to design, optimize, and test your database yourself. It also expects you to modify the database layer as your application matures and changes. If you want to avoid working at the database layer, you should not be using jOOQ.