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 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
//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
