Truth First, or Why You Should Mostly Implement Database First Designs

In this much overdue article, I will explain why I think that in almost all cases, you should implement a “database first” design in your application’s data models, rather than a “Java first” design (or whatever your client language is), the latter approach leading to a long road of pain and suffering, once your project grows.

This article is inspired by a recent Stack Overflow question.

Interesting reddit discussions on /r/java and /r/programming.

Code generation

To my surprise, a small group of first time jOOQ users seem to be appalled by the fact that jOOQ heavily relies on source code generation. No one keeps you from using jOOQ the way you want and you don’t have to use code generation, but the default way to use jOOQ according to the manual is to start with a (legacy) database schema, reverse engineer that using jOOQ’s code generator to get a bunch of classes representing your tables, and then to write type safe queries against those tables:

for (Record2<String, String> record : DSL.using(configuration)
//   ^^^^^^^^^^^^^^^^^^^^^^^ Type information derived from the 
//   generated code referenced from the below SELECT clause

       .select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
//           vvvvv ^^^^^^^^^^^^  ^^^^^^^^^^^^^^^ Generated names
       .from(ACTOR)
       .orderBy(1, 2)) {
    // ...
}

The code is generated either manually outside of the build, or automatically with every build. For instance, such a re-generation could follow immediately after a Flyway database migration, which can also be run either manually or automatically.

Source code generation

There are different philosophies, advantages, and disadvantages regarding these manual/automatic approaches, which I don’t want to discuss in this article. But essentially, the point of generated code is that it provides a Java representation of something that we take for granted (a “truth”) either within or outside of our system. In a way, compilers do the same thing when they generate byte code, machine code, or some other type of source code from the original sources – we get a representation of our “truth” in a different language, for whatever reason.

There are many such code generators out there. For instance, XJC can generate Java code from XSD or WSDL files. The principle is always the same:

  • There is some truth (internal or external), like a specification, data model, etc.
  • We need a local representation of that truth in our programming language

And it almost always makes sense to generate that latter, to avoid redundancy.

Type providers and annotation processing

Noteworthy: Another, more modern approach to jOOQ’s particular code generation use-case would be Type Providers, as implemented by F#, in case of which the code is generated by the compiler while compiling. It never really exists in its source form. A similar (but less sophisticated) tool in Java are annotation processors, e.g. Lombok.

In a way, this does the same thing except:

  • You don’t see the generated code (perhaps that’s less appalling to some?)
  • You must ensure the types can be provided, i.e. the “truth” must always be available. Easy in the case of Lombok, which annotates the “truth”. A bit more difficult with database models, which rely on an always available live connection.

What’s the problem with code generation?

Apart from the tricky question whether to trigger code generation manually or automatically, some people seem to think that code must not be generated at all. The reason I hear the most is the idea that it is difficult to set up in a build pipeline. And yes, that is true. There is extra infrastructure overhead. Especially if you’re new to a certain product (like jOOQ, or JAXB, or Hibernate, etc.), setting up an environment takes time you would rather spend learning the API itself and getting value out of it.

If the overhead of learning how the code generator works is too high, then indeed, the API failed to make the code generator easy to use (and later on, to customise). That should be a high priority for any such API. But that’s the only argument against code generation. Other than that, it makes absolutely no sense at all to hand-write the local representation of the internal or external truth.

Many people argue that they don’t have time for that stuff. They need to ship their MVPs. They can finalise their build pipelines later. I say:

“But Hibernate / JPA makes coding Java first easy”

Yes that’s true. And it’s both a bliss and a curse for Hibernate and its users. In Hibernate, you can just write a couple of entities, such as:

@Entity
class Book {
  @Id
  int id;
  String title;
}

And you’re almost set. Let Hibernate generate the boring “details” of how to define this entity in your SQL dialect’s DDL:

CREATE TABLE book (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR(50),

  CONSTRAINT pk_book PRIMARY KEY (id)
);

CREATE INDEX i_book_title ON book (title);

… and start running the application. That’s really cool to get started quickly and to try out things.

But, huh, wait. I cheated.

  • Will Hibernate really apply that named primary key definition?
  • Will it create the index on TITLE, which I know we’ll need?
  • Will it add an identity specification?

