jOOQ – A Really Nice Alternative to Hibernate

by
Michał Kołodziejski
Senior Software Engineer at Vertabelo

Posted: May 27, 2014

In the Java world, there are many standards. Some of them have grown up from commonly used libraries or frameworks. For example, JPA, which was highly influenced by Hibernate.

Standards are good. They eliminate chaos and bad practices. But standards shouldn’t be a kind of Bible for developers. A set of tools used in the project should be chosen consciously, not blindly “because it’s a standard and others use it.” It’s like in the business market, where a monopoly causes a lot more harm than good.

What Is jOOQ?

jOOQ, which stands for “Java Object Oriented Querying,” is an ORM (object-relational mapping) for Java.

With jOOQ you don’t write statements with “SQL-like language” like Hibernate’s HQL. Instead, you write well designed and intuitive DSL code which, for someone speaking SQL, is obvious and self-explanatory. Just take a look at the examples from the jOOQ’s main page:

create.selectFrom(BOOK)
      .where(BOOK.PUBLISHED_IN.eq(2011))
      .orderBy(BOOK.TITLE)

Simple? I’d say trivial. Maybe something more complex:

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)

Well, I don’t feel like having to explain this. And that’s exactly why jOOQ is so cool!

How to Start?

Let’s write a very simple app talking to a database. We need to follow a few steps:

  1. Create a database.
  2. Generate jOOQ code.
  3. Write the app code.

So, let’s do it:

  1. Log in to Vertabelo. If you don’t have your account yet, you can register for a free trial.

    Model a database and generate the SQL script. For our purposes, I will use a simple one-table model:




    -- Table: database_modelers 
    CREATE TABLE database_modelers ( 
        id int  NOT NULL, 
        name varchar(64)  NOT NULL, 
        is_online boolean  NOT NULL, 
        price decimal(8,2)  NOT NULL, 
        CONSTRAINT database_modelers_pk PRIMARY KEY (id) 
    ); 
    

    Now, run it against your database (it’s PostgreSQL in my case):

    mkolodziejski=> create database modelers; 
    CREATE DATABASE 
    mkolodziejski=> \c modelers 
    You are now connected to database "modelers" as user "mkolodziejski". 
    modelers=> 
    modelers=> \i database_modelers.sql 
    psql:database_modelers.sql:17: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "database_modelers_pk" for table "database_modelers" 
    CREATE TABLE 
    modelers=>
    modelers=> insert into database_modelers VALUES (1, 'Vertabelo', true, 99.0); 
    INSERT 0 1 
    modelers=>
    

  2. Download the jOOQ code from http://www.jooq.org/download/

    a) Unpack it.

    b) Create the modelers.xml file (it’s a configuration file for code generator) just like explained here or get mine and just change your DB connection settings.

    c) Run the generator:

    java -classpath jooq-3.3.2.jar:jooq-meta-3.3.2.jar:jooq-codegen-3.3.2.jar:postgresql-9.1-901.jdbc3.jar:. \
    org.jooq.util.GenerationTool /modelers.xml
    

    d) If everything goes right, you’ll see the generated Java files:

    $ ls src/com/vertabelo/blog/jooq/orm/ 
    Keys.java  Public.java  tables  Tables.java 
    $ ls src/com/vertabelo/blog/jooq/orm/tables/ 
    DatabaseModelers.java  records 
    

  3. It’s time for our app code.

    First, we need to connect to the DB as we do with a standard JDBC-based app:

    Class.forName("org.postgresql.Driver").newInstance(); 
    conn = DriverManager.getConnection(url, userName, password);
    

    And now the main part – jOOQ code:

    // SELECT * 
    //   FROM database_modelers 
    //   WHERE is_online = true 
    //   ORDER BY price ASC 
    //   LIMIT 1; 
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); 
    Result<Record> result = create 
            .select() 
            .from(DATABASE_MODELERS) 
            .where(DATABASE_MODELERS.IS_ONLINE.eq(true)) 
            .orderBy(DATABASE_MODELERS.PRICE.asc()) 
            .limit(1) 
            .fetch(); 
    
    if (result.size() > 0) { 
        Record record = result.get(0); 
        System.out.println("Id: " + record.getValue(DATABASE_MODELERS.ID)); 
        System.out.println("Name: " + record.getValue(DATABASE_MODELERS.NAME)); 
        System.out.println("Is online: " + record.getValue(DATABASE_MODELERS.IS_ONLINE)); 
        System.out.println("Price: " + record.getValue(DATABASE_MODELERS.PRICE)); 
    }
    

    Here we’ve got the result:

    Id: 1
    Name: Vertabelo
    Is online: true
    Price: 99.00
    

I like this concept. Clean, safe, typo-proof code. And last but not least, operating on Java constants instead of Strings make the code easy to manage and safe in terms of schema changes. This makes me feel that it’s worth it to give jOOQ a chance.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Online ● Interactive View course Discover our other courses: