MyBatis：MyBatis3 Dynamic Sql入门学习

r囧r小猫 2023-09-28 16:26 1阅读 0赞

> 我是 ABin-阿斌：写一生代码，创一世佳话，筑一览芳华。 如果小伙伴们觉得文章有点 feel ，那就点个赞再走哦。  
> ![在这里插入图片描述][20201016161427585.png_pic_center]

*  声明：
    
     *  原作者：CSDN：https://blog.csdn.net/a19870822cp?type=blog
     *  原文链接：https://blog.csdn.net/a19870822cp/article/details/119889509

### 文章目录 ###

*  一、前言
 *  二、编程风格：
 *  三、SpringBoot 整合 MyBatis3 Dynamic Sql
 *   *  1、创建数据库
     *  2、创建springboot项目
     *   *  2.1、引入对应的包
         *  2.2、自动生成表的对象
         *  2.3、创建业务代码
         *  2.4、Controller层
         *  2.5、service层
     *  四、关联查询
 *  总结

# 一、前言 #

*  [mybatis][]官方推出了一种新的动态sql的编程方式MyBatis3 Dynamic Sql。官方觉得xml已经过时了，不建议大家在使用了。
 *  而注解的方式也不是很优雅，功能比较少，很多sql不好实现，代码不好调试等，所以推荐正确的mybatis食用方式给大家。
 *  MyBatis3 Dynamic Sql使用了 java8的生成器来生成代码，使用流式以及 [lambda][]表达式来构建sql，看起来更简化、优雅，完全使用java代码编程，更加的流畅。

# 二、编程风格： #

@Test
    void testGeneralSelect() {
        
        try (SqlSession session = sqlSessionFactory.openSession()) {
        
            PersonMapper mapper = session.getMapper(PersonMapper.class);
    
            SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed,
                occupation, addressId)
            .from(person)
            .where(id, isEqualTo(1))
            .or(occupation, isNull())
            .build()
            .render(RenderingStrategies.MYBATIS3);
    
            List<PersonRecord> rows = mapper.selectMany(selectStatement);
            assertThat(rows).hasSize(3);
        }
    }

# 三、SpringBoot 整合 MyBatis3 Dynamic Sql #

## 1、创建数据库 ##

这里使用用户、角色、菜单表来描述：