Probably not. While you’re developing your greenfield project, it is convenient to always throw away your entire database and re-generate it from scratch, once you’ve added the additional annotations. So, the Book entity would eventually look like this:

@Entity
@Table(name = "book", indexes = {
  @Index(name = "i_book_title", columnList = "title")
})
class Book {
  @Id
  @GeneratedValue(strategy = IDENTITY)
  int id;
  String title;
}

Cool. Re-generate. Again, this makes it really easy to get started.

But you’ll pay the price later on

At some point, you go to production. And that’s when this model no longer works. Because

Once you go live, you can no longer throw away your database, as your database has become legacy.

From now on, you have to write DDL migration scripts, e.g. using Flyway. And then, what happens to your entities? You can either adapt them manually (so you double the work), or have Hibernate re-generate them for you (how big are your chances of the generation matching your expectations?) You can only lose.

Because once you go to production, you need hotfixes. And those have to go live fast. And since you didn’t prepare for pipelining your migrations to production smoothly, you’ll patch things wildly. And then you run out of time to do it right™. And you’ll blame Hibernate, because it’s always someone else’s fault…

Instead, you could have done things entirely differently from the beginning. Like using those round wheels.

Go Database First

The real “truth” of your database schema, and the “sovereignty” over it, resides with your database. The database is the only place where the schema is defined, and all clients have a copy of the database schema, not vice versa. The data is in your database, not in your client, so it makes perfect sense to enforce the schema and its integrity in the database, right where the data is.

This is old wisdom, nothing new. Primary and unique keys are good. Foreign keys are good. Check constraints are good. Assertions (when they’re finally implemented) are good.

And that’s not where it ends. For instance, if you’re using Oracle, you may want to specify:

  • In what tablespace your table resides
  • What PCTFREE value it has
  • What the cache size of your sequence (behind the identity) is

Maybe, all of this doesn’t matter in small systems, but you don’t have to go “big data” before you can profit from vendor-specific storage optimisations as the above. None of the ORMs I’ve ever seen (including jOOQ) will allow you to use the full set of DDL options that you may want to use on your database. ORMs offer some tools to help you write DDL.

But ultimately, a well-designed schema is hand written in DDL. All generated DDL is only an approximation of that.

What about the client model?

As mentioned before, you will need a copy of your database schema in your client, a client representation. Needless to say that this client representation needs to be in-sync with the real model. How to best do that? By using a code generator.

All databases expose their meta information through SQL. Here’s how to get all tables from your database in various SQL dialects:

-- H2, HSQLDB, MySQL, PostgreSQL, SQL Server
SELECT table_schema, table_name
FROM information_schema.tables

-- DB2
SELECT tabschema, tabname
FROM syscat.tables

-- Oracle
SELECT owner, table_name
FROM all_tables

-- SQLite
SELECT name
FROM sqlite_master

-- Teradata
SELECT databasename, tablename
FROM dbc.tables

These queries (or similar ones, e.g. depending on whether views, materialised views, table valued functions should also be considered) are also run by JDBC’s DatabaseMetaData.getTables() call, or by the jOOQ-meta module.

From the result of such queries, it’s relatively easy to generate any client representation of your database model, regardless what your client technology is.

  • If you’re using JDBC or Spring, you can create a bunch of String constants
  • If you’re using JPA, you can generate the entities themselves
  • If you’re using jOOQ, you can generate the jOOQ meta model

Depending on the amount of features your client API offers (e.g. jOOQ or JPA), the generated meta model can be really rich and complete. Consider, for instance, jOOQ 3.11’s implicit join feature, which relies on generated meta information about the foreign key relationships between your tables.

Now, any database increment will automatically lead to updated client code. For instance, imagine:

ALTER TABLE book RENAME COLUMN title TO book_title;

Would you really want to do this work twice? No way. Just commit the DDL, run it through your build pipeline, and have an updated entity:

@Entity
@Table(name = "book", indexes = {

  // Would you have thought of this?
  @Index(name = "i_book_title", columnList = "book_title")
})
class Book {
  @Id
  @GeneratedValue(strategy = IDENTITY)
  int id;

