关于java:干掉mapperxmlMyBatis新特性动态SQL真香

当咱们应用 MyBatis 的时候，须要在 mapper.xml 中书写大量的 SQL 语句。当咱们应用 MyBatis Generator（MBG）作为代码生成器时，也会生成大量的 mapper.xml 文件。其实从 MBG 1.3.6 版本当前，MyBatis 官网曾经举荐应用 Dynamic SQL，应用这一新个性根本就不必写 mapper.xml 文件了，应用起来十分不便，举荐给大家！

SpringBoot 实战电商我的项目 mall（40k+star）地址：https://github.com/macrozheng/mall

在咱们应用 Spring 的时候，有 XML 和 Java 两种配置形式。在应用 SpringBoot 时，曾经举荐应用 Java 配置，根本不必 xml 配置了。应用 Dynamic SQL 就好比是应用 Java 的形式来操作 MyBatis。Dynamic SQL 是用于生成动静 SQL 语句的框架，提倡应用 Java API 的形式来实现 SQL 操作，反对简单查问和多表查问。

Dynamic SQL 具备如下个性：

类型平安：能够确保参数类型和数据库字段类型相匹配；
富裕表现力：语句的构建形式能够分明地传播其含意；
应用灵便：能够应用 and，or 和 nested 条件的任意组合来构建 where 子句；
扩展性强：能够同时为 MyBatis3, Spring JDBC 和纯 JDBC 框架生成 SQL 语句；
轻量级：只需增加一个小的依赖项，没有传递依赖。

首先咱们通过一个入门示例将 Dynamic SQL 用起来，该示例会蕴含根底的 CRUD 操作。对 MBG 应用不理解的敌人能够先看下之前的文章《解放双手！MyBatis 官网代码生成工具给力！》

在 pom.xml 中增加如下依赖，比照之前应用 MBG，仅仅多增加了 MyBatis 的动静 SQL 依赖；

<dependencies>
    <!--SpringBoot 整合 MyBatis-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>
    <!--MyBatis 分页插件 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.3.0</version>
    </dependency>
    <!-- 集成 druid 连接池 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <!-- MyBatis 生成器 -->
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>
    <!-- MyBatis 动静 SQL 反对 -->
    <dependency>
        <groupId>org.mybatis.dynamic-sql</groupId>
        <artifactId>mybatis-dynamic-sql</artifactId>
        <version>1.2.1</version>
    </dependency>
    <!--Mysql 数据库驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
</dependencies>

在 application.yml 中对数据源和 MyBatis 的 mapper.xml 文件门路进行配置，只需配置自定义 mapper.xml 门路即可；

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: root

mybatis:
  mapper-locations:
    - classpath:dao/*.xml

增加 Java 配置，用于扫描 Mapper 接口门路，MBG 生成的放在 mapper 包下，自定义的放在 dao 包下。

/**
 * MyBatis 配置类
 * Created by macro on 2019/4/8.
 */
@Configuration
@MapperScan({"com.macro.mall.tiny.mbg.mapper","com.macro.mall.tiny.dao"})
public class MyBatisConfig {}

在应用 MBG 生成代码前，咱们还须要对其进行一些配置，首先在 generator.properties 文件中配置好数据库连贯信息；

jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.connectionURL=jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
jdbc.userId=root
jdbc.password=root

而后在 generatorConfig.xml 文件中对 MBG 进行配置，配置属性阐明间接参考正文即可；

