Core Java

Type Safe SQL in Java

Strings, String, Strings

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”:

1
2
3
4
5
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)

1-Java String

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:

2-Java String

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.

And then I found jOOQ!

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:

1
2
3
4
5
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?

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:

01
02
03
04
05
06
07
08
09
10
11
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:

4-Java String

Insert

1
2
3
4
dsl.insertInto(DEPARTMENT)
   .columns(DEPARTMENT.NAME)
   .values("HR")
   .execute();

Select

1
2
3
4
dsl.select(DEPARTMENT.NAME)
    .from(DEPARTMENT)
    .where(DEPARTMENT.NAME.eq("IT"))
    .fetchOne();

Update

1
2
3
4
dsl.update(DEPARTMENT)
   .set(DEPARTMENT.NAME, "IT2")
   .where(DEPARTMENT.ID.eq(departmentId))
   .execute();

Delete

1
2
3
dsl.deleteFrom(EMPLOYEE)
   .where(EMPLOYEE.ID.eq(employeeId))
   .execute();

What’s next?

That was just a short introduction. In the next blog post we will have a deeper look at all the features jOOQ provides.

In the meanwhile you can checkout the code here: https://github.com/simasch/jooq-hr

Published on Java Code Geeks with permission by Simon Martinelli, partner at our JCG program. See the original article here: Type Safe SQL in Java

Opinions expressed by Java Code Geeks contributors are their own.

Simon Martinelli

Simon Martinelli is a passionate Java, performance optimization and application integration expert and an active member of the Java community process (JSR-352 Java Batch and JSR-354 Money and Currency API). He is the owner of 72 Services LLC and an adjunct professor at Berne University of Applied Science in Switzerland, teaching software architecture and design and persistence technologies
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Alex Byrth
Alex Byrth
4 years ago

It sounds very promising! I’ve found both JPA very cumbersome and prolix, with no real advantage for plain JDBC, although both require extensive machine source code generation!

ElenaGillbert
4 years ago

Hi…
I’m Elena gillbert.Optionally the SQL queries can be validated against a specified SQL database schema. Each SQL query can be separately configured with different compile-time features. The integration between the Java and SQL is type-safe. The compiler automatically generates secure code against SQL injection attacks.

Back to top button