Spring Boot整合JOOQ(入门学习)

简介

JOOQ(Java Object Oriented Querying),是基于Java的轻量级数据库访问库,通过特定的语法将类以及数据库模式翻译成对应的SQL语句实现实体与关系的映射。

JOOQ具有如下的特点:
继承了ORM框架的优点,简单操作,类型安全等。jOOQ将SQL建模为内部DSL,使用Java编译器编译SQL语法,元数据和数据类型。
JOOQ会根据数据库的元数据来生成对应的实体类,省略了原有开发中不断修改对应数据库的类名,属性名。
JOOQ允许运行时配置数据库模式,且支持行级别的安全。 支持联合查询,多表查询,存储过程等数据库高级操作。

入门

源码地址

Spring Boot集成JOOQ

1.创建springboot基础项目,并添加相关依赖

		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.15</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.3</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-meta</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen</artifactId>
            <version>3.13.1</version>
            </dependency>

除了这些之外,还要在pom.xml中添加插件相关内容:

			<plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>

                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.21</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <configurationFile>src/main/resources/JooqConfig.xml</configurationFile>
                </configuration>
            </plugin>

2.通过第一步可以看出需要在项目工程中添加JooqConfig.xml到指定目录下,并填充信息

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <jdbc>
        <driver>com.mysql.jdbc.Driver</driver>
        <!-- 配置数据库地址 -->
        <url>jdbc:mysql://192.168.1.105:3306/demo?characterEncoding=UTF-8</url>
        <!-- 配置数据库用户名-->
        <user>root</user>
        <!-- 配置数据库密码-->
        <password>Root1234</password>
    </jdbc>
    <generator>
        <!-- 代码生成器 -->
        <!--   <name>org.jooq.meta.mysql.MySQLDatabase</name>-->
        <database>
            <!--强制为scheme1模式下所有的含有id域生成id-->

            <!--是否重写主键-->

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

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

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

        <generate>
            <!--是否生成dao和pojo-->
            <daos>true</daos>
            <pojos>true</pojos>
            <!--是否把数据库时间类型映射到java 8时间类型-->
            <javaTimeTypes>true</javaTimeTypes>
            <!--<interfaces>true</interfaces>-->
            <!--是否在生成的代码中添加spring注释,比如@Repository-->
            <springAnnotations>false</springAnnotations>
        </generate>

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

配置application.properties:

spring.datasource.url=jdbc:mysql://192.168.1.105:3306/demo?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=Root1234
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

3.创建数据库:
在这里插入图片描述
4.点击maven项目中的install,等待执行结束,便可以看到工程目录发生改变,在main包下生成很多和jooq相关类:
在这里插入图片描述

JOOQ包

在这里插入图片描述

tables包

tables包下面有三个自动生成的包,分别是daos,pojos,records。会根据JooqConfig.xml中的配置动态生成包内容。
1.daos
daos包下面的dao层会有自动生成的一些定义好的增删改方法,可以减少基本的crud操作。

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq.tables.daos;


import com.demo.main.jooq.tables.Student;
import com.demo.main.jooq.tables.records.StudentRecord;

import java.util.List;

import org.jooq.Configuration;
import org.jooq.impl.DAOImpl;


