JOOQ初体验-SpringBoot集成JOOQ

最新推荐文章于 2024-05-31 18:12:13 发布

孙治纲

最新推荐文章于 2024-05-31 18:12:13 发布

阅读量830

点赞数

分类专栏： ORM 文章标签： java spring boot

本文链接：https://blog.csdn.net/WillingGang/article/details/115583292

版权

ORM 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

简述

最近在公司项目中许多表结构一样，每个表中数据来源不一样。在提供给其他服端的接口中，需要根据来源查询不同表中的数据，表接口一样数据不一样在选用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
评论
JOOQ初体验-SpringBoot集成JOOQ

简述最近在公司项目中许多表结构一样，每个表中数据来源不一样。在提供给其他服端的接口中，需要根据来源查询不同表中的数据，表接口一样数据不一样在选用ORM框架的时候刚开始使用的是mango，一种使用注解实现的框架，无法灵活的切换表实现动态的构建sql语句，造成代码冗余严重。后来了解到JOOQ，一种基于api实现的sql创建可以灵活的动态构建sql语句，批量获取的时候发现效率不输其他ORM框架，在后边的使用中体验不错，特聊一下使用体验。SpringBoot集成JOOQJOOQ Maven依赖
复制链接

扫一扫