目录
1. UserMapper.java (在mapper包中)
1. UserService.java (在service包中)
2. EmployeeService.java (在service包中)
1. LoginController.java (在controller包中)
2. EmployeeController.java (在controller包中)
TestController.java (在controller包中)
DataPermissionInterceptor.java
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `permission_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `permission_db`;
-- 1. 部门表
CREATE TABLE `t_department` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dept_name` varchar(100) NOT NULL COMMENT '部门名称',
`parent_id` bigint(20) DEFAULT '0' COMMENT '上级部门ID',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
-- 2. 用户表（系统用户）
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(100) NOT NULL COMMENT '密码',
`real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
`dept_id` bigint(20) NOT NULL COMMENT '所属部门ID',
`role_type` int(11) NOT NULL COMMENT '角色类型：1-管理员 2-部门经理 3-普通员工',
`status` int(11) DEFAULT '1' COMMENT '状态：1-正常 0-禁用',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 3. 员工信息表（业务数据，用于权限测试）
CREATE TABLE `t_employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`emp_name` varchar(100) NOT NULL COMMENT '员工姓名',
`emp_no` varchar(50) NOT NULL COMMENT '员工编号',
`dept_id` bigint(20) NOT NULL COMMENT '所属部门',
`position` varchar(100) DEFAULT NULL COMMENT '职位',
`salary` decimal(10,2) DEFAULT '0.00' COMMENT '薪资',
`hire_date` date DEFAULT NULL COMMENT '入职日期',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_emp_no` (`emp_no`),
KEY `idx_dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';
-- 4. 操作日志表
CREATE TABLE `t_operation_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL COMMENT '操作用户ID',
`operation` varchar(200) NOT NULL COMMENT '操作内容',
`method` varchar(500) DEFAULT NULL COMMENT '操作方法',
`params` text COMMENT '参数',
`ip` varchar(50) DEFAULT NULL COMMENT 'IP地址',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
USE permission_db;
-- 插入部门数据
INSERT INTO `t_department` (`dept_name`, `parent_id`) VALUES
('总公司', 0),
('技术研发部', 1),
('产品设计部', 1),
('市场销售部', 1),
('人事行政部', 1);
-- 插入用户数据（密码都是123456的MD5加密：e10adc3949ba59abbe56e057f20f883e）
INSERT INTO `t_user` (`username`, `password`, `real_name`, `dept_id`, `role_type`, `status`) VALUES
('admin', 'e10adc3949ba59abbe56e057f20f883e', '系统管理员', 1, 1, 1), -- 管理员
('tech_manager', 'e10adc3949ba59abbe56e057f20f883e', '张经理', 2, 2, 1), -- 技术部经理
('product_manager', 'e10adc3949ba59abbe56e057f20f883e', '李经理', 3, 2, 1),-- 产品部经理
('tech_emp1', 'e10adc3949ba59abbe56e057f20f883e', '王技术', 2, 3, 1), -- 技术部员工
('product_emp1', 'e10adc3949ba59abbe56e057f20f883e', '赵设计', 3, 3, 1); -- 产品部员工
-- 插入员工数据（业务数据）
INSERT INTO `t_employee` (`emp_name`, `emp_no`, `dept_id`, `position`, `salary`) VALUES
('张三', 'EMP001', 2, '高级工程师', 25000.00),
('李四', 'EMP002', 2, '中级工程师', 18000.00),
('王五', 'EMP003', 3, '产品经理', 22000.00),
('赵六', 'EMP004', 3, 'UI设计师', 15000.00),
('钱七', 'EMP005', 4, '销售经理', 20000.00),
('孙八', 'EMP006', 4, '销售专员', 12000.00),
('周九', 'EMP007', 5, '人事主管', 18000.00),
('吴十', 'EMP008', 5, '行政助理', 10000.00);创建Mybatis项目：
src/main/java/com/example/demo/
├── entity/ # 实体类
├── mapper/ # Mybatis Mapper接口
├── service/ # 业务层
├── controller/ # 控制层
├── plugin/ # Mybatis插件
├── config/ # 配置类（新的）
├── annotation/ # 自定义注解
├── util/ # 工具类
└── interceptor/ # Web拦截器（用于权限验证）
配置pom.xml文件内容：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.18</version>
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId> <!-- 保持原名或改为 data-permission-demo -->
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>基于Mybatis的数据权限管理系统</description>
<properties>
<java.version>1.8</java.version>
<mysql.version>8.0.33</mysql.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>${mysql.version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- SQL解析工具 -->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.6</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>配置application.properties:
server.port=8080
# 数据源配置
spring.datasource.url=jdbc:mysql://localhost:3306/permission_db?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Mybatis配置
mybatis.type-aliases-package=com.example.demo.entity
mybatis.configuration.map-underscore-to-camel-case=true
# 日志配置
logging.level.com.example.demo=DEBUG
logging.level.org.springframework.jdbc.core=TRACE-
创建实体类：
User.java
package com.example.demo.entity;
import java.util.Date;
public class User {
private Long id;
private String username;
private String password;
private String realName;
private Long deptId;
private Integer roleType; // 1-管理员 2-部门经理 3-普通员工
private Integer status;
private Date createTime;
private Date updateTime;
// 构造函数
public User() {}
public User(String username, String password) {
this.username = username;
this.password = password;
}
// Getter和Setter方法
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getPassword() { return password; }
public void setPassword(String password) { this.password = password; }
public String getRealName() { return realName; }
public void setRealName(String realName) { this.realName = realName; }
public Long getDeptId() { return deptId; }
public void setDeptId(Long deptId) { this.deptId = deptId; }
public Integer getRoleType() { return roleType; }
public void setRoleType(Integer roleType) { this.roleType = roleType; }
public Integer getStatus() { return status; }
public void setStatus(Integer status) { this.status = status; }
public Date getCreateTime() { return createTime; }
public void setCreateTime(Date createTime) { this.createTime = createTime; }
public Date getUpdateTime() { return updateTime; }
public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; }
}Department.java
package com.example.demo.entity;
import java.util.Date;
public class Department {
private Long id;
private String deptName;
private Long parentId;
private Date createTime;
private Date updateTime;
// Getter和Setter方法
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getDeptName() { return deptName; }
public void setDeptName(String deptName) { this.deptName = deptName; }
public Long getParentId() { return parentId; }
public void setParentId(Long parentId) { this.parentId = parentId; }
public Date getCreateTime() { return createTime; }
public void setCreateTime(Date createTime) { this.createTime = createTime; }
public Date getUpdateTime() { return updateTime; }
public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; }
}Employee.java
package com.example.demo.entity;
import java.math.BigDecimal;
import java.util.Date;
public class Employee {
private Long id;
private String empName;
private String empNo;
private Long deptId;
private String position;
private BigDecimal salary;
private Date hireDate;
private Date createTime;
private Date updateTime;
// Getter和Setter方法
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getEmpName() { return empName; }
public void setEmpName(String empName) { this.empName = empName; }
public String getEmpNo() { return empNo; }
public void setEmpNo(String empNo) { this.empNo = empNo; }
public Long getDeptId() { return deptId; }
public void setDeptId(Long deptId) { this.deptId = deptId; }
public String getPosition() { return position; }
public void setPosition(String position) { this.position = position; }
public BigDecimal getSalary() { return salary; }
public void setSalary(BigDecimal salary) { this.salary = salary; }
public Date getHireDate() { return hireDate; }
public void setHireDate(Date hireDate) { this.hireDate = hireDate; }
public Date getCreateTime() { return createTime; }
public void setCreateTime(Date createTime) { this.createTime = createTime; }
public Date getUpdateTime() { return updateTime; }
public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; }
}创建Mapper接口
1. UserMapper.java (在mapper包中)
package com.example.demo.mapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserMapper {
@Select("SELECT * FROM t_user WHERE username = #{username} AND password = #{password} AND status = 1")
User selectByUsernameAndPassword(@Param("username") String username, @Param("password") String password);
@Select("SELECT * FROM t_user WHERE id = #{id}")
User selectById(@Param("id") Long id);
}2. EmployeeMapper.java
package com.example.demo.mapper;
import com.example.demo.entity.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface EmployeeMapper {
@Select("SELECT * FROM t_employee")
List<Employee> selectAll();
@Select("SELECT * FROM t_employee WHERE dept_id = #{deptId}")
List<Employee> selectByDeptId(@Param("deptId") Long deptId);
}创建Service层
1. UserService.java (在service包中)
package com.example.demo.service;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public User login(String username, String password) {
return userMapper.selectByUsernameAndPassword(username, password);
}
public User getUserById(Long id) {
return userMapper.selectById(id);
}
}2. EmployeeService.java (在service包中)
package com.example.demo.service;
import com.example.demo.entity.Employee;
import com.example.demo.mapper.EmployeeMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeeService {
@Autowired
private EmployeeMapper employeeMapper;
public List<Employee> getAllEmployees() {
return employeeMapper.selectAll();
}
public List<Employee> getEmployeesByDeptId(Long deptId) {
return employeeMapper.selectByDeptId(deptId);
}
}创建工具类
UserContext.java (在util包中)
package com.example.demo.util;
import com.example.demo.entity.User;
public class UserContext {
private static final ThreadLocal<User> userHolder = new ThreadLocal<>();
public static void setCurrentUser(User user) {
userHolder.set(user);
}
public static User getCurrentUser() {
return userHolder.get();
}
public static void clear() {
userHolder.remove();
}
}创建Mybatis插件（核心）
DataPermissionInterceptor.java
package com.example.demo.plugin;
import com.example.demo.entity.User;
import com.example.demo.util.UserContext;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
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.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.util.Properties;
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
public class DataPermissionInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// 获取当前用户
User currentUser = UserContext.getCurrentUser();
if (currentUser == null) {
// 如果没有登录用户，直接执行原SQL
System.out.println("用户未登录，不进行权限过滤");
return invocation.proceed();
}
// 只处理查询操作
String sqlCommandType = mappedStatement.getSqlCommandType().name();
if (!"SELECT".equals(sqlCommandType)) {
return invocation.proceed();
}
// 获取原始SQL
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
String originalSql = boundSql.getSql();
System.out.println("原始SQL: " + originalSql);
System.out.println("当前用户: " + currentUser.getUsername() + ", 角色: " + currentUser.getRoleType() + ", 部门: " + currentUser.getDeptId());
// 解析并重写SQL
String newSql = addDataPermissionCondition(originalSql, currentUser);
// 设置新的SQL
metaObject.setValue("delegate.boundSql.sql", newSql);
System.out.println("权限过滤后SQL: " + newSql);
return invocation.proceed();
}
private String addDataPermissionCondition(String sql, User user) {
try {
Statement statement = CCJSqlParserUtil.parse(sql);
if (statement instanceof Select) {
Select select = (Select) statement;
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
// 获取表名
String tableName = plainSelect.getFromItem().toString();
System.out.println("查询表名: " + tableName);
// 只对t_employee表进行权限过滤
if (tableName.contains("t_employee")) {
// 根据用户角色添加不同的条件
Expression where = plainSelect.getWhere();
Expression newWhere = buildPermissionExpression(where, user);
plainSelect.setWhere(newWhere);
}
return plainSelect.toString();
}
} catch (JSQLParserException e) {
System.err.println("SQL解析异常: " + e.getMessage());
}
return sql;
}
private Expression buildPermissionExpression(Expression existingWhere, User user) {
// 管理员：可以查看所有数据
if (user.getRoleType() == 1) {
System.out.println("管理员，查看所有数据");
return existingWhere;
}
// 部门经理：只能查看本部门数据
if (user.getRoleType() == 2) {
System.out.println("部门经理，只能查看部门ID=" + user.getDeptId() + "的数据");
EqualsTo deptCondition = new EqualsTo();
deptCondition.setLeftExpression(() -> "dept_id");
deptCondition.setRightExpression(new LongValue(user.getDeptId()));
return deptCondition;
}
// 普通员工：这里简化处理，也只查看本部门数据
// 实际应用中，普通员工可能只能查看自己的数据，但需要表中有关联字段
System.out.println("普通员工，只能查看部门ID=" + user.getDeptId() + "的数据");
EqualsTo deptCondition = new EqualsTo();
deptCondition.setLeftExpression(() -> "dept_id");
deptCondition.setRightExpression(new LongValue(user.getDeptId()));
return deptCondition;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}创建Controller层
1. LoginController.java (在controller包中)
package com.example.demo.controller;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import com.example.demo.util.UserContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpSession;
import java.util.HashMap;
import java.util.Map;
@RestController
@RequestMapping("/auth")
public class LoginController {
@Autowired
private UserService userService;
@PostMapping("/login")
public Map<String, Object> login(@RequestBody Map<String, String> params, HttpSession session) {
String username = params.get("username");
String password = params.get("password");
System.out.println("登录请求: username=" + username + ", password=" + password);
Map<String, Object> result = new HashMap<>();
User user = userService.login(username, password);
if (user != null) {
// 设置用户上下文
UserContext.setCurrentUser(user);
session.setAttribute("user", user);
result.put("success", true);
result.put("message", "登录成功");
result.put("user", user);
System.out.println("登录成功: " + user.getUsername() + ", 角色: " + user.getRoleType());
} else {
result.put("success", false);
result.put("message", "用户名或密码错误");
System.out.println("登录失败");
}
return result;
}
@GetMapping("/logout")
public Map<String, Object> logout(HttpSession session) {
session.removeAttribute("user");
UserContext.clear();
Map<String, Object> result = new HashMap<>();
result.put("success", true);
result.put("message", "退出成功");
return result;
}
@GetMapping("/currentUser")
public Map<String, Object> getCurrentUser(HttpSession session) {
User user = (User) session.getAttribute("user");
Map<String, Object> result = new HashMap<>();
result.put("success", user != null);
result.put("user", user);
return result;
}
}2. EmployeeController.java (在controller包中)
package com.example.demo.controller;
import com.example.demo.entity.Employee;
import com.example.demo.entity.User;
import com.example.demo.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpSession;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/employee")
public class EmployeeController {
@Autowired
private EmployeeService employeeService;
@GetMapping("/list")
public Map<String, Object> listEmployees(HttpSession session) {
Map<String, Object> result = new HashMap<>();
// 检查用户是否登录
User user = (User) session.getAttribute("user");
if (user == null) {
result.put("success", false);
result.put("message", "未登录");
return result;
}
try {
List<Employee> employees = employeeService.getAllEmployees();
result.put("success", true);
result.put("data", employees);
result.put("count", employees.size());
result.put("currentUser", user);
} catch (Exception e) {
result.put("success", false);
result.put("message", "查询失败: " + e.getMessage());
}
return result;
}
@GetMapping("/listByDept")
public Map<String, Object> listEmployeesByDeptId(@RequestParam Long deptId, HttpSession session) {
Map<String, Object> result = new HashMap<>();
// 检查用户是否登录
User user = (User) session.getAttribute("user");
if (user == null) {
result.put("success", false);
result.put("message", "未登录");
return result;
}
try {
List<Employee> employees = employeeService.getEmployeesByDeptId(deptId);
result.put("success", true);
result.put("data", employees);
result.put("count", employees.size());
} catch (Exception e) {
result.put("success", false);
result.put("message", "查询失败: " + e.getMessage());
}
return result;
}
}创建测试Controller
TestController.java (在controller包中)
package com.example.demo.controller;
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 TestController {
@GetMapping("/")
public String home() {
return "数据权限管理系统已启动！";
}
@GetMapping("/test")
public Map<String, Object> test() {
Map<String, Object> result = new HashMap<>();
result.put("status", "success");
result.put("message", "系统运行正常");
result.put("timestamp", System.currentTimeMillis());
return result;
}
}修改主应用类
DemoApplication.java
package com.example.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
System.out.println("=========================================");
System.out.println("数据权限管理系统启动成功！");
System.out.println("访问地址: http://localhost:8080/");
System.out.println("测试接口: http://localhost:8080/test");
System.out.println("登录接口: POST http://localhost:8080/auth/login");
System.out.println("员工列表: GET http://localhost:8080/employee/list (需要登录)");
System.out.println("=========================================");
}
}DataPermissionInterceptor.java
package com.example.demo.plugin;
import com.example.demo.entity.User;
import com.example.demo.util.UserContext;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
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.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.util.Properties;
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
public class DataPermissionInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// 获取当前用户
User currentUser = UserContext.getCurrentUser();
if (currentUser == null) {
// 如果没有登录用户，直接执行原SQL
System.out.println("用户未登录，不进行权限过滤");
return invocation.proceed();
}
// 只处理查询操作
String sqlCommandType = mappedStatement.getSqlCommandType().name();
if (!"SELECT".equals(sqlCommandType)) {
return invocation.proceed();
}
// 获取原始SQL
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
String originalSql = boundSql.getSql();
System.out.println("原始SQL: " + originalSql);
System.out.println("当前用户: " + currentUser.getUsername() + ", 角色: " + currentUser.getRoleType() + ", 部门: " + currentUser.getDeptId());
// 解析并重写SQL
String newSql = addDataPermissionCondition(originalSql, currentUser);
// 设置新的SQL
metaObject.setValue("delegate.boundSql.sql", newSql);
System.out.println("权限过滤后SQL: " + newSql);
return invocation.proceed();
}
private String addDataPermissionCondition(String sql, User user) {
try {
Statement statement = CCJSqlParserUtil.parse(sql);
if (statement instanceof Select) {
Select select = (Select) statement;
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
// 获取表名
String tableName = plainSelect.getFromItem().toString();
System.out.println("查询表名: " + tableName);
// 只对t_employee表进行权限过滤
if (tableName.contains("t_employee")) {
// 根据用户角色添加不同的条件
Expression where = plainSelect.getWhere();
Expression newWhere = buildPermissionExpression(where, user);
plainSelect.setWhere(newWhere);
}
return plainSelect.toString();
}
} catch (JSQLParserException e) {
System.err.println("SQL解析异常: " + e.getMessage());
}
return sql;
}
private Expression buildPermissionExpression(Expression existingWhere, User user) {
// 管理员：可以查看所有数据
if (user.getRoleType() == 1) {
System.out.println("管理员，查看所有数据");
return existingWhere;
}
// 部门经理：只能查看本部门数据
if (user.getRoleType() == 2) {
System.out.println("部门经理，只能查看部门ID=" + user.getDeptId() + "的数据");
EqualsTo deptCondition = new EqualsTo();
// 使用Column类而不是lambda表达式
deptCondition.setLeftExpression(new Column("dept_id"));
deptCondition.setRightExpression(new LongValue(user.getDeptId()));
return deptCondition;
}
// 普通员工：这里简化处理，也只查看本部门数据
// 实际应用中，普通员工可能只能查看自己的数据，但需要表中有关联字段
System.out.println("普通员工，只能查看部门ID=" + user.getDeptId() + "的数据");
EqualsTo deptCondition = new EqualsTo();
// 使用Column类而不是lambda表达式
deptCondition.setLeftExpression(new Column("dept_id"));
deptCondition.setRightExpression(new LongValue(user.getDeptId()));
return deptCondition;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}测试步骤
测试1：基础测试
GET http://localhost:8080/test
应返回：{"status":"success","message":"系统运行正常",...}
测试2：用户登录
POST http://localhost:8080/auth/login
Content-Type: application/json
{
"username": "admin",
"password": "123456"
}
应该返回成功，并显示用户信息
测试3：查看员工列表（测试权限过滤）
GET http://localhost:8080/employee/list
需要先登录，不同用户会看到不同数据：
-
admin：看到所有员工
-
tech_manager：只看到技术部的员工（dept_id=2）
-
product_manager：只看到产品部的员工（dept_id=3）
扫一扫
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