# 用户表
    CREATE TABLE `sys_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `account` varchar(50) NOT NULL COMMENT '账号',
      `describe` varchar(100) DEFAULT NULL COMMENT '描述',
      `nick_name` varchar(50) DEFAULT NULL COMMENT '昵称',
      `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
      `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
      `state` int(4) NOT NULL DEFAULT 0 COMMENT '状态0正常',
      `avatar_url` varchar(200) DEFAULT NULL COMMENT '头像url',
      `created_time` datetime NOT NULL COMMENT '创建时间',
      `modified_time` datetime DEFAULT NULL COMMENT '修改时间',
      `del_flag` int(4) NOT NULL DEFAULT 0 COMMENT '删除标记',
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_sys_user_account` (`account`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
    
    # 用户角色表
    CREATE TABLE `sys_user_role` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `account` varchar(50) NOT NULL COMMENT '用户账号',
      `role_name` varchar(50) NOT NULL COMMENT '角色名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
    
    # 角色表
    CREATE TABLE `sys_role` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL COMMENT '角色名称',
      `display_name` varchar(50) NOT NULL COMMENT '角色名称中文',
      `state` int(4) DEFAULT 0 COMMENT '状态',
      `describe` varchar(100) DEFAULT NULL COMMENT '描述',
      `platform_code` varchar(50) DEFAULT NULL COMMENT '平台code',
      `created_time` datetime NOT NULL,
      `modified_time` datetime DEFAULT NULL,
      `del_flag` int(2) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_sys_role_name` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
    
    # 菜单表
    CREATE TABLE `sys_menu` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL COMMENT '菜单名称',
      `display_name` varchar(50) NOT NULL COMMENT '菜单显示名称',
      `url` varchar(255) DEFAULT NULL COMMENT '菜单url',
      `menu_index` int(4) DEFAULT 0 COMMENT '菜单排序',
      `state` int(4) DEFAULT 0 COMMENT '状态0正常1禁用',
      `pid` int(11) NOT NULL COMMENT '上一级菜单id',
      `icon` varchar(50) DEFAULT NULL COMMENT '图标',
      `display` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否显示',
      `platform_code` varchar(50) DEFAULT NULL COMMENT '平台code',
      `del_flag` int(4) DEFAULT 0,
      `created_time` datetime DEFAULT NULL,
      `modified_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_sys_menu` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
    
    # 角色菜单表
    CREATE TABLE `sys_role_menu` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `role_name` varchar(50) NOT NULL COMMENT '角色名称',
      `menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;``

## 2、创建springboot项目 ##

### 2.1、引入对应的包 ###

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
    
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    
    
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>
    
    
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.7</version>
    </dependency>
    
    
    <dependency>
        <groupId>org.mybatis.dynamic-sql</groupId>
        <artifactId>mybatis-dynamic-sql</artifactId>
        <version>1.3.0</version>
    </dependency>

### 2.2、自动生成表的对象 ###

在resources下创建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>
        
        <context id="springboot-base" targetRuntime="MyBatis3DynamicSql">
            <commentGenerator>
                
                <property name="suppressAllComments" value="true" />
            </commentGenerator>
            <jdbcConnection
                    connectionURL="jdbc:mysql://127.0.0.1:3306/test?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true"
                    driverClass="com.mysql.jdbc.Driver"
                    userId="root"
                    password="123456"/>
    
            <javaTypeResolver>
                
                <property name="forceBigDecimals" value="false" />
                
                <property name="useJSR310Types" value="true"/>
            </javaTypeResolver>
    
            
            <javaModelGenerator
                    targetPackage="com.example.dynamic.entity"
                    targetProject="src/main/java">
                
                <property name="enableSubPackages" value="false" />
                
                <property name="trimStrings" value="true" />
            </javaModelGenerator>
    
            
            <javaClientGenerator
                    targetPackage="com.example.dynamic.mapper"
                    targetProject="src/main/java" type="ANNOTATEDMAPPER">
                
                <property name="enableSubPackages" value="false" />
            </javaClientGenerator>
    
            
            
            <table tableName="sys_%" >
                <domainObjectRenamingRule searchString="^Sys_" replaceString="Sys_"/>
            </table>
    
            
            
        </context>
    </generatorConfiguration>

*  Mybatis Generator目前有5种运行模式，分别为：MyBatis3DynamicSql、MyBatis3Kotlin、MyBatis3、MyBatis3Simple、MyBatis3DynamicSqlV1。
 *  这里使用的是 MyBatis3DynamicSql，只生成 pojo 和一个 mapper 类，不会生成xml。

在java下创建generator包和MybatisGenerator.java文件

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("/generatorConfig.xml");
            ConfigurationParser cp = new ConfigurationParser(warnings);
            Configuration config = cp.parseConfiguration(is);
            is.close();
            //当生成的代码重复时，覆盖原代码
            DefaultShellCallback callback = new DefaultShellCallback(true);
            //创建 MBG
            MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
            //执行生成代码
            myBatisGenerator.generate(null);
            //输出警告信息
            for (String warning : warnings) {
        
                System.out.println(warning);
            }
        }
    }

直接运行此java类，会生成对应的文件：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExOTg3MDgyMmNw_size_16_color_FFFFFF_t_70_pic_center][]

实体类就是表字段下划线转驼峰

mapper就是注解形式的mapper接口，包含一些常用的方法：