  @Column("book_title")
  String bookTitle;
}

Or an updated jOOQ class. Plus: Your client code might no longer compile, which can be a good thing! Most DDL changes are also semantic changes, not just syntactic ones. So, it’s great to be able to see in compiled client source code, what code is (or may be) affected by your database increment.

A single truth

Regardless what technology you’re using, there’s always one model that contains the single truth for a subsystem – or at least, we should aim for this goal and avoid the enterprisey mess where “truth” is everywhere and nowhere. It just makes everything much simpler. If you exchange XML files with some other system, you’re going to use XSD. Like jOOQ’s INFORMATION_SCHEMA meta model in XML form:
https://www.jooq.org/xsd/jooq-meta-3.10.0.xsd

  • XSD is well understood
  • XSD specifies XML content very well, and allows for validation in all client languages
  • XSD can be versioned easily, and evolved backwards compatibly
  • XSD can be translated to Java code using XJC

The last bullet is important. When communicating with an external system through XML messages, we want to be sure our messages are valid. That’s really really easy to do with JAXB, XJC, and XSD. It would be outright nuts to think that a Java-first approach where we design our messages as Java objects could somehow be reasonably mapped to XML for someone else to consume. That generated XML would be of very poor quality, undocumented, and hard to evolve. If there’s an SLA on such an interface, we’d be screwed.

Frankly, that’s what happens to JSON APIs all the time, but that’s another story, another rant…

Databases: Same thing

When you’re using databases, it’s the same thing. The database owns its data and it should be the master of the schema. All modifications to the schema should be implemented using DDL directly, to update the single truth.

Once that truth is updated, all clients need to update their copies of the model as well. Some clients may be written in Java, using either (or both of) jOOQ and Hibernate, or JDBC. Other clients may be written in Perl (good luck to them). Even other clients may be written in C#. It doesn’t matter. The main model is in the database. ORM-generated models are of poor quality, not well documented, and hard to evolve.

So, don’t do it. And, don’t do it from the very beginning. Instead, go database first. Build a deployment pipeline that can be automated. Include code generators to copy your database model back into the clients. And stop worrying about code generation. It’s a good thing. You’ll be productive. All it takes is a bit of initial effort to set it up, and you’ll get years of improved productivity for the rest of your project.

Thank me later.

Clarification

Just to be sure: This article in no way asserts that your database model should be imposed on your entire system (e.g. your domain, your business logic, etc. etc.). The claim I made here is that client code interacting with the database should act upon the database model, and not have its own first class model of the database instead. This logic typically resides in the data access layer of your client.

In 2-tier architectures, which still have their place sometimes, that may be the only model of your system. In most systems, however, I consider the data access layer a “subsystem” that encapsulates the database model. So, there.

Exceptions

There are always exceptions, and I promised that the database first and code generation approach may not always be the right choice. These exceptions are (probably not exhaustive):

  • When the schema is unknown and must be discovered. E.g. you’re a tool vendor helping users navigate any schema. Duh… No code generation. But still database first.
  • When the schema needs to be generated on the fly for some task. This sounds a lot like a more or less sophisticated version of the entity attribute value pattern, i.e. you don’t really have a well-defined schema. In that case, it’s often not even sure if an RDBMS will be the right choice.

The nature of exceptions is that they’re exceptional. In the majority of RDBMS usage, the schema is known in advance, placed inside the RDBMS as the single source of “truth”, and clients will have derived copies from it – ideally generated using a code generator.

How to Implement Your Own XJC Plugin to Generate toString(), equals(), and hashCode() Methods

When you work with JAXB to generate Java code from XSD (or *ghasp* WSDL) files, you’re going to use the XJC tool, which is shipped with the JDK (at least until JDK 9 – with Jigsaw, it will soon be externalised into its own external dependency).

Adding plugins to XJC when running it via Maven is fairly straightforward. There are a few useful plugins available for free from here: https://github.com/highsource/jaxb2-basics and from other sources. But if you’re not entirely happy with the results, you might need to roll your own.

In this article, we’ll look into how we can write a simple plugin to generate custom renditions of these methods:

  • toString()
  • equals()
  • hashCode()

