当前位置：首页 > news >正文

达州住房和城乡建设部网站/seo优化网站查询

news 2025/6/28 2:49:41

达州住房和城乡建设部网站,seo优化网站查询,saas建站没有网站源代码么,手表哪个网站正品前言在企业级应用中，数据权限控制是保证数据安全的重要环节。本文将详细介绍如何在 Spring Boot 3.4.3 项目中结合 JSqlParser 和 MyBatis 实现灵活的数据权限控制，通过动态 SQL 改写实现多租户、部门隔离等常见数据权限需求。一、环境准备确保开发…

前言

在企业级应用中，数据权限控制是保证数据安全的重要环节。本文将详细介绍如何在 Spring Boot 3.4.3 项目中结合 JSqlParser 和 MyBatis 实现灵活的数据权限控制，通过动态 SQL 改写实现多租户、部门隔离等常见数据权限需求。

一、环境准备

确保开发环境满足以下要求：

JDK 17+
Spring Boot 3.4.3
MyBatis 3.5.13+
JSqlParser 4.5+
Maven 3.6.3+

二、项目配置

1. 添加依赖

在 pom.xml 中添加以下依赖：

<dependencies><!-- Spring Boot Starter --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- MyBatis --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>3.0.3</version></dependency><!-- JSqlParser --><dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.6</version></dependency><!-- 数据库驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- 其他工具 --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency>
</dependencies>

2. 数据权限注解

定义数据权限注解：

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataPermission {/*** 部门表别名*/String deptAlias() default "";/*** 用户表别名*/String userAlias() default "";/*** 权限类型*/DataPermissionType type() default DataPermissionType.DEPT;
}public enum DataPermissionType {/*** 全部数据权限*/ALL,/*** 部门数据权限*/DEPT,/*** 部门及以下数据权限*/DEPT_AND_CHILD,/*** 仅本人数据权限*/SELF
}

三、核心实现

1. 数据权限上下文

public class DataPermissionContext {private static final ThreadLocal<DataPermissionInfo> CONTEXT = new ThreadLocal<>();public static void set(DataPermissionInfo info) {CONTEXT.set(info);}public static DataPermissionInfo get() {return CONTEXT.get();}public static void clear() {CONTEXT.remove();}
}@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataPermissionInfo {private Long userId;private Long deptId;private List<Long> deptIds; // 用户拥有的部门权限private DataPermissionType permissionType;
}

2. SQL 解析改写拦截器

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Slf4j
public class DataPermissionInterceptor implements Interceptor {private final JSqlParser sqlParser = new JSqlParser();@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();MetaObject metaObject = SystemMetaObject.forObject(statementHandler);// 获取Mapper方法上的DataPermission注解MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");String id = mappedStatement.getId();String className = id.substring(0, id.lastIndexOf("."));String methodName = id.substring(id.lastIndexOf(".") + 1);Method method = Class.forName(className).getMethod(methodName, mappedStatement.getParameterMap().getParameterTypes());DataPermission dataPermission = method.getAnnotation(DataPermission.class);if (dataPermission == null) {return invocation.proceed();}// 获取原始SQLBoundSql boundSql = statementHandler.getBoundSql();String originalSql = boundSql.getSql();// 解析并改写SQLString modifiedSql = processSql(originalSql, dataPermission);// 设置改写后的SQLmetaObject.setValue("delegate.boundSql.sql", modifiedSql);return invocation.proceed();}private String processSql(String sql, DataPermission dataPermission) throws JSQLParserException {DataPermissionInfo permissionInfo = DataPermissionContext.get();if (permissionInfo == null || permissionInfo.getPermissionType() == DataPermissionType.ALL) {return sql;}Statement statement = sqlParser.parse(sql);if (statement instanceof Select) {Select select = (Select) statement;PlainSelect plainSelect = (PlainSelect) select.getSelectBody();// 构建权限条件Expression permissionExpression = buildPermissionExpression(dataPermission, permissionInfo);if (permissionExpression != null) {if (plainSelect.getWhere() == null) {plainSelect.setWhere(permissionExpression);} else {plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), permissionExpression));}}return select.toString();}return sql;}private Expression buildPermissionExpression(DataPermission dataPermission, DataPermissionInfo permissionInfo) {DataPermissionType type = permissionInfo.getPermissionType();if (type == DataPermissionType.SELF && StringUtils.isNotBlank(dataPermission.userAlias())) {// 仅本人数据权限return new EqualsTo(new Column(dataPermission.userAlias() + ".user_id"),new LongValue(permissionInfo.getUserId()));} else if ((type == DataPermissionType.DEPT || type == DataPermissionType.DEPT_AND_CHILD) && StringUtils.isNotBlank(dataPermission.deptAlias())) {// 部门数据权限if (CollectionUtils.isEmpty(permissionInfo.getDeptIds())) {return null;}if (type == DataPermissionType.DEPT) {// 仅本部门return new InExpression(new Column(dataPermission.deptAlias() + ".dept_id"),new ExpressionList(permissionInfo.getDeptIds().stream().map(LongValue::new).collect(Collectors.toList())));} else {// 本部门及以下部门return new GreaterThanEquals(new Column(dataPermission.deptAlias() + ".dept_path"),new StringValue(permissionInfo.getDeptId() + "/%"));}}return null;}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {// 可配置化处理}
}

