如何扩展MybatisPlus的BaseMapper（Sql注入器的使用、自动填充功能、逻辑删除功能）

工作中常用的MybatisPlus的扩展功能：Sql注入器的使用、自动填充功能、逻辑删除功能

文章底部有git地址

SQL注入器的使用

我们只用MybatisPlus时，MybatisPlus在BaseMapper中提供了很多可以直接调用的方法，这些方法主要是通过ISqlInjector注入器进行注入，然后并提供使用的，

如果我们也想提供一个公用的方法，就可以通过sql注入器来解决

创建mp_user表

CREATE TABLE `mp_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `create_user` varchar(255) DEFAULT NULL COMMENT '创建人',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user` varchar(255) DEFAULT NULL COMMENT '更新人',
  `update_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '删除标识：0-未删除，1-已删除',
  `username` varchar(50) DEFAULT NULL COMMENT '用户名',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `birthday` varchar(50) DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1434793267970568194 DEFAULT CHARSET=utf8;

添加数据

INSERT INTO `test`.`mp_user`(`id`, `create_user`, `create_date`, `update_user`, `update_date`, `delete_flag`, `username`, `password`, `birthday`) VALUES (1, NULL, NULL, NULL, '2021-09-06 15:51:55', 0, 'lily', '123', '2019-12-12');
INSERT INTO `test`.`mp_user`(`id`, `create_user`, `create_date`, `update_user`, `update_date`, `delete_flag`, `username`, `password`, `birthday`) VALUES (2, NULL, NULL, NULL, '2021-09-06 15:51:56', 0, 'tom', '123', '2019-12-12');

创建User实体

package com.qjc.entity;


import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
@TableName("mp_user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;

    private String createUser;

    private Date createDate;

    private String updateUser;

    private Date updateDate;
    
    private Integer deleteFlag;

    private String username;

    private String password;

    private String birthday;

}

定义MyBaseMapper继承BaseMapper

并定义一个mySelectList方法

package com.qjc.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @ClassName: MyBaseMapper
 * @Description:
 * @Author: qjc
 * @Date: 2021/9/6 12:59 下午
 */
public interface MyBaseMapper<T> extends BaseMapper {

    List<T> mySelectList(@Param("ew") Wrapper<T> queryWrapper);

}

然后创建注入器MySqlInjector，继承SqlInjector的实现类DefaultSqlInjector

package com.qjc.sqlInjector;

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;

import java.util.List;

/**
 * @ClassName: MySqlInjector
 * @Description:
 * @Author: qjc
 * @Date: 2021/9/6 1:01 下午
 */
public class MySqlInjector extends DefaultSqlInjector {


    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new MySelectList());
        return methodList;
    }
}

创建该注入器需要将自定义的方法添加到methodList中，所以需要创建一个方法类（可以参照源码中定义的类）

定义方法类MySelectList（和刚才MyBaseMapper中定义的方法名不一致也没关系，这不是重点）

package com.qjc.sqlInjector;

import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

/**
 * @ClassName: FindAll
 * @Description:
 * @Author: qjc
 * @Date: 2021/9/6 1:02 下午
 */
public class MySelectList extends AbstractMethod {
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
//        String sqlMethod = "findAll";
//        String sql = "select * from " + tableInfo.getTableName();
//        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
//        return this.addSelectMappedStatement(mapperClass, sqlMethod, sqlSource, modelClass, tableInfo);

        SqlMethod sqlMethod = SqlMethod.SELECT_LIST;
        String sql = String.format(sqlMethod.getSql(), sqlFirst(),sqlSelectColumns(tableInfo, true), tableInfo.getTableName(),
                sqlWhereEntityWrapper(true, tableInfo), sqlComment());
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addSelectMappedStatementForTable(mapperClass, "mySelectList", sqlSource, tableInfo);
    }

    @Override
    public String sqlWhereEntityWrapper(boolean newLine, TableInfo table) {
        if (table.isLogicDelete()) {
            String sqlScript = table.getAllSqlWhere(true, true, WRAPPER_ENTITY_DOT);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY),
                    true);
            sqlScript += (NEWLINE + table.getLogicDeleteSql(true, true) + NEWLINE);
            String normalSqlScript = SqlScriptUtils.convertIf(String.format("AND ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_NONEMPTYOFNORMAL), true);
            normalSqlScript += NEWLINE;
            normalSqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_EMPTYOFNORMAL), true);
            sqlScript += normalSqlScript;
            sqlScript = SqlScriptUtils.convertChoose(String.format("%s != null", WRAPPER), sqlScript,
                    table.getLogicDeleteSql(false, true));
            sqlScript = SqlScriptUtils.convertWhere(sqlScript);
            return newLine ? NEWLINE + sqlScript : sqlScript;
        } else {
            String sqlScript = table.getAllSqlWhere(false, true, WRAPPER_ENTITY_DOT);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY), true);
            sqlScript += NEWLINE;
            sqlScript += SqlScriptUtils.convertIf(String.format(SqlScriptUtils.convertIf(" AND", String.format("%s and %s", WRAPPER_NONEMPTYOFENTITY, WRAPPER_NONEMPTYOFNORMAL), false) + " ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_NONEMPTYOFWHERE), true);
            sqlScript = SqlScriptUtils.convertWhere(sqlScript) + NEWLINE;
            sqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_EMPTYOFWHERE), true);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER), true);
            return newLine ? NEWLINE + sqlScript : sqlScript;
        }
    }
}

重写injectMappedStatement方法，来将方法注入到MappedStatement中，需要注意的是addSelectMappedStatement方法第二个参数，

是方法名，及MyBaseMapper中自定义的mySelectList

然后最重要的是sqlWhereEntityWrapper方法，该方法就是定义sql语句的，可在此修改为自己想要的查询语句

让UserMapper继承我们创建的MyBaseMapper

package com.qjc.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @ClassName: MyBaseMapper
 * @Description:
 * @Author: qjc
 * @Date: 2021/9/6 12:59 下午
 */
public interface MyBaseMapper<T> extends BaseMapper {

    List<T> mySelectList(@Param("ew") Wrapper<T> queryWrapper);

}

最后一步将自定义的sql注入器注册到Spring容器中

@Bean
    public MySqlInjector mySqlInjector() {
        return new MySqlInjector();
    }

然后进行测试

 自动填充功能

我们创建的表中有创建用户，创建时间，删除标识字段，这些都是在创建用户时应该自动填充的，更新时间和更新用户字段是在更新用户信息的时候自动填充的

具体操作方法如下：在插入时需要自动填充的的字段上用注解@TableField(fill = FieldFill.INSERT)，在更新时需要自动填充的字段上用注解@TableField(fill = FieldFill.UPDATE)

有其他需要填充的字段可参考FieldFill

User实体如下

package com.qjc.entity;


import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
@TableName("mp_user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;

    @TableField(fill = FieldFill.INSERT)
    private String createUser;

    @TableField(fill = FieldFill.INSERT)
    private Date createDate;

    @TableField(fill = FieldFill.UPDATE)
    private String updateUser;

    @TableField(fill = FieldFill.UPDATE)
    private Date updateDate;

    @TableField(fill = FieldFill.INSERT)
    private Integer deleteFlag;

    private String username;

    private String password;

    private String birthday;

}

然后定义MyMetaObjectHandler实现MetaObjectHandler接口，重写insertFill和updateFill方法

package com.qjc.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * @ClassName: MyMetaObjectHandler
 * @Description:
 * @Author: qjc
 * @Date: 2021/9/6 4:01 下午
 */
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createUser", String.class, "qjc");
        this.strictInsertFill(metaObject, "createDate", Date.class, new Date());
        this.strictInsertFill(metaObject, "deleteFlag", Integer.class, 0);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateUser", String.class, "qjc");
        this.strictUpdateFill(metaObject, "updateDate", Date.class, new Date());
    }
}

测试代码

@Test
    public void insert() {
        User user = new User();
        user.setUsername("lucy");
        user.setPassword("55555");
        user.setBirthday("2020-01-01");
        userMapper.insert(user);
        System.err.println(user.getId());
    }

结果如下

 然后进行更新操作

    @Test
    public void update() {
        User user = new User();
        user.setId(1435062640798867458L);
        user.setUsername("lucy");
        user.setPassword("66666");
        user.setBirthday("2020-01-01");
        userMapper.updateById(user);
    }

结果如下

逻辑删除

我们在实际工作中，一般数据是不被删除的，要留存下来，所以会在每张表中添加删除标识来避免数据真的被删除

我们可以在删除标识deleteFlag字段上添加注解@TableLogic(value = "0", delval = "1")

然后测试删除，在删除之前我们先插入一条数据

    @Test
    public void insert() {
        User user = new User();
        user.setUsername("cat");
        user.setPassword("777");
        user.setBirthday("2021-01-01");
        userMapper.insert(user);
        System.err.println(user.getId());
    }

结果如下

然后使用BaseMapper提供的删除方法测试删除

    @Test
    public void delete() {
        userMapper.deleteById(1435064337008955393L);
    }

结果如下

我们发现在删除的时候更新人字段还是空的，这是因为逻辑删除并没有用到自动填充，所以我们自定义一个可以自动填充的删除方法

在MyBaseMapper中添加自动填充属性的删除方法

    int myDeleteByIdWithFill(T t);

然后定义一个MyDeleteByIdWithFill方法类

package com.qjc.sqlInjector;

import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName: MyDeleteById
 * @Description: 填充属性值的删除
 * @Author: qjc
 * @Date: 2021/9/6 4:53 下午
 */
public class MyDeleteByIdWithFill extends AbstractMethod {
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql;
        SqlMethod sqlMethod;
        if (tableInfo.isLogicDelete()) {
            // 进行逻辑删除
            List<TableFieldInfo> fieldInfos = getWithUpdateFillFields(tableInfo);
            // 删除时自动填充需要填充的属性值
            String sqlLogicSet = "SET " + fieldInfos.stream().map(i -> i.getSqlSet(null)).collect(Collectors.joining(EMPTY))
                    + tableInfo.getLogicDeleteSql(false, false);
            sql = String.format(
                    "<script>
UPDATE %s %s WHERE %s=#{%s}
</script>",
                    tableInfo.getTableName(),
                    sqlLogicSet,
                    tableInfo.getKeyColumn(),
                    tableInfo.getKeyProperty());
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, Object.class);
            return addUpdateMappedStatement(mapperClass, modelClass, "myDeleteByIdWithFill", sqlSource);
        } else {
            sqlMethod = SqlMethod.DELETE_BY_ID;
            sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), tableInfo.getKeyColumn(),
                    tableInfo.getKeyProperty());
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, Object.class);
            return this.addDeleteMappedStatement(mapperClass, sqlMethod.getMethod(), sqlSource);
        }
    }

    /**
     * 过滤出更新时进行填充信息的字段列表
     *
     * @param
     * @return
     * @author qjc
     * @date 2021/9/6 5:09 下午
     */
    private List<TableFieldInfo> getWithUpdateFillFields(TableInfo tableInfo) {
        return tableInfo.getFieldList().stream()
                .filter(TableFieldInfo::isWithUpdateFill)
                .collect(Collectors.toList())
                ;
    }
}

将该方法添加到MethodList中

public class MySqlInjector extends DefaultSqlInjector {


    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new MySelectList());
        methodList.add(new MyDeleteByIdWithFill());
        return methodList;
    }
}

然后再插入一条数据

    @Test
    public void insert() {
        User user = new User();
        user.setUsername("dog");
        user.setPassword("888");
        user.setBirthday("2021-02-01");
        userMapper.insert(user);
        System.err.println(user.getId());
    }

结果如下

测试自动填充属性的删除方法

    @Test
    public void myDeleteByIdWithFill() {
        User user = new User();
        user.setId(1435066199628091394L);
        userMapper.myDeleteByIdWithFill(user);
    }

结果如下

这时更新用户信息就填充进去了

demo地址：https://gitee.com/xiaorenwu_dashije/mybatis-plus-demo.git