Set up a project

First, we need to set up a new Maven project, which contains the plugin code. This is quite straightforward. Just work with a single dependency and you’re done:

<project xmlns="http://maven.apache.org/POM/4.0.0" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>org.jooq</groupId>
    <artifactId>jooq-tools-xjc-plugin</artifactId>
    <version>3.11.0-SNAPSHOT</version>
    <name>jOOQ XJC Code Generation Plugin</name>
    
    <dependencies>      
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-xjc</artifactId>
            <version>2.3.0</version>
        </dependency>
    </dependencies>
</project>

Now, add the plugin logic

An empty plugin essentially looks like this:

package org.jooq.xjc;

import org.xml.sax.ErrorHandler;

import com.sun.tools.xjc.Options;
import com.sun.tools.xjc.Plugin;
import com.sun.tools.xjc.outline.ClassOutline;
import com.sun.tools.xjc.outline.Outline;

/**
 * @author Lukas Eder
 */
public class XJCPlugin extends Plugin {

    @Override
    public String getOptionName() {
        return "Xjooq-equals-hashcode-tostring";
    }

    @Override
    public int parseArgument(Options opt, String[] args, int i) {
        return 1;
    }

    @Override
    public String getUsage() {
        return "  -Xjooq-equals-hashcode-tostring    :  xjc plugin";
    }

    @Override
    public boolean run(Outline model, Options opt, ErrorHandler errorHandler) {
        return true;
    }
}

The important parts in our case are the getOptionName() method, which provides a flag that can be used from XJC code generation configuration, to activate our plugin, and the run() method, which will contain our code, adding the three desired methods.

Let’s fill in some actual code!

package org.jooq.xjc;

import static com.sun.codemodel.JMod.FINAL;
import static com.sun.codemodel.JMod.PUBLIC;
import static com.sun.codemodel.JMod.STATIC;

import java.util.Map.Entry;

import org.xml.sax.ErrorHandler;

import com.sun.codemodel.JBlock;
import com.sun.codemodel.JClass;
import com.sun.codemodel.JCodeModel;
import com.sun.codemodel.JConditional;
import com.sun.codemodel.JExpr;
import com.sun.codemodel.JFieldVar;
import com.sun.codemodel.JMethod;
import com.sun.codemodel.JOp;
import com.sun.codemodel.JVar;
import com.sun.tools.xjc.Options;
import com.sun.tools.xjc.Plugin;
import com.sun.tools.xjc.outline.ClassOutline;
import com.sun.tools.xjc.outline.Outline;

/**
 * @author Lukas Eder
 */
public class XJCPlugin extends Plugin {

    @Override
    public String getOptionName() {
        return "Xjooq-equals-hashcode-tostring";
    }

    @Override
    public int parseArgument(Options opt, String[] args, int i) {
        return 1;
    }

    @Override
    public String getUsage() {
        return "  -Xjooq-equals-hashcode-tostring    :  xjc example plugin";
    }

