MyBatis：MyBatis3 Dynamic Sql入门学习

最新推荐文章于 2024-04-18 15:56:30 发布

ABin-阿斌

最新推荐文章于 2024-04-18 15:56:30 发布

阅读量1.3k

点赞数

分类专栏： # MyBatis 文章标签： mybatis sql 学习

ABin-阿斌

本文链接：https://blog.csdn.net/Mango_Bin/article/details/125948096

版权

MyBatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

我是 ABin-阿斌：写一生代码，创一世佳话，筑一览芳华。如果小伙伴们觉得文章有点 feel ，那就点个赞再走哦。

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

一、前言

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>

<!-- MyBatis 生成器 -->
<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.4.0</version>
</dependency>

<!-- mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

<!-- MyBatis 动态SQL支持 -->
<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>
    <!--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:3306/test?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="true"/>
        </javaTypeResolver>

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

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

        <!--生成全部表tableName设为%-->
        <!-- 指定数据库表 -->
        <table tableName="sys_%" >
            <domainObjectRenamingRule searchString="^Sys_" replaceString="Sys_"/>
        </table>

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

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

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

ABin-阿斌

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MyBatis：MyBatis3 Dynamic Sql入门学习

mybatis官方推出了一种新的动态sql的编程方式MyBatis3DynamicSql。官方觉得xml已经过时了，不建议大家在使用了。而注解的方式也不是很优雅，功能比较少，很多sql不好实现，代码不好调试等，所以推荐正确的mybatis食用方式给大家。MyBatis3DynamicSql使用了java8的生成器来生成代码，使用流式以及lambda表达式来构建sql，看起来更简化、优雅，完全使用java代码编程，更加的流畅。基本上对sql中的关键字都有支持，详细请查看官方文档。https。...
复制链接

扫一扫