如何扩展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-已删除',  `useame` 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`, `useame`, `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`, `useame`, `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;@TableIdprivate Long id;private String createUser;private Date createDate;private String updateUser;private Date updateDate;private Integer deleteFlag;private String useame;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 {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {List<AbstractMethod> methodList = super.getMethodList(mapperClass);methodList.add(new MySelectList());retu 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 {@Overridepublic 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);//retu 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);retu this.addSelectMappedStatementForTable(mapperClass, "mySelectList", sqlSource, tableInfo);}@Overridepublic 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);retu 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);retu 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容器中

@Beanpublic MySqlInjector mySqlInjector() {retu 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;@TableIdprivate 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 useame;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 下午 */@Componentpublic class MyMetaObjectHandler implements MetaObjectHandler {@Overridepublic 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);}@Overridepublic void updateFill(MetaObject metaObject) {this.strictUpdateFill(metaObject, "updateUser", String.class, "qjc");this.strictUpdateFill(metaObject, "updateDate", Date.class, new Date());}}

测试代码

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

结果如下

 然后进行更新操作

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

结果如下

逻辑删除

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

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

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

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

结果如下

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

@Testpublic 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 {@Overridepublic 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>\nUPDATE %s %s WHERE %s=#{%s}\n</script>",tableInfo.getTableName(),sqlLogicSet,tableInfo.getKeyColumn(),tableInfo.getKeyProperty());SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, Object.class);retu 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);retu this.addDeleteMappedStatement(mapperClass, sqlMethod.getMethod(), sqlSource);}}/** * 过滤出更新时进行填充信息的字段列表 * * @param * @retu * @author qjc * @date 2021/9/6 5:09 下午 */private List<TableFieldInfo> getWithUpdateFillFields(TableInfo tableInfo) {retu tableInfo.getFieldList().stream().filter(TableFieldInfo::isWithUpdateFill).collect(Collectors.toList());}}

将该方法添加到MethodList中

public class MySqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {List<AbstractMethod> methodList = super.getMethodList(mapperClass);methodList.add(new MySelectList());methodList.add(new MyDeleteByIdWithFill());retu methodList;}}

然后再插入一条数据

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

结果如下

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

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

结果如下

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

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