Friday, March 13, 2015

JEPLayer, Java 8 and jOOQ a Match Made in Swiss Heaven

Note: this blog entry is a tutorial of JEPLayer, is alive and has been updated to JEPLayer v1.3, ignore the date of this blog entry.

Last update: 2015-6-3


Sometime ago I promised to Lukas Eder to make some example using JEPLayer and jOOQ, Lukas is the main author of such nice and powerful RDBMS Java toolset. A promise is a duty!


This blog entry is a tutorial of how we can use jOOQ runtime SQL generation with the ORM API of JEPLayer. jOOQ is a complete ORM, JEPLayer is also an ORM, they provide two different point of view of solving the Object-Relational problem in a Java environment.

JEPLayer is a non-intrusive approach to persistence of pure POJOs, a lightweight alternative to more intrusive and "magic" approaches like JPA implementations. JEPLayer tries to simplify to extreme the JDBC lifecycle hiding the JDBC API, but through listeners we can optionally access to the underlying pure JDBC layer avoiding as much as possible the API repetition of JDBC in JEPLayer. JEPLayer has a special focus on managing JDBC and JTA transactions.

In spite of JEPLayer ORM capabilities, a powerful SQL syntax modeled by Java objects is missing in JEPLayer (instead of plain text), jOOQ SQL modeling in Java is probably the most complete in the world. jOOQ is a perfect match for JEPLayer to write robust, error-free, refactoring friendly SQL code based on Java.

JEPLayer v1.3 introduces automatic implicit SQL generation for INSERT, UPDATE and DELETE actions, no explicit SQL is needed, anyway jOOQ SQL in Java is still invaluable for SELECT queries where complexity can be cumbersome and error prone.

In this tutorial the use of jOOQ is brief, SQL examples are very simple, SQL management of jOOQ is much more powerful than shown here.

The other new kid on the block is Java 8 (Java 1.8 specification), specially interesting for JEPLayer are streams and lambdas. JEPLayer is designed using a fluid API customized with listeners most of them based on a single method, perfect to get code simplified using lambdas, in the same time, JEPLayer returns "alive" result sets based on List (and ListIterator) interfaces, and as you know a List can be easily converted to a stream in Java 8 for some type of processing. The code of this tutorial could be even less verbose when using lambdas, but some extra unnecessary variables are exposed to show the name of the interface listener involved, because remember, this is a JEPLayer tutorial (not a Java 8 tutorial).

The first part of this tutorial just show the typical DAO class based on JEPLayer to manage a POJO class (Contact). One important feature of JEPLayer is the absolute respect to the user data model, no annotation, interface or similar artifact is needed in data model, data model is clean and independent from the persistence layer (JEPLayer), only some optional Java bean property conventions are required to easy class and attribute mapping to table and columns.

The second part of this tutorial shows some use examples of persistent actions grouped, most of them using JDBC transactions (no JTA API of JEPLayer is used in this example). Most of the code is repetitive (code similar doing the same) because the motivation is to show the extreme JDBC customization allowed by JEPLayer, especially in transaction management.


The code of this example can be found in:


This example has been coded in NetBeans using Maven, Java 8 JDK, MySQL and the C3PO connection pool. JEPLayer does not mandate a concrete connection pool, just to show a "real world" configuration.

Because there is no custom SQL code, any other RDBMS database could be used with no business code change (just changing data source bootstrap).

OK now show me the code!

No more blah blah. The methodology of this tutorial is simple, let's to show the code first, and later we will explain the details of every code snippet.

The code of this tutorial just have two parts, the DAO class and use examples managing a Contact POJO class and the second part talks about transactions.

We are presenting first the ultra complex Contact class

The DAO class managing Contact objects

Finally the use cases

Let's Explain: initialization

To initialize JEPLayer (non-JTA) we just need a DataSource:

to finally obtain a JEPLNonJTADataSource object, this object wraps the provided DataSource. In fact JEPLayer is basically a set of wrappers on top of JDBC, nothing new, the "new" JEPLayer specific part is you ever have optional access to original JDBC objects when you need some specific configuration and behavior, depending of the phase of the persistent lifecycle, avoiding re-inventing JDBC again and again.

