自定义注解实现拦截sql,并在sql中增加相应的条件

功能介绍

先说下这期实现的这个功能。 其实看起来很简单的一个功能。
原sql：select * from users WHERE username = ?
增加自定义注解后：

变成这样: SELECT * FROM users WHERE username = ? AND enabled = 1
看起来是不是一个很简单的功能，但我们要动态的实现这个功能。并且能要使用自定义注解的方式实现。 废话不多说，开干。

引入maven

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.4</version>
        </dependency>

对应数据库表和数据

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 80017
 Source Host           : 127.0.0.1:3306
 Source Schema         : nacos

 Target Server Type    : MySQL
 Target Server Version : 80017
 File Encoding         : 65001

 Date: 08/10/2021 22:00:11
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('nacos', '$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu', 1);

SET FOREIGN_KEY_CHECKS = 1;

配置文件如下

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/nacos?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=123456


server.port=8888

mybatis-plus.mapper-locations=classpath*:com/example/mybatis_plus_demo/**/impl/*.xml
# 打印sql到控制台
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#tomcat配置
server.tomcat.basedir=./${logging.file.path}/tmp
#logging
logging.file.path=./logs
logging.file.name=demo.log
logging.file.level=info

编写一个sql

 /**
     * 查询列表
     * @param username
     * @return
     */
    List<Users> selectListUsers(@Param("username") String username);

xml对应编写

<select id="selectListUsers" resultType="com.example.mybatis_plus_demo.model.Users">
		select * from users
		<where>
			<if test="username != null and username != ''">
				username = #{username}
			</if>
		 </where>
	</select>

编写一个接口

@RestController
public class UsersController {

    @Resource
    private IUsersService iUsersService;

    @GetMapping("/list")
    public Object getList(@RequestParam(required = false) String userName){
        return iUsersService.selectListUsers(userName);
    }
}

使用postman调用如下：

上述接口和功能是没问题的。 那我们选择开始进行改写。

自定义注解

/**
 * @author ：dzp
 * @date ：Created in 2021/9/8 10:53
 * @description：自定义mapper注解,指向SqlReplaceInterceptor,该注解的功能为:在查询sql条件中增加一些必要条件。
 */
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface AuthMapper {


}

如果加入自定义注解，应该加入什么字符串

这里我有常量简单实现。实际项目中可以在自定义注解中增加参数来实现，这里我为了简单。就暂时用常量来实现了。
例子：

进行切面

@Component
@Intercepts({
        @Signature(
                type= Executor.class,
                method = "query",
                args = {MappedStatement.class,Object.class
                        , RowBounds.class, ResultHandler.class,
                        CacheKey.class, BoundSql.class})})
public class SqlReplaceInterceptor implements Interceptor {



    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        return getSqlByInvocation(invocation);
    }



    /**
     * 获取sql语句
     * @param invocation
     * @return
     */
    private Object getSqlByInvocation(Invocation invocation) throws InvocationTargetException, IllegalAccessException, ClassNotFoundException {
        final Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        SqlCommandType sqlCommandType = ms.getSqlCommandType();
        Object parameterObject = args[1];
        BoundSql boundSql = ms.getBoundSql(parameterObject);

        String sql = boundSql.getSql();
        if (StringUtils.isBlank(sql)) {
            return invocation.proceed();
        }
        try {
            //todo  这里可以获取到用户id的信息 进行切面 我就暂时写死。  核心方法。 更改sql
            resetSql2Invocation(invocation, boundSql.getSql() , 1);
        }catch (Exception  e) {
            // 没有获取到用户消息
        }


        return invocation.proceed();
    }

     /**
     * 包装sql后，重置到invocation中
     * @param invocation
     * @param sql
     * @throws SQLException
     */
    private void resetSql2Invocation(Invocation invocation, String sql,Integer id) throws  ClassNotFoundException {
    //todo 核心方法进行切面和增加对应的sql
       }

    /**
     * 创建一个新的MappedStatement
     * newMappedStatement
     * @param ms
     * @param newSqlSource
     * @return
     */
    private 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();
    }


    class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;
        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }
        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }


    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    @Override
    public void setProperties(Properties properties) {
        // TODO Auto-generated method stub

    }
}

将注解写到对应的地方

测试功能是否ok

重启项目并调用测试接口看是否ok

源码地址

自定义注解实现拦截sql项目