Kill mapper XML! MyBatis new feature dynamic SQL really fragrant!

SpringBoot e-commerce project mall (40K + STAR) address: github.com/macrozheng/…

Abstract

When we use MyBatis, we need to write a lot of SQL statements in mapper.xml. When we use MyBatis Generator (MBG) as a code Generator, we also generate a large number of mapper.xml files. MBG 1.3.6, MyBatis official has recommended the use of Dynamic SQL, using this new feature basic need not write mapper. XML file, use is very convenient, recommend to everyone!

Dynamic SQL profile

When we used Spring, there were two configuration options: XML and Java. When using SpringBoot, Java configuration is already recommended, and XML configuration is rarely used. Using Dynamic SQL is like using Java to manipulate MyBatis. Dynamic SQL is a framework for generating Dynamic SQL statements. It advocates the use of Java APIS to implement SQL operations and supports complex queries and multi-table queries.

Dynamic SQL has the following features:

• Type safety: Ensures that parameter types match database field types;
• Expressive: Statements are constructed in such a way that their meaning is clearly conveyed;
• Flexibility: You can use any combination of and, OR, and nested conditions to build where clauses.
• Strong scalability: can generate SQL statements for MyBatis3, Spring JDBC and pure JDBC frameworks at the same time;
• Lightweight: Just add a small dependency and pass no dependencies.

Begin to use

Let’s start with Dynamic SQL with an introductory example that includes basic CRUD operations. MyBatis official code generation tool to power!

The integrated Dynamic SQL

• inpom.xmlTo add the following dependencies, compared with the previous use of MBG, only add more MyBatis dynamic SQL dependencies;

<dependencies>
    <! - MyBatis SpringBoot integration -- >
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>
    <! MyBatis paging plugin -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.3.0</version>
    </dependency>
    <! Druid connection pool -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <! -- MyBatis generator -->
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>
    <! MyBatis dynamic SQL support -->
    <dependency>
        <groupId>org.mybatis.dynamic-sql</groupId>
        <artifactId>mybatis-dynamic-sql</artifactId>
        <version>1.2.1</version>
    </dependency>
    <! Mysql database driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
</dependencies>
Copy the code

• inapplication.ymlFor data source and MyBatismapper.xmlTo configure the file path, you only need to configure the customized mapper. XML path.

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
Copy the code

• Add Java configuration for scanning Mapper interface path, MBG generated onmapperPackage under, custom placeddaoUnder the bag.

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

Using code generators

• Before we can use MBG to generate code, we need to configure it a few more times, first ingenerator.propertiesConfigure the database connection information in the file.

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=rootCopy the code

• Then, ingeneratorConfig.xmlTo configure MBG in the file, refer to the notes for configuration attributes.

      
<! DOCTYPEgeneratorConfiguration
        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"/>
        <! Generate serialization method for model -->
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <! Create a toString method for the generated Java model
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>
        <! -- You can customize the code comment to generate model -->
        <commentGenerator type="com.macro.mall.tiny.mbg.CommentGenerator">
            <! -- Whether to remove automatically generated comments true: yes: false: no -->
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
            <property name="addRemarkComments" value="true"/>
        </commentGenerator>
        <! -- Configure database connection -->
        <jdbcConnection driverClass="${jdbc.driverClass}"
                        connectionURL="${jdbc.connectionURL}"
                        userId="${jdbc.userId}"
                        password="${jdbc.password}">
            <! Mysql > update mysql > update mysql > update mysql > update mysql
            <property name="nullCatalogMeansCurrent" value="true" />
        </jdbcConnection>
        <! -- Specify the path to generate the model -->
        <javaModelGenerator targetPackage="com.macro.mall.tiny.mbg.model" targetProject="mall-tiny-dynamic-sql\src\main\java"/>
        <! -- Specify the path to generate mapper interface -->
        <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>
Copy the code

• Different from MBG, targetRuntime needs to be changed to MyBatis3DynamicSql. SqlMapGenerator tag used to configure mapper.xml path does not need to be configured.

• Before using MBG, we customized the generation of entity class annotations. We wrote a class CommentGenerator that inherits DefaultCommentGenerator and wrote the Swagger annotation to the attributes of the entity class in the addFieldComment method.

/** * Created by macro on 2018/4/26. */
public class CommentGenerator extends DefaultCommentGenerator {
    /** * Add comment to field */
    @Override
    public void addFieldComment(Field field, IntrospectedTable introspectedTable, IntrospectedColumn introspectedColumn) {
        String remarks = introspectedColumn.getRemarks();
        // Determine whether to add remarks based on the parameters and remarks
        if(addRemarkComments&&StringUtility.stringHasValue(remarks)){
            Special characters in the database need to be escaped
            if(remarks.contains("\" ")){
                remarks = remarks.replace("\" "."'");
            }
            // Add swagger annotation to model field
            field.addJavaDocLine("@ApiModelProperty(value = \""+remarks+"\")"); }}}Copy the code

• When using Dynamic SQL, this method is useless and needs to be used inaddFieldAnnotationWrite the Swagger annotation to the attributes of the entity class.

