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 SQLSELECT lastname, firstnameINTO :lastname, :firstnameFROM employeeWHERE 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.
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:
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. |
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!
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.