<?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>
    <properties resource="generator.properties"/>
    <context id="MySqlContext" targetRuntime="MyBatis3DynamicSQL">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>
        <property name="javaFileEncoding" value="UTF-8"/>
        <!-- 为模型生成序列化办法 -->
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <!-- 为生成的 Java 模型创立一个 toString 办法 -->
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>
        <!-- 能够自定义生成 model 的代码正文 -->
        <commentGenerator type="com.macro.mall.tiny.mbg.CommentGenerator">
            <!-- 是否去除主动生成的正文 true：是：false: 否 -->
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
            <property name="addRemarkComments" value="true"/>
        </commentGenerator>
        <!-- 配置数据库连贯 -->
        <jdbcConnection driverClass="${jdbc.driverClass}"
                        connectionURL="${jdbc.connectionURL}"
                        userId="${jdbc.userId}"
                        password="${jdbc.password}">
            <!-- 解决 mysql 驱动降级到 8.0 后不生成指定数据库代码的问题 -->
            <property name="nullCatalogMeansCurrent" value="true" />
        </jdbcConnection>
        <!-- 指定生成 model 的门路 -->
        <javaModelGenerator targetPackage="com.macro.mall.tiny.mbg.model" targetProject="mall-tiny-dynamic-sql\src\main\java"/>
        <!-- 指定生成 mapper 接口的的门路 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.macro.mall.tiny.mbg.mapper"
                             targetProject="mall-tiny-dynamic-sql\src\main\java"/>
        <!-- 生成全副表 tableName 设为 %-->
        <table tableName="ums_admin">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="ums_role">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="ums_admin_role_relation">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
    </context>
</generatorConfiguration>

与之前应用 MBG 有所不同，targetRuntime须要改为 MyBatis3DynamicSql，用于配置生成 mapper.xml 门路的sqlMapGenerator 标签也不须要配置了；
之前应用 MBG 时自定义了实体类注解的生成，写了个类 CommentGenerator 继承 DefaultCommentGenerator，在 addFieldComment 办法中将 Swagger 注解写入到了实体类的属性上；

/**
 * 自定义正文生成器
 * Created by macro on 2018/4/26.
 */
public class CommentGenerator extends DefaultCommentGenerator {
    /**
     * 给字段增加正文
     */
    @Override
    public void addFieldComment(Field field, IntrospectedTable introspectedTable,
                                IntrospectedColumn introspectedColumn) {String remarks = introspectedColumn.getRemarks();
        // 依据参数和备注信息判断是否增加备注信息
        if(addRemarkComments&&StringUtility.stringHasValue(remarks)){
            // 数据库中特殊字符须要本义
            if(remarks.contains("\"")){remarks = remarks.replace("\"","'");
            }
            // 给 model 的字段增加 swagger 注解
            field.addJavaDocLine("@ApiModelProperty(value = \""+remarks+"\")");
        }
    }
}

在应用 Dynamic SQL 的时候，这种办法曾经无用，须要在 addFieldAnnotation 中将 Swagger 注解写入到了实体类的属性上；

/**
 * 自定义正文生成器
 * Created by macro on 2018/4/26.
 */
public class CommentGenerator extends DefaultCommentGenerator {

    @Override
    public void addFieldAnnotation(Field field, IntrospectedTable introspectedTable, IntrospectedColumn introspectedColumn, Set<FullyQualifiedJavaType> imports) {if (!addRemarkComments || CollUtil.isEmpty(imports)) return;
        long count = imports.stream()
                .filter(item -> API_MODEL_PROPERTY_FULL_CLASS_NAME.equals(item.getFullyQualifiedName()))
                .count();
        if (count <= 0L) {return;}
        String remarks = introspectedColumn.getRemarks();
        // 依据参数和备注信息判断是否增加备注信息
        if (StringUtility.stringHasValue(remarks)) {
            // 数据库中特殊字符须要本义
            if (remarks.contains("\"")) {remarks = remarks.replace("\"", "'");
            }
            // 给 model 的字段增加 swagger 注解
            field.addJavaDocLine("@ApiModelProperty(value = \"" + remarks + "\")");
        }
    }
}

所有准备就绪，执行 Generator 类的 main 办法，生成代码构造信息如下，能够发现曾经不再生成 mapper.xml 文件和 Example 类，取而代之的是生成了 DynamicSqlSupport 类。

这里应用的是 mall-tiny 我的项目中权限治理性能相干表，具体能够参考《还在从零开始搭建我的项目？手撸了款疾速开发脚手架！》。

查看下 MBG 生成的 Mapper 接口，比之前应用 MBG 时减少了很多办法，并且有了一些默认的办法实现，可见之前在 mapper.xml 中的实现都曾经转移到 Mapper 接口中去了，单表 CRUD 间接调用对应办法即可；

@Mapper
public interface UmsAdminMapper {@Generated("org.mybatis.generator.api.MyBatisGenerator")
    BasicColumn[] selectList = BasicColumn.columnList(id, username, password, icon, email, nickName, note, createTime, loginTime, status);

