Data Geekery released version 3.9.0 of jOOQ, their object-relational mapping (ORM) Java toolkit that generates code from a database for type-safe queries. First introduced in August 2010, new features in this release include:
- Experimental parser:
- Parses string-based SQL into jOOQ expression trees.
- Checker framework integration:
- Additional type safety for jOOQ.
- Improved integration with Oracle 12c and PL/SQL:
- Support for new features in Oracle 12c.
- JSR-310 Java Time API:
- A single API to support traditional JDBC date/time types and JSR-310 Java Time API.
The open-source version of jOOQ supports the following databases:
- MySQL 5.5, 5.6, 5.7, 8.0
- PostgreSQL 9.3 and later
- MariaDB 5.2 and later
- CUBRID 8.4 and later
- Apache Derby 10.10 and later
- Firebird 2.5, 3.0
- H2 1.3, 1.4
- HSQLDB 2.2 and later
- SQLite 3
A variety of license options are available on jOOQ's licensing page.
Getting Started
To get started building a jOOQ application, consider the following entity relationship diagram to model a publications database named pubs:
Using Maven, dependencies include the database driver and jOOQ’s code generator:
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.9.0</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.9.0</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.9.0</version>
</dependency>
</dependencies>
The database properties are defined in the <profile> section:
<profile>
<id>default</id>
<activation>
<activeByDefault>true</activeByDefault>
</activation>
<properties>
<jdbc.user>root</jdbc.user>
<jdbc.password></jdbc.password>
<jdbc.url>jdbc:mysql://localhost:3306/pubs</jdbc.url>
<jdbc.driver>com.mysql.cj.jdbc.Driver</jdbc.driver>
</properties>
</profile>
The following plugin defines the Maven goal, generate, and the properties for jOOQ’s code generator as defined within the <generate></generate> tags. This includes the database being used, the database schema, and the package in which to write the Java code.
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.9.0</version>
<!-- The plugin should hook into the generate goal -->
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbc>
<driver>${jdbc.driver}</driver>
<url>${jdbc.url}</url>
<user>${jdbc.user}</user>
<password>${jdbc.password}</password>
</jdbc>
<generator>
<database>
<name>org.jooq.util.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>pubs</inputSchema>
</database>
<target>
<packageName>org.redlich.pubs.model</packageName>
<directory>src/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
The sample SQL query on the pubs database will be modeled with jOOQ:
SELECT title,publish_date,authors.last_name,types.type,publishers.publisher
FROM publications
INNER JOIN authors ON authors.id = publications.author_id
INNER JOIN types ON types.id = publications.type_id
INNER JOIN publishers ON publishers.id = publications.publisher_id;
Using the code generated by jOOQ, an application may be written to access the database and write type-safe queries:
public class Application {
public static void main(String[] args) throws Exception {
String user = System.getProperty("jdbc.user");
String password = System.getProperty("jdbc.password");
String url = System.getProperty("jdbc.url");
String driver = System.getProperty("jdbc.driver");
Class.forName(driver).newInstance();
try(Connection connection = DriverManager.getConnection(url,user,password)) {
DSLContext dslContext = DSL.using(connection,SQLDialect.MYSQL);
Result<Record> result = dslContext.select()
.from(PUBLICATIONS)
.join(AUTHORS)
.on(AUTHORS.ID.equal(PUBLICATIONS.AUTHOR_ID))
.join(TYPES)
.on(TYPES.ID.equal(PUBLICATIONS.TYPE_ID))
.join(PUBLISHERS)
.on(PUBLISHERS.ID.equal(PUBLICATIONS.PUBLISHER_ID))
.fetch();
for(Record record : result) {
Long id = record.getValue(PUBLICATIONS.ID);
String title = record.getValue(PUBLICATIONS.TITLE);
Long authorID = record.getValue(PUBLICATIONS.AUTHOR_ID);
String lastName = record.getValue(AUTHORS.LAST_NAME);
String firstName = record.getValue(AUTHORS.FIRST_NAME);
String type = record.getValue(TYPES.TYPE);
String publisher = record.getValue(PUBLISHERS.PUBLISHER);
Date publishDate = record.getValue(PUBLICATIONS.PUBLISH_DATE);
}
}
catch(Exception exception) {
exception.printStackTrace();
}
}
}
Note the table and field names generated by jOOQ are expressed in caps. The entire jOOQ project can be found on GitHub.
Lukas Eder, founder and CEO of Data Geekery GmbH, spoke to InfoQ about this latest release of jOOQ.
InfoQ: What are your current responsibilities at Data Geekery?
Eder: I’m the founder and CEO at Data Geekery, the company behind jOOQ. We also do SQL and PL/SQL consultancy, mostly for performance optimisations. I’m running SQL trainings and I’ll be writing a book on SQL this year.
InfoQ: What sets jOOQ apart from other Java ORM frameworks such as Hibernate, Speedment, and Apache Torque?
Eder: I personally think that it is much more interesting to compare philosophies/approaches with one another, and think of individual implementations as … well, implementation details.
I believe there are mostly five approaches to working with RDBMS. With each, I’m listing some example products, far from being exhaustive:
- Keeping the SQL logic out of Java:
MyBatis, vendor-specific stored procedures, views, jOOQ stored procedures
- Embedding SQL logic in Java as SQL strings:
JDBC, jOOQ plain SQL, Spring, JDBi, JPA native query
- Embedding SQL logic in Java as a SQL-centric internal domain-specific language:
jOOQ, Criteria API (for JPQL, not SQL)
- Embedding SQL logic in Java as "idiomatic" collection APIs:
Speedment, JINQ, Slick (in Scala), LINQ (in .NET)
- Embedding the SQL logic in Java through object-relational mapping or active records:
JPA and its implementations including Hibernate, jOOQ UpdatableRecords, ActiveJDBCAll of these approaches have their advantages and disadvantages. jOOQ emphasizes on the first three approaches, because all of those approaches are SQL centric, which means that the SQL language is an important part of how users want to interact with their databases. For these users, the cognitive friction imposed by “idiomatic” collection APIs or ORMs is prohibitive. They just want to write SQL.
The main feature of jOOQ is that its code-generation supported fluent API provides a high level of compile time type safety to developers without too much compromise on the SQL language. I’ve blogged about this internal DSL technique here:
https://blog.jooq.org/2012/01/05/the-java-fluent-api-designer-crash-course
At the same time, jOOQ also encourages developers to:
- Push business logic into the database through views and stored procedures, which are very easy to use with jOOQ as well.
- Use advanced standard and vendor-specific SQL features, which is useful especially when it comes to analytics or reporting.
- Think in an RDBMS-centric way, which is a great way of working when your system grows beyond the trivial single client / 10-table database.
Of course, the interesting thing here is to understand that the different approaches do not necessarily need to compete with each other. A lot of jOOQ users also use Hibernate in the same applications, e.g.:
- jOOQ for analytics, ETL, reporting, and complex querying
- Hibernate for complex CRUD, when updating lots of entities in a transaction
In the end, as an implementation, jOOQ emphasizes the importance of the SQL language for those customers that look for precisely that. And, I dare say, it helps some customers understand that SQL is often the better answer to a lot of questions, than they would have thought.
InfoQ: What can you tell us about your partners and customers using jOOQ?
Eder: jOOQ helps them excel and become better developers in general. Let me explain.
I’m constantly intrigued to learn how diverse our customer base is. Originally, we’ve designed jOOQ for companies that work and think like ourselves. Before founding Data Geekery, I worked for a large Swiss bank’s e-banking system team and today, I still help them with Oracle SQL performance issues – obviously, they’re using jOOQ with Oracle, too.
That system has some 500 tables, some of them billion-row strong, thousands of views, hundreds of PL/SQL packages, along with lots and lots of Java code. A lot of business logic is implemented in deeply nested SQL views and procedures. Of course, this system could be built in many other ways, but the SQL-centricity of this system leads to a very elegant design that performs really well on the Oracle database.
Not all companies have complex databases as the above. Nonetheless, what distinguishes jOOQ for those companies is the simplicity and the fun jOOQ brings to Java/SQL development. See, SQL is an old language, and it often feels quirky with all the keywords and the interesting syntax (I’ve recently blogged about that here: https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/).
But there is great beauty in declarative programming using a language that is inspired by relational algebra. Being able to write SQL statements simply and intuitively with jOOQ really inspires developers. The nicest thing I’ve heard (from many customers!) is how jOOQ helps them discover the vast and exciting world of SQL with curiosity.
In other words: We’re just a catalyzer for our brilliant customers and they’re in all industries.
InfoQ: What’s on the horizon for jOOQ?
Eder: We love everything related to SQL. Currently, we’re experimenting with a built-in SQL parser (which was added in version 3.9). See, jOOQ is “just” a SQL expression tree model library. Users who build SQL statements with jOOQ don’t build strings, they build expression trees, which then generate the SQL string. Why not go the other way round and parse a SQL string into a jOOQ expression tree? This would allow users to translate from a SQL string (e.g. DB2) to another SQL string (e.g. PostgreSQL) – a great tool to migrate off a legacy database! Stay tuned for that!
jOOQ already has a SQL transformation SPI built in, which allows to transform the aforementioned expression tree to something else. The possible killer features here are things like multi-tenancy, row-level security, performance optimisations, and much more. These features are available out of the box in expensive commercial databases, but not in many open source ones. We already have the framework. In the future, we’ll offer the features built on top out of the box.
One of the most exciting features in the SQL:2011 standard is the possibility of writing temporal queries. Imagine you’re an insurance company and a given policy is modified. Unlike in classic SQL, you don’t actually modify the record with an UPDATE statement. You create a new record for the same logical policy that has a different temporal validity. SQL:2011 allows for transparently write a temporal UPDATE statement that is internally translated into multiple UPDATE and INSERT statements – and the same is true for temporal DELETE statements. We’re looking into emulating this feature for all databases, including the ones that don’t support temporal queries (so most DBs other than Oracle and DB2).
And there’s much more. The SQL language is a huge ecosystem, and we embrace everything that is SQL. So, stay tuned for more exciting jOOQ releases!
Resources
Additional resources for more information on jOOQ can be found via the following articles:
- Getting Started with jOOQ.
- Spring Tips: jOOQ video by Josh Long.
- A Functional Programming Approach to Dynamic SQL with jOOQ blog post by jOOQ.
- How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ blog post by jOOQ.
