[jOOQ中国語]2.jOOQとSpringとDruidを統合する

https://segmentfault.com/a/1190000010496053
jOOQとSpringは統合しやすいです。この例では、統合します。

Alibababa Druid（ただし、BoneCP、C 3 P 0、DBCPなどの他の接続池も使用できます。）

Spring TXは事物としてlibraryを管理します。

jOOQはSQLとして構築され、ライラックを実行する。

一、データベースの準備

DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `year_of_birth` int(11) DEFAULT NULL,
  `distinguished` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `title` varchar(400) NOT NULL,
  `published_in` int(11) NOT NULL,
  `language_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book_store`;
CREATE TABLE `book_store` (
  `name` varchar(400) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book_to_book_store`;
CREATE TABLE `book_to_book_store` (
  `name` varchar(400) NOT NULL,
  `book_id` int(11) NOT NULL,
  `stock` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `language`;
CREATE TABLE `language` (
  `id` int(11) NOT NULL,
  `cd` char(2) NOT NULL,
  `description` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `author`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `book`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_book_author` (`author_id`),
  ADD KEY `fk_book_language` (`language_id`);

ALTER TABLE `book_store`
  ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `book_to_book_store`
  ADD PRIMARY KEY (`name`,`book_id`),
  ADD KEY `fk_b2bs_book` (`book_id`);

ALTER TABLE `language`
  ADD PRIMARY KEY (`id`);

二、必要なMaven依存項を追加する。
この例では、以下のMaven依存項を作成します。

xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
    <groupId>com.jsysogroupId>
      <artifactId>jooq-tutorials-2artifactId>
      <packaging>jarpackaging>
    <name>jooq-tutorials-2name>
    <version>1.0.0version>
    
    <properties>
        
        <spring.version>4.1.9.RELEASEspring.version>
        

        
        <jdk.version>1.8jdk.version>
        <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
        <downloadSources>truedownloadSources>
        <slf4j.version>1.7.7slf4j.version>
        
        
        
        <mysql.driver.version>5.1.30mysql.driver.version>
        <druid.version>1.0.18druid.version>
        
        
        
        <jooq.version>3.9.5jooq.version>
        
        
    properties>
    
    <repositories>
        <repository>
            <id>aliyun-reposid>
            <name>Aliyun Repositoryname>
            <url>http://maven.aliyun.com/nexus/content/groups/publicurl>
        repository>
    repositories>

    <pluginRepositories> 
        <pluginRepository>
            <id>aliyun-reposid>
            <name>Aliyun Repositoryname>
            <url>http://maven.aliyun.com/nexus/content/groups/publicurl>
        pluginRepository>
    pluginRepositories>
    
    <dependencies>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>${mysql.driver.version}version>
        dependency>
        
        <dependency>
            <groupId>junitgroupId>
            <artifactId>junitartifactId>
            <version>4.11version>
            <scope>testscope>
        dependency>
        
        <dependency>
          <groupId>org.jooqgroupId>
          <artifactId>jooqartifactId>
          <version>${jooq.version}version>
        dependency>
        <dependency>
          <groupId>org.jooqgroupId>
          <artifactId>jooq-metaartifactId>
          <version>${jooq.version}version>
        dependency>
        <dependency>
          <groupId>org.jooqgroupId>
          <artifactId>jooq-codegenartifactId>
          <version>${jooq.version}version>
        dependency>

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druidartifactId>
            <version>${druid.version}version>
        dependency>

        
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-coreartifactId>
            <version>${spring.version}version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logginggroupId>
                    <artifactId>commons-loggingartifactId>
                exclusion>
            exclusions>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-beansartifactId>
            <version>${spring.version}version>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-contextartifactId>
            <version>${spring.version}version>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-context-supportartifactId>
            <version>${spring.version}version>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-aopartifactId>
            <version>${spring.version}version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logginggroupId>
                    <artifactId>commons-loggingartifactId>
                exclusion>
            exclusions>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-txartifactId>
            <version>${spring.version}version>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-ormartifactId>
            <version>${spring.version}version>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-jdbcartifactId>
            <version>${spring.version}version>
        dependency>

        
        <dependency>
            <groupId>junitgroupId>
            <artifactId>junitartifactId>
            <version>4.11version>
            <scope>testscope>
        dependency>
        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-testartifactId>
            <version>${spring.version}version>
            <scope>testscope>
        dependency>
        

        
        <dependency>
            <groupId>org.slf4jgroupId>
            <artifactId>slf4j-apiartifactId>
            <version>${slf4j.version}version>
        dependency>
        <dependency>
            <groupId>org.slf4jgroupId>
            <artifactId>slf4j-log4j12artifactId>
            <version>${slf4j.version}version>
        dependency>
        
        <dependency>
            <groupId>org.slf4jgroupId>
            <artifactId>jcl-over-slf4jartifactId>
            <version>${slf4j.version}version>
        dependency>
        
        <dependency>
            <groupId>org.slf4jgroupId>
            <artifactId>jul-to-slf4jartifactId>
            <version>${slf4j.version}version>
        dependency>
        

    dependencies>
    
    <build>
        <plugins>
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-compiler-pluginartifactId>
                <version>3.5.1version>
                <configuration>
                    <source>${jdk.version}source>
                    <target>${jdk.version}target>
                    <showWarnings>trueshowWarnings>
                configuration>
            plugin>
            
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-jar-pluginartifactId>
                <version>2.4version>
            plugin>

            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-resources-pluginartifactId>
                <version>2.7version>
            plugin>
            
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-install-pluginartifactId>
                <version>2.5.2version>
            plugin>
            
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-clean-pluginartifactId>
                <version>2.6.1version>
            plugin>
            
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-dependency-pluginartifactId>
                <version>2.10version>
            plugin>
            
            
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-surefire-pluginartifactId>
                <version>2.5version>
                <configuration>
                    <skipTests>trueskipTests>
                configuration>
            plugin>
        plugins>
    build>
    
    <developers>  
        <developer>
            <id>com.jsysoid>
            <name>Janname>
            <email>[email protected]email>
            <timezone>+8timezone>
        developer>
    developers>
    
project>

三、Springプロファイル

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:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
    xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd
        http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd"
    default-lazy-init="true">
    <description>Spring Configurationdescription>

    
    <context:property-placeholder ignore-unresolvable="true" location="classpath:config.properties" />

    
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        
        <property name="driverClassName" value="${jdbc.driver}" />

        
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        
        <property name="initialSize" value="${jdbc.pool.init}" />
        <property name="minIdle" value="${jdbc.pool.minIdle}" />
        <property name="maxActive" value="${jdbc.pool.maxActive}" />

        
        <property name="maxWait" value="60000" />

        
        <property name="timeBetweenEvictionRunsMillis" value="60000" />

        
        <property name="minEvictableIdleTimeMillis" value="300000" />

        <property name="validationQuery" value="${jdbc.testSql}" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />

        
        <property name="filters" value="stat" />
    bean>

    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    bean>
    
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
    
    <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>
    
    <bean id="exceptionTranslator" class="test.generated.exception.ExceptionTranslator" />

    <bean class="org.jooq.impl.DefaultConfiguration" name="config">
        <property name="SQLDialect"><value type="org.jooq.SQLDialect">MYSQLvalue>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>

    
    <bean id="dsl" class="org.jooq.impl.DefaultDSLContext">
        <constructor-arg ref="config" />
    bean>

beans>

四、Spring Test+JUnit統合テスト
クエリーテスト:

package test.generated.service;

import static java.util.Arrays.asList;
import static org.jooq.impl.DSL.countDistinct;
import static org.junit.Assert.assertEquals;
import static test.generated.Tables.*;

import org.jooq.DSLContext;
import org.jooq.Record3;
import org.jooq.Result;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import test.generated.tables.Author;
import test.generated.tables.Book;
import test.generated.tables.BookStore;
import test.generated.tables.BookToBookStore;
import test.generated.tables.records.BookRecord;

/**
 * @author Lukas Eder
 */
@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> 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.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)));
    }

}

