SpringBoot动态拼接sql的两种方式

最新推荐文章于 2024-08-10 23:17:37 发布

Chrisf Zhang

最新推荐文章于 2024-08-10 23:17:37 发布

阅读量4.9k

点赞数 4

分类专栏： Java 文章标签： mybatis interceptor aop

本文链接：https://blog.csdn.net/Crisf/article/details/116330183

版权

Java 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

今天搭建了一个简易的demo项目，测试了两种动态拼接sql的方式，特此记录一下，以备后用。

代码地址：https://gitee.com/chrisfzh/dailytest

项目结构如下：

基类

package com.chrisf.web.domain;

import java.io.Serializable;
import java.util.Map;

public class BaseEntity implements Serializable {
    protected Map<String, Object> params;

    public Map<String, Object> getParams() {
        return params;
    }

    public void setParams(Map<String, Object> params) {
        this.params = params;
    }
}

业务代码：

package com.chrisf.business.controller;

import com.chrisf.business.domain.Dept;
import com.chrisf.business.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.Map;

@RestController
public class DeptController {

    @Autowired
    private DeptService deptService;

    /**
     * 方式一
     * @param dept
     * @return
     */
    @GetMapping("get")
    public Map<String, Object> getList(Dept dept){
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("code", 200);
        resultMap.put("msg", "success");
        resultMap.put("data", deptService.getList(dept));
        return resultMap;
    }

    /**
     * 方式二
     * @param dept
     * @return
     */
    @GetMapping("get2")
    public Map<String, Object> getDepts(Dept dept){
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("code", 200);
        resultMap.put("msg", "success");
        resultMap.put("data", deptService.getList2(dept));
        return resultMap;
    }
}

package com.chrisf.business.service;

import com.chrisf.business.domain.Dept;

import java.util.List;

public interface DeptService {

    List<Dept> getList(Dept dept);

    List<Dept> getList2(Dept dept);
}

package com.chrisf.business.service.impl;

import com.chrisf.aspectj.annotation.DataScope;
import com.chrisf.business.domain.Dept;
import com.chrisf.business.mapper.DeptMapper;
import com.chrisf.business.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DeptServiceImpl implements DeptService {

    @Autowired
    private DeptMapper deptMapper;

    @Override
    @DataScope
    public List<Dept> getList(Dept dept) {
        return deptMapper.getDeptList(dept);
    }

    @Override
    public List<Dept> getList2(Dept dept) {
        return deptMapper.getDepts(dept);
    }
}

package com.chrisf.business.mapper;

import com.chrisf.aspectj.annotation.InterceptAnnotation;
import com.chrisf.business.domain.Dept;

import java.util.List;

public interface DeptMapper {

    List<Dept> getDeptList(Dept dept);

    @InterceptAnnotation
    List<Dept> getDepts(Dept dept);
}

<?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.chrisf.business.mapper.DeptMapper">

    <select id="getDeptList" parameterType="Dept" resultType="com.chrisf.business.domain.Dept">
        select * from dept
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>
    <select id="getDepts" parameterType="Dept" resultType="com.chrisf.business.domain.Dept">
        select * from dept
    </select>
</mapper>

数据库

方式一：在基类上增加共有属性params，所有的需要过滤数据的业务实体可以继承该基类，在需要的过滤数据的方法上加上自定义注解，切面方法中在执行前拦截，获取参数或者登录账户信息增加筛选条件，并转成相应的sql语句，放入params字段，在执行sql的时候会进行拼接。

注解类：

package com.chrisf.aspectj.annotation;

import java.lang.annotation.*;

/**
 * 数据权限过滤注解
 *
 * @author ruoyi
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope
{

}

切面方法

package com.chrisf.aspectj;

import com.chrisf.web.domain.BaseEntity;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

import java.util.HashMap;

/**
 * 数据过滤处理
 */
@Aspect
@Component
public class DataScopeAspect
{
    /**
     * 数据权限过滤关键字
     */
    public static final String DATA_SCOPE = "dataScope";

    // 配置织入点
    @Pointcut("@annotation(com.chrisf.aspectj.annotation.DataScope)")
    public void dataScopePointCut()
    {
    }

    @Before("dataScopePointCut()")
    public void doBefore(JoinPoint point) throws Throwable
    {
        handleDataScope(point);
    }

    protected void handleDataScope(final JoinPoint joinPoint)
    {
        //获取参数
        BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
        //params如果为null，则New一个Map
        if (baseEntity.getParams() == null){
            baseEntity.setParams(new HashMap<String, Object>());
        }
        baseEntity.getParams().put(DATA_SCOPE, "where name like '%总务%'");
    }

}

运行项目，浏览器访问结果

方式二：通过拦截器动态修改sql

注解

package com.chrisf.aspectj.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface InterceptAnnotation {
    boolean flag() default true;
}

拦截器

package com.chrisf.intercepter;

import com.chrisf.aspectj.annotation.InterceptAnnotation;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Properties;

@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class TestMybatisIntercepter implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        //先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //id为执行的mapper方法的全路径名，如com.chrisf.business.mapper.DeptMapper.getDepts
        String id = mappedStatement.getId();
        //sql语句类型 select、delete、insert、update
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        BoundSql boundSql = statementHandler.getBoundSql();

        //获取到原始sql语句
        String sql = boundSql.getSql();
        String mSql = sql;

        //注解逻辑判断  添加注解了才拦截
        Class<?> classType = Class.forName(id.substring(0, mappedStatement.getId().lastIndexOf(".")));
        String mName = mappedStatement.getId().substring(id.lastIndexOf(".") + 1, mappedStatement.getId().length());
        for (Method method : classType.getDeclaredMethods()) {
            if (method.isAnnotationPresent(InterceptAnnotation.class) && mName.equals(method.getName())) {
                InterceptAnnotation interceptorAnnotation = method.getAnnotation(InterceptAnnotation.class);
                if (interceptorAnnotation.flag()) {
                    mSql = sql + " where name like '%开发%'";
                }
            }
        }

        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

浏览器访问测试