As you can see jOOQ initialization for MySQL is trivial because we are going to use just a subset of its capabilities.

The previous code configures by default the JEPLNonJTADataSource root object to disable transactions using a JEPLConnectionListener, this is the verbose version of jds.setDefaultAutoCommit(true), in fact it is also unnecessary because transactions are disabled by default (later we are going to see many options to execute transactions with no need of enable them by default, in a ideal world all persistent actions must be inside transactions but in practice only changing actions should).

The method createTables() shows how to execute SQL raw code  using a JEPLDAL (DAL=Data Access Layer) object. A JEPLDAL object can be a singleton (the same as JEPLDAO objects in fact this interface inherits from JEPLDAL) and is designed to execute persistent actions when you do not need to convert requested data to POJOs.

Let's see how ContactDAO is initialized:

This constructor creates a JEPLDAO<Contact> object, this object can be a singleton and can be used to manage Contact objects, besides implementing the interface JEPLDAL, JEPLDAO<T> provides methods to manage the persistence of the class T specified, in this case Contact.

The constructor is very verbose to show the options of mapping columns and attributes, the parameter mappingMode decides the approach used for mapping. In this example all approaches are the same, all attributes are mapped to the columns with the same name ignoring case. The first one is enough and the simplest in this case:

The JEPLUpdateDAOListenerDefault when registered will be used to internally generate the SQL code and parameters to execute JEPLDAO<Contact>.insert(Contact obj)/update(Contact obj)/delete(Contact obj) methods.

The JEPLResultSetDAOListenerDefault when registered will be used to create the POJOs mapped to resulting rows when executing DAO queries.

If you need more complex bindings and data transformation use other more specific approach. The JEPLResultSetDAOBeanMapper is interesting when most of them match by default but someone need a custom binding or excluding.

Inserting persistent objects

Insertion example:

This is the insert() method in DAO:

Because we are not going to return Contact objects, this method uses a DAL query.The values "email", "name", "phone" are non-sense values, they are required by jOOQ and will be replaced by ?, if you need to provide inline values use inline("") and similar as parameters (this is jOOQ specific). jOOQ generates parameters with format ? , JEPLayer also allows parameters with :name format to avoid "counting accidents", because of jOOQ they are not shown in this tutorial (see the JEPLayer Manual). Because of insertion we finally call the method getGeneratedKey() calling under the hood the similar JDBC method.

There is another example of insertion, the result is the same but it is defined to show how we can optionally modify how the results are processed (in this case only one row and column, the generated id, is expected):

This specified JEPLResultSetDALListener could be registered on the constructor of ContactDAO (do not confuse with JEPLResultSetDAOListener, is DAL not DAO).

Finally there is a simple DAO sentence for insertion without explicit SQL code and parameters, the JEPLDAO<T>.insert(T obj) method uses under the hood the JEPLUpdateDAOListener<T> registered, this listener provides the column mapping and values to insert.

Alternatively we can use raw SQL code with named or numbered parameters, the code is self explanatory:

Updating persistent objects

Now the code to update:

Nothing to explain, very similar to insertion, again jOOQ in action. In this case we call executeUpdate() returning the number of "rows" involved (one in this case).

Similar to insertion we can use the simple DAO sentence for updating without explicit SQL code and parameters, the JEPLDAO<T>.update(T obj) method uses under the hood the JEPLUpdateDAOListener<T> registered.

Deleting persistent objects

The code to delete one row:

Again the 0 literal value is not used and a ? is generated instead. The call executeUpdate()returns the number of "rows" involved (one in this case).

The same to insertion and update we can use the simple DAO sentence for deleting without explicit SQL code and parameters, the JEPLDAO<T>.delete(T obj) method uses under the hood the JEPLUpdateDAOListener<T> registered.

Query and processing alive/active results

The DAO method getJEPLResultSetDAO():

only can be called inside a Connection got from DataSource, we cannot directly call the same as we execute executeUpdate() because JEPLResultSetDAO holds an active JDBC ResultSet.So we need to wrap the call and data extraction using a JEPLTask.

