SpringBoot整合Mybatis3 Dynamic Sql

前置条件：

CREATE TABLE `volunteer` (
  `vt_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '志愿者id',
  `vt_integral_count` int(11) DEFAULT NULL COMMENT '剩余总积分',
  `vt_avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `vt_name` varchar(255) DEFAULT NULL COMMENT '名字',
  PRIMARY KEY (`vt_id`) USING BTREE
)

java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

maven

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>MybatisDynamicSql</artifactId>
    <version>1.0-SNAPSHOT</version>


    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath/>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

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


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.dynamic-sql</groupId>
            <artifactId>mybatis-dynamic-sql</artifactId>
            <version>1.1.4</version>
        </dependency>


        <!-- MyBatis 生成器 -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.4.0</version>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

代码目录结构

代码自动生成 generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!--Mybatis Generator目前有5种运行模式，分别为：MyBatis3DynamicSql、MyBatis3Kotlin、MyBatis3、MyBatis3Simple、MyBatis3DynamicSqlV1。-->
    <context id="springboot-base" targetRuntime="MyBatis3DynamicSql">
        <commentGenerator>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <jdbcConnection
                connectionURL="jdbc:mysql://127.0.0.1:3307/ruoyi_vue?tinyInt1isBit=false&amp;useUnicode=true&amp;
                characterEncoding=utf-8&amp;serverTimezone=Asia/Shanghai&amp;nullCatalogMeansCurrent=true"
                driverClass="com.mysql.jdbc.Driver"
                userId="root"
                password="123456"/>

        <javaTypeResolver>
            <!-- 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer， 为 true时把JDBC DECIMAL
            和 NUMERIC 类型解析为java.math.BigDecimal -->
            <property name="forceBigDecimals" value="false" />
            <!--是否试用jdk8时间类-->
            <property name="useJSR310Types" value="false"/>
        </javaTypeResolver>

        <!-- targetProject:生成PO类的位置 -->
        <javaModelGenerator
                targetPackage="com.my.entity"
                targetProject="src/main/java">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
            <!-- 从数据库返回的值被清理前后的空格 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!-- targetProject:mapper映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="mybatis.mapping"
                         targetProject="src/main/resources">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>

        <!-- targetPackage：mapper接口生成的位置 -->
        <javaClientGenerator
                targetPackage="com.my.mapper"
                targetProject="src/main/java" type="XMLMAPPER">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </javaClientGenerator>

        <!--生成全部表tableName设为%-->
        <!--<table tableName="%"/>-->

        <!-- 指定数据库表
  schema:数据库的schema,可以使用SQL通配符匹配。如果设置了该值，生成SQL的表名会变成如schema.tableName的形式。
  domainObjectName:生成对象的基本名称。如果没有指定，MBG会自动根据表名来生成名称。
  -->
        <table schema="volunteer" tableName="volunteer" domainObjectName="Volunteer"/>
    </context>
</generatorConfiguration>

package com.my.util;


import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MybatisGenerator {
    public static void main(String[] args) throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
        //MBG 执行过程中的警告信息
        List<String> warnings = new ArrayList<>();
        //读取我们的 MBG 配置文件
        InputStream is = MybatisGenerator.class.getResourceAsStream("/mybatis/generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        is.close();
        //当生成的代码重复时，不要覆盖原代码
        DefaultShellCallback callback = new DefaultShellCallback(false);
        //创建 MBG
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        //执行生成代码
        myBatisGenerator.generate(null);
        //输出警告信息
        for (String warning : warnings) {
            System.out.println(warning);
        }
    }
}

接口

package com.my.controller;

import com.my.entity.Volunteer;
import com.my.mapper.VolunteerMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.dynamic.sql.SqlBuilder;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.time.Duration;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

import static com.my.mapper.volunteerDynamicSqlSupport.*;
import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
import static org.mybatis.dynamic.sql.SqlBuilder.isLike;

@RestController
@RequestMapping("/importDataDynamicSQL")
public class VotController {
    @Autowired
    //@Autowired(required = false)
    private VolunteerMapper volunteerMapper;


    /**
    查询指定列
     */
    @GetMapping("/list01")
    public List<Volunteer> list( ) {
        SelectStatementProvider select = SqlBuilder.select(vtId)
                .from(volunteer)
                .build()
                .render(RenderingStrategies.MYBATIS3);

        //List<volunteer> list = volunteerService.list1();
        return volunteerMapper.selectMany(select);
    }



    /**
       查询找所有列
        */
    @GetMapping("/list02")
    public List<Volunteer> list02( ) {
        SelectStatementProvider select = SqlBuilder.select(volunteerMapper.selectList)
                .from(volunteer)
                .build()
                .render(RenderingStrategies.MYBATIS3);

        System.out.println("=========="+select.toString());

        return volunteerMapper.selectMany(select);
    }

    /**


     * @return
     */
    @PostMapping("/list03")
    public List<Volunteer> list3(@RequestBody(required = false) Volunteer stu) {
        SelectStatementProvider select = SqlBuilder.select(volunteerMapper.selectList)
                .from(volunteer)
                .where(vtName, isLike("%" + stu.getVtName() + "%"))
                .and(vtName, isEqualTo("1"))
                //.or(sex, isEqualTo("女"))
                .orderBy(vtName)
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return volunteerMapper.selectMany(select);
    }

    /**
     * 新增单条添加
     * @return
     */

    @PostMapping("/votAdd01")
    public int votAdd01(@RequestBody(required = false) Volunteer stu) {

        return volunteerMapper.insert(stu);
    }

    /**
     * 批量新增
     * @param
     * @return
     */

    @PostMapping("/votAdd02")
    public int votAdd02(@RequestBody(required = false) List<Volunteer> votList) {

        return volunteerMapper.insertMultiple(votList);
    }

    /**
     * 十万条插入性能测试
     * 批量新增
     * @param
     * @return
     */

    @PostMapping("/votAdd03")
    public String votAdd03() {
        List<Volunteer> votList =new ArrayList<>();
        Volunteer volt=new Volunteer();
        int count=10;
        for(int i=0;i<count;i++){

            volt.setVtAvatar("图片地址"+i);
            volt.setVtIntegralCount(i);
            volt.setVtName("姓名"+i);
            votList.add(volt);
        }
        LocalDateTime currentTimeStart = LocalDateTime.now();
        int addAllFlag=volunteerMapper.insertMultiple(votList);
        LocalDateTime currentTimeEND = LocalDateTime.now();


        Duration duration = Duration.between(currentTimeStart, currentTimeEND);

        return"开始时间：" +currentTimeStart +"结束时间："+currentTimeEND +"时间差值(秒):"+duration.getSeconds();
    }
}