データ挿入、SpringのTransactionagerを使って、トランザクションを明示的に処理します。

package test.generated.service;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import static test.generated.Tables.BOOK;

import java.util.concurrent.atomic.AtomicBoolean;

import org.jooq.DSLContext;

import org.junit.After;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

/**
 * @author Petri Kainulainen
 * @author Lukas Eder
 *
 * @see http://www.petrikainulainen.net/programming/jooq/using-jooq-with-spring-configuration/
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"/jooq-spring.xml"})
@TransactionConfiguration(transactionManager="transactionManager")
public class TransactionTest {

    @Autowired DSLContext                   dsl;
    @Autowired DataSourceTransactionManager txMgr;

    @Test
    public void testDelBooks() {

        // Delete all books that were created in any test
        dsl.delete(BOOK).where(BOOK.ID.gt(4)).execute();
    }

    @Test
    public void testAddBooks() {
        TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
        for (int i = 1; i <= 6; i++)
            dsl.insertInto(BOOK)
                    .set(BOOK.ID, i)
                    .set(BOOK.PUBLISHED_IN, 1)
                    .set(BOOK.LANGUAGE_ID, 1)
                    .set(BOOK.AUTHOR_ID, 1)
                    .set(BOOK.TITLE, "Book " + i)
                    .execute();
        txMgr.commit(tx);
    }

    @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 = 7; i <=9; i++)
                dsl.insertInto(BOOK)
                        .set(BOOK.ID, i)
                        .set(BOOK.AUTHOR_ID, 1)
                        .set(BOOK.TITLE, "Book " + i)
                        .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);
    }
}

【jOOQ中国語】教程コードは全部コードクラウドに置いています。スターにたくさん宣伝してほしいです。

https://gitee.com/xujian_jaso…

転載先:https://www.cnblogs.com/xiang--liu/p/9710271.html