查看: 479|回复: 0

[Java代码] 【jOOQ中文】2. jOOQ与Spring和Druid整合

发表于 2017-8-7 08:00:02

jOOQ和Spring很容易整合。 在这个例子中,我们将整合:

Alibaba Druid(但您也可以使用其他连接池,如BoneCP,C3P0,DBCP等)。

Spring TX作为事物管理library。

jOOQ作为SQL构建和执行library。

一、准备数据库
  1. DROP TABLE IF EXISTS `author`;
  2. CREATE TABLE `author` (
  3. `id` int(11) NOT NULL,
  4. `first_name` varchar(50) DEFAULT NULL,
  5. `last_name` varchar(50) NOT NULL,
  6. `date_of_birth` date DEFAULT NULL,
  7. `year_of_birth` int(11) DEFAULT NULL,
  8. `distinguished` int(1) DEFAULT NULL
  9. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  10. DROP TABLE IF EXISTS `book`;
  11. CREATE TABLE `book` (
  12. `id` int(11) NOT NULL,
  13. `author_id` int(11) NOT NULL,
  14. `title` varchar(400) NOT NULL,
  15. `published_in` int(11) NOT NULL,
  16. `language_id` int(11) NOT NULL
  17. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  18. DROP TABLE IF EXISTS `book_store`;
  19. CREATE TABLE `book_store` (
  20. `name` varchar(400) NOT NULL
  21. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  22. DROP TABLE IF EXISTS `book_to_book_store`;
  23. CREATE TABLE `book_to_book_store` (
  24. `name` varchar(400) NOT NULL,
  25. `book_id` int(11) NOT NULL,
  26. `stock` int(11) DEFAULT NULL
  27. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  28. DROP TABLE IF EXISTS `language`;
  29. CREATE TABLE `language` (
  30. `id` int(11) NOT NULL,
  31. `cd` char(2) NOT NULL,
  32. `description` varchar(50) DEFAULT NULL
  33. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  34. ALTER TABLE `author`
  35. ADD PRIMARY KEY (`id`);
  36. ALTER TABLE `book`
  37. ADD PRIMARY KEY (`id`),
  38. ADD KEY `fk_book_author` (`author_id`),
  39. ADD KEY `fk_book_language` (`language_id`);
  40. ALTER TABLE `book_store`
  41. ADD UNIQUE KEY `name` (`name`);
  42. ALTER TABLE `book_to_book_store`
  43. ADD PRIMARY KEY (`name`,`book_id`),
  44. ADD KEY `fk_b2bs_book` (`book_id`);
  45. ALTER TABLE `language`
  46. ADD PRIMARY KEY (`id`);
复制代码
二、添加所需的Maven依赖项

在这个例子中,我们将创建以下Maven依赖项:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <modelVersion>4.0.0</modelVersion>
  5. <groupId>com.jsyso</groupId>
  6. <artifactId>jooq-tutorials-2</artifactId>
  7. <packaging>jar</packaging>
  8. <name>jooq-tutorials-2</name>
  9. <version>1.0.0</version>
  10. <properties>
  11. <!-- spring -->
  12. <spring.version>4.1.9.RELEASE</spring.version>
  13. <!-- /spring -->
  14. <!-- environment -->
  15. <jdk.version>1.8</jdk.version>
  16. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  17. <downloadSources>true</downloadSources>
  18. <slf4j.version>1.7.7</slf4j.version>
  19. <!-- /environment -->
  20. <!-- jdbc -->
  21. <mysql.driver.version>5.1.30</mysql.driver.version>
  22. <druid.version>1.0.18</druid.version>
  23. <!-- /jdbc -->
  24. <!-- jooq -->
  25. <jooq.version>3.9.5</jooq.version>
  26. <!-- /jooq -->
  27. </properties>
  28. <repositories>
  29. <repository>
  30. <id>aliyun-repos</id>
  31. <name>Aliyun Repository</name>
  32. <url>http://maven.aliyun.com/nexus/content/groups/public</url>
  33. </repository>
  34. </repositories>
  35. <pluginRepositories>
  36. <pluginRepository>
  37. <id>aliyun-repos</id>
  38. <name>Aliyun Repository</name>
  39. <url>http://maven.aliyun.com/nexus/content/groups/public</url>
  40. </pluginRepository>
  41. </pluginRepositories>
  42. <dependencies>
  43. <dependency>
  44. <groupId>mysql</groupId>
  45. <artifactId>mysql-connector-java</artifactId>
  46. <version>${mysql.driver.version}</version>
  47. </dependency>
  48. <dependency>
  49. <groupId>junit</groupId>
  50. <artifactId>junit</artifactId>
  51. <version>4.11</version>
  52. <scope>test</scope>
  53. </dependency>
  54. <dependency>
  55. <groupId>org.jooq</groupId>
  56. <artifactId>jooq</artifactId>
  57. <version>${jooq.version}</version>
  58. </dependency>
  59. <dependency>
  60. <groupId>org.jooq</groupId>
  61. <artifactId>jooq-meta</artifactId>
  62. <version>${jooq.version}</version>
  63. </dependency>
  64. <dependency>
  65. <groupId>org.jooq</groupId>
  66. <artifactId>jooq-codegen</artifactId>
  67. <version>${jooq.version}</version>
  68. </dependency>
  69. <dependency>
  70. <groupId>com.alibaba</groupId>
  71. <artifactId>druid</artifactId>
  72. <version>${druid.version}</version>
  73. </dependency>
  74. <!-- spring -->
  75. <dependency>
  76. <groupId>org.springframework</groupId>
  77. <artifactId>spring-core</artifactId>
  78. <version>${spring.version}</version>
  79. <exclusions>
  80. <exclusion>
  81. <groupId>commons-logging</groupId>
  82. <artifactId>commons-logging</artifactId>
  83. </exclusion>
  84. </exclusions>
  85. </dependency>
  86. <dependency>
  87. <groupId>org.springframework</groupId>
  88. <artifactId>spring-beans</artifactId>
  89. <version>${spring.version}</version>
  90. </dependency>
  91. <dependency>
  92. <groupId>org.springframework</groupId>
  93. <artifactId>spring-context</artifactId>
  94. <version>${spring.version}</version>
  95. </dependency>
  96. <dependency>
  97. <groupId>org.springframework</groupId>
  98. <artifactId>spring-context-support</artifactId>
  99. <version>${spring.version}</version>
  100. </dependency>
  101. <dependency>
  102. <groupId>org.springframework</groupId>
  103. <artifactId>spring-aop</artifactId>
  104. <version>${spring.version}</version>
  105. <exclusions>
  106. <exclusion>
  107. <groupId>commons-logging</groupId>
  108. <artifactId>commons-logging</artifactId>
  109. </exclusion>
  110. </exclusions>
  111. </dependency>
  112. <dependency>
  113. <groupId>org.springframework</groupId>
  114. <artifactId>spring-tx</artifactId>
  115. <version>${spring.version}</version>
  116. </dependency>
  117. <dependency>
  118. <groupId>org.springframework</groupId>
  119. <artifactId>spring-orm</artifactId>
  120. <version>${spring.version}</version>
  121. </dependency>
  122. <dependency>
  123. <groupId>org.springframework</groupId>
  124. <artifactId>spring-jdbc</artifactId>
  125. <version>${spring.version}</version>
  126. </dependency>
  127. <!-- TEST begin -->
  128. <dependency>
  129. <groupId>junit</groupId>
  130. <artifactId>junit</artifactId>
  131. <version>4.11</version>
  132. <scope>test</scope>
  133. </dependency>
  134. <dependency>
  135. <groupId>org.springframework</groupId>
  136. <artifactId>spring-test</artifactId>
  137. <version>${spring.version}</version>
  138. <scope>test</scope>
  139. </dependency>
  140. <!-- TEST end -->
  141. <!-- LOGGING begin -->
  142. <dependency>
  143. <groupId>org.slf4j</groupId>
  144. <artifactId>slf4j-api</artifactId>
  145. <version>${slf4j.version}</version>
  146. </dependency>
  147. <dependency>
  148. <groupId>org.slf4j</groupId>
  149. <artifactId>slf4j-log4j12</artifactId>
  150. <version>${slf4j.version}</version>
  151. </dependency>
  152. <!-- common-logging 实际调用slf4j -->
  153. <dependency>
  154. <groupId>org.slf4j</groupId>
  155. <artifactId>jcl-over-slf4j</artifactId>
  156. <version>${slf4j.version}</version>
  157. </dependency>
  158. <!-- java.util.logging 实际调用slf4j -->
  159. <dependency>
  160. <groupId>org.slf4j</groupId>
  161. <artifactId>jul-to-slf4j</artifactId>
  162. <version>${slf4j.version}</version>
  163. </dependency>
  164. <!-- LOGGING end -->
  165. </dependencies>
  166. <build>
  167. <plugins>
  168. <!-- Compiler 插件, 设定JDK版本 -->
  169. <plugin>
  170. <groupId>org.apache.maven.plugins</groupId>
  171. <artifactId>maven-compiler-plugin</artifactId>
  172. <version>3.5.1</version>
  173. <configuration>
  174. <source>${jdk.version}</source>
  175. <target>${jdk.version}</target>
  176. <showWarnings>true</showWarnings>
  177. </configuration>
  178. </plugin>
  179. <!-- 打包jar文件时,配置manifest文件,加入lib包的jar依赖 -->
  180. <plugin>
  181. <groupId>org.apache.maven.plugins</groupId>
  182. <artifactId>maven-jar-plugin</artifactId>
  183. <version>2.4</version>
  184. </plugin>
  185. <!-- resource插件 -->
  186. <plugin>
  187. <groupId>org.apache.maven.plugins</groupId>
  188. <artifactId>maven-resources-plugin</artifactId>
  189. <version>2.7</version>
  190. </plugin>
  191. <!-- install插件 -->
  192. <plugin>
  193. <groupId>org.apache.maven.plugins</groupId>
  194. <artifactId>maven-install-plugin</artifactId>
  195. <version>2.5.2</version>
  196. </plugin>
  197. <!-- clean插件 -->
  198. <plugin>
  199. <groupId>org.apache.maven.plugins</groupId>
  200. <artifactId>maven-clean-plugin</artifactId>
  201. <version>2.6.1</version>
  202. </plugin>
  203. <!-- dependency插件 -->
  204. <plugin>
  205. <groupId>org.apache.maven.plugins</groupId>
  206. <artifactId>maven-dependency-plugin</artifactId>
  207. <version>2.10</version>
  208. </plugin>
  209. <!-- 打包时跳过不执行测试用例 -->
  210. <plugin>
  211. <groupId>org.apache.maven.plugins</groupId>
  212. <artifactId>maven-surefire-plugin</artifactId>
  213. <version>2.5</version>
  214. <configuration>
  215. <skipTests>true</skipTests>
  216. </configuration>
  217. </plugin>
  218. </plugins>
  219. </build>
  220. <developers>
  221. <developer>
  222. <id>com.jsyso</id>
  223. <name>Jan</name>
  224. <email>xujian_jason@163.com</email>
  225. <timezone>+8</timezone>
  226. </developer>
  227. </developers>
  228. </project>
复制代码
三、Spring配置文件
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
  4. xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
  5. xmlns:util="http://www.springframework.org/schema/util" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation="
  6. http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
  7. http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
  8. http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
  9. http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd
  10. http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
  11. http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd
  12. http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd"
  13. default-lazy-init="true">
  14. <description>Spring Configuration</description>
  15. <!-- 加载配置属性文件 -->
  16. <context:property-placeholder ignore-unresolvable="true" location="classpath:config.properties" />
  17. <!-- 数据源配置 -->
  18. <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  19. <!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass -->
  20. <property name="driverClassName" value="${jdbc.driver}" />
  21. <!-- 基本属性 url、user、password -->
  22. <property name="url" value="${jdbc.url}" />
  23. <property name="username" value="${jdbc.username}" />
  24. <property name="password" value="${jdbc.password}" />
  25. <!-- 配置初始化大小、最小、最大 -->
  26. <property name="initialSize" value="${jdbc.pool.init}" />
  27. <property name="minIdle" value="${jdbc.pool.minIdle}" />
  28. <property name="maxActive" value="${jdbc.pool.maxActive}" />
  29. <!-- 配置获取连接等待超时的时间 -->
  30. <property name="maxWait" value="60000" />
  31. <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
  32. <property name="timeBetweenEvictionRunsMillis" value="60000" />
  33. <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
  34. <property name="minEvictableIdleTimeMillis" value="300000" />
  35. <property name="validationQuery" value="${jdbc.testSql}" />
  36. <property name="testWhileIdle" value="true" />
  37. <property name="testOnBorrow" value="false" />
  38. <property name="testOnReturn" value="false" />
  39. <!-- 配置监控统计拦截的filters -->
  40. <property name="filters" value="stat" />
  41. </bean>
  42. <!-- 定义事务 -->
  43. <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  44. <property name="dataSource" ref="dataSource" />
  45. </bean>
  46. <!-- 配置 Annotation 驱动,扫描@Transactional注解的类定义事务 -->
  47. <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
  48. <!-- 配置jOOQ的ConnectionProvider使用Spring的TransactionAwareDataSourceProx -->
  49. <bean id="transactionAwareDataSource"
  50. class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
  51. <constructor-arg ref="dataSource" />
  52. </bean>
  53. <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">
  54. <constructor-arg ref="transactionAwareDataSource" />
  55. </bean>
  56. <!-- 可选,重写jOOQ异常,抛出Spring Exception -->
  57. <bean id="exceptionTranslator" class="test.generated.exception.ExceptionTranslator" />
  58. <bean class="org.jooq.impl.DefaultConfiguration" name="config">
  59. <property name="SQLDialect"><value type="org.jooq.SQLDialect">MYSQL</value></property>
  60. <property name="connectionProvider" ref="connectionProvider" />
  61. <property name="executeListenerProvider">
  62. <array>
  63. <bean class="org.jooq.impl.DefaultExecuteListenerProvider">
  64. <constructor-arg index="0" ref="exceptionTranslator"/>
  65. </bean>
  66. </array>
  67. </property>
  68. </bean>
  69. <!-- 配置jOOQ的dsl对象 -->
  70. <bean id="dsl" class="org.jooq.impl.DefaultDSLContext">
  71. <constructor-arg ref="config" />
  72. </bean>
  73. </beans>
复制代码
四、Spring Test + JUnit集成测试

查询测试:

  1. package test.generated.service;
  2. import static java.util.Arrays.asList;
  3. import static org.jooq.impl.DSL.countDistinct;
  4. import static org.junit.Assert.assertEquals;
  5. import static test.generated.Tables.*;
  6. import org.jooq.DSLContext;
  7. import org.jooq.Record3;
  8. import org.jooq.Result;
  9. import org.junit.Test;
  10. import org.junit.runner.RunWith;
  11. import org.springframework.beans.factory.annotation.Autowired;
  12. import org.springframework.test.context.ContextConfiguration;
  13. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  14. import test.generated.tables.Author;
  15. import test.generated.tables.Book;
  16. import test.generated.tables.BookStore;
  17. import test.generated.tables.BookToBookStore;
  18. import test.generated.tables.records.BookRecord;
  19. /**
  20. * @author Lukas Eder
  21. */
  22. @RunWith(SpringJUnit4ClassRunner.class)
  23. @ContextConfiguration(locations = {"/jooq-spring.xml"})
  24. public class QueryTest {
  25. @Autowired
  26. DSLContext create;
  27. @Test
  28. public void testJoin() throws Exception {
  29. // All of these tables were generated by jOOQ's Maven plugin
  30. Book b = BOOK.as("b");
  31. Author a = AUTHOR.as("a");
  32. BookStore s = BOOK_STORE.as("s");
  33. BookToBookStore t = BOOK_TO_BOOK_STORE.as("t");
  34. Result<Record3<String, String, Integer>> result =
  35. create.select(a.FIRST_NAME, a.LAST_NAME, countDistinct(s.NAME))
  36. .from(a)
  37. .join(b).on(b.AUTHOR_ID.eq(a.ID))
  38. .join(t).on(t.BOOK_ID.eq(b.ID))
  39. .join(s).on(t.NAME.eq(s.NAME))
  40. .groupBy(a.FIRST_NAME, a.LAST_NAME)
  41. .orderBy(countDistinct(s.NAME).desc())
  42. .fetch();
  43. assertEquals(2, result.size());
  44. assertEquals("Paulo", result.getValue(0, a.FIRST_NAME));
  45. assertEquals("George", result.getValue(1, a.FIRST_NAME));
  46. assertEquals("Coelho", result.getValue(0, a.LAST_NAME));
  47. assertEquals("Orwell", result.getValue(1, a.LAST_NAME));
  48. assertEquals(Integer.valueOf(3), result.getValue(0, countDistinct(s.NAME)));
  49. assertEquals(Integer.valueOf(2), result.getValue(1, countDistinct(s.NAME)));
  50. }
  51. }
复制代码

数据插入,使用Spring的TransactionManager来显式处理事务:

  1. package test.generated.service;
  2. import static org.junit.Assert.assertEquals;
  3. import static org.junit.Assert.assertTrue;
  4. import static test.generated.Tables.BOOK;
  5. import java.util.concurrent.atomic.AtomicBoolean;
  6. import org.jooq.DSLContext;
  7. import org.junit.After;
  8. import org.junit.Assert;
  9. import org.junit.Test;
  10. import org.junit.runner.RunWith;
  11. import org.springframework.beans.factory.annotation.Autowired;
  12. import org.springframework.dao.DataAccessException;
  13. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  14. import org.springframework.test.context.ContextConfiguration;
  15. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  16. import org.springframework.test.context.transaction.TransactionConfiguration;
  17. import org.springframework.transaction.TransactionStatus;
  18. import org.springframework.transaction.support.DefaultTransactionDefinition;
  19. /**
  20. * @author Petri Kainulainen
  21. * @author Lukas Eder
  22. *
  23. * @see <a
  24. * href="http://www.petrikainulainen.net/programming/jooq/using-jooq-with-spring-configuration/">http://www.petrikainulainen.net/programming/jooq/using-jooq-with-spring-configuration/</a>
  25. */
  26. @RunWith(SpringJUnit4ClassRunner.class)
  27. @ContextConfiguration(locations = {"/jooq-spring.xml"})
  28. @TransactionConfiguration(transactionManager="transactionManager")
  29. public class TransactionTest {
  30. @Autowired DSLContext dsl;
  31. @Autowired DataSourceTransactionManager txMgr;
  32. @Test
  33. public void testDelBooks() {
  34. // Delete all books that were created in any test
  35. dsl.delete(BOOK).where(BOOK.ID.gt(4)).execute();
  36. }
  37. @Test
  38. public void testAddBooks() {
  39. TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
  40. for (int i = 1; i <= 6; i++)
  41. dsl.insertInto(BOOK)
  42. .set(BOOK.ID, i)
  43. .set(BOOK.PUBLISHED_IN, 1)
  44. .set(BOOK.LANGUAGE_ID, 1)
  45. .set(BOOK.AUTHOR_ID, 1)
  46. .set(BOOK.TITLE, "Book " + i)
  47. .execute();
  48. txMgr.commit(tx);
  49. }
  50. @Test
  51. public void testExplicitTransactions() {
  52. boolean rollback = false;
  53. TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
  54. try {
  55. // This is a "bug". The same book is created twice, resulting in a
  56. // constraint violation exception
  57. for (int i = 7; i <=9; i++)
  58. dsl.insertInto(BOOK)
  59. .set(BOOK.ID, i)
  60. .set(BOOK.AUTHOR_ID, 1)
  61. .set(BOOK.TITLE, "Book " + i)
  62. .execute();
  63. Assert.fail();
  64. }
  65. // Upon the constraint violation, we explicitly roll back the transaction.
  66. catch (DataAccessException e) {
  67. txMgr.rollback(tx);
  68. rollback = true;
  69. }
  70. assertEquals(4, dsl.fetchCount(BOOK));
  71. assertTrue(rollback);
  72. }
  73. }
复制代码

【jOOQ中文】教程代码都会放在码云,希望多多宣传给Star(^_?)☆。

https://gitee.com/xujian_jaso...



回复

使用道具 举报