Introduction
In this article, I want to announce the first stable release 1.4.2 of jOOQ, which stands for Java Object Oriented Querying. Find more information on http://jooq.sourceforge.net
Abstract
Many companies and software projects seem to implement one of the following two approaches to interfacing Java with SQL
- The very basic approach: Using JDBC directly or adding a home-grown abstraction on top of it. There is a lot of manual work associated with the creation, maintenance, and extension of the data layer code base. Developers can easily use the full functionality of the underlying database, but will always operate on a very low level, concatenating Strings all over the place.
- The very sophisticated approach: There is a lot of configuration and a steep learning curve associated with the introduction of sophisticated database abstraction layers, such as the ones created by Hibernate or iBatis, or even plain old EJB entity beans. While the generated objects and API's may allow for easy manipulation of data, the setup and maintenance of the abstraction layer may become very complex. Besides, these abstraction layers provide so much abstraction on top of SQL, that SQL-experienced developers have to rethink.
A new paradigm
I tried to find a new solution addressing many issues that I think most developers face every day. With jOOQ - Java Object Oriented Querying, I want to embrace the following paradigm:
- SQL is a good thing. Many things can be expressed quite nicely in SQL. There is no need for complete abstraction of SQL.
- The relational data model is a good thing. It has proven the best data model for the last 40 years. There is no need for XML databases or truly object oriented data models. Instead, your company runs several instances of Oracle, MySQL, MSSQL, DB2 or any other RDBMS.
- SQL has a structure and syntax. It should not be expressed using "low-level" String concatenation in JDBC - or "high-level" String concatenation in HQL - both of which are prone to hold syntax errors.
- Variable binding tends to be very complex when dealing with major queries. THAT is something that should be abstracted.
- POJO's are great when writing Java code manipulating database data.
- POJO's are a pain to write and maintain manually. Code generation is the way to go. You will have compile-safe queries including datatype-safety.
- The database comes first. While the application on top of your database may change over time, the database itself is probably going to last longer.
- Yes, you do have stored procedures and user defined types (UDT's) in your legacy database. Your database-tool should support that.
How does jOOQ fit in this paradigm?
Not only does jOOQ completely address the above paradigm, it does so quite elegantly. Let's say you have this database that models your bookstore. And you need to run a query selecting all books by authors born after 1920. You know how to do this in SQL:
-- Select all books by authors born after 1920 from a catalogue:
SELECT *
FROM t_author a
JOIN t_book b ON a.id = b.author_id
WHERE a.year_of_birth > 1920
ORDER BY b.title
Now, from your single starting point - the "Factory" - jOOQ lets you assemble the same SQL statement in objects.
// Instanciate your factory using a JDBC Connection or DataSource,
// and specify the SQL dialect you're using. Of course you can
// have several factories in your application.
Factory create = new Factory(datasource, SQLDialect.MYSQL);
// Create the query using generated, type-safe objects. You could
// write even less code when static importing generated classes!
SelectQuery q = create.selectQuery();
q.addFrom(TAuthor.T_AUTHOR);
q.addJoin(TBook.T_BOOK, TAuthor.ID, TBook.AUTHOR_ID);
q.addCompareCondition(TAuthor.YEAR_OF_BIRTH, 1920, Comparator.GREATER);
q.addOrderBy(TBook.TITLE);
But that's not it! jOOQ comes with a lot of convenience methods, so that you can write your SQL in a very intuitive way, almost like it were a domain specific language inside of Java
// Do it all "on one line".
SelectQuery q = create.select()
.from(T_AUTHOR)
.join(T_BOOK).on(TAuthor.ID.equal(TBook.AUTHOR_ID))
.where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920))
.orderBy(TBook.TITLE).getQuery();
Executing the query and fetching results is simple
// Execute the query and fetch the results
q.execute();
Result<?> result = q.getResult();
// Loop over the resulting records
for (Record record : result) {
// Type safety assured with generics
String firstName = record.getValue(TAuthor.FIRST_NAME);
String lastName = record.getValue(TAuthor.LAST_NAME);
String title = record.getValue(TBook.TITLE);
Integer publishedIn = record.getValue(TBook.PUBLISHED_IN);
System.out.println(title + " (published in " + publishedIn + ") by " + firstName + " " + lastName);
}
If you're not joining several tables, but just selecting from one table instead, you can take advantage of the fact that jOOQ knows the datatype ahead of selection. In that case, jOOQ will provide you with basic OR-mapping functionality:
// Similar query, but don't join books to authors.
// Note the generic type that is added to your query:
SimpleSelectQuery<TAuthorRecord> q = create.select(T_AUTHOR)
.where(TAuthor.YEAR_OF_BIRTH.greaterThan(1920))
.orderBy(TAuthor.LAST_NAME).getQuery();
// When executing this query, also Result holds a specific generic type:
q.execute();
Result<TAuthorRecord> result = q.getResult();
for (TAuthorRecord author : result) {
// With generate record classes, you can use generated getters and setters:
String firstName = author.getFirstName();
String lastName = author.getLastName();
System.out.println("Author : " + firstName + " " + lastName + " wrote : ");
// Use generated foreign key navigation methods
for (TBookRecord book : author.getTBooks()) {
System.out.println(" Book : " + book.getTitle());
}
}
And even more, you can use jOOQ for simple CRUD operations, as you would expect from an OR-mapper:
// Create a new record and insert it into the database
TBookRecord book = create.newRecord(T_BOOK);
book.setTitle("My first book");
book.store();
// Update it with new values
book.setPublishedIn(2010);
book.store();
// Delete it
book.delete();
How is jOOQ different from JPA and CriteriaQuery?
The JPA has somehow adopted Hibernate's CriteriaQuery functionality, implementing roughly similar concepts. Nevertheless, Hibernate and JPA have added a lot of abstraction on top of SQL and the neat SQL features of your database. It is very difficult, if not impossible, to integrate stored procedures / functions, UDT's, database-specific functions (such as DECODE, NVL, COALESCE, INSTR, etc), which are performed a lot faster in the database than in Java. Also, relational joins and nested selects are not easily supported. The JPA stack adds a lot of power to persistence, the price for which is their complexity and the fact that useful database features are hidden from you.
jOOQ is a good compromise. While it does not have the full OR-mapping power like Hibernate - while it does not yet provide you with JPA-annotated POJO's (not yet) it will allow for a full use of your database and SQL the way you're used to.
jOOQ brings the relational world to Java without trying to cover up its origins. jOOQ is relational. And object oriented. Just in a different way. Try it for yourself and I would be very glad for any feedback you may have. Find jOOQ on http://jooq.sourceforge.net
Cheers
Lukas Eder
About the author
Lukas Eder graduated from Ecole Polytechnique Fédérale de Lausanne with a Masters degree in Computer Science. He works as a senior software engineer in a major Swiss software company, focusing on the design and implementation of software in the E-Banking field.