/** * 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();
        // Determine whether to add remarks based on the parameters and remarks
        if (StringUtility.stringHasValue(remarks)) {
            Special characters in the database need to be escaped
            if (remarks.contains("\" ")) {
                remarks = remarks.replace("\" "."'");
            }
            // Add swagger annotation to model field
            field.addJavaDocLine("@ApiModelProperty(value = \"" + remarks + "\")"); }}}Copy the code

• When all is ready, execute the main method of the Generator class and generate the following code structure. Instead of generating the mapper. XML file and Example class, DynamicSqlSupport is generated.

Implement basic CRUD operations

Here is the table related to the permission management function in the Mall – Tiny project. For details, please refer to “Building a Project from Scratch? Developing scaffolding Quickly by Hand!” .

• Mapper.xml (CRUD); Mapper (CRUD); mapper.xml (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) {
        returnupdate(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)) ); }}Copy the code

• Some of the generated code DynamicSqlSupport classes, such as UmsAdminDynamicSqlSupport, main is to put the database tables and fields became SqlTable and SqlColumn abstract objects, presumably to prevent us from hard-coded;

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(a) {
            super("ums_admin"); }}}Copy the code

• Take advantage of the MBG-generated code to perform CRUD operations on a single table, such as the most common operations below.

/** * 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);
        returnadminMapper.select(SelectDSLCompleter.allRows()); }}Copy the code

Use the advanced

In order to use Dynamic SQL well, the above basic operations are not enough and some more advanced techniques are required.

SqlBuilder

SqlBuilder is a very useful class that can be used to flexibly build conditions for SQL statements. Some common conditional building methods are as follows.

conditions	example	The corresponding 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

Recall the way we defined select tags in mapper.xml earlier. Each select tag is equivalent to a Statement. The StatementProvider encapsulates the parameters and SQL statements in a Statement, making it easier to create a Statement in Java.

Conditions of the query

Use the SqlBuilder class to build the StatementProvider, and then call the methods in the Mapper interface.

• In this example, background users are queried by user name and status and sorted in descending order by creation time. The SQL implementation is as follows:

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;
Copy the code

• The Java code corresponding to Dynamic SQL is implemented as follows, using SqlBuilder select method can specify query column, using FROM method can specify query table, using WHERE method can build query conditions, using orderBy method can specify sort.

/** * 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);
        returnadminMapper.selectMany(selectStatement); }}Copy the code

Lambda conditional query

It is easier to use Lambda expressions to implement a single table conditional query. To implement the above conditional query, the corresponding Java code implementation is as follows.

/** * 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()));
        returnlist; }}Copy the code

The subquery

Whereas MBG previously required handwritten SQL in mapper.xml to implement subqueries, Dynamic SQL can be implemented directly in Java code.

• The SQL implementation of querying background users by role ID is as follows.

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

• Use Dynamic SQL corresponding Java code to achieve the following, you can find SqlBuilder condition constructor isIn can also nest SqlBuilder query.

/** * 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);
        returnadminMapper.selectMany(selectStatement); }}Copy the code

Group and Join queries

When it comes to multi-table queries, MBG can only be implemented in mapper. XML using handwritten SQL. Dynamic SQL can support multi-table queries.

• In this example, the SQL implementation is as follows:

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;
Copy the code

• Start by adding one to your DaogroupListMethod, and then use@ResultsAnnotations define a 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);
}
Copy the code

• It is then called in the ServicegroupListMethod is passed to StatementProvider, and the corresponding Java code implementation is as follows.

/** * Created by macro on 2020/12/8. */
@Service
public class UmsAdminServiceImpl implements UmsAdminService {
    @Override
    public List<RoleStatDto> groupList(a) {
        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);
        returnadminDao.groupList(selectStatement); }}Copy the code

Conditions to delete

Dynamic SQL is used to achieve conditional deletion by directly calling the delete method generated in the Mapper interface.

• Here, the SQL implementation of deleting background users by user name is as follows.

DELETE 
FROM
	ums_admin 
WHERE
	username = 'andy';
Copy the code

• Using Dynamic SQL corresponds to the Java implementation as follows.

/** * 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); }}Copy the code

Conditions change

Use Dynamic SQL to implement conditional modification by directly calling the generated UPDATE method in the Mapper interface.

• This section takes changing the status of background users by the specified ID as an example. The SQL implementation is as follows:

UPDATE ums_admin 
SET STATUS = 1 
WHERE
	id IN ( 1.2 );
Copy the code

• Using Dynamic SQL corresponds to the Java implementation as follows.

/** * 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); }}Copy the code

One-to-many query

One-to-many queries can also be implemented using Dynamic SQL, but since Java annotations do not allow circular references, a one-to-many resultMap can only be configured in mapper.xml, which is probably the only place where mapper.xml is needed.

• This section uses querying background user information (including the role list) by ID as an example. The SQL implementation is as follows:

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
Copy the code

• Then add it in the Dao interfaceselectWithRoleListMethod, used here@ResultMapAnnotations refer to a resultMap defined in mapper.xml;

/** * Created by macro on 2020/12/9. */
public interface UmsAdminDao {

    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    @ResultMap("AdminRoleResult")
    AdminRoleDto selectWithRoleList(SelectStatementProvider selectStatement);
}
Copy the code

• Add the name to mapper.xmlAdminRoleResultResultMap, there is a trick that can refer directly to the resultMap defined in the Mapper interface;

<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>
Copy the code

• It is then called in the Service implementation class, with the query columns aliased to facilitate the mapping of the result set.

/** * 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);
        returnadminDao.selectWithRoleList(selectStatement); }}Copy the code

conclusion

When we use the MyBatis official code generator MBG, the configured targetRuntime determines how to use it. Dynamic SQL is more likely to use Java APIS to implement SQL operations than the traditional way of implementing SQL operations by hand in Mapper.xml. Although MyBatis officially recommends using Dynamic SQL, it is up to you to choose which way.

The resources

Official documentation: mybatis.org/mybatis-dyn…

Project source code address

Github.com/macrozheng/…

In this paper, making github.com/macrozheng/… Already included, welcome everyone Star!