简述
最近在公司项目中许多表结构一样,每个表中数据来源不一样。在提供给其他服端的接口中,需要根据来源查询不同表中的数据,表接口一样数据不一样在选用ORM框架的时候刚开始使用的是mango,一种使用注解实现的框架,无法灵活的切换表实现动态的构建sql语句,造成代码冗余严重。后来了解到JOOQ,一种基于api实现的sql创建可以灵活的动态构建sql语句,批量获取的时候发现效率不输其他ORM框架,在后边的使用中体验不错,特聊一下使用体验。
SpringBoot集成JOOQ
JOOQ Maven依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
<exclusions>
<exclusion>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jooq/jooq-meta -->
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.14.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jooq/jooq-codegen -->
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.14.8</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.14.8</version>
</dependency>
JOOQ代码生成插件
JOOQ可以完全使用api进行操作,在这里我使用的是生成代码插件生成代码(切记:生成代码的路径最好不要放到target中,切记)
在这里我还区分在dev环境中和test环境中生成代码,生产环境中自动化部署有可能因为权限无法访问数据库造成无法打包,本人在进行线上环境部署时遇到这样的问题
<properties>
<app.package>com.sun.jooq.codegen</app.package>
</properties>
<profiles>
<profile>
<id>dev</id>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.14.8</version>
<configuration>
<jdbc>
<driver>com.mysql.cj.jdbc.Driver</driver>
<url>jdbc:mysql://127.0.0.1:3306</url>
<user>sun</user>
<password>123456</password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<generate>
<pojos>true</pojos>
<daos>true</daos>
<springAnnotations>true</springAnnotations>
<fluentSetters>true</fluentSetters>
<javaTimeTypes>true</javaTimeTypes>
</generate>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<!--include和exclude用于控制为数据库中哪些表生成代码-->
<includes>
tableA
|tableB
</includes>
<!--数据库名称-->
<inputSchema>DBName</inputSchema>
<includeRoutines>false</includeRoutines>
</database>
<target>
<packageName>${app.package}</packageName>
<directory>src/main/java/</directory>
</target>
</generator>
</configuration>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</profile>
<profile>
<id>test</id>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.14.8</version>
<configuration>
<jdbc>
<driver>com.mysql.cj.jdbc.Driver</driver>
<url>jdbc:mysql://127.0.0.1:3306</url>
<user>sun</user>
<password>12345</password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<generate>
<pojos>true</pojos>
<daos>true</daos>
<springAnnotations>true</springAnnotations>
<fluentSetters>true</fluentSetters>
<javaTimeTypes>true</javaTimeTypes>
</generate>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<!--include和exclude用于控制为数据库中哪些表生成代码-->
<includes>
tableA
|tableB
</includes>
<!--数据库名称-->
<inputSchema>DBName</inputSchema>
<includeRoutines>false</includeRoutines>
</database>
<target>
<packageName>${app.package}</packageName>
<directory>src/main/java/</directory>
</target>
</generator>
</configuration>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</profile>
<profile>
<id>prod</id>
<build>
<plugins>
</plugins>
</build>
</profile>
</profiles>
yml配置文件
在配置文件中需要配置数据源
Spring:
datasource:
password: 123456
url: jdbc:mysql://127.0.0.1:3306/test
username: sun
driver-class-name: com.mysql.cj.jdbc.Driver
jooq:
sql-dialect: mysql
JOOQ Configuration类
package com.sun.config;
/**
* @className: JooqAutoConfiguration
* @description: TODO 类描述
* @author: Szg
* @date: 2021/3/24
**/
import com.mysql.cj.jdbc.MysqlDataSource;
import com.zaxxer.hikari.HikariDataSource;
import org.jooq.*;
import org.jooq.conf.Settings;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultDSLContext;
import org.jooq.impl.DefaultExecuteListenerProvider;
import org.jooq.meta.jdbc.JDBCDatabase;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator;
import org.springframework.boot.autoconfigure.jooq.JooqProperties;
import org.springframework.boot.autoconfigure.jooq.SpringTransactionProvider;
import org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
/**
* {@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;
}
}
}
生成代码
至此,基本的集成已经完成,接下来点击编译就可以生成响应的代码
生成代码示例
生成代码示例
结语
至此集成JOOQ完成,总体来说还是比较简单,尽管在期间遇到了一些问题,但是还是解决了。