【jOOQ中文】2. jOOQ与Spring和Druid整合-白红宇

【jOOQ中文】2. jOOQ与Spring和Druid整合

阅读量：7137 次

发布时间：2019-06-28

本文共 14837 字，大约阅读时间需要 49 分钟。

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

Alibaba Druid（但您也可以使用其他连接池，如BoneCP，C3P0，DBCP等）。

Spring TX作为事物管理library。

jOOQ作为SQL构建和执行library。

一、准备数据库

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依赖项：


    
        
     
      4.0.0
         
     
      com.jsyso
           
     
      jooq-tutorials-2
           
     
      jar
         
     
      jooq-tutorials-2
         
     
      1.0.0
             
             
              
      
       4.1.9.RELEASE
              
              
              
      
       1.8
              
      
       UTF-8
              
      
       true
              
      
       1.7.7
              
                      
              
      
       5.1.30
              
      
       1.0.18
              
                      
              
      
       3.9.5
              
                  
             
             
                  
       
        aliyun-repos
                   
       
        Aliyun Repository
                   
       
        http://maven.aliyun.com/nexus/content/groups/public
               
          
         
              
                  
       
        aliyun-repos
                   
       
        Aliyun Repository
                   
       
        http://maven.aliyun.com/nexus/content/groups/public
               
          
             
             
                  
       
        mysql
                   
       
        mysql-connector-java
                   
       
        ${mysql.driver.version}
               
                      
                  
       
        junit
                   
       
        junit
                   
       
        4.11
                   
       
        test
               
                      
                
       
        org.jooq
                 
       
        jooq
                 
       
        ${jooq.version}
               
              
                
       
        org.jooq
                 
       
        jooq-meta
                 
       
        ${jooq.version}
               
              
                
       
        org.jooq
                 
       
        jooq-codegen
                 
       
        ${jooq.version}
               
              
                  
       
        com.alibaba
                   
       
        druid
                   
       
        ${druid.version}
               
              
              
                  
       
        org.springframework
                   
       
        spring-core
                   
       
        ${spring.version}
                   
                       
                            
         
          commons-logging
                             
         
          commons-logging
                         
                    
               
              
                  
       
        org.springframework
                   
       
        spring-beans
                   
       
        ${spring.version}
               
              
                  
       
        org.springframework
                   
       
        spring-context
                   
       
        ${spring.version}
               
              
                  
       
        org.springframework
                   
       
        spring-context-support
                   
       
        ${spring.version}
               
              
                  
       
        org.springframework
                   
       
        spring-aop
                   
       
        ${spring.version}
                   
                       
                            
         
          commons-logging
                             
         
          commons-logging
                         
                    
               
              
                  
       
        org.springframework
                   
       
        spring-tx
                   
       
        ${spring.version}
               
              
                  
       
        org.springframework
                   
       
        spring-orm
                   
       
        ${spring.version}
               
              
                  
       
        org.springframework
                   
       
        spring-jdbc
                   
       
        ${spring.version}
               
              
              
                  
       
        junit
                   
       
        junit
                   
       
        4.11
                   
       
        test
               
              
                  
       
        org.springframework
                   
       
        spring-test
                   
       
        ${spring.version}
                   
       
        test
               
              
              
              
                  
       
        org.slf4j
                   
       
        slf4j-api
                   
       
        ${slf4j.version}
               
              
                  
       
        org.slf4j
                   
       
        slf4j-log4j12
                   
       
        ${slf4j.version}
               
              
              
                  
       
        org.slf4j
                   
       
        jcl-over-slf4j
                   
       
        ${slf4j.version}
               
              
              
                  
       
        org.slf4j
                   
       
        jul-to-slf4j
                   
       
        ${slf4j.version}
               
              
          
             
             
                  
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-compiler-plugin
                        
        
         3.5.1
                        
                            
         ${jdk.version}                    
         
          ${jdk.version}
                             
         
          true
                         
                    
                               
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-jar-plugin
                        
        
         2.4
                    
                   
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-resources-plugin
                        
        
         2.7
                    
                               
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-install-plugin
                        
        
         2.5.2
                    
                               
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-clean-plugin
                        
        
         2.6.1
                    
                               
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-dependency-plugin
                        
        
         2.10
                    
                               
                   
                       
        
         org.apache.maven.plugins
                        
        
         maven-surefire-plugin
                        
        
         2.5
                        
                            
         
          true
                         
                    
               
          
             
               
                  
       
        com.jsyso
                   
       
        Jan
                   
       
        xujian_jason@163.com
                   
       
        +8

三、Spring配置文件


    
        
     
      Spring Configuration
         
         
         
         
             
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
          
         
         
             
          
         
         
         
         
             
          
         
             
          
         
         
         
             
      
       
        MYSQL

四、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的TransactionManager来显式处理事务：

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中文】教程代码都会放在码云，希望多多宣传给Star(^_−)☆。

转载地址：http://ywtrl.baihongyu.com/

你可能感兴趣的文章

基于Serverless架构最新应用场景详解

查看>>

[BTCC] 要“工程师”“工程师”“工程师”

在Centos7中使用firewall添加端口