Saturday, February 25, 2012

Productivity by example: Liferay with JOOQ

This article shows how to use jOOQ for the Liferay data model.
Minuteproject generates jOOQ artifacts for Liferay.
The main interest of using minuteproject to generate jOOQ artefacts are 
  • Liferay database does not have any foreign key, but minuteproject is able to detect relationship based on patterns.
  •  Some DB naming convention might need to be adapted at Java level
    • DB tables ending with '_' will have underscore stripped
    • Column name ending with '_id' corresponding to PK or FK will have this particule stripped when not coliding with other variable
  • Code is updatable, meaning that you can change part of it or entirely and consecutive generation will keep your alterations
For more information about the enrichment made on top of Liferay by minuteproject check another post targeting Liferay with JPA2.
Here the reverse-engineering targets jOOQ framework and the configuration mentions jOOQ track reference

<!DOCTYPE root>
   <target-convention type="enable-updatable-code-feature" />
  <model name="liferay" version="1.0" package-root="">
    <driver name="mysql" version="5.1.16" groupId="mysql"
    <primaryKeyPolicy oneGlobal="true">
     <primaryKeyPolicyPattern name="none"></primaryKeyPolicyPattern>
     <condition type="exclude" startsWith="QUARTZ"></condition>
      <entity-naming-convention type="apply-strip-table-name-suffix"
       pattern-to-strip="_" />
      <!-- manipulate the structure and entities BEFORE manipulating the 
       entities -->
       column-ending="id" column-starting="" />
      <column-naming-convention type="apply-strip-column-name-suffix"
       pattern-to-strip="ID" />
       type="apply-referenced-alias-when-no-ambiguity" is-to-plurialize="true" />
   <target refname="JOOQ" name="JOOQ" fileName="mp-template-config-JOOQ.xml"
    outputdir-root="../../dev/JOOQ/liferay" templatedir-root="../../template/framework/jooq">
    <property name="jooq-version" value="2.0.4"></property>
   <target refname="LIB" fileName="mp-template-config-bsla-LIB-features.xml"
Install Liferay mysql 6.0.6 running
Download minuteproject
Drop this config in /mywork/config
And execute model-generation.(sh/cmd) mp-config-LIFERAY-JOOQ.xml

The resulting artefacts are
  • A maven project with following dependencies
    • jOOQ
    • jUnit
    •  mysql drive
  • jOOQ artifacts
    • factory, keys, tables, records...
  • unit test
The artifacts are generated in /dev/JOOQ/liferay
If you build there will be a compilation error in jOOQ artifacts:
Table 'expandotable' has a field call 'table' that collides with a Jooq method. 
But just by altering the code to remove the compilation error and flaging to take into account this modification:
In ExpandotableRecord 
  • Change getTable into getTable_ 
  • Exclude code from been overwritten: line 24 //MP-MANAGED-UPDATABLE-BEGINNING-ENABLE
  * An uncommented item
    public void setTable(java.lang.Long value) {
        setValue(, value);
  * An uncommented item
    public java.lang.Long getTable_() {
        return getValue(;

Remark: Future version will handle this issue but it is interesting to see how the code can be customized without losing the productivity of consecutive generations.

Eventually run: mvn clean package
  • A set of unittest are executed:
    • The default unit test consist to retrieve the first row of each entity.
  • A JOOQ package is released
The code can be downloaded from minuteproject googlecode here.

Active record:
Providing the Foreign key gives the ability to provide jOOQ records with some methods
One to many

 * This class is generated by minuteproject 4 jOOQ


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import static;

import org.jooq.Record;
import org.jooq.Result;
import org.junit.Test;

import static;
import static;

//MP-MANAGED-ADDED-AREA-ENDING @class-annotation@
@javax.annotation.Generated(value = { "", "2.0.4" }, comments = "This class is generated by minuteproject 4 jOOQ")
public class TestUser {

 public void testUser() {
  Connection conn = null;
  String userName = "root";
  String password = "mysql";
  String url = "jdbc:mysql://";

  try {
   conn = DriverManager.getConnection(url, userName, password);
   LiferayFactory create = new LiferayFactory(conn);

   // @jooq-unittest-testUser-liferay@
   // write your own tests, just set DISABLE to ENABLE in the comment
   // above
   // future generation will not erase your code ;)
   Result<Record> result = create
   for (Record r : result) {
    String firstname = r.getValue(__USER.FIRSTNAME);
    String lastname = r.getValue(__USER.LASTNAME);
    String web = r.getValue(__COMPANY.WEB);
    String accountname = r.getValue(__ACCOUNT.NAME);

    System.out.println(" firstname : " + firstname + " lastname : "
      + lastname + " web : " + web + " accountname : "
      + accountname);

  } catch (Exception e) {
  } finally {
   if (conn != null) {
    try {
    } catch (SQLException e) {
     // TODO Auto-generated catch block

 // MP-MANAGED-ADDED-AREA-ENDING @implementation@

Giving at execution
 firstname :  lastname :  web : accountname : Liferay
 firstname : Joe lastname : Bloggs web : accountname : Liferay
 firstname : Test lastname : DLC 1 web : accountname : Liferay
 firstname : Test lastname : DLC 10 web : accountname : Liferay
 firstname : Test lastname : DLC 2 web : accountname : Liferay
 firstname : Test lastname : DLC 3 web : accountname : Liferay
 firstname : Test lastname : DLC 4 web : accountname : Liferay
 firstname : Test lastname : DLC 5 web : accountname : Liferay
 firstname : Test lastname : DLC 6 web : accountname : Liferay
 firstname : Test lastname : DLC 7 web : accountname : Liferay
JOOQ quick review
  • Typesafe query speeds up the development process
  • No ORM 
  • Focus on CRUD and complexe query 
  • No configuration loading meaning fast to test

No comments:

Post a Comment