根据 MybatisPlus 的 AutoSqlInjector 可以自定义各种你想要的 sql ,注入到全局中,相当于自定义 Mybatisplus 自动注入的方法。 之前需要在 xml 中进行配置的 SQL 语句,现在通过扩展 AutoSqlInjector 在加载 mybatis 环境时就注入。
1、AutoSqlInjector
(1)、在 Mapper 接口中定义相关的 CRUD 方法。
public interface EmployeeMapper extends BaseMapper<Employee> {
int deleteAll();
}
(2)、扩展 AutoSqlInjector inject 方法,实现 Mapper 接口中方法要注入的 SQL。
/**
* 自定义全局操作
*/
public class MySqlInjector extends AutoSqlInjector{
/**
* 扩展inject 方法,完成自定义全局操作
*/
@Override
public void inject(Configuration configuration, MapperBuilderAssistant builderAssistant, Class<?> mapperClass,
Class<?> modelClass, TableInfo table) {
//将EmployeeMapper中定义的deleteAll,处理成对应的MappedStatement对象,加入到configuration对象中。
//注入的SQL语句
String sql = "delete from " +table.getTableName();
//注入的方法名一定要与EmployeeMapper接口中的方法名一致
String method = "deleteAll" ;
//构造SqlSource对象
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
//构造一个删除的MappedStatement
this.addDeleteMappedStatement(mapperClass, method, sqlSource);
}
}
(3)、在 MybatisPlus 全局策略中,配置自定义注入器。
<bean id ="globalConfiguration" class="com.baomidou.mybatisplus.entity.GlobalConfiguration">
<!-- 注入自定义全局操作 -->
<property name="sqlInjector" ref="mySqlInjector"></property>
</bean>
<!-- 定义自定义注入器 -->
<bean id="mySqlInjector" class="com.atguigu.mp.injector.MySqlInjector"></bean>
测试:
@Test
public void testMySqlInjector() {
Integer result = employeeMapper.deleteAll();
System.out.println("result: " +result );
}
控制台输出:
Preparing: delete from tbl_employee
Parameters:
Updates: 4
result: 4
Time:52 ms - ID:com.atguigu.mp.mapper.EmployeeMapper.deleteAll
Execute SQL:
delete
from
tbl_employee]
2、自定义注入器的应用之逻辑删除
假删除、逻辑删除: 并不会真正的从数据库中将数据删除掉,而是将当前被删除的这条数据中的一个逻辑删除字段置为删除状态。 logic_flag → -1
创建表和数据:
CREATE TABLE tbl_user(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
logic_flag int(11)
);
INSERT INTO tbl_user(name,logic_flag) VALUES('Tom',1);
INSERT INTO tbl_user(name,logic_flag) VALUES('wangwu',1);
INSERT INTO tbl_user(name,logic_flag) VALUES('lizq',1);
INSERT INTO tbl_user(name,logic_flag) VALUES('zhangsan',1);
INSERT INTO tbl_user(name,logic_flag) VALUES('lisi',1);
(1)、com.baomidou.mybatisplus.mapper.LogicSqlInjector。
(2)、logicDeleteValue 逻辑删除全局值。
(3)、logicNotDeleteValue 逻辑未删除全局值。
<bean id ="globalConfiguration" class="com.baomidou.mybatisplus.entity.GlobalConfiguration">
<!-- 注入逻辑删除 -->
<property name="sqlInjector" ref="logicSqlInjector"></property>
<!-- 注入逻辑删除全局值 -->
<property name="logicDeleteValue" value = "-1"></property>
<property name="logicNotDeleteValue" value="1"></property>
</bean>
<!-- 逻辑删除 -->
<bean id="logicSqlInjector" class="com.baomidou.mybatisplus.mapper.LogicSqlInjector"></bean>
(4)、在 POJO 的逻辑删除字段添加 @TableLogic 注解。
public class User {
private Integer id ;
private String name ;
@TableLogic // 逻辑删除属性
private Integer logicFlag ;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getLogicFlag() {
return logicFlag;
}
public void setLogicFlag(Integer logicFlag) {
this.logicFlag = logicFlag;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", logicFlag=" + logicFlag + "]";
}
}
(5)、会在 MybatisPlus 自带查询和更新方法的 sql 后面,追加『逻辑删除字段』 = 『LogicNotDeleteValue 默认值』 删除方法: deleteById()和其他 delete 方法, 底层 SQL 调用的是 update tbl_xxx set 『逻辑删除字段』=『logicDeleteValue 默认值』
@Test
public void testLogicDelete() {
Integer result = userMapper.deleteById(1);
System.out.println("result:" +result );
User user = userMapper.selectById(1);
System.out.println("result:" +user);
}
控制台输出:
deleteById方法:
Preparing: UPDATE tbl_user SET logic_flag=-1 WHERE id=?
Parameters: 1(Integer)
Updates: 1
result:1
Time:34 ms - ID:com.atguigu.mp.mapper.UserMapper.deleteById
Execute SQL:
UPDATE
tbl_user
SET
logic_flag=-1
WHERE
id=1]
selectById方法:
Preparing: SELECT id,`name`,logic_flag AS logicFlag FROM tbl_user WHERE id=? AND logic_flag=1
Parameters: 1(Integer)
Total: 0
result:null
Time:14 ms - ID:com.atguigu.mp.mapper.UserMapper.selectById
Execute SQL:
SELECT
id,
`name`,
logic_flag AS logicFlag
FROM
tbl_user
WHERE
id=1
AND logic_flag=1]
原理分析:
public class LogicSqlInjector extends AutoSqlInjector {
public LogicSqlInjector() {
}
protected void injectDeleteByIdSql(boolean batch, Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
if (table.isLogicDelete()) {
SqlMethod sqlMethod = SqlMethod.LOGIC_DELETE_BY_ID;
String idStr = table.getKeyProperty();
if (batch) {
sqlMethod = SqlMethod.LOGIC_DELETE_BATCH_BY_IDS;
StringBuilder ids = new StringBuilder();
ids.append("\n<foreach item=\"item\" index=\"index\" collection=\"coll\" separator=\",\">");
ids.append("#{item}");
ids.append("\n</foreach>");
idStr = ids.toString();
}
String sql = String.format(sqlMethod.getSql(), table.getTableName(), this.sqlLogicSet(table), table.getKeyColumn(), idStr);
SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
} else {
super.injectDeleteByIdSql(batch, mapperClass, modelClass, table);
}
}
protected void injectDeleteSql(Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
if (table.isLogicDelete()) {
SqlMethod sqlMethod = SqlMethod.LOGIC_DELETE;
String sql = String.format(sqlMethod.getSql(), table.getTableName(), this.sqlLogicSet(table), this.sqlWhereEntityWrapper(table));
SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
} else {
super.injectDeleteSql(mapperClass, modelClass, table);
}
}
protected void injectDeleteByMapSql(Class<?> mapperClass, TableInfo table) {
if (table.isLogicDelete()) {
SqlMethod sqlMethod = SqlMethod.LOGIC_DELETE_BY_MAP;
String sql = String.format(sqlMethod.getSql(), table.getTableName(), this.sqlLogicSet(table), this.sqlWhereByMap(table));
SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, Map.class);
this.addUpdateMappedStatement(mapperClass, Map.class, sqlMethod.getMethod(), sqlSource);
} else {
super.injectDeleteByMapSql(mapperClass, table);
}
}
protected void injectSelectByIdSql(boolean batch, Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
if (table.isLogicDelete()) {
SqlMethod sqlMethod = SqlMethod.LOGIC_SELECT_BY_ID;
Object sqlSource;
if (batch) {
sqlMethod = SqlMethod.LOGIC_SELECT_BATCH_BY_IDS;
StringBuilder ids = new StringBuilder();
ids.append("\n<foreach item=\"item\" index=\"index\" collection=\"coll\" separator=\",\">");
ids.append("#{item}");
ids.append("\n</foreach>");
sqlSource = this.languageDriver.createSqlSource(this.configuration, String.format(sqlMethod.getSql(), this.sqlSelectColumns(table, false), table.getTableName(), table.getKeyColumn(), ids.toString(), this.getLogicDeleteSql(table)), modelClass);
} else {
sqlSource = new RawSqlSource(this.configuration, String.format(sqlMethod.getSql(), this.sqlSelectColumns(table, false), table.getTableName(), table.getKeyColumn(), table.getKeyProperty(), this.getLogicDeleteSql(table)), Object.class);
}
this.addSelectMappedStatement(mapperClass, sqlMethod.getMethod(), (SqlSource)sqlSource, modelClass, table);
} else {
super.injectSelectByIdSql(batch, mapperClass, modelClass, table);
}
}
protected void injectUpdateByIdSql(boolean selective, Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
if (table.isLogicDelete()) {
SqlMethod sqlMethod = selective ? SqlMethod.LOGIC_UPDATE_BY_ID : SqlMethod.LOGIC_UPDATE_ALL_COLUMN_BY_ID;
String sql = String.format(sqlMethod.getSql(), table.getTableName(), this.sqlSet(selective, table, "et."), table.getKeyColumn(), "et." + table.getKeyProperty(), "<if test=\"et instanceof java.util.Map\"><if test=\"et.MP_OPTLOCK_VERSION_ORIGINAL!=null\">and ${et.MP_OPTLOCK_VERSION_COLUMN}=#{et.MP_OPTLOCK_VERSION_ORIGINAL}</if></if>" + this.getLogicDeleteSql(table));
SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
} else {
super.injectUpdateByIdSql(selective, mapperClass, modelClass, table);
}
}
public String getLogicDeleteSql(TableInfo table) {
StringBuilder sql = new StringBuilder();
List<TableFieldInfo> fieldList = table.getFieldList();
Iterator i$ = fieldList.iterator();
while(i$.hasNext()) {
TableFieldInfo fieldInfo = (TableFieldInfo)i$.next();
if (fieldInfo.isLogicDelete()) {
sql.append(" AND ").append(fieldInfo.getColumn());
if (StringUtils.isCharSequence(fieldInfo.getPropertyType())) {
sql.append("='").append(fieldInfo.getLogicNotDeleteValue()).append("'");
} else {
sql.append("=").append(fieldInfo.getLogicNotDeleteValue());
}
}
}
return sql.toString();
}
protected String sqlLogicSet(TableInfo table) {
List<TableFieldInfo> fieldList = table.getFieldList();
StringBuilder set = new StringBuilder("SET ");
int i = 0;
Iterator i$ = fieldList.iterator();
while(i$.hasNext()) {
TableFieldInfo fieldInfo = (TableFieldInfo)i$.next();
if (fieldInfo.isLogicDelete()) {
++i;
if (i > 1) {
set.append(",");
}
set.append(fieldInfo.getColumn()).append("=");
if (StringUtils.isCharSequence(fieldInfo.getPropertyType())) {
set.append("'").append(fieldInfo.getLogicDeleteValue()).append("'");
} else {
set.append(fieldInfo.getLogicDeleteValue());
}
}
}
return set.toString();
}
protected String sqlWhere(TableInfo table) {
if (!table.isLogicDelete()) {
return super.sqlWhere(table);
} else {
StringBuilder where = new StringBuilder("\n<where>");
List<TableFieldInfo> fieldList = table.getFieldList();
if (StringUtils.isNotEmpty(table.getKeyProperty())) {
where.append("\n<if test=\"ew.").append(table.getKeyProperty()).append("!=null\">");
where.append(" AND ").append(table.getKeyColumn()).append("=#{ew.");
where.append(table.getKeyProperty()).append("}");
where.append("</if>");
}
Iterator i$ = fieldList.iterator();
while(i$.hasNext()) {
TableFieldInfo fieldInfo = (TableFieldInfo)i$.next();
where.append(this.convertIfTag(fieldInfo, "ew.", false));
where.append(" AND ").append(this.sqlCondition(fieldInfo.getCondition(), fieldInfo.getColumn(), "ew." + fieldInfo.getEl()));
where.append(this.convertIfTag(fieldInfo, true));
}
where.append("\n").append(this.getLogicDeleteSql(table));
where.append("\n</where>");
return where.toString();
}
}
protected String sqlWhereEntityWrapper(TableInfo table) {
if (!table.isLogicDelete()) {
return super.sqlWhereEntityWrapper(table);
} else {
StringBuilder where = new StringBuilder(128);
where.append("\n<where>");
where.append("\n<choose><when test=\"ew!=null\">");
where.append("\n<if test=\"ew.entity!=null\">");
if (StringUtils.isNotEmpty(table.getKeyProperty())) {
where.append("\n<if test=\"ew.entity.").append(table.getKeyProperty()).append("!=null\">");
where.append(" AND ").append(table.getKeyColumn()).append("=#{ew.entity.");
where.append(table.getKeyProperty()).append("}");
where.append("</if>");
}
List<TableFieldInfo> fieldList = table.getFieldList();
Iterator i$ = fieldList.iterator();
while(i$.hasNext()) {
TableFieldInfo fieldInfo = (TableFieldInfo)i$.next();
where.append(this.convertIfTag(fieldInfo, "ew.entity.", false));
where.append(" AND ").append(this.sqlCondition(fieldInfo.getCondition(), fieldInfo.getColumn(), "ew.entity." + fieldInfo.getEl()));
where.append(this.convertIfTag(fieldInfo, true));
}
where.append("\n</if>");
where.append("\n").append(this.getLogicDeleteSql(table));
where.append("\n<if test=\"ew.sqlSegment!=null\">${ew.sqlSegment}\n</if>");
where.append("\n</when><otherwise>");
where.append("\n").append(this.getLogicDeleteSql(table));
where.append("\n</otherwise></choose>");
where.append("\n</where>");
return where.toString();
}
}
protected String sqlWhereByMap(TableInfo table) {
if (table.isLogicDelete()) {
StringBuilder where = new StringBuilder();
where.append("\n<where>");
where.append("\n<if test=\"cm!=null and !cm.isEmpty\">");
where.append("\n<foreach collection=\"cm.keys\" item=\"k\" separator=\"AND\">");
where.append("\n<if test=\"cm[k] != null\">");
where.append(SqlReservedWords.convert(this.getGlobalConfig(), "\n${k}")).append(" = #{cm[${k}]}");
where.append("</if>");
where.append("\n</foreach>");
where.append("\n</if>");
where.append("\n").append(this.getLogicDeleteSql(table));
where.append("\n</where>");
return where.toString();
} else {
return super.sqlWhereByMap(table);
}
}
}