在当前微服务盛行的情况下,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})