/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class StudentDao extends DAOImpl<StudentRecord, com.demo.main.jooq.tables.pojos.Student, Integer> {

    /**
     * Create a new StudentDao without any configuration
     */
    public StudentDao() {
        super(Student.STUDENT, com.demo.main.jooq.tables.pojos.Student.class);
    }

    /**
     * Create a new StudentDao with an attached configuration
     */
    public StudentDao(Configuration configuration) {
        super(Student.STUDENT, com.demo.main.jooq.tables.pojos.Student.class, configuration);
    }

    @Override
    public Integer getId(com.demo.main.jooq.tables.pojos.Student object) {
        return object.getId();
    }

    /**
     * Fetch records that have <code>id BETWEEN lowerInclusive AND upperInclusive</code>
     */
    public List<com.demo.main.jooq.tables.pojos.Student> fetchRangeOfId(Integer lowerInclusive, Integer upperInclusive) {
        return fetchRange(Student.STUDENT.ID, lowerInclusive, upperInclusive);
    }

    /**
     * Fetch records that have <code>id IN (values)</code>
     */
    public List<com.demo.main.jooq.tables.pojos.Student> fetchById(Integer... values) {
        return fetch(Student.STUDENT.ID, values);
    }

    /**
     * Fetch a unique record that has <code>id = value</code>
     */
    public com.demo.main.jooq.tables.pojos.Student fetchOneById(Integer value) {
        return fetchOne(Student.STUDENT.ID, value);
    }

    /**
     * Fetch records that have <code>classname BETWEEN lowerInclusive AND upperInclusive</code>
     */
    public List<com.demo.main.jooq.tables.pojos.Student> fetchRangeOfClassname(String lowerInclusive, String upperInclusive) {
        return fetchRange(Student.STUDENT.CLASSNAME, lowerInclusive, upperInclusive);
    }

    /**
     * Fetch records that have <code>classname IN (values)</code>
     */
    public List<com.demo.main.jooq.tables.pojos.Student> fetchByClassname(String... values) {
        return fetch(Student.STUDENT.CLASSNAME, values);
    }
}

2.pojos
序列化java类,包含get,set和toString方法

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq.tables.pojos;


import java.io.Serializable;


/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Student implements Serializable {

    private static final long serialVersionUID = -750399146;

    private Integer id;
    private String  classname;

    public Student() {}

    public Student(Student value) {
        this.id = value.id;
        this.classname = value.classname;
    }

    public Student(
        Integer id,
        String  classname
    ) {
        this.id = id;
        this.classname = classname;
    }

    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getClassname() {
        return this.classname;
    }

    public void setClassname(String classname) {
        this.classname = classname;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder("Student (");

        sb.append(id);
        sb.append(", ").append(classname);

        sb.append(")");
        return sb.toString();
    }
}

3.records
可以参考https://blog.csdn.net/compassy/article/details/104646748/去理解

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq.tables.records;


import com.demo.main.jooq.tables.Student;

import org.jooq.Field;
import org.jooq.Record1;
import org.jooq.Record2;
import org.jooq.Row2;
import org.jooq.impl.UpdatableRecordImpl;


/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class StudentRecord extends UpdatableRecordImpl<StudentRecord> implements Record2<Integer, String> {

    private static final long serialVersionUID = -530818566;

    /**
     * Setter for <code>demo.student.id</code>.
     */
    public void setId(Integer value) {
        set(0, value);
    }

    /**
     * Getter for <code>demo.student.id</code>.
     */
    public Integer getId() {
        return (Integer) get(0);
    }

    /**
     * Setter for <code>demo.student.classname</code>.
     */
    public void setClassname(String value) {
        set(1, value);
    }

    /**
     * Getter for <code>demo.student.classname</code>.
     */
    public String getClassname() {
        return (String) get(1);
    }

    // -------------------------------------------------------------------------
    // Primary key information
    // -------------------------------------------------------------------------

    @Override
    public Record1<Integer> key() {
        return (Record1) super.key();
    }

    // -------------------------------------------------------------------------
    // Record2 type implementation
    // -------------------------------------------------------------------------

    @Override
    public Row2<Integer, String> fieldsRow() {
        return (Row2) super.fieldsRow();
    }

    @Override
    public Row2<Integer, String> valuesRow() {
        return (Row2) super.valuesRow();
    }

    @Override
    public Field<Integer> field1() {
        return Student.STUDENT.ID;
    }

    @Override
    public Field<String> field2() {
        return Student.STUDENT.CLASSNAME;
    }

    @Override
    public Integer component1() {
        return getId();
    }

    @Override
    public String component2() {
        return getClassname();
    }

    @Override
    public Integer value1() {
        return getId();
    }

    @Override
    public String value2() {
        return getClassname();
    }

    @Override
    public StudentRecord value1(Integer value) {
        setId(value);
        return this;
    }

    @Override
    public StudentRecord value2(String value) {
        setClassname(value);
        return this;
    }

    @Override
    public StudentRecord values(Integer value1, String value2) {
        value1(value1);
        value2(value2);
        return this;
    }

    // -------------------------------------------------------------------------
    // Constructors
    // -------------------------------------------------------------------------

    /**
     * Create a detached StudentRecord
     */
    public StudentRecord() {
        super(Student.STUDENT);
    }

    /**
     * Create a detached, initialised StudentRecord
     */
    public StudentRecord(Integer id, String classname) {
        super(Student.STUDENT);

        set(0, id);
        set(1, classname);
    }
}