    @Override
    public boolean run(Outline model, Options opt, ErrorHandler errorHandler) {
        JCodeModel m = new JCodeModel();

        for (ClassOutline o : model.getClasses()) {

            // toString()
            // ---------------------------------------------------------------------------
            {
                JMethod method = o.implClass.method(PUBLIC, String.class, "toString");
                method.annotate(Override.class);
                JBlock body = method.body();
                JClass sbType = m.ref(StringBuilder.class);
                JVar sb = body.decl(0, sbType, "sb", JExpr._new(sbType));

                for (Entry<String, JFieldVar> e : o.implClass.fields().entrySet()) {
                    JFieldVar v = e.getValue();

                    if ((v.mods().getValue() & STATIC) == 0) {
                        body.invoke(sb, "append").arg("<" + e.getKey() + ">");
                        body.invoke(sb, "append").arg(v);
                        body.invoke(sb, "append").arg("</" + e.getKey() + ">");
                    }
                }

                body._return(JExpr.invoke(sb, "toString"));
            }

            // equals()
            // ---------------------------------------------------------------------------
            {
                JMethod method = o.implClass.method(PUBLIC, boolean.class, "equals");
                method.annotate(Override.class);
                JVar that = method.param(Object.class, "that");
                JBlock body = method.body();
                body._if(JExpr._this().eq(that))
                    ._then()._return(JExpr.lit(true));
                body._if(that.eq(JExpr._null()))
                    ._then()._return(JExpr.lit(false));
                body._if(JExpr.invoke("getClass").ne(JExpr.invoke(that, "getClass")))
                    ._then()._return(JExpr.lit(false));

                JVar other = body.decl(0, o.implClass, "other", JExpr.cast(o.implClass, that));

                for (Entry<String, JFieldVar> e : o.implClass.fields().entrySet()) {
                    JFieldVar v = e.getValue();

                    if ((v.mods().getValue() & STATIC) == 0) {
                        if (v.type().isPrimitive()) {
                            body._if(v.ne(other.ref(v)))
                                ._then()._return(JExpr.lit(false));
                        }
                        else {
                            JConditional i = body._if(v.eq(JExpr._null()));
                            i._then()._if(other.ref(v).ne(JExpr._null()))
                                     ._then()._return(JExpr.lit(false));
                            i._elseif(v.invoke("equals").arg(other.ref(v)).not())
                             ._then()._return(JExpr.lit(false));
                        }
                    }
                }

                body._return(JExpr.lit(true));
            }

            // hashCode()
            {
                JMethod method = o.implClass.method(PUBLIC, int.class, "hashCode");
                method.annotate(Override.class);
                JBlock body = method.body();
                JVar prime = body.decl(FINAL, m.INT, "prime", JExpr.lit(31));
                JVar result = body.decl(0, m.INT, "result", JExpr.lit(1));

                for (Entry<String, JFieldVar> e : o.implClass.fields().entrySet()) {
                    JFieldVar v = e.getValue();

                    if ((v.mods().getValue() & STATIC) == 0) {
                        body.assign(result, prime.mul(result).plus(
                            v.type().isPrimitive()
                          ? v
                          : JOp.cond(v.eq(JExpr._null()), JExpr.lit(0), v.invoke("hashCode"))
                        ));
                    }
                }

                body._return(result);
            }
        }

        return true;
    }
}

The above logic generates an XML document fragment of the JAXB-annotated classes (without formatting) and an equals() and hashCode() implementation that is inspired by the generated code you would obtain from an IDE like Eclipse.

Some example output:

@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "MappedTable", propOrder = {

})
public class MappedTable {

    protected String input;
    @XmlElement(type = String.class)
    protected String inputExpression;
    @XmlElement(required = true)
    protected String output;

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("<input>");
        sb.append(input);
        sb.append("</input>");
        sb.append("<inputExpression>");
        sb.append(inputExpression);
        sb.append("</inputExpression>");
        sb.append("<output>");
        sb.append(output);
        sb.append("</output>");
        return sb.toString();
    }

    @Override
    public boolean equals(Object that) {
        if (this == that) {
            return true;
        }
        if (that == null) {
            return false;
        }
        if (getClass()!= that.getClass()) {
            return false;
        }
        MappedTable other = ((MappedTable) that);
        if (input == null) {
            if (other.input!= null) {
                return false;
            }
        } else {
            if (!input.equals(other.input)) {
                return false;
            }
        }
        if (inputExpression == null) {
            if (other.inputExpression!= null) {
                return false;
            }
        } else {
            if (!inputExpression.equals(other.inputExpression)) {
                return false;
            }
        }
        if (output == null) {
            if (other.output!= null) {
                return false;
            }
        } else {
            if (!output.equals(other.output)) {
                return false;
            }
        }
        return true;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = ((prime*result)+((input == null)? 0 :input.hashCode()));
        result = ((prime*result)+((inputExpression == null)? 0 :inputExpression.hashCode()));
        result = ((prime*result)+((output == null)? 0 :output.hashCode()));
        return result;
    }

}

Don’t forget to register your plugin

The last step prior to building your plugin is to create a file in your project at:

src/main/resources/META-INF/services/com.sun.tools.xjc.Plugin

And put the qualified name of your plugin in it:

org.jooq.xjc.XJCPlugin

Done. Now install your plugin…

mvn clean install

… and use it from your code generation configuration as follows:

<plugin>
    <groupId>org.jvnet.jaxb2.maven2</groupId>
    <artifactId>maven-jaxb2-plugin</artifactId>
    <version>0.13.1</version>
    <executions>
        <execution>
            <id>codegen</id>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>

                <!-- The usual configuration -->
                <encoding>UTF-8</encoding>
                <locale>us</locale>
                <forceRegenerate>true</forceRegenerate>
                <extension>true</extension>
                <strict>false</strict>
                <schemaDirectory>../jOOQ-meta/src/main/resources/xsd</schemaDirectory>
                <bindingDirectory>../jOOQ-meta/src/main/resources/xjb/codegen</bindingDirectory>
                <generateDirectory>../jOOQ-meta/src/main/java</generateDirectory>
                <generatePackage>org.jooq.util.jaxb</generatePackage>
                <schemaIncludes>
                    <include>jooq-codegen-3.11.0.xsd</include>
                </schemaIncludes>

                <!-- activate it with this line. Must match getOptionName() -->
                <args>
                    <arg>-Xjooq-equals-hashcode-tostring</arg>
                </args>
                <plugins>

                    <!-- include it with these lines. -->
                    <plugin>
                        <groupId>org.jooq.trial</groupId>
                        <artifactId>jooq-tools-xjc-plugin</artifactId>
                        <version>3.11.0-SNAPSHOT</version>
                    </plugin>
                </plugins>
            </configuration>
        </execution>
    </executions>
</plugin>

Done!

jOOQ Newsletter: August 15, 2014 – jOOQ 3.5 Outlook

Subscribe to this newsletter here

jOOQ 3.5 Outlook

We’re working hard on the next release. Already 90 issues for jOOQ 3.5 are closed and counting! Today, we’re going to look at the highlights of what will be implemented in the next, exciting minor release, due for Q4 2014:

  • Support for new databases

    Our customers have been asking us for support of the Informix and Oracle TimesTen databases. While Informix is a very popular (and also old!) database, still widely used in the Industry, Oracle TimesTen is a promising new in-memory database with a very similar syntax to that of Oracle.

    With these two new additions, jOOQ will now support 18 RDBMS!

  • File-based code generation support

    This has been on our roadmap for a very long time, and finally we’re tackling it! If your development workflow prevents you from accessing a database during code generation, you can now also supply database meta information in XML format. We chose XML over any other format as it will be very easy to transform arbitrary pre-existing formats using XSLT (e.g. Hibernate hbm.xml, or ERD tools like Vertabelo‘s export format).

    We’re really looking forward to going live with this awesome feature, and in seeing a variety of community-contributed XSLT pop up, to help you integrate jOOQ with your favourite database schema definition format.

  • TypeProviders

    Sophisticated databases like PostgreSQL ship with a large variety of vendor-specific data types. It’s hard for jOOQ to support them all natively, but why add native support, when we can add another awesome SPI?

    TypeProviders will allow for abstracting over the “<T>” type, jOOQ’s column type. This will go far beyond data type conversion, it will allow you to specify how jOOQ will bind your user type to JDBC completely transparently.

These are just a few major features that we’ll be including in jOOQ 3.5, along with a lot of minor ones – so stay tuned for more jOOQ goodness.

Data Geekery Partner Network

Isaac Newton coined it like no one else:

If I have seen further it is by standing on ye sholders of Giants.

At Data Geekery, we’re looking into seeing further with jOOQ as well as we are now starting to offer and recommend services to the jOOQ ecosystem through our trusted integration partners. Today, we’re happy to recommend to you:


Germany based UWS Software Service (UWS) specialises in custom software development, application modernisation and outsourcing with a distinct focus on the Java Enterprise ecosystem.

UWS has successfully integrated the jOOQ Open-Source Edition with a variety of enterprise software projects. Their offering include custom jOOQ integrations into your system landscape and migration solutions from JDBC and/or JPA to jOOQ. UWS further offers development of custom enterprise applications using jOOQ.


“Almost every performance problem is caused by excessive use of ORM tools or improper indexing.”

