测试用例准备
实体类
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`user_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(4) NULL DEFAULT NULL COMMENT '年龄',
`gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
`birth_date` datetime(0) NULL DEFAULT NULL,
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
`password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
`confirm_password` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_effective` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`address` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '详细地址',
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '省',
`city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '市',
`district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '区',
`role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
`is_admin` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`duty` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
`delete_time` datetime(0) NULL DEFAULT NULL COMMENT '删除时间',
`deleted` int(1) NOT NULL DEFAULT 0 COMMENT '是否删除',
`row_version` int(8) NOT NULL DEFAULT 1 COMMENT '版本',
`create_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
`update_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '更新人',
`delete_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '删除人',
`position_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职位',
`org_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '组织',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `user_u1`(`user_code`) USING BTREE,
UNIQUE INDEX `user_u2`(`phone`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('100000', 'A00001000', 'admin', 100, 'male', '2023-11-29 11:56:55', '15180001530', '123456', NULL, 'Y', '远大四期', '四川省', '成都市', '双流区', NULL, '2180288483@qq.com', 'Y', NULL, '2023-06-19 11:57:46', '2023-06-19 11:57:46', NULL, 0, 1, '100000', '100000', NULL, NULL, NULL);
INSERT INTO `user` VALUES ('100021', 'A00001100', '张三', 25, 'male', '2002-06-19 11:56:55', '15126001510', '123456', NULL, 'Y', '远大三期', '四川省', '成都市', '双流区', NULL, '33172008112@qq.com', 'N', NULL, '2023-06-19 11:57:46', '2023-06-19 11:57:46', NULL, 0, 1, '100000', '100000', NULL, NULL, NULL);
INSERT INTO `user` VALUES ('100033', 'A00001006', '任娟敏', 21, 'female', '2000-07-19 20:07:30', '15130176502', '123456', NULL, 'Y', '腾龙首府八栋', '贵州省', '遵义市', '习水县', NULL, '12089312002@qq.com', NULL, NULL, '2023-12-05 20:09:11', '2023-12-05 20:09:11', NULL, 0, 1, '100000', '100000', NULL, NULL, NULL);
INSERT INTO `user` VALUES ('100034', 'A00001101', '李慧玲', 23, 'female', '2000-05-01 17:21:22', '18719001201', '123456', NULL, 'Y', '育才诸岛', '四川省', '成都市', '双流区', NULL, '17811201561@163.com', 'N', NULL, '2023-12-05 17:22:28', '2023-12-05 17:22:28', NULL, 0, 1, '100000', '100000', NULL, NULL, NULL);
SET FOREIGN_KEY_CHECKS = 1;
package com.djz.entity;
import java.io.*;
/**
* @author : dujiangzhou
* @date 2023/6/9 17:52
*/
public class BaseEntity implements Serializable {
private static final long serialVersionUID = 199811219527L;
private String id;
private String createUser;
private String updateUser;
private String deleteUser;
private String createTime;
private String updateTime;
private String deleteTime;
private Integer rowVersion;
private Integer deleted;
private String attr1;
private String attr2;
private String attr3;
private String attr4;
private String attr5;
private String attr6;
private Integer page = 1;
private Boolean pageFlag = true;
private Integer rows = 10000;
private Integer total;
private Integer startRow;
private Integer endRow;
private Integer totalPage;
private String positionId;
private String orgId;
/**
* 需要去重查询的字段
*/
private String distinctFields;
/**
* 重新加载缓存
*/
private String reloadCache = "N";
/**
* 指定的更新字段
*/
private String updateFields;
/**
* 安全性菜单
*/
private String oauth;
/**
* 该字段用来处理filterRaw条件的最后拼接运算符是AND还是OR
*/
private String filterOperator;
private String stayFields;
private String queryFields;
private Boolean totalFlag = false;
private Boolean onlyCountFlag = false;
/**
* 是否跳过标准sql重写拦截
*/
private Boolean skip = false;
private String conditionSql;
/**
* 是否开启版本控制
*/
private Boolean checkVersion = false;
/**
* 是否开启逻辑删除
*/
private Boolean openDeleteFlag = false;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getCreateUser() {
return createUser;
}
public void setCreateUser(String createUser) {
this.createUser = createUser;
}
public String getUpdateUser() {
return updateUser;
}
public void setUpdateUser(String updateUser) {
this.updateUser = updateUser;
}
public String getDeleteUser() {
return deleteUser;
}
public void setDeleteUser(String deleteUser) {
this.deleteUser = deleteUser;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getUpdateTime() {
return updateTime;
}
public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}
public String getDeleteTime() {
return deleteTime;
}
public void setDeleteTime(String deleteTime) {
this.deleteTime = deleteTime;
}
public Integer getRowVersion() {
return rowVersion;
}
public void setRowVersion(Integer rowVersion) {
this.rowVersion = rowVersion;
}
public Integer getDeleted() {
return deleted;
}
public void setDeleted(Integer deleted) {
this.deleted = deleted;
}
public String getAttr1() {
return attr1;
}
public void setAttr1(String attr1) {
this.attr1 = attr1;
}
public String getAttr2() {
return attr2;
}
public void setAttr2(String attr2) {
this.attr2 = attr2;
}
public String getAttr3() {
return attr3;
}
public void setAttr3(String attr3) {
this.attr3 = attr3;
}
public String getAttr4() {
return attr4;
}
public void setAttr4(String attr4) {
this.attr4 = attr4;
}
public String getAttr5() {
return attr5;
}
public void setAttr5(String attr5) {
this.attr5 = attr5;
}
public String getAttr6() {
return attr6;
}
public void setAttr6(String attr6) {
this.attr6 = attr6;
}
public Integer getPage() {
if (page == null || page < 1) {
page = 1;
}
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Boolean getPageFlag() {
return pageFlag;
}
public void setPageFlag(Boolean pageFlag) {
this.pageFlag = pageFlag;
}
public Integer getRows() {
if (rows == null || rows < 1) {
rows = 10000;
}
return rows;
}
public void setRows(Integer rows) {
this.rows = rows;
}
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}
public Integer getStartRow() {
return startRow;
}
public void setStartRow(Integer startRow) {
this.startRow = startRow;
}
public Integer getEndRow() {
return endRow;
}
public void setEndRow(Integer endRow) {
this.endRow = endRow;
}
public Integer getTotalPage() {
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
public String getPositionId() {
return positionId;
}
public void setPositionId(String positionId) {
this.positionId = positionId;
}
public String getOrgId() {
return orgId;
}
public void setOrgId(String orgId) {
this.orgId = orgId;
}
public String getDistinctFields() {
return distinctFields;
}
public void setDistinctFields(String distinctFields) {
this.distinctFields = distinctFields;
}
public String getReloadCache() {
return reloadCache;
}
public void setReloadCache(String reloadCache) {
this.reloadCache = reloadCache;
}
public String getUpdateFields() {
return updateFields;
}
public void setUpdateFields(String updateFields) {
this.updateFields = updateFields;
}
public String getOauth() {
return oauth;
}
public void setOauth(String oauth) {
this.oauth = oauth;
}
public String getFilterOperator() {
return filterOperator;
}
public void setFilterOperator(String filterOperator) {
this.filterOperator = filterOperator;
}
public String getStayFields() {
return stayFields;
}
public void setStayFields(String stayFields) {
this.stayFields = stayFields;
}
public String getQueryFields() {
return queryFields;
}
public void setQueryFields(String queryFields) {
this.queryFields = queryFields;
}
public Boolean getTotalFlag() {
return totalFlag;
}
public void setTotalFlag(Boolean totalFlag) {
this.totalFlag = totalFlag;
}
public Boolean getOnlyCountFlag() {
return onlyCountFlag;
}
public void setOnlyCountFlag(Boolean onlyCountFlag) {
this.onlyCountFlag = onlyCountFlag;
}
public static long getSerialVersionUID() {
return serialVersionUID;
}
public Boolean getSkip() {
if (skip == null) {
skip = false;
}
return skip;
}
public void setSkip(Boolean skip) {
this.skip = skip;
}
public String getConditionSql() {
return conditionSql;
}
public void setConditionSql(String conditionSql) {
this.conditionSql = conditionSql;
}
public Object deepClone() throws IOException, ClassNotFoundException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(this);
ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
ObjectInputStream ois = new ObjectInputStream(bis);
return (ois.readObject());
}
public Boolean getCheckVersion() {
if (checkVersion == null) {
checkVersion = false;
}
return checkVersion;
}
public void setCheckVersion(Boolean checkVersion) {
this.checkVersion = checkVersion;
}
public Boolean getOpenDeleteFlag() {
if (openDeleteFlag == null) {
openDeleteFlag = false;
}
return openDeleteFlag;
}
public void setOpenDeleteFlag(Boolean openDeleteFlag) {
this.openDeleteFlag = openDeleteFlag;
}
}
创建空父maven
添加maven依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<!-- <dependency>-->
<!-- <groupId>org.mybatis</groupId>-->
<!-- <artifactId>mybatis</artifactId>-->
<!-- <version>3.5.6</version>-->
<!-- </dependency>-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.9.7</version>
</dependency>
</dependencies>
创建子模块
创建自己的业务根包和启动类
创建业务包和yml启动文件
配置mybaitis重写拦截配置
配置类和拦截类
package com.djz.config;
import com.djz.intercept.MyBatisInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
/**
* 自定义拦截开启配置
*
* @author dujiangzhou
* @date 2023/12/5 19:38
*/
@Configuration
public class MyBatisConfig {
@Resource
private SqlSessionFactory sqlSessionFactory;
@PostConstruct
public void addMySqlInterceptor() {
MyBatisInterceptor interceptor = new MyBatisInterceptor();
sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
}
}
package com.djz.intercept;
import com.djz.constant.ConstantPool;
import com.djz.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.util.StringUtils;
import java.lang.reflect.Method;
import java.util.Properties;
/**
* mybatis拦截处理
*
* @author dujiangzhou
* @date 2023/12/5 19:35
*/
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
@Slf4j
public class MyBatisInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
System.out.println("=======拦截开始===========");
if (args == null) {
return null;
}
System.out.println("====数据大小:" + args.length);
//MappedStatement
MappedStatement statement = (MappedStatement) args[0];
// 执行sql的mapper
String id = statement.getId();
//解析执行sql的map方法,开始自定义规则匹配逻辑
int lastIndex = id.lastIndexOf(".");
//xml 的sql的方法名
String mapperClassMethod = id.substring((lastIndex + 1));
//执行方式
String option = statement.getSqlCommandType().name();
if (StringUtils.isEmpty(option)) {
return null;
}
System.out.println("处理id===" + mapperClassMethod);
if (StringUtils.isEmpty(mapperClassMethod)) {
return null;
}
//查询拦截
if (mapperClassMethod.matches(ConstantPool.PAGE) && ConstantPool.SELECT.equalsIgnoreCase(option)) {
System.out.println("查询处理处理中====");
MethodInterceptor.queryRebuildSql(invocation);
System.out.println("查询处理处理结束=====");
}
//新增拦截
if ((mapperClassMethod.matches(ConstantPool.INSERT_END) || mapperClassMethod.matches(ConstantPool.INSERT_UP))
&& ConstantPool.INSERT.equalsIgnoreCase(option)) {
System.out.println("新增处理中====");
MethodInterceptor.insertRebuildSql(invocation);
System.out.println("新增处理处理结束=====");
}
//更新拦截
if ((mapperClassMethod.matches(ConstantPool.UPDATE_END) || mapperClassMethod.matches(ConstantPool.UPDATE_UP))
&& ConstantPool.UPDATE.equalsIgnoreCase(option)) {
System.out.println("更新拦截处理中====");
MethodInterceptor.updateRebuildSql(invocation);
System.out.println("更新拦截结束====");
}
//删除拦截
if ((mapperClassMethod.matches(ConstantPool.DELETE_END) || mapperClassMethod.matches(ConstantPool.DELETE_UP))
&& ConstantPool.DELETE.equalsIgnoreCase(option)) {
System.out.println("删除处理中=====");
MethodInterceptor.deleteRebuildSql(invocation);
System.out.println("删除处理结束=====");
}
//根据id删除拦截
if (mapperClassMethod.matches(ConstantPool.DELETE_BY_ID)
&& ConstantPool.DELETE.equalsIgnoreCase(option)) {
System.out.println("根据Id删除处理中======");
MethodInterceptor.deleteByIdRebuildSql(invocation);
System.out.println("根据Id删除处理结束======");
}
//根据id查询拦截
if (mapperClassMethod.matches(ConstantPool.QUERY_BY_ID)
&& ConstantPool.SELECT.equalsIgnoreCase(option)) {
System.out.println("根据Id查询处理");
}
//根据count计数查询拦截
if (mapperClassMethod.matches(ConstantPool.METHOD_COUNT)
&& ConstantPool.SELECT.equalsIgnoreCase(option)) {
System.out.println("根据count计数查询处理");
MethodInterceptor.countRebuildSql(invocation);
}
System.out.println("=======拦截结束===========");
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
System.out.println("!!---------" + properties + "--------------------");
}
}
拦截重写
package com.djz.intercept;
import com.djz.constant.ConstantPool;
import com.djz.entity.BaseEntity;
import com.djz.utils.ExecutorPluginUtils;
import javafx.beans.binding.SetExpression;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Invocation;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.util.StringUtils;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
/**
* 方法拦截重写类
*
* @author dujiangzhou
* @date 2023/12/5 20:31
*/
public class MethodInterceptor {
private static final Logger logger = LogManager.getLogger(MethodInterceptor.class);
public static void updateRebuildSql(Invocation invocation) throws Exception {
Object[] args = invocation.getArgs();
logger.info("更新参数个数===" + args.length);
//校验更新参数是否正常
if (args.length < ConstantPool.Two) {
return;
}
BaseEntity entity = null;
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
String sql = boundSql.getSql();
logger.info("获取更新sql===" + sql + " ===");
if (sql == null) {
return;
}
//校验更新sql的更新参数是否存在
Object object = boundSql.getParameterObject();
if (object == null) {
logger.error("参数异常");
return;
}
// 校验当前对象是否继承基础类,是才能重新sql
if (object instanceof BaseEntity) {
entity = (BaseEntity) object;
} else {
return;
}
//不重写标识
if (entity.getSkip()) {
return;
}
Statement statement = CCJSqlParserUtil.parse(sql);
Update updateStatement = (Update) statement;
System.out.println("==旧的的sql==" + updateStatement.toString());
//指定更新字段有值
if (!StringUtils.isEmpty(entity.getUpdateFields())) {
String updateFields = entity.getUpdateFields();
List<String> columns = new ArrayList<>();
try {
columns = Arrays.asList(updateFields.split(","));
} catch (Exception e) {
logger.error("指定更新参数格式异常");
return;
}
if (columns.size() < 1) {
return;
}
} else {
Table table = updateStatement.getTable();
if (table != null) {
updateStatement.addUpdateSet(new Column("update_user"),
CCJSqlParserUtil.parseExpression("100001"));
updateStatement.addUpdateSet(new Column("update_time"),
new LongValue("now()"));
} else {
return;
}
//是否开启版本更新校验
if (entity.getCheckVersion()) {
if (null == entity.getRowVersion()) {
logger.error("版本参数为空");
return;
}
//版本加一
updateStatement.addUpdateSet(new Column("row_version"),
new LongValue((entity.getRowVersion() + 1)));
//原sql的where条件
Expression whereExpression = updateStatement.getWhere();
// 新的条件 版本号条件
Expression newCondition = new EqualsTo(new Column("row_version"),
new LongValue(entity.getRowVersion()));
if (whereExpression == null) {
updateStatement.setWhere(newCondition);
} else {
updateStatement.setWhere(new AndExpression(whereExpression, newCondition));
}
}
}
System.out.println("===重写的sql==" + updateStatement.toString());
ExecutorPluginUtils.rebuildSqlInvocation(invocation, updateStatement.toString());
}
/**
* 插入标准方法拦截重写
*
* @param invocation
*/
public static void insertRebuildSql(Invocation invocation) throws JSQLParserException, SQLException {
Object[] args = invocation.getArgs();
logger.info("新增参数个数===" + args.length);
//校验新增参数是否正常
if (args.length < ConstantPool.Two) {
return;
}
BaseEntity entity = null;
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
String sql = boundSql.getSql();
logger.info("获取新增sql===" + sql + " ===");
if (sql == null) {
return;
}
//校验新增sql的参数是否存在
Object object = boundSql.getParameterObject();
if (object == null) {
logger.error("参数异常");
return;
}
// 校验当前对象是否继承基础类,是才能重写sql
if (object instanceof BaseEntity) {
entity = (BaseEntity) object;
} else {
return;
}
Statement statement = CCJSqlParserUtil.parse(sql);
Insert insertStatement = (Insert) statement;
logger.info("===旧的的sql" + insertStatement.toString());
Table table = insertStatement.getTable();
if (table != null) {
//新增字段
insertStatement.addColumns(new Column("create_user"));
insertStatement.addColumns(new Column("create_time"));
insertStatement.addColumns(new Column("update_user"));
insertStatement.addColumns(new Column("update_time"));
ExpressionList itemsList = (ExpressionList) insertStatement.getItemsList();
itemsList.getExpressions().add(CCJSqlParserUtil.parseExpression("100001"));
itemsList.getExpressions().add(new LongValue("now()"));
itemsList.getExpressions().add(CCJSqlParserUtil.parseExpression("100001"));
itemsList.getExpressions().add(new LongValue("now()"));
} else {
return;
}
logger.info("===重写的sql==" + insertStatement.toString());
ExecutorPluginUtils.rebuildSqlInvocation(invocation, insertStatement.toString());
}
public static void deleteRebuildSql(Invocation invocation) throws JSQLParserException, SQLException {
Object[] args = invocation.getArgs();
logger.info("删除参数个数===" + args.length);
//校验删除参数是否正常
if (args.length < ConstantPool.Two) {
return;
}
BaseEntity entity = null;
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
String sql = boundSql.getSql();
logger.info("获取删除sql===" + sql + " ===");
if (sql == null) {
return;
}
//校验删除sql的参数是否存在
Object object = boundSql.getParameterObject();
if (object == null) {
logger.error("参数异常");
return;
}
// 校验当前对象是否继承基础类,是才能重写sql
if (object instanceof BaseEntity) {
entity = (BaseEntity) object;
} else {
return;
}
Statement statement = CCJSqlParserUtil.parse(sql);
Delete deleteStatement = (Delete) statement;
logger.info("===旧的的sql==" + deleteStatement);
Table table = deleteStatement.getTable();
Update update = new Update();
if (table != null) {
if (entity.getOpenDeleteFlag()) {
update.setTable(table);
update.addUpdateSet(new Column(ConstantPool.DELETED), new LongValue(1));
Expression whereExpression = deleteStatement.getWhere();
update.setWhere(whereExpression);
}
} else {
return;
}
logger.info("===重写的sql==" + deleteStatement.toString());
if (entity.getOpenDeleteFlag()) {
ExecutorPluginUtils.rebuildSqlInvocation(invocation, update.toString());
} else {
ExecutorPluginUtils.rebuildSqlInvocation(invocation, deleteStatement.toString());
}
}
public static void queryRebuildSql(Invocation invocation) {
Object[] args = invocation.getArgs();
logger.info("查询参数个数===" + args.length);
//校验查询参数是否正常
if (args.length < ConstantPool.Three) {
return;
}
BaseEntity entity = null;
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
String sql = boundSql.getSql();
logger.info("获取查询sql===" + sql + " ===");
if (sql == null) {
return;
}
//校验查询sql的参数是否存在
Object object = boundSql.getParameterObject();
if (object == null) {
logger.error("参数异常");
return;
}
// 校验当前对象是否继承基础类,是才能重写sql
if (object instanceof BaseEntity) {
entity = (BaseEntity) object;
} else {
return;
}
//最后一个where 位置 (保证不是子查询里的where暂不支持复杂union格式sql解析)
//where空格,保证是关键字,不是带where的命名字段
int whereIndex = sql.toLowerCase().lastIndexOf(" where ");
if (sql.contains("limit") && whereIndex > 0) {
int index = sql.lastIndexOf("limit");
if (index > whereIndex) {
sql = sql.substring(0, index);
System.out.println("=====" + sql);
sql = sql.concat(" ").concat("limit ").
concat(((entity.getPage() - 1) * entity.getRows()) + "").concat(",").
concat(entity.getRows() + "");
} else {
sql = sql.concat(" ").concat("limit ").
concat(((entity.getPage() - 1) * entity.getRows()) + "").concat(",").
concat(entity.getRows() + "");
}
} else {
sql = sql.concat(" ").concat("limit ").
concat(((entity.getPage() - 1) * entity.getRows()) + "").concat(",").
concat(entity.getRows() + "");
}
System.out.println("拼接的查询====" + sql);
ExecutorPluginUtils.rebuildSqlInvocation(invocation, sql);
}
public static void countRebuildSql(Invocation invocation) {
Object[] args = invocation.getArgs();
System.out.println("计数参数个数===" + args.length);
String sql = ExecutorPluginUtils.getSqlByInvocation(invocation);
System.out.println("获取计数sql==" + sql + "===");
}
public static void deleteByIdRebuildSql(Invocation invocation) throws JSQLParserException, SQLException {
Object[] args = invocation.getArgs();
logger.info("删除参数个数===" + args.length);
//校验删除参数是否正常
if (args.length < ConstantPool.Two) {
return;
}
BaseEntity entity = null;
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
String sql = boundSql.getSql();
logger.info("获取删除sql===" + sql + " ===");
if (sql == null) {
return;
}
//校验删除sql的参数是否存在
Object object = boundSql.getParameterObject();
if (object == null) {
logger.error("参数异常");
return;
}
// 校验当前对象是否继承基础类,是才能重新sql
if (object instanceof BaseEntity) {
entity = (BaseEntity) object;
} else {
return;
}
Statement statement = CCJSqlParserUtil.parse(sql);
Delete deleteStatement = (Delete) statement;
logger.info("===旧的的sql==" + deleteStatement);
Table table = deleteStatement.getTable();
Update update = new Update();
//逻辑删除 根据id删除,id一定不能为空
if (table != null && !StringUtils.isEmpty(entity.getId())) {
if (entity.getOpenDeleteFlag()) {
update.setTable(table);
update.addUpdateSet(new Column(ConstantPool.DELETED), new LongValue(1));
update.addUpdateSet(new Column(ConstantPool.DELETE_TIME), new LongValue("now()"));
update.addUpdateSet(new Column(ConstantPool.DELETE_USER), CCJSqlParserUtil.parseExpression("100000"));
Expression whereExpression = deleteStatement.getWhere();
update.setWhere(whereExpression);
}
} else {
return;
}
//是否逻辑删除
if (entity.getOpenDeleteFlag()) {
logger.info("===重写的sql==" + update.toString());
ExecutorPluginUtils.rebuildSqlInvocation(invocation, update.toString());
} else {
logger.info("===重写的sql==" + deleteStatement.toString());
ExecutorPluginUtils.rebuildSqlInvocation(invocation, deleteStatement.toString());
}
}
}
添加测试类
user的contrller
package com.djz.controller;
import com.djz.entity.User;
import com.djz.service.UserService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author dujiangzhou
* @date 2023/12/5 15:27
*/
@Controller
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;
/**
* 用户查询
*/
@RequestMapping("/queryByDataPage")
@ResponseBody
public Map<String, Object> queryByDataPage(@RequestBody User entity) {
Map<String, Object> result = new HashMap(4);
try {
System.out.println("===" + entity.toString());
List<User> list = userService.queryByDataPage(entity);
result.put("success", true);
result.put("rows", list);
result.put("total", list.size());
} catch (Exception e) {
result.put("success", false);
result.put("result", e.getMessage());
}
return result;
}
/**
* 用户插入
*/
@RequestMapping("/insert")
@ResponseBody
public Map<String, Object> insert(@RequestBody User entity) {
Map<String, Object> result = new HashMap(4);
try {
System.out.println("===" + entity.toString());
Integer data = userService.insert(entity);
result.put("success", true);
result.put("result", data);
} catch (Exception e) {
result.put("success", false);
result.put("result", e.getMessage());
}
return result;
}
/**
* 用户更新
*/
@RequestMapping("/update")
@ResponseBody
public Map<String, Object> update(@RequestBody User entity) {
Map<String, Object> result = new HashMap(4);
try {
System.out.println("===" + entity.toString());
Integer data = userService.update(entity);
result.put("success", true);
result.put("result", data);
} catch (Exception e) {
result.put("success", false);
result.put("result", e.getMessage());
}
return result;
}
/**
* 用户删除
*/
@RequestMapping("/deleteById")
@ResponseBody
public Map<String, Object> deleteById(@RequestBody User entity) {
Map<String, Object> result = new HashMap(4);
try {
System.out.println("===" + entity.toString());
Integer data = userService.deleteById(entity);
result.put("success", true);
result.put("result", data);
} catch (Exception e) {
result.put("success", false);
result.put("result", e.getMessage());
}
return result;
}
/**
* 用户查询
*/
@RequestMapping("/queryById")
@ResponseBody
public Map<String, Object> queryById(@RequestBody User entity) {
Map<String, Object> result = new HashMap(4);
try {
System.out.println("===" + entity.toString());
User one = userService.queryById(entity);
result.put("success", true);
result.put("rows", one);
} catch (Exception e) {
result.put("success", false);
result.put("result", e.getMessage());
}
return result;
}
}
user的service
package com.djz.service;
import com.djz.entity.User;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* @author dujiangzhou
* @date 2023/12/5 15:27
*/
public interface UserService {
/**
* 查询分页
*
* @param user
* @return
*/
List<User> queryByDataPage(User user);
/**
* 插入
*
* @param user
* @return
*/
Integer insert(User user);
/**
* 更新
*
* @param user
* @return
*/
Integer update(User user);
/**
* 删除
*
* @param user
* @return
*/
Integer deleteById(User user);
/**
* 删除
*
* @param user
* @return
*/
User queryById(User user);
}
package com.djz.service;
import com.djz.entity.User;
import com.djz.mapper.UserMapper;
import com.djz.utils.IdUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
import java.util.List;
/**
* @author dujiangzhou
* @date 2023/12/5 15:28
*/
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
@Override
public List<User> queryByDataPage(User user) {
return userMapper.queryByDataPage(user);
}
@Override
public Integer insert(User user) {
if (StringUtils.isEmpty(user.getId())) {
user.setId(IdUtils.keyGenerate());
}
return userMapper.insert(user);
}
@Override
public Integer update(User user) {
if (StringUtils.isEmpty(user.getId())) {
return 0;
}
return userMapper.update(user);
}
@Override
public Integer deleteById(User user) {
if (StringUtils.isEmpty(user.getId())) {
return 0;
}
return userMapper.deleteById(user);
}
@Override
public User queryById(User user) {
if (StringUtils.isEmpty(user.getId())) {
return null;
}
return userMapper.queryById(user);
}
}
用户mapper
package com.djz.mapper;
import com.djz.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
*在对应mapper类加@Mapper实现绑定 或者在启动类上加 @MapperScan("com.djz.mapper")
* @author dujiangzhou
* @date 2023/12/5 15:27
*/
@Mapper
public interface UserMapper {
/**
* 查询分页
*
* @param user
* @return
*/
List<User> queryByDataPage(User user);
/**
* 插入
*
* @param user
* @return
*/
Integer insert(User user);
/**
* 更新
*
* @param user
* @return
*/
Integer update(User user);
/**
* 删除
*
* @param user
* @return
*/
Integer deleteById(User user);
/**
* 删除
*
* @param user
* @return
*/
User queryById(User user);
}
user的xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.djz.mapper.UserMapper">
<resultMap type="com.djz.entity.User" id="userMap"/>
<sql id="querySql">
select t1.id as id
, t1.user_code as userCode
, t1.name as name
, t1.age as age
, t1.gender as gender
, t1.birth_date as birthDate
, t1.phone as phone
, t1.address as address
, t1.province as province
, t1.city as city
, t1.district as district
, t1.mail as mail
</sql>
<sql id="fromTable">
from `user` t1
where 1 = 1
</sql>
<sql id="whereCondition">
<if test="openDeleteFlag == true ">
and t1.deleted = 0
</if>
<if test="age !=null ">
and t1.age = #{age}
</if>
<if test="phone !=null and phone !='' ">
and t1.phone = #{phone}
</if>
</sql>
<select id="queryByDataPage" resultType="com.djz.entity.User" resultMap="userMap">
<include refid="querySql"/>
<include refid="fromTable"/>
<include refid="whereCondition"/>
</select>
<insert id="insert" parameterType="com.djz.entity.User">
insert into `user`(id,user_code, `name`, age, gender, birth_date, phone, password, confirm_password, is_effective,
address, province, city, district, role, mail, is_admin, duty)
values (#{id},#{userCode}, #{name}, #{age}, #{gender}, #{birthDate}, #{phone}, #{password}, #{password},
#{isEffective}, #{address}, #{province}, #{city}, #{district}, #{role}, #{mail}, #{isAdmin}, #{duty})
</insert>
<update id="update" parameterType="com.djz.entity.User">
update `user`
set age=#{age},
phone=#{phone},
password =#{password},
province =#{province}
where id = #{id}
</update>
<delete id="deleteById" parameterType="com.djz.entity.User">
delete
from `user`
where id = #{id}
</delete>
<select id="queryById" parameterType="com.djz.entity.User" resultMap="userMap">
<include refid="querySql"/>
<include refid="fromTable"/>
and t1.id = #{id}
</select>
</mapper>
常量类
package com.djz.constant;
import org.apache.ibatis.annotations.One;
/**
* @author dujiangzhou
* @date 2023/12/5 20:34
*/
public class ConstantPool {
public static final String UPDATE = "update";
public static final String QUERY = "query";
public static final String INSERT = "insert";
public static final String DELETE = "delete";
public static final String SELECT = "select";
/**
* 统计计数结尾
*/
public static final String METHOD_COUNT = ".*Count$";
/**
* delete结尾
*/
public static final String DELETE_END = ".*delete$";
/**
* Delete结尾
*/
public static final String DELETE_UP = ".*Delete$";
/**
* deleteById结尾
*/
public static final String DELETE_BY_ID = ".*deleteById$";
/**
* queryById方法
*/
public static final String QUERY_BY_ID = ".*queryById$";
/**
* Page结尾
*/
public static final String PAGE = ".*Page$";
/**
* Update结尾
*/
public static final String UPDATE_UP = ".*Update$";
/**
* update结尾
*/
public static final String UPDATE_END = ".*update$";
/**
* insert结尾
*/
public static final String INSERT_END = ".*insert$";
/**
* Insert结尾
*/
public static final String INSERT_UP = ".*Insert$";
/**
* 常量2
*/
public static final Integer Two = 2;
/**
* 常量最大分页10000
*/
public static final Integer ROWS = 10000;
/**
* 常量1
*/
public static final Integer One = 1;
/**
* 常量Y
*/
public static final String Y = "Y";
/**
* 常量N
*/
public static final String N = "N";
/**
* 常量0
*/
public static final Integer ZERO = 0;
/**
* 常量3
*/
public static final Integer Three = 3;
/**
* 基础字段常量
*/
public static final String ROW_VERSION = "row_version";
/**
* 基础字段常量
*/
public static final String ID = "id";
/**
* 基础字段常量
*/
public static final String UPDATE_USER = "update_user";
/**
* 基础字段常量
*/
public static final String UPDATE_TIME = "update_time";
/**
* 基础字段常量
*/
public static final String CREATE_USER = "create_user";
/**
* 基础字段常量
*/
public static final String CREATE_TIME = "create_time";
/**
* 基础字段常量
*/
public static final String DELETE_TIME = "delete_time";
/**
* 基础字段常量
*/
public static final String DELETE_USER = "delete_user";
/**
* 基础字段常量
*/
public static final String POSITION_ID = "position_id";
/**
* 基础字段常量
*/
public static final String ORG_ID = "org_id";
/**
* 基础字段常量
*/
public static final String DELETED = "deleted";
}
工具类
package com.djz.utils;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import java.sql.SQLException;
/**
* @author dujiangzhou
* @date 2023/12/5 20:53
*/
public class ExecutorPluginUtils {
/**
* 获取sql语句
*/
public static String getSqlByInvocation(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
System.out.println("获取参数==:" + parameterObject);
BoundSql boundSql = ms.getBoundSql(parameterObject);
return boundSql.getSql();
}
/**
* 包装sql后,重置到invocation中
*/
public static void rebuildSqlInvocation(Invocation invocation, String sql) throws SQLException {
final Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
msObject.setValue("sqlSource.boundSql.sql", sql);
args[0] = newStatement;
}
private static MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder =
new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
/**
* 定义一个内部辅助类,作用是包装sql
*/
static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
测试启动与接口调用
结尾:基本框架已经搭建,只需要在自己对应的判断层重写自己的sql就行,标准接口重写添加标准字段,分页实现等后续更新
试例文件下载链接
链接:https://gitee.com/dujiangzhou/mybatis-intercept.git