4.DefaultCatalog,Demo,Keys,Tables,( Indexes,因为没创建索引没有)
在这里插入图片描述
DefaultCatalog.java:里面存放的是Demo.java也就是数据库名.

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq;


import java.util.Arrays;
import java.util.List;

import org.jooq.Schema;
import org.jooq.impl.CatalogImpl;


/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class DefaultCatalog extends CatalogImpl {

    private static final long serialVersionUID = -1340258766;

    /**
     * The reference instance of <code>DEFAULT_CATALOG</code>
     */
    public static final DefaultCatalog DEFAULT_CATALOG = new DefaultCatalog();

    /**
     * The schema <code>demo</code>.
     */
    public final Demo DEMO = Demo.DEMO;

    /**
     * No further instances allowed
     */
    private DefaultCatalog() {
        super("");
    }

    @Override
    public final List<Schema> getSchemas() {
        return Arrays.<Schema>asList(
            Demo.DEMO);
    }
}

Demo.java里面存放的是数据库的表.

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq;


import com.demo.main.jooq.tables.Student;

import java.util.Arrays;
import java.util.List;

import org.jooq.Catalog;
import org.jooq.Table;
import org.jooq.impl.SchemaImpl;


/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Demo extends SchemaImpl {

    private static final long serialVersionUID = 1935931376;

    /**
     * The reference instance of <code>demo</code>
     */
    public static final Demo DEMO = new Demo();

    /**
     * The table <code>demo.student</code>.
     */
    public final Student STUDENT = Student.STUDENT;

    /**
     * No further instances allowed
     */
    private Demo() {
        super("demo", null);
    }


    @Override
    public Catalog getCatalog() {
        return DefaultCatalog.DEFAULT_CATALOG;
    }

    @Override
    public final List<Table<?>> getTables() {
        return Arrays.<Table<?>>asList(
            Student.STUDENT);
    }
}

Tables.java里面存放的是表名,一般都是通过这个类去访问表和字段的.

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq;


import com.demo.main.jooq.tables.Student;


/**
 * Convenience access to all tables in demo
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Tables {

    /**
     * The table <code>demo.student</code>.
     */
    public static final Student STUDENT = Student.STUDENT;
}

Keys.java里面有两个类Identities0存放的自增字段,UniqueKeys0存放的是唯一字段.

/*
 * This file is generated by jOOQ.
 */
package com.demo.main.jooq;


import com.demo.main.jooq.tables.Student;
import com.demo.main.jooq.tables.records.StudentRecord;

import org.jooq.TableField;
import org.jooq.UniqueKey;
import org.jooq.impl.Internal;


/**
 * A class modelling foreign key relationships and constraints of tables of 
 * the <code>demo</code> schema.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Keys {

    // -------------------------------------------------------------------------
    // IDENTITY definitions
    // -------------------------------------------------------------------------


    // -------------------------------------------------------------------------
    // UNIQUE and PRIMARY KEY definitions
    // -------------------------------------------------------------------------

    public static final UniqueKey<StudentRecord> KEY_STUDENT_PRIMARY = UniqueKeys0.KEY_STUDENT_PRIMARY;

    // -------------------------------------------------------------------------
    // FOREIGN KEY definitions
    // -------------------------------------------------------------------------


    // -------------------------------------------------------------------------
    // [#1459] distribute members to avoid static initialisers > 64kb
    // -------------------------------------------------------------------------

    private static class UniqueKeys0 {
        public static final UniqueKey<StudentRecord> KEY_STUDENT_PRIMARY = Internal.createUniqueKey(Student.STUDENT, "KEY_student_PRIMARY", new TableField[] { Student.STUDENT.ID }, true);
    }
}

Indexes.java存放的是表的主键(索引).

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值