springBoot整合Jooq

Jooq配置可参考外链:https://www.jianshu.com/p/bb558aa56191

 

整合步骤:
1、引入依赖
2、引入插件并配置jooq
3、jooq应用

1、引入依赖

<!-- 引入jooq -->
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.13.2</version>
</dependency>

2、配置application.xml文件并pom.xml文件中配置插件

# 数据库配置
spring.datasource.parameters=?useSSL=false&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/jooq_flyway
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.dbname=jooq_flyway
#此处LZ使用的是mysql，不同的数据库不同的配置
spring.jooq.sql-dialect=mysql

<plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>3.13.2</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jdbc>
                        <driver>com.mysql.jdbc.Driver</driver>
                        <url>jdbc:mysql://localhost:3306/jooq_flyway</url>
                        <user>root</user>
                        <password>123456</password>
                    </jdbc>
                    <generator>
                        <!-- 代码生成器 -->
                        <name>org.jooq.codegen.JavaGenerator</name>
                        <database>
                            <!--下面这两行是为view也生成代码的关键-->
                            <!--force generating id'sfor everything in public schema, that has an 'id' field-->
                            <syntheticPrimaryKeys>public\..*\.id</syntheticPrimaryKeys>
                            <!--name for fake primary key-->
                            <overridePrimaryKeys>override_primmary_key</overridePrimaryKeys>

                            <name>org.jooq.meta.mysql.MySQLDatabase</name>

                            <!--include和exclude用于控制为数据库中哪些表生成代码-->
                            <!--<includes>.*</includes>-->
                            <excludes>schema_version</excludes>

                            <!--数据库名称-->
                            <inputSchema>jooq_flyway</inputSchema>
                        </database>

                        <generate>
                            <!--Jooq配置可参考外链:https://www.jianshu.com/p/bb558aa56191-->
                            <!--生成dao和pojo-->
                            <daos>false</daos>
                            <pojos>true</pojos>
                            <!--把数据库时间类型映射到java 8时间类型-->
                            <javaTimeTypes>true</javaTimeTypes>
                            <!-- 生成interface -->
                            <interfaces>true</interfaces>
                            <!--不在生成的代码中添加spring注释，比如@Repository-->
                            <springAnnotations>true</springAnnotations>
                            <deprecated>false</deprecated>
                            <relations>true</relations>
                            <immutablePojos>false</immutablePojos>
                            <generatedAnnotation>false</generatedAnnotation>
                            <serializablePojos>true</serializablePojos>
                            <pojosToString>true</pojosToString>
                            <pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
                            <fluentSetters>true</fluentSetters>
                            <validationAnnotations>true</validationAnnotations>
                        </generate>

                        <target>
                            <!--生成代码文件的包名及放置目录-->
                            <packageName>generated-jooq</packageName>
                            <directory>src/main/java</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>

3、注入DSLContext，书写DAO层操作数据库

package com.luotj.jooqtest.dao;

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;

/**
 * @author create by luotj
 * @Date: 2020/6/4 3:15 下午
 **/
public abstract class BaseDao {

    @Qualifier("dsl")
    @Autowired
    protected DSLContext mDSLContext;

    public BaseDao(){
    }

}

DAOImpl
 

package com.luotj.jooqtest.dao.impl;

import com.luotj.jooqtest.dao.BaseDao;
import com.luotj.jooqtest.dao.RoleDao;
import com.luotj.jooqtest.ui.controller.request.ModifyRoleInfoRequest;
import com.luotj.jooqtest.ui.controller.response.RoleInfoResponse;
import generated_jooq.tables.pojos.Role;
import generated_jooq.tables.records.RoleRecord;
import org.jooq.InsertQuery;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

import static generated_jooq.tables.Role.ROLE;

/**
 * @author create by luotj
 * @Date: 2020/6/5 6:11 下午
 **/
@Repository
public class RoleDaoImpl extends BaseDao implements RoleDao {

    @Override
    public int testTransaction(List<Long> listUserIds) {
        InsertQuery<RoleRecord> query = mDSLContext.insertQuery(ROLE);
        listUserIds.forEach(it -> {
            RoleRecord roleRecord = new RoleRecord()
                    .setRoleName("测试")
                    .setUserId(it);
            query.addRecord(roleRecord);
        });
        return query.execute();
    }

    @Override
    public void DeleteAllRoleInfo() {
        mDSLContext.deleteFrom(ROLE)
                .execute();
    }

    @Override
    public void createRoleInfo(RoleRecord record) {
        InsertQuery<RoleRecord> insertQuery = mDSLContext.insertQuery(ROLE);
        insertQuery.addRecord(record);
        insertQuery.execute();
    }

    @Override
    public void modifyRoleInfoBy(ModifyRoleInfoRequest request) {
        mDSLContext.update(ROLE)
                .set(ROLE.ROLE_NAME, request.getRoleName())
                .where(ROLE.ID.eq(request.getRoleId()))
                .execute();
    }

    @Override
    public List<RoleInfoResponse> queryAllRole() {
        return mDSLContext.selectFrom(ROLE)
                .fetchInto(RoleInfoResponse.class);
    }

    @Override
    public Optional<Role> queryRoleInfoBy(Long roleId) {
        return mDSLContext.selectFrom(ROLE)
                .where(ROLE.ID.eq(roleId))
                .fetchOptionalInto(Role.class);
    }

    @Override
    public void deleteRoleInfoBy(Long roleId) {
        mDSLContext.delete(ROLE)
                .where(ROLE.ID.eq(roleId))
                .execute();
    }

}

应用完毕------------
git项目地址：https://github.com/tangel-luotj/springboot-jooq-flyway

有问题可以一起探讨
QQ"949379865