Markus Winand specialises in these topics and provides SQL training and tuning services for developers. “It is difficult to tell Java developers to use SQL when Hibernate is not the right tool for a particular query” Winand said, and continued “JDBC is just too cumbersome and dangerous. jOOQ makes SQL in Java simple and safe—now I can show people how to get best of both worlds.”


 

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

Majid Azimi, who is writing SQL like a boss with jOOQ

Christoph Henkelmann, who Has found the most awesome of all stacks to build great web applications. And that consists of Ninjaframework, jOOQ, BoneCP – Slim, Fast, Reliable. We couldn’t have said it any better, ourselves.

Nat Pryce, who simply loves doing SQL queries with jOOQ in Java 8.

Thanks for the shouts, guys! You make the jOOQ experience rock!

SQL Zone – Keyset Pagination

Markus Winand, author of Use The Index, Luke! has recently started a promotion against OFFSET pagination, in favour of keyset pagination, which he called #NoOffset.

We’ve blogged about this ourselves, before. Most people make use of OFFSET pagination because it is the default that is supported by almost all RDBMS.

In many cases, however, you do not need to paginate using OFFSETs, which can turn out to be very slow for large results and large offsets. Keyset pagination is very useful when you want to implement infinite scrolling, like Twitter, Facebook, etc.

jOOQ is one of the few APIs, and the only Java API that natively support keyset pagination.

SQL Zone – PIVOT your data

Every now and then, you have one of those fancy reporting problems where SQL just fits in perfectly. We’ve blogged about it: Are You Using PIVOT Yet?

With the Oracle and SQL Server PIVOT clause, it is very easy to flip rows and columns in a table. Imagine you have a table like this:

+------+----------------+-------------------+
| dnId |  propertyName  |   propertyValue   |
+------+----------------+-------------------+
|    1 | objectsid      | S-1-5-32-548      |
|    1 | _objectclass   | group             |
|    1 | cn             | Account Operators |
|    1 | samaccountname | Account Operators |
|    1 | name           | Account Operators |
|    2 | objectsid      | S-1-5-32-544      |
|    2 | _objectclass   | group             |
|    2 | cn             | Administrators    |
|    2 | samaccountname | Administrators    |
|    2 | name           | Administrators    |
|    3 | objectsid      | S-1-5-32-551      |
|    3 | _objectclass   | group             |
|    3 | cn             | Backup Operators  |
|    3 | samaccountname | Backup Operators  |
|    3 | name           | Backup Operators  |
+------+----------------+-------------------+

And now, you’d like to transform this table to the below:

+------+--------------+--------------+-------------------+-----
| dnId |  objectsid   | _objectclass |        cn         | ... 
+------+--------------+--------------+-------------------+-----
|    1 | S-1-5-32-548 | group        | Account Operators | ... 
|    2 | S-1-5-32-544 | group        | Administrators    | ... 
|    3 | S-1-5-32-551 | group        | Backup Operators  | ... 
+------+--------------+--------------+-------------------+-----

This is a piece of cake using PIVOT:

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) AS t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    objectsid, 
    _objectclass, 
    cn, 
    samaccountname, 
    name
  )
) AS p;

jOOQ natively supports the PIVOT clause, which is definitely one of those tools to have on every reporting SQL developer’s tool chain. Read more about it here and here (original source on Stack Overflow).

Using Oracle AQ in Java Won’t Get Any Easier Than This

As recently announced in our newsletter, the upcoming jOOQ 3.5 will include an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you’ll be putting those AQs all over your database immediately.

How does it work?

jOOQ rationale

The biggest reason why many of our users love jOOQ is our code generator. It generates a Java representation of your database schema, with all the relevant objects that you need when writing SQL. So far, this has included tables, sequences, user-defined-types, packages, procedures.

What’s new is that AQ objects are now also generated and associated with the generated object type.

A simple schema

Let’s consider writing this simple schema (all sources available on GitHub)

CREATE OR REPLACE TYPE book_t 
  AS OBJECT (
  ID         NUMBER(7),
  title      VARCHAR2(100 CHAR),
  language   VARCHAR2(2 CHAR)
)
/

