二、Jooq整合SpringBoot

在当前微服务盛行的情况下，Spring Boot 或 Spring Cloud 为基础的微服务体系是主流， 也是目前业务场景中新的选型方向

数据库表

-- filesystem.`user` definition

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

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.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>JooqSpringBoot</artifactId>
    <version>1.0.0</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
    </parent>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <jooq.version>3.12.3</jooq.version>
    </properties>

    <repositories>
        <repository>
            <id>nexus</id>
            <name>nexus-repository</name>
            <url>http://nexus.caih.local/repository/maven-public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <executions>
                    <execution>
                        <goals>
                            <goal>build-info</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <executable>true</executable>
                </configuration>
            </plugin>

            <!-- 代码生成器插件 -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <configuration>
                    <jdbc>
                        <driver>com.mysql.cj.jdbc.Driver</driver>
                        <url>jdbc:mysql://112.126.80.77:12345/filesystem?serverTimezone=GMT%2B8</url>
                        <user>root</user>
                        <password>admin123456</password>
                    </jdbc>
                    <generator>
                        <database>
                            <includes>.*</includes>
                            <inputSchema>filesystem</inputSchema>
                        </database>
                        <target>
                            <packageName>com.mxl.jooq</packageName>
                            <directory>/src/main/java</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

配置

Spring Boot最大的一个特性就是有很多 AutoConfiguration 自动配置, spring-boot-starter-jooq 依赖于 spring-boot-starter-jdbc， 其自动配置了数据源，事务管理器等

spring-boot-starter-jooq 自动配置了 org.jooq.Configuration 和 org.jooq.DSLContext 对象。 我们只需要在 src/main/resources/application.yml 内写好数据源相关配置，其他的一切都可以交给Spring Boot进行处理

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/learn-jooq?serverTimezone=GMT%2B8
    username: root
    password: root

新增springboot启动类

package com.mxl;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JooqSpringBootDemo {
    public static void main(String[] args) {
        SpringApplication.run(JooqSpringBootDemo.class, args);
    }
}

编译jooq代码

mvn jooq-codegen:generate

编写业务代码

package com.mxl.controller;

import com.mxl.info.UserInfo;
import com.mxl.info.UserInfoRequest;
import com.mxl.jooq.tables.records.UserRecord;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.SerializationUtils;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;

import static com.mxl.jooq.Tables.USER;

@RestController
@RequestMapping(value = "/demo")
public class DemoController {
    private DSLContext dslContext;

    public DemoController(DSLContext dslContext) {
        this.dslContext = dslContext;
    }

    @GetMapping("/query")
    public List<UserInfo> query(){
        // fetch方法可以返回一个结果集对象 Result
        // jOOQ的Result对象实现了List接口，可以直接当做集合使用
        List<UserRecord> fetchIntoClassResultList = dslContext.select().from(USER).fetchInto(UserRecord.class);
        List<UserInfo> result = new ArrayList<>();
        for (UserRecord userRecord : fetchIntoClassResultList) {
            UserInfo userInfo = new UserInfo();
            userInfo.setUserName(userRecord.getUsername());
            userInfo.setPassWord(userRecord.getPassword());
            result.add(userInfo);
        }
        return result;
    }

    @PostMapping("/insert")
    public String insert(@RequestBody UserInfoRequest request){
        UserRecord userRecord = dslContext.newRecord(USER);
        userRecord.setUsername(request.getUserName());
        userRecord.setPassword(request.getPassWord());
        int insert = userRecord.insert();
        return "success:" + insert;
    }

    @PostMapping("/update")
    public String update(@RequestBody UserInfoRequest request){
        UserRecord userRecord = dslContext.newRecord(USER);
        userRecord.setId(request.getId());
        userRecord.setUsername(request.getUserName());
        userRecord.setPassword(request.getPassWord());
        userRecord.update();
        return "success";
    }

    @DeleteMapping("/delete")
    public String delete(@RequestBody UserInfoRequest request){
        UserRecord userRecord = dslContext.newRecord(USER);
        userRecord.setId(request.getId());
        userRecord.delete();
        return "success";
    }

}

package com.mxl.info;

