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

Configuration
mp-config-LIFERAY-JOOQ.xml

<!DOCTYPE root>
<generator-config>
 <configuration>
  <conventions>
   <target-convention type="enable-updatable-code-feature" />
  </conventions>
  <model name="liferay" version="1.0" package-root="net.sf.mp.demo">
   <data-model>
    <driver name="mysql" version="5.1.16" groupId="mysql"
     artifactId="mysql-connector-java"></driver>
    <dataSource>
     <driverClassName>org.gjt.mm.mysql.Driver</driverClassName>
     <url>jdbc:mysql://127.0.0.1:3306/lportal</url>
     <username>root</username>
     <password>mysql</password>
    </dataSource>
    <schema>lportal</schema>
    <primaryKeyPolicy oneGlobal="true">
     <primaryKeyPolicyPattern name="none"></primaryKeyPolicyPattern>
    </primaryKeyPolicy>
   </data-model>
   <business-model>
    <generation-condition>
     <condition type="exclude" startsWith="QUARTZ"></condition>
    </generation-condition>
    <enrichment>
     <conventions>
      <entity-naming-convention type="apply-strip-table-name-suffix"
       pattern-to-strip="_" />
      <!-- manipulate the structure and entities BEFORE manipulating the 
       entities -->
      <foreign-key-convention
       type="autodetect-foreign-key-based-on-similarity-and-map"
       column-ending="id" column-starting="" />
      <column-naming-convention type="apply-strip-column-name-suffix"
       pattern-to-strip="ID" />
      <reference-naming-convention
       type="apply-referenced-alias-when-no-ambiguity" is-to-plurialize="true" />
     </conventions>
    </enrichment>
   </business-model>
  </model>
  <targets>
   <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>
   <target refname="LIB" fileName="mp-template-config-bsla-LIB-features.xml"
    templatedir-root="../../template/framework/bsla">
   </target>
  </targets>
 </configuration>
</generator-config>

Execution
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

Result 
Artifacts
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

 //MP-MANAGED-UPDATABLE-BEGINNING-ENABLE @jooq-record-pk-liferay@
 /**
  * An uncommented item
  * 
  * PRIMARY KEY
  */
    public void setTable(java.lang.Long value) {
        setValue(net.sf.mp.demo.liferay.tables.Expandotable.__EXPANDOTABLE.TABLE, value);
    }
 /**
  * An uncommented item
  * 
  * PRIMARY KEY
  */
    public java.lang.Long getTable_() {
        return getValue(net.sf.mp.demo.liferay.tables.Expandotable.__EXPANDOTABLE.TABLE);
    }
 //MP-MANAGED-UPDATABLE-ENDING

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

Code

The code can be downloaded from minuteproject googlecode here.

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

Testing

/**
 * This class is generated by minuteproject 4 jOOQ
 */
package net.sf.mp.demo.liferay.tables;

import net.sf.mp.demo.liferay.tables.records.UserRecord;
import net.sf.mp.demo.liferay.Liferay;
import net.sf.mp.demo.liferay.Keys;

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

import net.sf.mp.demo.liferay.LiferayFactory;
import static net.sf.mp.demo.liferay.tables.User.__USER;

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

//MP-MANAGED-ADDED-AREA-BEGINNING @import@
import static net.sf.mp.demo.liferay.tables.Company.__COMPANY;
import static net.sf.mp.demo.liferay.tables.Account.__ACCOUNT;
//MP-MANAGED-ADDED-AREA-ENDING @import@

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

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

  try {
   Class.forName("org.gjt.mm.mysql.Driver").newInstance();
   conn = DriverManager.getConnection(url, userName, password);
   LiferayFactory create = new LiferayFactory(conn);

   // MP-MANAGED-UPDATABLE-BEGINNING-ENABLE
   // @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
     .select(__USER.FIRSTNAME, __USER.LASTNAME, __COMPANY.WEB, __ACCOUNT.NAME)
     .from(__USER).join(__COMPANY)
     .on(__USER.COMPANY.equal(__COMPANY.COMPANY))
     .join(__ACCOUNT)
     .on(__COMPANY.ACCOUNT.equal(__ACCOUNT.ACCOUNT))
     .where(__COMPANY.WEB.like("%ray.com"))
     .orderBy(__USER.LASTNAME.asc().nullsFirst()).limit(10)
     .fetch();
   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);
   }
   // MP-MANAGED-UPDATABLE-ENDING

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

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

}

Giving at execution

 firstname :  lastname :  web : liferay.com accountname : Liferay
 firstname : Joe lastname : Bloggs web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 1 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 10 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 2 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 3 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 4 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 5 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 6 web : liferay.com accountname : Liferay
 firstname : Test lastname : DLC 7 web : liferay.com accountname : Liferay

JOOQ quick review

//todo

• Typesafe query speeds up the development process
• No ORM 
• Focus on CRUD and complexe query 
• No configuration loading meaning fast to test