CREATE OR REPLACE TYPE books_t 
  AS VARRAY(32) OF book_t
/

CREATE OR REPLACE TYPE author_t 
  AS OBJECT (
  ID         NUMBER(7),
  first_name VARCHAR2(100 CHAR),
  last_name  VARCHAR2(100 CHAR),
  books      books_t
)
/

CREATE OR REPLACE TYPE authors_t 
  AS VARRAY(32) OF author_t
/

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'new_author_aq_t',
    queue_payload_type => 'author_t'
  );

  DBMS_AQADM.CREATE_QUEUE(
    queue_name => 'new_author_aq',
    queue_table => 'new_author_aq_t'
  );

  DBMS_AQADM.START_QUEUE(
    queue_name => 'new_author_aq'
  );
  COMMIT;
END;
/

So, essentially, we have both OBJECT and VARRAY types for books and authors. You might prefer using TABLE types rather than VARRAY types, but for the sake of simplicity, we stick with VARRAY (as it isn’t so easy to use nested TABLE types with AQs in Oracle).

We have also created a queue that notifies listeners every time a new author is added to the database – along with their books. Imagine enqueue operations being done in a trigger on either the author or the book table.

jOOQ-generated code

When you run the jOOQ codegenerator (version 3.5 upwards) against the above schema, you’ll get a new Queues.java file, which contains:

public class Queues {
    public static final Queue<AuthorT> NEW_AUTHOR_AQ 
      = new QueueImpl<AuthorT>(
         "NEW_AUTHOR_AQ", SP, AUTHOR_T);
}

Obviously, also the previously shown OBJECT and VARRAY types are also generated by jOOQ, just like lables.

(of course, the actual naming patterns for generated Java code are completely configurable)

Using the generated artefacts

The above code is not really nicely formatted on this blog, but you don’t see any of this in your every day work. Because when you want to enqueue a message to this queue, you can simply write:

// Create a new OBJECT type with nested
// VARRAY type
AuthorT author = new AuthorT(
    1,
    "George",
    "Orwell",
    new BooksT(
        new BookT(1, "1984", "en"),
        new BookT(2, "Animal Farm", "en")
    )
);

// ... and simply enqueue that on NEW_AUTHOR_AQ
DBMS_AQ.enqueue(configuration, NEW_AUTHOR_AQ, author);

Seriously? That easy? Yes!

Compare the above to anything you’ve written before through JDBC, or using Oracle’s native APIs. You’ll find a couple of examples about how to serialise / deserialise RAW types, but frankly, queues are awesome because you can send OBJECT types through the database, and we don’t see those examples from Oracle. In fact, trust us, you don’t want to serialise OBJECT, VARRAY, or TABLE types through JDBC. You don’t. That’s our job. We’re hacking JDBC so you don’t have to.

Of course, you can also pass MESSAGE_PROPERTIES_T, ENQUEUE_OPTIONS_T, and DEQUEUE_OPTIONS_T types as arguments to the enqueue() and dequeue() methods.

Dequeuing is just as easy. The following will generate a blocking call and wait for the next AUTHOR_T message to arrive:

AuthorT author =
  DBMS_AQ.dequeue(configuration, NEW_AUTHOR_AQ);

That’s it. Can’t be that hard, can it?

jOOQ: The best way to use Oracle AQ in Java

Goodie: Java 8 and Oracle AQ

With the above simple API and Java 8, we can do what Oracle must’ve known long ago, when they renamed Oracle AQ’s marketing name to Oracle Streams. Let’s create a Java 8 Stream of AQ-produced OBJECT types with jOOQ. Easy as pie. Just write:

static <R extends UDTRecord<R>> Stream<R> stream(
    Configuration c, 
    Queue<R> queue
) {
    return Stream.generate(() -> 
        DBMS_AQ.dequeue(c, queue)
    );
}

And now, use this beauty like so:

stream(configuration, NEW_AUTHOR_AQ)
    .limit(10)
    .forEach(author -> {
        System.out.println(
            author.getFirstName() + " " +
            author.getLastName());
    });

The above statement takes the next 10 messages dequeued this way and prints them to the console.