public class UserInfo {
    private String userName;
    private String passWord;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }
}

package com.mxl.info;

public class UserInfoRequest {
    private String userName;
    private String passWord;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }
}

JooqAutoConfiguration 源码解析

以下是jOOQ的自动配置源码，从 spring-boot-autoconfigure 内拷贝而来，可以看出此配置会在数据源配置和事务配置之后执行

对于我们经常用到的就是 Bean dslContext 和 jooqConfiguration ，大部分自动配置的Bean都会使用 @ConditionalOnMissingBean 注解， 此注解标识在没有某个Bean的情况下，才会执行该注解所标注的配置

• org.springframework.boot.autoconfigure.jooq.JooqAutoConfiguration

/**@link EnableAutoConfiguration Auto-configuration} for JOOQ.
 *
 * @author Andreas Ahlenstorf
 * @author Michael Simons
 * @author Dmytro Nosan
 * @since 1.3.0
 */
 * {
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(DSLContext.class)
@ConditionalOnBean(DataSource.class)
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, TransactionAutoConfiguration.class })
public class JooqAutoConfiguration {

	@Bean
	@ConditionalOnMissingBean(ConnectionProvider.class)
	public DataSourceConnectionProvider dataSourceConnectionProvider(DataSource dataSource) {
		return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
	}

	@Bean
	@ConditionalOnBean(PlatformTransactionManager.class)
	public SpringTransactionProvider transactionProvider(PlatformTransactionManager txManager) {
		return new SpringTransactionProvider(txManager);
	}

	@Bean
	@Order(0)
	public DefaultExecuteListenerProvider jooqExceptionTranslatorExecuteListenerProvider() {
		return new DefaultExecuteListenerProvider(new JooqExceptionTranslator());
	}

	@Configuration(proxyBeanMethods = false)
	@ConditionalOnMissingBean(DSLContext.class)
	@EnableConfigurationProperties(JooqProperties.class)
	public static class DslContextConfiguration {

		@Bean
		public DefaultDSLContext dslContext(org.jooq.Configuration configuration) {
			return new DefaultDSLContext(configuration);
		}

		@Bean
		@ConditionalOnMissingBean(org.jooq.Configuration.class)
		public DefaultConfiguration jooqConfiguration(JooqProperties properties, ConnectionProvider connectionProvider,
				DataSource dataSource, ObjectProvider<TransactionProvider> transactionProvider,
				ObjectProvider<RecordMapperProvider> recordMapperProvider,
				ObjectProvider<RecordUnmapperProvider> recordUnmapperProvider, ObjectProvider<Settings> settings,
				ObjectProvider<RecordListenerProvider> recordListenerProviders,
				ObjectProvider<ExecuteListenerProvider> executeListenerProviders,
				ObjectProvider<VisitListenerProvider> visitListenerProviders,
				ObjectProvider<TransactionListenerProvider> transactionListenerProviders,
				ObjectProvider<ExecutorProvider> executorProvider) {
			DefaultConfiguration configuration = new DefaultConfiguration();
			configuration.set(properties.determineSqlDialect(dataSource));
			configuration.set(connectionProvider);
			transactionProvider.ifAvailable(configuration::set);
			recordMapperProvider.ifAvailable(configuration::set);
			recordUnmapperProvider.ifAvailable(configuration::set);
			settings.ifAvailable(configuration::set);
			executorProvider.ifAvailable(configuration::set);
			configuration.set(recordListenerProviders.orderedStream().toArray(RecordListenerProvider[]::new));
			configuration.set(executeListenerProviders.orderedStream().toArray(ExecuteListenerProvider[]::new));
			configuration.set(visitListenerProviders.orderedStream().toArray(VisitListenerProvider[]::new));
			configuration.setTransactionListenerProvider(
					transactionListenerProviders.orderedStream().toArray(TransactionListenerProvider[]::new));
			return configuration;
		}
	}
}

如果我们需要使用多数据源，可以在启动入口中的 @SpringBootApplication 中使用 

exclude 选项，不让 Spring Boot 自动配置数据源和 jOOQ 的配置。

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, 
                                  JooqAutoConfiguration.class, 
                                  TransactionAutoConfiguration.class})