spring boot 中使用jooq管理数据

一、jooq是什么？

　　jOOQ是Data Geekery提供的基于Java的轻量级数据库访问库，通过特定的语法将类以及数据库模型翻译成对应的SQL语句实现实体与关系的映射，在数据库与类之间建立起一一对应关系，也可以让我们通过它的流式API构建出类型安全的SQL查询。jOOQ是一个基于Java编写SQL的工具包，具有简单、轻量、函数式编程写SQL等独特优势，非常适合敏捷快速迭代开发。jOOQ不管是商业版，还是开源版本都能跟Spring Boot一块使用。

二、jooq 的特点：

JOOQ是一套持久层框架，主要特点是：

逆向工程，自动根据数据库结构生成对应的类
流式的API，像写SQL一样
- 提供类型安全的SQL查询，JOOQ的主要优势，可以帮助我们在写SQL时就做检查
- 支持几乎所有DDL，DML
- 可以内部避免SQL注入安全问题
- 支持SQL渲染，打印，绑定
使用非常轻便灵活
- 可以用JPA做大部分简单的查询，用JOOQ写复杂的
- 可以只用JOOQ作为SQL执行器
- 可以只用来生成SQL语句（类型安全）
- 可以只用来处理SQL执行结果
- 支持Flyway，JAX-RS，JavaFX，Kotlin，Nashorn，Scala，Groovy，NoSQL
- 支持XML，CSV，JSON，HTML导入导出
- 支持事物回滚

配置依赖和插件

pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>springboot-jooq</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-jooq</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.15</version>
                    </dependency>
                </dependencies>
                <configuration>
<!--                    <jdbc>-->
<!--                        <url>jdbc:mysql://localhost:3306/demo?serverTimezone=UTC</url>-->
<!--                        <user>root</user>-->
<!--                        <password>root</password>-->
<!--                        <driver>com.mysql.cj.jdbc.Driver</driver>-->
<!--                    </jdbc>-->
<!--                    &lt;!&ndash;逆向生成配置文件&ndash;&gt;-->
                    <configurationFile>src/main/resources/library.xml</configurationFile>
                    <generator>
                        <generate>
                            <pojos>true</pojos>
                            <fluentSetters>true</fluentSetters>
                        </generate>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

逆向工程配置文件

在项目的resources目录下新建library.xml，由于网上JOOQ的教程比较少，且比较老，所以建议去官网拷贝对应版本的配置文件，并酌情修改，否则会无法生成。

library.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd">
    <!-- Configure the database connection here -->
    <jdbc>
        <driver>com.mysql.cj.jdbc.Driver</driver>
        <url>jdbc:mysql://localhost:3306/demo?serverTimezone=UTC</url>
        <user>root</user>
        <password>root</password>
    </jdbc>

    <generator>
        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.codegen.JavaGenerator
             - org.jooq.codegen.ScalaGenerator
             Defaults to org.jooq.codegen.JavaGenerator -->
        <name>org.jooq.codegen.JavaGenerator</name>

        <database>
            <!-- The database type. The format here is:
                 org.jooq.meta.[database].[database]Database -->
            <name>org.jooq.meta.mysql.MySQLDatabase</name>

            <!-- The database schema (or in the absence of schema support, in your RDBMS this
                 can be the owner, user, database name) to be generated -->
            <inputSchema>demo</inputSchema>

            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->
            <includes>.*</includes>

            <!-- All elements that are excluded from your schema
                 (A Java regular expression. Use the pipe to separate several expressions).
                 Excludes match before includes, i.e. excludes have a higher priority -->
            <excludes></excludes>
        </database>

        <target>
            <!-- The destination package of your generated classes (within the destination directory) -->
            <packageName>com.example.springbootjooq.generated</packageName>

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->
            <directory>src/main/java</directory>
        </target>
    </generator>
</configuration>

数据表

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

使用maven点击lifecycle下单compile 来实施逆向工程生产相关的类

下面以user表为了使用jooq进行增删改查

controller

package com.example.springbootjooq.demo;

import com.example.springbootjooq.generated.tables.pojos.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author chenling
 */
@RestController
@RequestMapping("/demo/")
public class DemoController {

    @Autowired
    private DemoService service;

    @RequestMapping("/insert/user/{name}/{age}")
    public void insert(@PathVariable String age, @PathVariable String name){
        service.insert(new User().setAge(age).setName(name));
    }

    @RequestMapping("/update/user/{name}/{age}")
    public void update(@PathVariable String age, @PathVariable String name){
        service.update(new User().setAge(age).setName(name));
    }

    @RequestMapping("/delete/user/{id}")
    public void delete(@PathVariable Integer id){
        service.delete(id);
    }

    @RequestMapping("/select/user/{id}")
    public User selectByID(@PathVariable Integer id){
        return service.selectById(id);
    }

    @RequestMapping("/select/user/")
    public List<User> selectByID(){
        return service.selectAll();
    }
}

service

package com.example.springbootjooq.demo;


import com.example.springbootjooq.generated.tables.pojos.User;
import org.springframework.transaction.annotation.Transactional;

import java.util.Iterator;
import java.util.List;

/**
 * @author chenling
 */
public interface DemoService {
    /** 删除 */
    @Transactional
    public void delete(int id);
    /** 增加*/
    @Transactional
    public void insert(User user);
    /** 更新*/
    @Transactional
    public int update(User user);
    /** 查询单个*/
    public User selectById(int id);
    /** 查询全部列表*/
    public List<User> selectAll();
}

serviceimpl

package com.example.springbootjooq.demo;

import com.example.springbootjooq.generated.tables.pojos.User;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Iterator;
import java.util.List;

@Service
public class DemoServiceImpl implements DemoService {

    @Autowired
    DSLContext create;

    com.example.springbootjooq.generated.tables.User USER = com.example.springbootjooq.generated.tables.User.USER;

    @Override
    public void delete(int id) {
        create.delete(USER).where(USER.ID.eq(id)).execute();
    }

    @Override
    public void insert(User user) {

        create.insertInto(USER)
                .columns(USER.NAME,USER.AGE)
                .values(user.getName(), user.getAge())
                .execute();
    }

    @Override
    public int update(User user) {
        create.update(USER).set((Record) user);
        return 0;
    }

    @Override
    public User selectById(int id) {
        return create.select(USER.NAME,USER.AGE).from(USER).where(USER.ID.eq(id)).fetchInto(User.class).get(0);
    }

    @Override
    public List<User> selectAll() {
        return create.select().from(USER).fetchInto(User.class);
    }
}

posted @ 2022-03-17 16:56 程序那点事阅读(818) 评论(0) 编辑收藏举报

刷新页面返回顶部

繁星