JOOQ初体验-SpringBoot集成JOOQ

简述

最近在公司项目中许多表结构一样,每个表中数据来源不一样。在提供给其他服端的接口中,需要根据来源查询不同表中的数据,表接口一样数据不一样在选用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完成,总体来说还是比较简单,尽管在期间遇到了一些问题,但是还是解决了。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值