Using jOOQ with Spring

Today a brief tutorial on how to integrate jOOQ (http://www.jooq.org/) with Spring.
It covers the basic integration that could be easily applied to any web application.
For this tutorial I am referring to Spring 3.2.0 and jOOQ 2.6.1.
I'm referring to Oracle because the applications I am working on use this database, but all the considerations done here are valid for each database vendor supported by jOOQ.

Suppose you have a web application Spring based and want to integrate jOOQ with the most popular Inversion of Control container. First of all you have to put the jOOQ libraries in the WEB-INF/lib folder of  your application. The required jOOQ libraries are the following:

• jooq-2.6.1.jar
• jooq-codegen-2.6.1.jar
• jooq-meta-2.6.1.jar

If you use the jOOQ code generation feature you need to add to the classpath also the jooq-codegen-2.6.1.jar library.
Then you have to put in the WEB-INF/lib folder also the jOOQ dependencies:

• log4j-1.2.16.jar
• persistence-api-1.0.jar
• slf4j-api-1.6.1.jar
• validation-api-1.1.0.Alpha1.jar

The configuration has to be done in the application context file. First you have to set the datasource


<bean id="appDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@dbhost:1700:DBSID"/>
<property name="username" value="username"/>
<property name="password" value="password"/>
</bean>


<bean id="datasourceConnection" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean" 
      lazy-init="true" depends-on="appDataSource">
        <property name="targetObject">
            <ref bean="appDataSource"/>
        </property>
        <property name="targetMethod">
            <value>getConnection</value>
        </property>
    </bean>

Then you have to extend the jOOQ default Factory (org.jooq.impl.Factory) this way:


public class CustomFactory extends Factory {
private static final long serialVersionUID = -4943523198068931839L;

public CustomFactory (Connection connection, SQLDialect dialect) {
super(connection, dialect);
}

}


and register it as bean in the application context file:


<bean id="customFactory" class="jooqspring.dataaccess.CustomFactory" lazy-init="true"
      depends-on="datasourceConnection" scope="prototype">
        <constructor-arg index="0" ref="datasourceConnection"  />
        <constructor-arg index="1" type="org.jooq.SQLDialect" value="ORACLE" />
</bean>


passing the datasource connection already defined and the jOOQ SQLDialect type (changing database vendor you just need to change the SQLDialect value here) as arguments for the overridden constructor. Now you can implement your class that builds and execute queries through jOOQ. A simple example (Java implementation + bean registration) :


public class ApplicationDataAccess {
private CustomFactory customFactory;

public void setCustomFactory(CustomFactory customFactory) {
this.customFactory= customFactory;
}

public void getApplications() {
Result<Record> result = customFactory.select().from(Tables.APPLICATION).fetch();
                // Do something with the results...
}
}



<bean id="applicationDataAccess" class="jooqspring.dataaccess.ApplicationDataAccess">
<property name="customFactory" ref="customFactory"/>
</bean>


and its usage:


<bean id="applicationsController" class="jooqspring.controller.ApplicationsController">
<property name="applicationDataAccess" ref="applicationDataAccess"/>
</bean>


public class ApplicationsController extends AbstractController {
private ApplicationDataAccess applicationDataAccess;

public void setApplicationDataAccess(ApplicationDataAccess applicationDataAccess) {
this.applicationDataAccess = applicationDataAccess;
}

@Override
protected ModelAndView handleRequestInternal(HttpServletRequest arg0,
HttpServletResponse arg1) throws Exception {
...

applicationDataAccess.getApplications();

...
}

}



This is just a simple tutorial but it helps to understand how to quickly realize the integration between jOOQ and Spring. In the following weeks we will see how to make more complicated things ;)