mysql update mapper_干掉mapper.xml！MyBatis新特性动态SQL真香！

摘要

当我们使用MyBatis的时候，需要在mapper.xml中书写大量的SQL语句。当我们使用MyBatis Generator(MBG)作为代码生成器时，也会生成大量的mapper.xml文件。其实从MBG 1.3.6版本以后，MyBatis官方已经推荐使用Dynamic SQL，使用这一新特性基本就不用写mapper.xml文件了，使用起来非常方便，推荐给大家！

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操作。

集成Dynamic SQL在pom.xml中添加如下依赖，对比之前使用MBG，仅仅多添加了MyBatis的动态SQL依赖；

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.3

com.github.pagehelper

pagehelper-spring-boot-starter

1.3.0

com.alibaba

druid-spring-boot-starter

1.1.10

org.mybatis.generator

mybatis-generator-core

1.4.0

org.mybatis.dynamic-sql

mybatis-dynamic-sql

1.2.1

mysql

mysql-connector-java

8.0.15

在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进行配置，配置属性说明直接参考注释即可；

/p>

PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

connectionURL="${jdbc.connectionURL}"

userId="${jdbc.userId}"

password="${jdbc.password}">

targetProject="mall-tiny-dynamic-sql\src\main\java"/>

与之前使用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 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类。

image

实现基本的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 insertStatement);

@Generated("org.mybatis.generator.api.MyBatisGenerator")

@SelectProvider(type=SqlProviderAdapter.class, method="select")

@ResultMap("UmsAdminResult")

Optional 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 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 selectOne(SelectDSLCompleter completer) {

return MyBatis3Utils.selectOne(this::selectOne, selectList, umsAdmin, completer);

}

@Generated("org.mybatis.generator.api.MyBatisGenerator")

default List select(SelectDSLCompleter completer) {

return MyBatis3Utils.selectList(this::selectMany, selectList, umsAdmin, completer);

}

@Generated("org.mybatis.generator.api.MyBatisGenerator")

default List selectDistinct(SelectDSLCompleter completer) {

return MyBatis3Utils.selectDistinct(this::selectMany, selectList, umsAdmin, completer);

}

@Generated("org.mybatis.generator.api.MyBatisGenerator")

default Optional 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 updateAllColumns(UmsAdmin record, UpdateDSL 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 updateSelectiveColumns(UmsAdmin record, UpdateDSL 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 id = umsAdmin.id;

public static final SqlColumn username = umsAdmin.username;

public static final SqlColumn password = umsAdmin.password;

public static final SqlColumn icon = umsAdmin.icon;

public static final SqlColumn email = umsAdmin.email;

public static final SqlColumn nickName = umsAdmin.nickName;

public static final SqlColumn note = umsAdmin.note;

public static final SqlColumn createTime = umsAdmin.createTime;

public static final SqlColumn loginTime = umsAdmin.loginTime;

public static final SqlColumn status = umsAdmin.status;

@Generated("org.mybatis.generator.api.MyBatisGenerator")

public static final class UmsAdmin extends SqlTable {

public final SqlColumn id = column("id", JDBCType.BIGINT);

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

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

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

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

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

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

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

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

public final SqlColumn 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 optionalEntity = adminMapper.selectByPrimaryKey(id);

return optionalEntity.orElse(null);

}

@Override

public List listAll(Integer pageNum, Integer pageSize) {

PageHelper.startPage(pageNum, pageSize);

return adminMapper.select(SelectDSLCompleter.allRows());

}

}

进阶使用想要用好Dynamic SQL，上面的基础操作是不够的，还需要一些进阶的使用技巧。

SqlBuilder

SqlBuilder是一个非常有用的类，使用它可以灵活地构建SQL语句的条件，一些常用的条件构建方法如下。

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 list(Integer pageNum, Integer pageSize, String username, List 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 lambdaList(Integer pageNum, Integer pageSize, String username, List statusList) {

PageHelper.startPage(pageNum, pageSize);

List 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 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 groupList(SelectStatementProvider selectStatement);

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

/**

* 后台用户管理Service实现类

* Created by macro on 2020/12/8.

*/

@Service

public class UmsAdminServiceImpl implements UmsAdminService {

@Override

public List 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 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；

extends="com.macro.mall.tiny.mbg.mapper.UmsAdminMapper.UmsAdminResult">

然后在Service实现类中调用即可，为了方便结果集映射给查询列取了别名。

/**

* 后台用户管理Service实现类

* Created by macro on 2020/12/8.

*/

@Service

public class UmsAdminServiceImpl implements UmsAdminService {

@Override

public AdminRoleDto selectWithRoleList(Long id) {

List 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，但选择那种方式全看个人习惯了！