使用hibernate 下的StatementInspector 接口类实现sql拦截并替换
一、配置文件进行配置,根据自己项目去使用对应配置
1、properties文件配置方式
spring.jpa.properties.hibernate.session_factory.statement_inspector=com.datalook.manage.JpaStatementInspector
2、yaml文件配置
spring:
jpa:
properties:
hibernate:
session_factory:
statement_inspector: com.datalook.manage.JpaStatementInspector
二、新建拦截类,继承StatementInspector 接口类
package com.datalook.manage;
import com.datalook.model.SysUser;
import com.datalook.util.common.OperatorUtil;
import com.datalook.util.log.LogUtil;
import org.hibernate.resource.jdbc.spi.StatementInspector;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author guoyunlong
* @description: 数据拦截
* @date: 2022年6月8日, 0008 13:27
*/
public class JpaStatementInspector implements StatementInspector {
// 当前方法会在你业务执行之前拦截下你的sql,具体你要干的事情就是在inspect里面去实现你的业务
@Override
public String inspect(String sql) {
LogUtil.debug("sql拦截器:-------------", sql);
Optional<SysUser> currentOperator = OperatorUtil.currentOperator();
if (currentOperator.isPresent()) {
Long userid = currentOperator.get().getId();
// 截取select查询类sql,过滤insert updata
Pattern compile = Pattern.compile("select", Pattern.CASE_INSENSITIVE);
Matcher matcher = compile.matcher(sql);
if (!matcher.find()) {
LogUtil.debug("没检测到SQL中from关键字,数据权限拦截{}", sql);
return sql;
}
String newSql = "select id from SYS_ORGANIZATION_INFO \n" +
"start with id in (select organid from sys_userorgan_bind where userid=" + userid + ")\n " +
"connect by prior id = parentid";
// 如果sql存在orgid则进行拦截
int orgid = sql.indexOf("orgid");
String newsql = "";
if (orgid != -1) {
if (sql.contains("orgid =2")) {
newsql = sql.replaceAll("orgid =2", "orgid in (" + newSql + ")");
} else if (sql.contains("orgid =?")) {
newsql = sql.replaceAll("orgid =\\?", "orgid in (" + newSql + ")");
}
System.err.println("新sql:-------------:" + newsql);
return newsql;
}
}
return sql;
}
// 测试
public static void main(String[] args) {
String words = "select * from ( SELECT u.*,ssd.SCHOOLNAME as schoolname FROM CARD_USERINFO u inner join BASE_ACCOUNTINFO b on u.ID=b.USERID LEFT JOIN SYS_SCHOOL_DEF ssd ON u.SCHOOLCODE = ssd.SCHOOLCODE where 1=1 and u.orgid =? order by u.idserial,u.modifydate desc ) where rownum <= ?";
String newStr = words.replaceAll("orgid =\\?", "orgid in (3)");
System.out.println(newStr);
}
}