Type Safe SQL in Java with jOOQ

blog-post-img
By Simon Martinelli
28.06.2019

Writing SQL statements directly in Java strings can lead to errors and security vulnerabilities. This post explores jOOQ, a powerful library that enables type-safe SQL for Java developers.

The Pain of String-Based SQL

No matter if you are using frameworks like JPA, MyBatis or Spring Data JDBC you always end up declaring the SQL statements as a Java String.
The problem with this approach is that you have to write tests for every statement to make sure that it is even valid SQL. There is no compile time guarantee that the SQL statement will execute.

Get rid of the Strings!

Embedded SQL

I started professional software development in 1995 on IBM mainframe computers programming in COBOL. To access the database we used something called “Embedded SQL”:

EXEC SQL
SELECT lastname, firstname
INTO :lastname, :firstname
FROM employee
WHERE id = :id

The cool thing about Embedded SQL was that a pre-compiler was checking every SQL statement and only if it was valid the code compiled.
Bellow you can see the compile steps. (Source: http://www.redbooks.ibm.com/redbooks/pdfs/sg246435.pdf)

SQLJ

When I first met Java and JDBC in 2000 I was confused that nothing similar existed. I found out that there was an initiative called SQLJ started in 1997 but never took off. I don’t have an idea why, maybe because this was hard to integrate for IDE vendors and pre-compilers where not very common for Java. At least the compile steps are similar to Embedded SQL:

When comparing JDBC and SQLJ we can see that there is not much difference from the amount of code you have to write but everything after #sql is type safe because the pre-compiler checks the syntax where as with JDBC there is a String that could contain any error and the error will happen late in production.

Introducing jOOQ: Type Safety for SQL

Ten years ago Lukas Eder release the first version of jOOQ. According to the website is jOOQ “The easiest way to write SQL in Java”

Let’s try to write the same query as above with jOOQ:

List<EmployeeDTO> records = create
         .select(EMPLOYEE.LASTNAME, EMPLOYEE.FIRSTNAME, EMPLOYEE.SALARY)
         .from(EMPLOYEE)
         .where(EMPLOYEE.SALARY.between(80000, 100000))
         .fetchInto(EmployeeDTO.class);

Pretty cool, isn’t it? Yes – but how does it work?

Exploring jOOQ Features

1. Code Generator

jOOQ uses a code generator to generate Java classes from database objects.

For example this is an extract of the class generated by jOOQ for the table EMPLOYEE:

public class Employee extends TableImpl<EmployeeRecord> {

    public static final Employee EMPLOYEE = new Employee();

    public final TableField<EmployeeRecord, Integer> ID = createField("ID", org.jooq.impl.SQLDataType.INTEGER.nullable(false).identity(true), this, "");
    public final TableField<EmployeeRecord, String> LASTNAME = createField("LASTNAME", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");
    public final TableField<EmployeeRecord, String> FIRSTNAME = createField("FIRSTNAME", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");
    public final TableField<EmployeeRecord, Integer> SALARY = createField("SALARY", org.jooq.impl.SQLDataType.INTEGER, this, "");
    public final TableField<EmployeeRecord, Integer> DEPARTMENT_ID = createField("DEPARTMENT_ID", org.jooq.impl.SQLDataType.INTEGER.nullable(false), this, "");
    public final TableField<EmployeeRecord, Integer> MANAGER_ID = createField("MANAGER_ID", org.jooq.impl.SQLDataType.INTEGER, this, "");
}

There are constants for the table and all the columns. Thanks to these meta data classes it’s not possible to use a type in a SQL statement that does not exists in the database. And because you can generate the meta data every time, the database model changes your code will not compile if there are breaking changes.

How to configure the generator and what input formats for the generator are possible will be described in a future post. (Stay tuned)

2. Domain Specific Language

The second part of jOOQ is the DSL (Domain Specific Language) that allows to write SQL code in Java.
And in contrast to SQL in Strings the DSL forces me to write valid SQL!

Examples

So let’s see some more examples. The examples are based on this data model:

Insert
dsl.insertInto(DEPARTMENT)
   .columns(DEPARTMENT.NAME)
   .values("HR")
   .execute();
Select
dsl.select(DEPARTMENT.NAME)
    .from(DEPARTMENT)
    .where(DEPARTMENT.NAME.eq("IT"))
    .fetchOne();
Update
dsl.update(DEPARTMENT)
   .set(DEPARTMENT.NAME, "IT2")
   .where(DEPARTMENT.ID.eq(departmentId))
   .execute();
Delete
dsl.deleteFrom(EMPLOYEE)
   .where(EMPLOYEE.ID.eq(employeeId))
   .execute();

You can check out the code here: https://github.com/simasch/jooq-hr

Next Steps: Deep Dive into jOOQ

In this post, we explored using jOOQ for type-safe SQL. To learn more about how jOOQ and Vaadin can work together, check out our guide: Build Interactive Data Apps with Vaadin & jOOQ (Java).

Simon Martinelli
Follow Me
Latest posts by Simon Martinelli (see all)