    @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")
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="record.id", before=false, resultType=Long.class)
    int insert(InsertStatementProvider<UmsAdmin> insertStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("UmsAdminResult")
    Optional<UmsAdmin> selectOne(SelectStatementProvider selectStatement);

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @Results(id="UmsAdminResult", value = {@Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
        @Result(column="username", property="username", jdbcType=JdbcType.VARCHAR),
        @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
        @Result(column="icon", property="icon", jdbcType=JdbcType.VARCHAR),
        @Result(column="email", property="email", jdbcType=JdbcType.VARCHAR),
        @Result(column="nick_name", property="nickName", jdbcType=JdbcType.VARCHAR),
        @Result(column="note", property="note", jdbcType=JdbcType.VARCHAR),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="login_time", property="loginTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="status", property="status", jdbcType=JdbcType.INTEGER)
    })
    List<UmsAdmin> 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, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int delete(DeleteDSLCompleter completer) {return MyBatis3Utils.deleteFrom(this::delete, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int deleteByPrimaryKey(Long id_) {
        return delete(c -> 
            c.where(id, isEqualTo(id_))
        );
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int insert(UmsAdmin record) {
        return MyBatis3Utils.insert(this::insert, record, umsAdmin, c ->
            c.map(username).toProperty("username")
            .map(password).toProperty("password")
            .map(icon).toProperty("icon")
            .map(email).toProperty("email")
            .map(nickName).toProperty("nickName")
            .map(note).toProperty("note")
            .map(createTime).toProperty("createTime")
            .map(loginTime).toProperty("loginTime")
            .map(status).toProperty("status")
        );
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int insertSelective(UmsAdmin record) {
        return MyBatis3Utils.insert(this::insert, record, umsAdmin, c ->
            c.map(username).toPropertyWhenPresent("username", record::getUsername)
            .map(password).toPropertyWhenPresent("password", record::getPassword)
            .map(icon).toPropertyWhenPresent("icon", record::getIcon)
            .map(email).toPropertyWhenPresent("email", record::getEmail)
            .map(nickName).toPropertyWhenPresent("nickName", record::getNickName)
            .map(note).toPropertyWhenPresent("note", record::getNote)
            .map(createTime).toPropertyWhenPresent("createTime", record::getCreateTime)
            .map(loginTime).toPropertyWhenPresent("loginTime", record::getLoginTime)
            .map(status).toPropertyWhenPresent("status", record::getStatus)
        );
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default Optional<UmsAdmin> selectOne(SelectDSLCompleter completer) {return MyBatis3Utils.selectOne(this::selectOne, selectList, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default List<UmsAdmin> select(SelectDSLCompleter completer) {return MyBatis3Utils.selectList(this::selectMany, selectList, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default List<UmsAdmin> selectDistinct(SelectDSLCompleter completer) {return MyBatis3Utils.selectDistinct(this::selectMany, selectList, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default Optional<UmsAdmin> selectByPrimaryKey(Long 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, umsAdmin, completer);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    static UpdateDSL<UpdateModel> updateAllColumns(UmsAdmin record, UpdateDSL<UpdateModel> dsl) {return dsl.set(username).equalTo(record::getUsername)
                .set(password).equalTo(record::getPassword)
                .set(icon).equalTo(record::getIcon)
                .set(email).equalTo(record::getEmail)
                .set(nickName).equalTo(record::getNickName)
                .set(note).equalTo(record::getNote)
                .set(createTime).equalTo(record::getCreateTime)
                .set(loginTime).equalTo(record::getLoginTime)
                .set(status).equalTo(record::getStatus);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    static UpdateDSL<UpdateModel> updateSelectiveColumns(UmsAdmin record, UpdateDSL<UpdateModel> dsl) {return dsl.set(username).equalToWhenPresent(record::getUsername)
                .set(password).equalToWhenPresent(record::getPassword)
                .set(icon).equalToWhenPresent(record::getIcon)
                .set(email).equalToWhenPresent(record::getEmail)
                .set(nickName).equalToWhenPresent(record::getNickName)
                .set(note).equalToWhenPresent(record::getNote)
                .set(createTime).equalToWhenPresent(record::getCreateTime)
                .set(loginTime).equalToWhenPresent(record::getLoginTime)
                .set(status).equalToWhenPresent(record::getStatus);
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int updateByPrimaryKey(UmsAdmin record) {
        return update(c ->
            c.set(username).equalTo(record::getUsername)
            .set(password).equalTo(record::getPassword)
            .set(icon).equalTo(record::getIcon)
            .set(email).equalTo(record::getEmail)
            .set(nickName).equalTo(record::getNickName)
            .set(note).equalTo(record::getNote)
            .set(createTime).equalTo(record::getCreateTime)
            .set(loginTime).equalTo(record::getLoginTime)
            .set(status).equalTo(record::getStatus)
            .where(id, isEqualTo(record::getId))
        );
    }

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    default int updateByPrimaryKeySelective(UmsAdmin record) {
        return update(c ->
            c.set(username).equalToWhenPresent(record::getUsername)
            .set(password).equalToWhenPresent(record::getPassword)
            .set(icon).equalToWhenPresent(record::getIcon)
            .set(email).equalToWhenPresent(record::getEmail)
            .set(nickName).equalToWhenPresent(record::getNickName)
            .set(note).equalToWhenPresent(record::getNote)
            .set(createTime).equalToWhenPresent(record::getCreateTime)
            .set(loginTime).equalToWhenPresent(record::getLoginTime)
            .set(status).equalToWhenPresent(record::getStatus)
            .where(id, isEqualTo(record::getId))
        );
    }
}

生成代码中有一些 DynamicSqlSupport 类，比方 UmsAdminDynamicSqlSupport，次要是把数据库表和字段形象成了 SqlTable 和 SqlColumn 对象，预计是为了避免咱们硬编码；

public final class UmsAdminDynamicSqlSupport {@Generated("org.mybatis.generator.api.MyBatisGenerator")
    public static final UmsAdmin umsAdmin = new UmsAdmin();

    public static final SqlColumn<Long> id = umsAdmin.id;

    public static final SqlColumn<String> username = umsAdmin.username;

    public static final SqlColumn<String> password = umsAdmin.password;

    public static final SqlColumn<String> icon = umsAdmin.icon;

    public static final SqlColumn<String> email = umsAdmin.email;

    public static final SqlColumn<String> nickName = umsAdmin.nickName;

    public static final SqlColumn<String> note = umsAdmin.note;

    public static final SqlColumn<Date> createTime = umsAdmin.createTime;

    public static final SqlColumn<Date> loginTime = umsAdmin.loginTime;

    public static final SqlColumn<Integer> status = umsAdmin.status;

    @Generated("org.mybatis.generator.api.MyBatisGenerator")
    public static final class UmsAdmin extends SqlTable {public final SqlColumn<Long> id = column("id", JDBCType.BIGINT);

        public final SqlColumn<String> username = column("username", JDBCType.VARCHAR);

        public final SqlColumn<String> password = column("password", JDBCType.VARCHAR);

        public final SqlColumn<String> icon = column("icon", JDBCType.VARCHAR);

        public final SqlColumn<String> email = column("email", JDBCType.VARCHAR);

        public final SqlColumn<String> nickName = column("nick_name", JDBCType.VARCHAR);

        public final SqlColumn<String> note = column("note", JDBCType.VARCHAR);

        public final SqlColumn<Date> createTime = column("create_time", JDBCType.TIMESTAMP);

        public final SqlColumn<Date> loginTime = column("login_time", JDBCType.TIMESTAMP);

        public final SqlColumn<Integer> status = column("status", JDBCType.INTEGER);

        public UmsAdmin() {super("ums_admin");
        }
    }
}

利用好 MBG 生成的代码即可实现单表的 CRUD 操作了，比方上面最常见的操作。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Autowired
    private UmsAdminMapper adminMapper;

    @Override
    public void create(UmsAdmin entity) {adminMapper.insert(entity);
    }

    @Override
    public void update(UmsAdmin entity) {adminMapper.updateByPrimaryKeySelective(entity);
    }

    @Override
    public void delete(Long id) {adminMapper.deleteByPrimaryKey(id);
    }

    @Override
    public UmsAdmin select(Long id) {Optional<UmsAdmin> optionalEntity = adminMapper.selectByPrimaryKey(id);
        return optionalEntity.orElse(null);
    }

    @Override
    public List<UmsAdmin> listAll(Integer pageNum, Integer pageSize) {PageHelper.startPage(pageNum, pageSize);
        return adminMapper.select(SelectDSLCompleter.allRows());
    }
}

想要用好 Dynamic SQL，下面的根底操作是不够的，还须要一些进阶的应用技巧。

SqlBuilder 是一个十分有用的类，应用它能够灵便地构建 SQL 语句的条件，一些罕用的条件构建办法如下。

条件	例子	对应 SQL
Between	where(foo, isBetween(x).and(y))	where foo between ? and ?
Equals	where(foo, isEqualTo(x))	where foo = ?
Greater Than	where(foo, isGreaterThan(x))	where foo > ?
In	where(foo, isIn(x, y))	where foo in (?,?)
Like	where(foo, isLike(x))	where foo like ?
Not Equals	where(foo, isNotEqualTo(x))	where foo <> ?
Null	where(foo, isNull())	where foo is null
Present Equals	where(foo, isEqualToWhenPresent(x))	where foo = ? (will render if x is non-null)

回忆一下之前咱们在 mapper.xml 中定义 select 标签的形式，各个 select 标签相当于 Statement。而这里的 StatementProvider 好比是 Statement 中参数和 SQL 语句的封装，不便以 Java 的形式创立 Statement。

应用 SqlBuilder 类构建 StatementProvider，而后调用 Mapper 接口中的办法即可。

这里以按用户名和状态查问后盾用户并按创立工夫降序排列为例，SQL 实现如下；

SELECT
    id,
    username,
    PASSWORD,
    icon,
    email,
    nick_name,
    note,
    create_time,
    login_time,
STATUS 
FROM
    ums_admin 
WHERE
    (username = 'macro' AND STATUS IN ( 0, 1) ) 
ORDER BY
    create_time DESC;

应用 Dynamic SQL 对应的 Java 代码实现如下，应用 SqlBuilder 的 select 办法能够指定查问列，应用 from 办法能够指定查问表，应用 where 办法能够构建查问条件，应用 orderBy 办法能够指定排序。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public List<UmsAdmin> list(Integer pageNum, Integer pageSize, String username, List<Integer> statusList) {PageHelper.startPage(pageNum, pageSize);
        SelectStatementProvider selectStatement = SqlBuilder.select(UmsAdminMapper.selectList)
                .from(UmsAdminDynamicSqlSupport.umsAdmin)
                .where(UmsAdminDynamicSqlSupport.username, isEqualToWhenPresent(username))
                .and(UmsAdminDynamicSqlSupport.status, isIn(statusList))
                .orderBy(UmsAdminDynamicSqlSupport.createTime.descending())
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return adminMapper.selectMany(selectStatement);
    }
}

应用 Lambda 表达式实现单表条件查问更加简略，实现下面的条件查问，对应 Java 代码实现如下。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public List<UmsAdmin> lambdaList(Integer pageNum, Integer pageSize, String username, List<Integer> statusList) {PageHelper.startPage(pageNum, pageSize);
        List<UmsAdmin> list = adminMapper.select(c -> c.where(UmsAdminDynamicSqlSupport.username, isEqualToWhenPresent(username))
                .and(UmsAdminDynamicSqlSupport.status, isIn(statusList))
                .orderBy(UmsAdminDynamicSqlSupport.createTime.descending()));
        return list;
    }
}

之前应用 MBG 须要在 mapper.xml 中手写 SQL 能力实现子查问，应用 Dynamic SQL 能够间接在 Java 代码中实现。

这里以按角色 ID 查问后盾用户为例，SQL 实现如下；

SELECT
    * 
FROM
    ums_admin 
WHERE
    id IN (SELECT admin_id FROM ums_admin_role_relation WHERE role_id = 1)

应用 Dynamic SQL 对应的 Java 代码实现如下，能够发现 SqlBuilder 的条件构造方法 isIn 中还能够嵌套 SqlBuilder 的查问。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public List<UmsAdmin> subList(Long roleId) {SelectStatementProvider selectStatement = SqlBuilder.select(UmsAdminMapper.selectList)
                .from(UmsAdminDynamicSqlSupport.umsAdmin)
                .where(UmsAdminDynamicSqlSupport.id, isIn(SqlBuilder.select(UmsAdminRoleRelationDynamicSqlSupport.adminId)
                        .from(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
                        .where(UmsAdminRoleRelationDynamicSqlSupport.roleId, isEqualTo(roleId))))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return adminMapper.selectMany(selectStatement);
    }
}

波及到多表查问，之前应用 MBG 的时候根本只能在 mapper.xml 中手写 SQL 实现，应用 Dynamic SQL 能够反对多表查问。

这里以按角色统计后盾用户数量为例，SQL 实现如下；

SELECT
    ur.id AS roleId,
    ur.NAME AS roleName,
    count(ua.id) AS count 
FROM
    ums_role ur
    LEFT JOIN ums_admin_role_relation uarr ON ur.id = uarr.role_id
    LEFT JOIN ums_admin ua ON uarr.admin_id = ua.id 
GROUP BY
    ur.id;

先在 Dao 中增加一个 groupList 办法，而后应用 @Results 注解定义好 resultMap；

/**
 * Created by macro on 2020/12/9.
 */
public interface UmsAdminDao {@SelectProvider(type = SqlProviderAdapter.class, method = "select")
    @Results(id = "RoleStatResult", value = {@Result(column = "roleId", property = "roleId", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "roleName", property = "roleName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "count", property = "count", jdbcType = JdbcType.INTEGER)
    })
    List<RoleStatDto> groupList(SelectStatementProvider selectStatement);
}

而后在 Service 中调用 groupList 办法传入 StatementProvider 即可，对应的 Java 代码实现如下。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public List<RoleStatDto> groupList() {SelectStatementProvider selectStatement = SqlBuilder.select(UmsRoleDynamicSqlSupport.id.as("roleId"), UmsRoleDynamicSqlSupport.name.as("roleName"), count(UmsAdminDynamicSqlSupport.id).as("count"))
                .from(UmsRoleDynamicSqlSupport.umsRole)
                .leftJoin(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
                .on(UmsRoleDynamicSqlSupport.id, equalTo(UmsAdminRoleRelationDynamicSqlSupport.roleId))
                .leftJoin(UmsAdminDynamicSqlSupport.umsAdmin)
                .on(UmsAdminRoleRelationDynamicSqlSupport.adminId, equalTo(UmsAdminDynamicSqlSupport.id))
                .groupBy(UmsRoleDynamicSqlSupport.id)
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return adminDao.groupList(selectStatement);
    }
}

应用 Dynamic SQL 实现条件删除，间接调用 Mapper 接口中生成好的 delete 办法即可。

这里以按用户名删除后盾用户为例，SQL 实现如下；

DELETE 
FROM
    ums_admin 
WHERE
    username = 'andy';

应用 Dynamic SQL 对应 Java 中的实现如下。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public void deleteByUsername(String username) {DeleteStatementProvider deleteStatement = SqlBuilder.deleteFrom(UmsAdminDynamicSqlSupport.umsAdmin)
                .where(UmsAdminDynamicSqlSupport.username, isEqualTo(username))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        adminMapper.delete(deleteStatement);
    }
}

应用 Dynamic SQL 实现条件批改，间接调用 Mapper 接口中生成好的 update 办法即可。

这里以按指定 ID 批改后盾用户的状态为例，SQL 实现如下；

UPDATE ums_admin 
SET STATUS = 1 
WHERE
    id IN (1, 2);

应用 Dynamic SQL 对应 Java 中的实现如下。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public void updateByIds(List<Long> ids, Integer status) {UpdateStatementProvider updateStatement = SqlBuilder.update(UmsAdminDynamicSqlSupport.umsAdmin)
                .set(UmsAdminDynamicSqlSupport.status).equalTo(status)
                .where(UmsAdminDynamicSqlSupport.id, isIn(ids))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        adminMapper.update(updateStatement);
    }
}

应用 Dynamic SQL 也能够实现一对多查问，只是因为 Java 注解无奈实现循环援用，所以一对多的 resultMap 只能在 mapper.xml 来配置，这可能是惟一须要应用 mapper.xml 的中央。

这里以按 ID 查问后盾用户信息（蕴含对应角色列表）为例，SQL 实现如下；

SELECT
    ua.*,
    ur.id AS role_id,
    ur.NAME AS role_name,
    ur.description AS role_description,
    ur.create_time AS role_create_time,
    ur.STATUS AS role_status,
    ur.sort AS role_sort 
FROM
    ums_admin ua
    LEFT JOIN ums_admin_role_relation uarr ON ua.id = uarr.admin_id
    LEFT JOIN ums_role ur ON uarr.role_id = ur.id 
WHERE
    ua.id = 1

而后在 Dao 接口中增加 selectWithRoleList 办法，这里应用 @ResultMap 注解援用 mapper.xml 中定义的 resultMap；

/**
 * Created by macro on 2020/12/9.
 */
public interface UmsAdminDao {@SelectProvider(type = SqlProviderAdapter.class, method = "select")
    @ResultMap("AdminRoleResult")
    AdminRoleDto selectWithRoleList(SelectStatementProvider selectStatement);
}

在 mapper.xml 中增加名称为 AdminRoleResult 的 resultMap，这里有个小技巧，能够间接援用在 Mapper 接口中定义好的 resultMap；

<resultMap id="AdminRoleResult" type="com.macro.mall.tiny.domain.AdminRoleDto"
           extends="com.macro.mall.tiny.mbg.mapper.UmsAdminMapper.UmsAdminResult">
    <collection property="roleList" resultMap="com.macro.mall.tiny.mbg.mapper.UmsRoleMapper.UmsRoleResult" columnPrefix="role_">
    </collection>
</resultMap>

而后在 Service 实现类中调用即可，为了不便后果集映射给查问列取了别名。

/**
 * 后盾用户治理 Service 实现类
 * Created by macro on 2020/12/8.
 */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {   
    @Override
    public AdminRoleDto selectWithRoleList(Long id) {List<BasicColumn> columnList = new ArrayList<>(CollUtil.toList(UmsAdminMapper.selectList));
        columnList.add(UmsRoleDynamicSqlSupport.id.as("role_id"));
        columnList.add(UmsRoleDynamicSqlSupport.name.as("role_name"));
        columnList.add(UmsRoleDynamicSqlSupport.description.as("role_description"));
        columnList.add(UmsRoleDynamicSqlSupport.createTime.as("role_create_time"));
        columnList.add(UmsRoleDynamicSqlSupport.status.as("role_status"));
        columnList.add(UmsRoleDynamicSqlSupport.sort.as("role_sort"));
        SelectStatementProvider selectStatement = SqlBuilder.select(columnList)
                .from(UmsAdminDynamicSqlSupport.umsAdmin)
                .leftJoin(UmsAdminRoleRelationDynamicSqlSupport.umsAdminRoleRelation)
                .on(UmsAdminDynamicSqlSupport.id, equalTo(UmsAdminRoleRelationDynamicSqlSupport.adminId))
                .leftJoin(UmsRoleDynamicSqlSupport.umsRole)
                .on(UmsAdminRoleRelationDynamicSqlSupport.roleId, equalTo(UmsRoleDynamicSqlSupport.id))
                .where(UmsAdminDynamicSqlSupport.id, isEqualTo(id))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return adminDao.selectWithRoleList(selectStatement);
    }
}

当咱们应用 MyBatis 官网代码生成器 MBG 时，配置的 targetRuntime 决定了应用它的应用形式。Dynamic SQL 更偏向于应用 Java API 来实现 SQL 操作，传统的形式更偏向于在 mapper.xml 中手写 SQL 来实现 SQL 操作。尽管 MyBatis 官网举荐应用 Dynamic SQL，但抉择那种形式全看集体习惯了！

官网文档：https://mybatis.org/mybatis-d…

https://github.com/macrozheng…

本文 GitHub https://github.com/macrozheng/mall-learning 曾经收录，欢送大家 Star！

Dynamic SQL 简介

开始应用

集成 Dynamic SQL

应用代码生成器

实现根本的 CRUD 操作

进阶应用

SqlBuilder

StatementProvider

条件查问

Lambda 条件查问

子查问

Group 和 Join 查问

条件删除

条件批改

一对多查问

总结

参考资料

我的项目源码地址

站内搜索