乐趣区

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

jiezi

当咱们应用 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

Dynamic SQL 简介

在咱们应用 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 官网代码生成工具给力！》

集成 Dynamic SQL

  • 在 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 类。

实现根本的 CRUD 操作

这里应用的是 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

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)

StatementProvider

回忆一下之前咱们在 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 条件查问

应用 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);
    }
}

Group 和 Join 查问

波及到多表查问，之前应用 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！

退出移动版