The jOOQ User Manual : Getting started with jOOQ : Tutorials : Using jOOQ with Spring and Apache DBCP | previous : next |
Available in versions: Dev (3.17) | Latest (3.16) | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7
Using jOOQ with Spring and Apache DBCP
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ and Spring are easy to integrate. In this example, we shall integrate:
- Apache DBCP (but you may as well use some other connection pool, like BoneCP, C3P0, HikariCP, and various others).
- Spring TX as the transaction management library.
- jOOQ as the SQL building and execution library.
Before you copy the manual examples, consider also these further resources:
- The complete example can also be downloaded from GitHub.
- Another example using Spring and Guice for transaction management can be downloaded from GitHub.
- Another, excellent tutorial by Petri Kainulainen can be found here.
Add the required Maven dependencies
For this example, we'll create the following Maven dependencies
<!-- Use this or the latest Spring RELEASE version --> <properties> <org.springframework.version>3.2.3.RELEASE</org.springframework.version> </properties> <dependencies> <!-- Database access --> <dependency> <!-- Use org.jooq for the Open Source Edition org.jooq.pro for commercial editions, org.jooq.pro-java-8 for commercial editions with Java 8 support, org.jooq.pro-java-6 for commercial editions with Java 6 support, org.jooq.trial for the free trial edition Note: Only the Open Source Edition is hosted on Maven Central. Import the others manually from your distribution --> <groupId>org.jooq</groupId> <artifactId>jooq</artifactId> <version>3.14.15</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.197</version> </dependency> <!-- Logging --> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-slf4j-impl</artifactId> <version>2.11.0</version> </dependency> <!-- Spring (transitive dependencies are not listed explicitly) --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> </dependency> <!-- Testing --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <type>jar</type> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${org.springframework.version}</version> <scope>test</scope> </dependency> </dependencies>
Note that only the jOOQ Open Source Edition is available from Maven Central. If you're using the jOOQ Professional Edition or the jOOQ Enterprise Edition, you will have to manually install jOOQ in your local Nexus, or in your local Maven cache. For more information, please refer to the licensing pages.
Create a minimal Spring configuration file
The above dependencies are configured together using a Spring Beans configuration:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd"> <!-- This is needed if you want to use the @Transactional annotation --> <tx:annotation-driven transaction-manager="transactionManager"/> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" > <!-- These properties are replaced by Maven "resources" --> <property name="url" value="${db.url}" /> <property name="driverClassName" value="${db.driver}" /> <property name="username" value="${db.username}" /> <property name="password" value="${db.password}" /> </bean> <!-- Configure Spring's transaction manager to use a DataSource --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <!-- Configure jOOQ's ConnectionProvider to use Spring's TransactionAwareDataSourceProxy, which can dynamically discover the transaction context --> <bean id="transactionAwareDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy"> <constructor-arg ref="dataSource" /> </bean> <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider"> <constructor-arg ref="transactionAwareDataSource" /> </bean> <!-- Configure the DSL object, optionally overriding jOOQ Exceptions with Spring Exceptions --> <bean id="dsl" class="org.jooq.impl.DefaultDSLContext"> <constructor-arg ref="config" /> </bean> <bean id="exceptionTranslator" class="org.jooq.example.spring.exception.ExceptionTranslator" /> <!-- Invoking an internal, package-private constructor for the example Implement your own Configuration for more reliable behaviour --> <bean class="org.jooq.impl.DefaultConfiguration" name="config"> <property name="SQLDialect"><value type="org.jooq.SQLDialect">H2</value></property> <property name="connectionProvider" ref="connectionProvider" /> <property name="executeListenerProvider"> <array> <bean class="org.jooq.impl.DefaultExecuteListenerProvider"> <constructor-arg index="0" ref="exceptionTranslator"/> </bean> </array> </property> </bean> <!-- This is the "business-logic" --> <bean id="books" class="org.jooq.example.spring.impl.DefaultBookService"/> </beans>
Run a query using the above configuration:
With the above configuration, you should be ready to run queries pretty quickly. For instance, in an integration-test, you could use Spring to run JUnit:
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = {"/jooq-spring.xml"}) public class QueryTest { @Autowired DSLContext create; @Test public void testJoin() throws Exception { // All of these tables were generated by jOOQ's Maven plugin Book b = BOOK.as("b"); Author a = AUTHOR.as("a"); BookStore s = BOOK_STORE.as("s"); BookToBookStore t = BOOK_TO_BOOK_STORE.as("t"); Result<Record3<String, String, Integer>> result = create.select(a.FIRST_NAME, a.LAST_NAME, countDistinct(s.NAME)) .from(a) .join(b).on(b.AUTHOR_ID.eq(a.ID)) .join(t).on(t.BOOK_ID.eq(b.ID)) .join(s).on(t.BOOK_STORE_NAME.eq(s.NAME)) .groupBy(a.FIRST_NAME, a.LAST_NAME) .orderBy(countDistinct(s.NAME).desc()) .fetch(); assertEquals(2, result.size()); assertEquals("Paulo", result.getValue(0, a.FIRST_NAME)); assertEquals("George", result.getValue(1, a.FIRST_NAME)); assertEquals("Coelho", result.getValue(0, a.LAST_NAME)); assertEquals("Orwell", result.getValue(1, a.LAST_NAME)); assertEquals(Integer.valueOf(3), result.getValue(0, countDistinct(s.NAME))); assertEquals(Integer.valueOf(2), result.getValue(1, countDistinct(s.NAME))); } }
Run a queries in an explicit transaction:
The following example shows how you can use Spring's TransactionManager to explicitly handle transactions:
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = {"/jooq-spring.xml"}) @TransactionConfiguration(transactionManager="transactionManager") public class TransactionTest { @Autowired DSLContext dsl; @Autowired DataSourceTransactionManager txMgr; @Autowired BookService books; @After public void teardown() { // Delete all books that were created in any test dsl.delete(BOOK).where(BOOK.ID.gt(4)).execute(); } @Test public void testExplicitTransactions() { boolean rollback = false; TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition()); try { // This is a "bug". The same book is created twice, resulting in a // constraint violation exception for (int i = 0; i < 2; i++) dsl.insertInto(BOOK) .set(BOOK.ID, 5) .set(BOOK.AUTHOR_ID, 1) .set(BOOK.TITLE, "Book 5") .execute(); Assert.fail(); } // Upon the constraint violation, we explicitly roll back the transaction. catch (DataAccessException e) { txMgr.rollback(tx); rollback = true; } assertEquals(4, dsl.fetchCount(BOOK)); assertTrue(rollback); } }
Run queries using declarative transactions
Spring-TX has very powerful means to handle transactions declaratively, using the @Transactional
annotation. The BookService
that we had defined in the previous Spring configuration can be seen here:
public interface BookService { /** * Create a new book. * <p> * The implementation of this method has a bug, which causes this method to * fail and roll back the transaction. */ @Transactional void create(int id, int authorId, String title); }
And here is how we interact with it:
@Test public void testDeclarativeTransactions() { boolean rollback = false; try { // The service has a "bug", resulting in a constraint violation exception books.create(5, 1, "Book 5"); Assert.fail(); } catch (DataAccessException ignore) { rollback = true; } assertEquals(4, dsl.fetchCount(BOOK)); assertTrue(rollback); }
Run queries using jOOQ's transaction API
jOOQ has its own programmatic transaction API that can be used with Spring transactions by implementing the jOOQ org.jooq.TransactionProvider SPI and passing that to your jOOQ Configuration. More details about this transaction API can be found in the manual's section about transaction management.
You can try the above example yourself by downloading it from GitHub.
Feedback
Do you have any feedback about this page? We'd love to hear it!