2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Umair's PlanetPostgreSQL3 / JOOQ Object Oriented Querying (jOOQ)
Umair Shahid

JOOQ Object Oriented Querying (jOOQ)

August 18, 2017/2 Comments/in Umair's PlanetPostgreSQL /by Umair Shahid

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.

 

 

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
2 replies
  1. Luca Ferrari
    Luca Ferrari says:
    August 22, 2017 at 7:40 am

    Really interesting.
    How would you compare JOOQ with DBIc (Perl)? Seems to me they are really close together.

    Please note in the last code snippet there’s a typo or something I cannot understand:

    Result<Record1> result

    isn’t there an extra ‘1’ after the ‘Record’ type? Or is such a type declared but not shown in the snippet?

    Reply
    • Umair Shahid
      Umair Shahid says:
      August 29, 2017 at 1:13 pm

      Really your own preference on which is better.

      Record1 is not a typo. It is used to hold a subset of the query. Please see: http://www.jooq.org/javadoc/3.8.3/org/jooq/Result.html

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Kanban & devops culture at 2ndQuadrant – Part 3 OmniDB: Average execution time of PostgreSQL webmethods
Scroll to top
×