3. 注册拦截器

@Configuration
public class MyBatisConfig {@Beanpublic DataPermissionInterceptor dataPermissionInterceptor() {return new DataPermissionInterceptor();}
}

四、使用示例

1. 设置数据权限上下文

@RestControllerAdvice
public class DataPermissionAdvice {@ModelAttributepublic void setDataPermission() {// 实际项目中应从登录用户信息中获取DataPermissionInfo info = new DataPermissionInfo();info.setUserId(1001L);info.setDeptId(10L);info.setDeptIds(Arrays.asList(10L, 11L, 12L));info.setPermissionType(DataPermissionType.DEPT_AND_CHILD);DataPermissionContext.set(info);}
}

2. Mapper 接口使用

public interface UserMapper {@DataPermission(deptAlias = "u", userAlias = "u")@Select("SELECT * FROM sys_user u")List<User> selectAllUsers();@DataPermission(deptAlias = "u", type = DataPermissionType.SELF)@Select("SELECT * FROM sys_user u WHERE u.status = 1")List<User> selectActiveUsers();
}

3. 实体类定义

@Data
public class User {private Long userId;private String username;private Long deptId;private Integer status;// 其他字段...
}

五、高级功能

1. 多表关联查询支持

public interface OrderMapper {@DataPermission(deptAlias = "u", userAlias = "u")@Select("SELECT o.*, u.username FROM sys_order o " +"LEFT JOIN sys_user u ON o.user_id = u.user_id")List<Order> selectAllOrders();
}

2. 自定义权限处理器

public interface DataPermissionHandler {Expression handle(DataPermission dataPermission, DataPermissionInfo permissionInfo);
}@Component
public class DefaultDataPermissionHandler implements DataPermissionHandler {@Overridepublic Expression handle(DataPermission dataPermission, DataPermissionInfo permissionInfo) {// 自定义权限逻辑return null;}
}// 在拦截器中注入使用
public class DataPermissionInterceptor implements Interceptor {@Autowiredprivate DataPermissionHandler permissionHandler;// ...其他代码
}

3. 动态表名支持

public class DynamicTableProcessor {public static String process(String sql, String tableName, String dynamicTableName) {try {Statement statement = CCJSqlParserUtil.parse(sql);if (statement instanceof Select) {Select select = (Select) statement;select.getSelectBody().accept(new TableNamesFinder() {@Overridepublic void visit(Table table) {if (table.getName().equalsIgnoreCase(tableName)) {table.setName(dynamicTableName);}}});return select.toString();}} catch (JSQLParserException e) {log.error("动态表名处理失败", e);}return sql;}
}