package com.example.dynamic.mapper;
    
    import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
    import static org.mybatis.dynamic.sql.SqlBuilder.*;
    
    import com.example.dynamic.entity.SysUser;
    import java.util.Collection;
    import java.util.List;
    import java.util.Optional;
    import javax.annotation.Generated;
    import org.apache.ibatis.annotations.DeleteProvider;
    import org.apache.ibatis.annotations.InsertProvider;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.ResultMap;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.SelectProvider;
    import org.apache.ibatis.annotations.UpdateProvider;
    import org.apache.ibatis.type.JdbcType;
    import org.mybatis.dynamic.sql.BasicColumn;
    import org.mybatis.dynamic.sql.delete.DeleteDSLCompleter;
    import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
    import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
    import org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider;
    import org.mybatis.dynamic.sql.select.CountDSLCompleter;
    import org.mybatis.dynamic.sql.select.SelectDSLCompleter;
    import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
    import org.mybatis.dynamic.sql.update.UpdateDSL;
    import org.mybatis.dynamic.sql.update.UpdateDSLCompleter;
    import org.mybatis.dynamic.sql.update.UpdateModel;
    import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
    import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
    import org.mybatis.dynamic.sql.util.mybatis3.MyBatis3Utils;
    
    @Mapper
    public interface SysUserMapper {
        
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        BasicColumn[] selectList = BasicColumn.columnList(id, account, describe, nickName, phone, email, state, avatarUrl, createdTime, modifiedTime, delFlag);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @SelectProvider(type=SqlProviderAdapter.class, method="select")
        long count(SelectStatementProvider selectStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @DeleteProvider(type=SqlProviderAdapter.class, method="delete")
        int delete(DeleteStatementProvider deleteStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @InsertProvider(type=SqlProviderAdapter.class, method="insert")
        int insert(InsertStatementProvider<SysUser> insertStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @InsertProvider(type=SqlProviderAdapter.class, method="insertMultiple")
        int insertMultiple(MultiRowInsertStatementProvider<SysUser> multipleInsertStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @SelectProvider(type=SqlProviderAdapter.class, method="select")
        @ResultMap("SysUserResult")
        Optional<SysUser> selectOne(SelectStatementProvider selectStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @SelectProvider(type=SqlProviderAdapter.class, method="select")
        @Results(id="SysUserResult", value = {
        
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
            @Result(column="describe", property="describe", jdbcType=JdbcType.VARCHAR),
            @Result(column="nick_name", property="nickName", jdbcType=JdbcType.VARCHAR),
            @Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="email", property="email", jdbcType=JdbcType.VARCHAR),
            @Result(column="state", property="state", jdbcType=JdbcType.INTEGER),
            @Result(column="avatar_url", property="avatarUrl", jdbcType=JdbcType.VARCHAR),
            @Result(column="created_time", property="createdTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="modified_time", property="modifiedTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="del_flag", property="delFlag", jdbcType=JdbcType.INTEGER)
        })
        List<SysUser> selectMany(SelectStatementProvider selectStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        @UpdateProvider(type=SqlProviderAdapter.class, method="update")
        int update(UpdateStatementProvider updateStatement);
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default long count(CountDSLCompleter completer) {
        
            return MyBatis3Utils.countFrom(this::count, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int delete(DeleteDSLCompleter completer) {
        
            return MyBatis3Utils.deleteFrom(this::delete, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int deleteByPrimaryKey(Integer id_) {
        
            return delete(c -> 
                c.where(id, isEqualTo(id_))
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int insert(SysUser record) {
        
            return MyBatis3Utils.insert(this::insert, record, sysUser, c ->
                c.map(id).toProperty("id")
                .map(account).toProperty("account")
                .map(describe).toProperty("describe")
                .map(nickName).toProperty("nickName")
                .map(phone).toProperty("phone")
                .map(email).toProperty("email")
                .map(state).toProperty("state")
                .map(avatarUrl).toProperty("avatarUrl")
                .map(createdTime).toProperty("createdTime")
                .map(modifiedTime).toProperty("modifiedTime")
                .map(delFlag).toProperty("delFlag")
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int insertMultiple(Collection<SysUser> records) {
        
            return MyBatis3Utils.insertMultiple(this::insertMultiple, records, sysUser, c ->
                c.map(id).toProperty("id")
                .map(account).toProperty("account")
                .map(describe).toProperty("describe")
                .map(nickName).toProperty("nickName")
                .map(phone).toProperty("phone")
                .map(email).toProperty("email")
                .map(state).toProperty("state")
                .map(avatarUrl).toProperty("avatarUrl")
                .map(createdTime).toProperty("createdTime")
                .map(modifiedTime).toProperty("modifiedTime")
                .map(delFlag).toProperty("delFlag")
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int insertSelective(SysUser record) {
        
            return MyBatis3Utils.insert(this::insert, record, sysUser, c ->
                c.map(id).toPropertyWhenPresent("id", record::getId)
                .map(account).toPropertyWhenPresent("account", record::getAccount)
                .map(describe).toPropertyWhenPresent("describe", record::getDescribe)
                .map(nickName).toPropertyWhenPresent("nickName", record::getNickName)
                .map(phone).toPropertyWhenPresent("phone", record::getPhone)
                .map(email).toPropertyWhenPresent("email", record::getEmail)
                .map(state).toPropertyWhenPresent("state", record::getState)
                .map(avatarUrl).toPropertyWhenPresent("avatarUrl", record::getAvatarUrl)
                .map(createdTime).toPropertyWhenPresent("createdTime", record::getCreatedTime)
                .map(modifiedTime).toPropertyWhenPresent("modifiedTime", record::getModifiedTime)
                .map(delFlag).toPropertyWhenPresent("delFlag", record::getDelFlag)
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default Optional<SysUser> selectOne(SelectDSLCompleter completer) {
        
            return MyBatis3Utils.selectOne(this::selectOne, selectList, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default List<SysUser> select(SelectDSLCompleter completer) {
        
            return MyBatis3Utils.selectList(this::selectMany, selectList, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default List<SysUser> selectDistinct(SelectDSLCompleter completer) {
        
            return MyBatis3Utils.selectDistinct(this::selectMany, selectList, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default Optional<SysUser> selectByPrimaryKey(Integer id_) {
        
            return selectOne(c ->
                c.where(id, isEqualTo(id_))
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int update(UpdateDSLCompleter completer) {
        
            return MyBatis3Utils.update(this::update, sysUser, completer);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        static UpdateDSL<UpdateModel> updateAllColumns(SysUser record, UpdateDSL<UpdateModel> dsl) {
        
            return dsl.set(id).equalTo(record::getId)
                    .set(account).equalTo(record::getAccount)
                    .set(describe).equalTo(record::getDescribe)
                    .set(nickName).equalTo(record::getNickName)
                    .set(phone).equalTo(record::getPhone)
                    .set(email).equalTo(record::getEmail)
                    .set(state).equalTo(record::getState)
                    .set(avatarUrl).equalTo(record::getAvatarUrl)
                    .set(createdTime).equalTo(record::getCreatedTime)
                    .set(modifiedTime).equalTo(record::getModifiedTime)
                    .set(delFlag).equalTo(record::getDelFlag);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        static UpdateDSL<UpdateModel> updateSelectiveColumns(SysUser record, UpdateDSL<UpdateModel> dsl) {
        
            return dsl.set(id).equalToWhenPresent(record::getId)
                    .set(account).equalToWhenPresent(record::getAccount)
                    .set(describe).equalToWhenPresent(record::getDescribe)
                    .set(nickName).equalToWhenPresent(record::getNickName)
                    .set(phone).equalToWhenPresent(record::getPhone)
                    .set(email).equalToWhenPresent(record::getEmail)
                    .set(state).equalToWhenPresent(record::getState)
                    .set(avatarUrl).equalToWhenPresent(record::getAvatarUrl)
                    .set(createdTime).equalToWhenPresent(record::getCreatedTime)
                    .set(modifiedTime).equalToWhenPresent(record::getModifiedTime)
                    .set(delFlag).equalToWhenPresent(record::getDelFlag);
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int updateByPrimaryKey(SysUser record) {
        
            return update(c ->
                c.set(account).equalTo(record::getAccount)
                .set(describe).equalTo(record::getDescribe)
                .set(nickName).equalTo(record::getNickName)
                .set(phone).equalTo(record::getPhone)
                .set(email).equalTo(record::getEmail)
                .set(state).equalTo(record::getState)
                .set(avatarUrl).equalTo(record::getAvatarUrl)
                .set(createdTime).equalTo(record::getCreatedTime)
                .set(modifiedTime).equalTo(record::getModifiedTime)
                .set(delFlag).equalTo(record::getDelFlag)
                .where(id, isEqualTo(record::getId))
            );
        }
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        default int updateByPrimaryKeySelective(SysUser record) {
        
            return update(c ->
                c.set(account).equalToWhenPresent(record::getAccount)
                .set(describe).equalToWhenPresent(record::getDescribe)
                .set(nickName).equalToWhenPresent(record::getNickName)
                .set(phone).equalToWhenPresent(record::getPhone)
                .set(email).equalToWhenPresent(record::getEmail)
                .set(state).equalToWhenPresent(record::getState)
                .set(avatarUrl).equalToWhenPresent(record::getAvatarUrl)
                .set(createdTime).equalToWhenPresent(record::getCreatedTime)
                .set(modifiedTime).equalToWhenPresent(record::getModifiedTime)
                .set(delFlag).equalToWhenPresent(record::getDelFlag)
                .where(id, isEqualTo(record::getId))
            );
        }
    }

会多出一个XXXDynamicSqlSupport的一个类，这个类在构建动态sql的时候会用到，后面再提。

public final class SysMenuDynamicSqlSupport {
        
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SysMenu sysMenu = new SysMenu();
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Integer> id = sysMenu.id;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<String> name = sysMenu.name;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<String> displayName = sysMenu.displayName;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<String> url = sysMenu.url;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Integer> menuIndex = sysMenu.menuIndex;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Integer> state = sysMenu.state;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Integer> pid = sysMenu.pid;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<String> icon = sysMenu.icon;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Byte> display = sysMenu.display;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<String> platformCode = sysMenu.platformCode;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<Integer> delFlag = sysMenu.delFlag;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<LocalDateTime> createdTime = sysMenu.createdTime;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final SqlColumn<LocalDateTime> modifiedTime = sysMenu.modifiedTime;
    
        @Generated("org.mybatis.generator.api.MyBatisGenerator")
        public static final class SysMenu extends SqlTable {
        
            public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
    
            public final SqlColumn<String> name = column("name", JDBCType.VARCHAR);
    
            public final SqlColumn<String> displayName = column("display_name", JDBCType.VARCHAR);
    
            public final SqlColumn<String> url = column("url", JDBCType.VARCHAR);
    
            public final SqlColumn<Integer> menuIndex = column("menu_index", JDBCType.INTEGER);
    
            public final SqlColumn<Integer> state = column("state", JDBCType.INTEGER);
    
            public final SqlColumn<Integer> pid = column("pid", JDBCType.INTEGER);
    
            public final SqlColumn<String> icon = column("icon", JDBCType.VARCHAR);
    
            public final SqlColumn<Byte> display = column("display", JDBCType.TINYINT);
    
            public final SqlColumn<String> platformCode = column("platform_code", JDBCType.VARCHAR);
    
            public final SqlColumn<Integer> delFlag = column("del_flag", JDBCType.INTEGER);
    
            public final SqlColumn<LocalDateTime> createdTime = column("created_time", JDBCType.TIMESTAMP);
    
            public final SqlColumn<LocalDateTime> modifiedTime = column("modified_time", JDBCType.TIMESTAMP);
    
            public SysMenu() {
        
                super("sys_menu");
            }
        }

### 2.3、创建业务代码 ###

配置springboot的启动文件app：

server:
      servlet:
        context-path: mybatis
      port: 8082
    
    spring:
      datasource:
        url: jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: 123456

### 2.4、Controller层 ###

对sys\_user表的查新、新增、修改、分页查询、count:

package com.example.dynamic.web;
    
    import com.example.dynamic.entity.SysMenu;
    import com.example.dynamic.entity.SysUser;
    import com.example.dynamic.service.SysMenuService;
    import com.example.dynamic.service.SysUserService;
    import com.example.dynamic.utils.PageBean;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    @RequestMapping("test")
    public class WebController {
        
    
        @Autowired
        private SysUserService sysUserService;
    
        @Autowired
        private SysMenuService sysMenuService;
    
        /**
         * 查询用户
         * @param account
         * @return
         */
        @GetMapping("getSysUserList")
        public List<SysUser> getSysUserList(String account) {
        
            return sysUserService.getSysUserList(account);
        }
    
        /**
         * 新增
         * @param account
         * @param name
         * @return
         */
        @GetMapping("insert")
        public String insert(String account, String name) {
        
            sysUserService.insert(account, name);
            return "ok";
        }
    
        /**
         * 修改
         * @param account
         * @param name
         * @param state
         * @return
         */
        @GetMapping("update")
        public String update(String account, String name, Integer state) {
        
            sysUserService.update(account, name, state);
            return "ok";
        }
    
        /**
         * 删除
         * @param account
         * @return
         */
        @GetMapping("delete")
        public String delete(String account) {
        
            sysUserService.delete(account);
            return "ok";
        }
    
        /**
         * count
         * @return
         */
        @GetMapping("getCount")
        public long getCount() {
        
            return sysUserService.getCount();
        }
    
        /**
         * 分页查询
         * @param account account 可空
         * @param pageIndex 页号
         * @param pageSize 页数量
         * @return
         */
        @GetMapping("getSysUsersByPage")
        public PageBean<SysUser> getSysUsersByPage(String account, Integer pageIndex, Integer pageSize) {
        
            return sysUserService.getSysUsersByPage(account, pageIndex, pageSize);
        }
    
        /**
         * 查询用户的角色
         * @param account account
         * @return List<SysMenu>
         */
        @GetMapping("getMenuList")
        public List<SysMenu> getMenuList(String account) {
        
            return sysMenuService.getMenuList(account);
        }
    }

### 2.5、service层 ###

由于业务比较简单，直接把拼sql写在service层，实际开发中建议使用一个dao层。

package com.example.dynamic.service;
    
    import com.example.dynamic.entity.SysUser;
    import com.example.dynamic.mapper.SysUserMapper;
    import com.example.dynamic.utils.PageBean;
    import com.github.pagehelper.PageHelper;
    import org.mybatis.dynamic.sql.SqlBuilder;
    import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
    import org.mybatis.dynamic.sql.render.RenderingStrategies;
    import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
    import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import javax.annotation.Resource;
    
    import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
    import static org.mybatis.dynamic.sql.SqlBuilder.*;
    import static org.mybatis.dynamic.sql.select.SelectDSL.select;
    
    import java.time.LocalDateTime;
    import java.util.List;
    import java.util.Objects;
    
    @Service
    public class SysUserService {
        
    
        @Resource
        private SysUserMapper sysUserMapper;
    
        public List<SysUser> getSysUserList(String account1) {
        
            SelectStatementProvider select = SqlBuilder.select(sysUser.allColumns())
                    .from(sysUser)
                    .where(account, isLike(account1).filter(Objects::isNull).map(s -> "%" + s + "%"))
                    .and(state, isEqualTo(0))
                    .and(delFlag, isEqualTo(0))
                    .orderBy(createdTime.descending())
                    .build()
                    .render(RenderingStrategies.MYBATIS3);
            return sysUserMapper.selectMany(select);
        }
    
        @Transactional(rollbackFor = Exception.class)
        public boolean insert(String account, String name) {
        
            SysUser sysUser = new SysUser();
            sysUser.setAccount(account);
            sysUser.setNickName(name);
            sysUser.setCreatedTime(LocalDateTime.now());
            sysUser.setState(0);
            sysUser.setDelFlag(0);
            return sysUserMapper.insert(sysUser) > 0;
        }
    
        @Transactional(rollbackFor = Exception.class)
        public void delete(String account1) {
        
            DeleteStatementProvider del = deleteFrom(sysUser)
                    .where(account, isEqualTo(account1))
                    .build()
                    .render(RenderingStrategies.MYBATIS3);
            sysUserMapper.delete(del);
        }
    
        @Transactional(rollbackFor = Exception.class)
        public boolean update(String account1, String nickName1, Integer state1) {
        
            UpdateStatementProvider update = SqlBuilder.update(sysUser)
                    .set(nickName).equalTo(nickName1)
                    .set(state).equalTo(state1)
                    .set(modifiedTime).equalTo(LocalDateTime.now())
                    .where(account, isEqualTo(account1))
                    .build()
                    .render(RenderingStrategies.MYBATIS3);
            return sysUserMapper.update(update) > 0;
        }
    
        public PageBean<SysUser> getSysUsersByPage(String account1, Integer pageIndex, Integer pageSize) {
        
            PageHelper.startPage(pageIndex, pageSize);
            return new PageBean<SysUser>(sysUserMapper.selectMany(select(sysUser.allColumns())
                    .from(sysUser)
                    .where(account, isLike(account1).filter(Objects::isNull).map(x -> "%" + x + "%"))
                    .and(state, isEqualTo(0))
                    .orderBy(createdTime.descending())
                    .build()
                    .render(RenderingStrategies.MYBATIS3)));
    
        }
    
        public long getCount() {
        
            SelectStatementProvider selectStatement = select(count(sysUser.id))
                    .from(sysUser)
                    .where(state, isEqualTo(0))
                    .build()
                    .render(RenderingStrategies.MYBATIS3);
            return sysUserMapper.count(selectStatement);
        }
    }

例子中基本完成对表的基本操作，看起来十分的优雅。基本上普通的业务逻辑都包含了，分页使用PageHelper插件和mybatis其他的用法类似。

基本就是lambda的方式在java中编写sql。

静态导入一些mapper类，可以简化代表的编写：

import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;import static org.mybatis.dynamic.sql.SqlBuilder.*;
    import static org.mybatis.dynamic.sql.select.SelectDSL.select;

## 四、关联查询 ##

直接在java中使用实体对象来关联

package com.example.dynamic.service;
    
    import com.example.dynamic.entity.SysMenu;
    import com.example.dynamic.mapper.SysMenuDynamicSqlSupport;
    import com.example.dynamic.mapper.SysMenuMapper;
    import org.mybatis.dynamic.sql.render.RenderingStrategies;
    import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
    import org.springframework.stereotype.Service;
    
    import javax.annotation.Resource;
    
    import static org.mybatis.dynamic.sql.SqlBuilder.*;
    import static org.mybatis.dynamic.sql.select.SelectDSL.select;
    import static com.example.dynamic.mapper.SysMenuDynamicSqlSupport.*;
    import static com.example.dynamic.mapper.SysRoleMenuDynamicSqlSupport.*;
    import static com.example.dynamic.mapper.SysUserRoleDynamicSqlSupport.*;
    import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
    
    import java.util.List;
    
    @Service
    public class SysMenuService {
        
    
        @Resource
        private SysMenuMapper sysMenuMapper;
    
        public List<SysMenu> getMenuList(String account1) {
        
            return sysMenuMapper.selectMany(select(sysMenu.allColumns())
                    .from(sysMenu, "m")
                    .leftJoin(sysRoleMenu, "mr").on(sysMenu.name, equalTo(sysRoleMenu.menuName))
                    .leftJoin(sysUserRole, "ur").on(sysRoleMenu.roleName, equalTo(sysUserRole.roleName))
                    .leftJoin(sysUser, "u").on(sysUserRole.account, equalTo(sysUser.account))
                    .where(sysUser.account, isEqualTo(account1))
                    .and(sysUser.state, isEqualTo(0))
                    .build()
                    .render(RenderingStrategies.MYBATIS3));
        }
    
    }

# 总结 #

*  基本上对sql中的关键字都有支持，详细请查看官方文档。
 *  [https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html][https_mybatis.org_mybatis-dynamic-sql_docs_introduction.html]

[20201016161427585.png_pic_center]: https://img-blog.csdnimg.cn/20201016161427585.png#pic_center
[mybatis]: https://so.csdn.net/so/search?q=mybatis&spm=1001.2101.3001.7020
[lambda]: https://so.csdn.net/so/search?q=lambda&spm=1001.2101.3001.7020
[xml]: https://so.csdn.net/so/search?q=xml&spm=1001.2101.3001.7020
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExOTg3MDgyMmNw_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/71f39ecf53dc463d8e83348280309d23.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExOTg3MDgyMmNw,size_16,color_FFFFFF,t_70#pic_center
[https_mybatis.org_mybatis-dynamic-sql_docs_introduction.html]: https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html