One shot query

If you know the number of resulting rows or you just want to load an affordable subset of rows, there is no need of using a JEPLResultSetDAO. Instead we call getResultSet() which returns a conventional List<T> (remember you can optionally register a JEPLResultSetDALListener and a mapping listener calling addJEPLListener() before getResultSet()).

The method setMaxResults() is used in this example to limit the number of results.

One shot query, alternative

The method setMaxResults()is enough but to show how much configuration is possible, we are going to show an alternative to do the same registering a JEPLPreparedStatementListener listener to customize the PrepatedStatement used under the hood (we have seen before the same kind of customization of ResultSet). By the way do not worry about threading, a PreparedStatement is bound to a Connection and only one thread can hold a Connection.

Counting rows

Because we expect just one row and a single field, there is a specific method getOneRowFromSingleField().

Select a range

Most of your queries need a range of the results based on a search criteria and order. This is why setFirstResult() and setMaxResults() exist.

Select a range, alternative
If you are an obsessed for control, you can alternatively control how the range is got through JDBC level methods.

Data Access Layer (DAL) level queries

Frequently you want to execute queries returning diverse data beyond model objects, for instance we need the number of columns and the average value of a column of a table in a single query. JEPLayer provides two approaches, using an alive java.sql.ResultSet wrapped by the interface JEPLResultSet and by using a cached result set with the interface JEPLCachedResultSet.

DAL Active Queries

When an alive java.sql.ResultSet wrapped by the interface JEPLResultSet is returned, it is similar to JEPLResultSetDAO, in this case diverse data is returned instead data model objects. Because result iteration requires an alive connection a task is required.

DAL Not Active Queries

When returned a JEPLCachedResultSet, it is similar to the POJO List returned by JEPLDAOQuery<T>.getResultList(), again diverse data is returned instead data model objects. No task is required because a connection is not required for iterating the result, everything is cached into JEPLCachedResultSet.

Transactions, transactions, transactions...

One of the most important features of RDBMS is transactions, more specifically, the implied rollback capability of transactions. The everything or nothing persistence is one of the most important features when evaluating the confidence of a IT system.

JEPLayer is conscious of how important is transactions and how much tedious and error prone is the typical manual demarcation of other DB APIs (I am not talking about jOOQ, which follows a similar approach to JEPLayer). This is why transaction demarcation (begin/commit) is defined by a single user method (nested transactions are possible) and commit is ever implicit when the method normally ends or rollback if some exception is thrown. Later we are going to see how to optionally we can manually demarcate transactions.

JEPLayer provides support to JDBC and JTA transactions, this tutorial only shows JDBC transactions (controlled by the auto-commit JDBC mode). When auto-commit is set to true (the default) every SQL sentence is executed into a built-in transaction according to the guaranties typical of an ACID system. Our interest is when auto-commit is set to false and we need to change several rows by several sentences inside a transaction.

The following examples are ever rollback examples because rollbacking our changes is how we can evaluate if our sentences have been executed into a transactions.

The simplest transaction

In JEPLayer code executed into a transaction is ever wrapped by the only method (exec()) of a JEPLTask. By setting the autoCommit parameter to false we ensure JEPLayer executes the task into a transaction and execute a commit just in the end of the task (or rollback when an exception is thrown).

Transaction by configuring the connection

By using a JEPLConnectionListener we can set auto-commit false in the Connectiongoing to be used in the transaction. JEPLayer executes the task into a transaction.

Transaction by configuring the connection (2)

With a JEPLConnectionListener we can make much more complex things and manual transaction control.

Previous example can be coded in a generic way hiding the JDBC Connection object usingJEPLTransaction instead.

Transaction by annotation

Finally we can specify a task is going to be executed into a transaction specifying @JEPLTransactionalNonJTA.


We have seen how to mix JEPLayer, Java 8 and jOOQ to code true POJO based persistent applications with less verbosity thanks to Java 8 and code less error-prone by using jOOQ.


No comments:

Post a Comment