宗旨
减少代码开发，实现零代码数据CURD
一、思路
通过spring mvc 动态去加载接口配置实现数据的CURD接口，配置保存在数据表中让CURD飞起来。
1.采用mybatis-plus dynamic-datasource动态数据源处理多数据源问题
2.采样Spring MVC RequestMappingHandlerMapping 加载自定义接口
3.采用mybatis-plus Provider实现对sql的处理
二、依赖
<!-- MyBatis-Plus 依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version> <!-- 根据最新版本调整 -->
</dependency>
<!-- MyBatis 依赖（MyBatis-Plus 是基于 MyBatis 的）-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.10</version> <!-- 根据实际需要的版本调整 -->
</dependency>
<!-- 数据库连接池（例如 HikariCP） -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>4.0.3</version> <!-- 可以根据实际需要选择合适的连接池 -->
</dependency>
<!-- 数据库驱动（以 MySQL 为例） -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version> <!-- 根据使用的 MySQL 版本调整 -->
</dependency>
<!-- mybatis-plus 动态数据源 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>4.3.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
三、表结构
CREATE TABLE `dynamic_api` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名称',
`url_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'URL 路径',
`input_params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '入参',
`fields` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '字段',
`method_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '方法类型',
`sql_statements` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'SQL语句',
`enable` binary(1) NOT NULL DEFAULT '1' COMMENT '1 启用 0 停用 ',
`permission_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '*' COMMENT '权限标识符',
`sql_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'sql类型',
`datasouce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'master' COMMENT '数据源',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='动态api接口';
四、mybatis-plus配置
spring:
datasource:
dynamic:
primary: master
datasource:
master:
url: jdbc:mysql://192.168.0.111:3306/g_admin?characterEncoding=utf8&useUnicode=true&serverTimezone=GMT%2B8
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
hikari:
connection-test-query: SELECT 1
minimum-idle: 5
maximum-pool-size: 5
# sqlite:
# url: jdbc:sqlite:${sqlite.db-path}
# driver-class-name: org.sqlite.JDBC
# type: com.zaxxer.hikari.HikariDataSource
# hikari:
# connection-test-query: SELECT 1
# minimum-idle: 5
# maximum-pool-size: 5
mybatis-plus:
mapperLocations: classpath*:mapper/*Mapper.xml
type-aliases-package: com.zj.admin.pojo.entity
五、dynamic_api表mybatis-plus文件
-
entity
package com.g.admin.framework.web.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import java.util.Date;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
/**
* <p>
* 动态api接口
* </p>
*
* @author gongzhiqiang
* @since 2025-01-16
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("dynamic_api")
public class DynamicApi implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 名称
*/
private String name;
/**
* URL 路径
*/
private String urlPath;
/**
* 入参
*/
private String inputParams;
/**
* 字段
*/
private String fields;
/**
* 方法类型
*/
private String methodType;
/**
* SQL语句
*/
private String sqlStatements;
/**
* 1 启用 0 停用
*/
private Boolean enable;
/**
* 权限标识符
*/
private String permissionIdentifier;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改时间
*/
private Date updateTime;
/**
* 数据源
*/
private String datasouce;
/**
* sql方法
*/
private String sqlMethod;
}
-
mapper
package com.g.admin.framework.web.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.g.admin.framework.web.entity.DynamicApi;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
/**
* <p>
* 动态api接口 Mapper 接口
* </p>
*
* @author gongzhiqiang
* @since 2025-01-16
*/
public interface DynamicApiMapper extends BaseMapper<DynamicApi> {
}
-
service
package com.g.admin.framework.web.service;
import com.g.admin.framework.web.entity.DynamicApi;
import com.baomidou.mybatisplus.extension.service.IService;
/**
* <p>
* 动态api接口 服务类
* </p>
*
* @author gongzhiqiang
* @since 2025-01-16
*/
public interface IDynamicApiService extends IService<DynamicApi> {
}
package com.g.admin.framework.web.service.impl;
import com.g.admin.framework.web.entity.DynamicApi;
import com.g.admin.framework.web.mapper.DynamicApiMapper;
import com.g.admin.framework.web.service.IDynamicApiService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
/**
* <p>
* 动态api接口 服务实现类
* </p>
*
* @author gongzhiqiang
* @since 2025-01-16
*/
@Service
public class DynamicApiServiceImpl extends ServiceImpl<DynamicApiMapper, DynamicApi> implements IDynamicApiService {
}
六、DynamicApiHandlerMapping 实现动态加载接口
实例化并且表并且注入mvc
package com.g.admin.framework.web;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.g.admin.comment.web.Response;
import com.g.admin.framework.web.entity.DynamicApi;
import com.g.admin.framework.web.mapper.DynamicMapper;
import com.g.admin.framework.web.service.IDynamicApiService;
import com.g.admin.utils.SpringContextUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.mvc.method.RequestMappingInfo;
import org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping;
import javax.annotation.PostConstruct;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
@Configuration
public class DynamicApiHandlerMapping {
private static final Logger log = LoggerFactory.getLogger(DynamicApiHandlerMapping.class);
@Autowired
private RequestMappingHandlerMapping handlerMapping;
@Autowired
private IDynamicApiService dynamicApiService;
@PostConstruct
public void initRegisterDynamicApiHandlerMapping() {
log.info("Initializing dynamic API handler mappings...");
List<DynamicApi> apiList = dynamicApiService.list();
apiList.forEach(api -> {
if (api.getEnable()) {
try {
// 创建 RequestMapping 信息
RequestMethod method = RequestMethod.valueOf(api.getMethodType().toUpperCase());
RequestMappingInfo rmi = RequestMappingInfo.paths(api.getUrlPath())
.methods(method)
.consumes(MediaType.APPLICATION_JSON_VALUE)
.produces(MediaType.APPLICATION_JSON_VALUE)
.build();
// 动态创建控制器并注册方法
Object controller = new Object() {
public ResponseEntity<Object> handle(@RequestBody(required = false) Map<String, Object> request) {
try {
log.info("Handling request for API: {}\t{}", api.getName(), api.getUrlPath());
String sqlStatements = api.getSqlStatements();
if (sqlStatements != null && !sqlStatements.isEmpty()) {
DynamicMapper mapper = SpringContextUtils.getBean(DynamicMapper.class);
DynamicDataSourceContextHolder.push(api.getDatasouce());
switch (api.getSqlMethod()){
case "select":
List<Map<String, Object>> maps = mapper.selectBySql(sqlStatements, request);
return ResponseEntity.ok(Response.success(maps));
case "insert":
int insert = mapper.insertBySql(sqlStatements, request);
return ResponseEntity.ok(Response.success(insert));
case "update":
int update = mapper.updateBySql(sqlStatements,request);
return ResponseEntity.ok(Response.success(update));
case "delete":
int delete = mapper.deleteBySql(sqlStatements,request);
return ResponseEntity.ok(Response.success(delete));
}
}
return ResponseEntity.ok(Response.fail("query params error"));
} catch (Exception e) {
log.error("Error while handling request for API: {}", api.getName(), e);
return ResponseEntity.ok(Response.fail("Internal server error: " + e.getMessage()));
}finally {
DynamicDataSourceContextHolder.clear();
request.clear();
request = null;
}
}
};
// 获取控制器方法
Method methodToRegister = controller.getClass().getMethod("handle", Map.class);
// 注册映射
handlerMapping.registerMapping(rmi, controller, methodToRegister);
log.info("Successfully registered API: {} at path: {}", api.getName(), api.getUrlPath());
} catch (Exception e) {
log.error("Failed to register API: {} at path: {}", api.getName(), api.getUrlPath(), e);
}
}
});
log.info("Dynamic API handler mappings initialization completed.");
}
}
七、sql拼接
- mapper
package com.g.admin.framework.web.mapper;
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import com.g.admin.framework.datasource.DynamicSqlProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import java.util.List;
import java.util.Map;
public interface DynamicMapper {
@DSTransactional(rollbackFor = Exception.class)
@UpdateProvider(type = DynamicSqlProvider.class, method = "buildUpdateSql")
int updateBySql(@Param("sql") String sql, @Param("params") Map<String, Object> params);
@SelectProvider(type = DynamicSqlProvider.class, method = "buildSelectSql")
List<Map<String, Object>> selectBySql(@Param("sql") String sql, @Param("params") Map<String, Object> params);
@DSTransactional(rollbackFor = Exception.class)
@DeleteProvider(type = DynamicSqlProvider.class, method = "buildDeleteSql")
int deleteBySql(@Param("sql") String sql, @Param("params") Map<String, Object> params);
@DSTransactional(rollbackFor = Exception.class)
@InsertProvider(type = DynamicSqlProvider.class, method = "buildInsertSql")
int insertBySql(@Param("sql") String sql, @Param("params") Map<String, Object> params);
}
- DynamicSqlProvider
package com.g.admin.framework.datasource;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class DynamicSqlProvider {
public String buildSelectSql(Map<String, Object> params) {
return processSql((String) params.get("sql"), params);
}
public String buildUpdateSql(Map<String, Object> params) {
return processSql((String) params.get("sql"), params);
}
public String buildDeleteSql(Map<String, Object> params) {
return processSql((String) params.get("sql"), params);
}
public String buildInsertSql(Map<String, Object> params) {
return processSql((String) params.get("sql"), params);
}
/**
* 处理动态 SQL，防止注入并支持参数替换
*
* @param sql 动态 SQL 字符串
* @param params 参数集合，键为参数名，值为参数值
* @return 处理后的安全 SQL 字符串
*/
private String processSql(String sql, Map<String, Object> params) {
// 验证 SQL 是否为空
if (sql == null || sql.trim().isEmpty()) {
throw new IllegalArgumentException("SQL cannot be null or empty");
}
// 验证是否包含非法字符，防止 SQL 注入
if (containsDangerousSql(sql)) {
throw new IllegalArgumentException("SQL contains potentially dangerous content.");
}
// 替换 SQL 中的占位符，例如 :paramName 为参数值
String processedSql = replacePlaceholders(sql, params);
params.clear();
params = null;
return processedSql;
}
/**
* 检查 SQL 中是否包含危险关键字或语法
*
* @param sql 待检查的 SQL
* @return 如果包含危险内容，返回 true，否则返回 false
*/
private boolean containsDangerousSql(String sql) {
// 可能的危险关键字或语法
String[] dangerousKeywords = {
"--", ";", "/*", "*/", "xp_", "exec", "drop"
};
for (String keyword : dangerousKeywords) {
if (sql.toLowerCase().contains(keyword.toLowerCase())) {
return true;
}
}
return false;
}
/**
* 替换 SQL 中的占位符为参数值
*
* @param sql 动态 SQL 字符串
* @param params 参数集合，键为参数名，值为参数值
* @return 替换后的 SQL
*/
private String replacePlaceholders(String sql, Map<String, Object> params) {
if (params == null || params.isEmpty()) {
return sql;
}
// 使用正则表达式匹配 :paramName 格式的占位符
Pattern pattern = Pattern.compile(":([a-zA-Z0-9_]+)");
Matcher matcher = pattern.matcher(sql);
StringBuffer replacedSql = new StringBuffer();
while (matcher.find()) {
String paramName = matcher.group(1);
Map<String, Object> map = (Map<String, Object>) params.get("params");
Object paramValue = map.get(paramName);
// 如果参数不存在，抛出异常
if (paramValue == null) {
throw new IllegalArgumentException("Missing value for SQL parameter: " + paramName);
}
// 将参数值替换到占位符位置
String replacement = paramValue instanceof String
? "'" + escapeSql((String) paramValue) + "'" // 字符串值需要加单引号并转义
: paramValue.toString(); // 其他类型直接转换为字符串
matcher.appendReplacement(replacedSql, replacement);
}
matcher.appendTail(replacedSql);
return replacedSql.toString();
}
/**
* 转义 SQL 参数值中的危险字符
*
* @param value 参数值
* @return 转义后的安全值
*/
private String escapeSql(String value) {
if (value == null) {
return null;
}
// 转义单引号
return value.replace("'", "''");
}
}
八、测试
INSERT INTO `g_yan`.`dynamic_api` (`id`, `name`, `url_path`, `input_params`, `fields`, `method_type`, `sql_statements`, `enable`, `permission_identifier`, `sql_method`, `datasouce`, `create_time`, `update_time`) VALUES (1, '查询动态api信息', '/test', NULL, NULL, 'POST', 'SELECT * FROM dynamic_api where id = :id', 0x31, '*', 'select', 'master', '2025-01-16 03:55:56', '2025-01-17 03:15:05');
后续
继续优化sql及其出入参，使之更好的适配各种场景，如出入参对date的序列化
