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

【设计】设计一个web版的数据库管理平台后端精要

news 2025/8/18 6:24:04

需求

springboot设计开发一个系统，在这个系统的数据库表中存放着2000个数据库实例，有MySQL、Oracle、sql server3种数据库类型，用户可以在页面上选择不同的实例，连接这些实例上的数据库，来执行业务sql

实现

@Service
public class DatabaseService {@Autowiredprivate DynamicDataSource dynamicDataSource;@Autowiredprivate JdbcTemplate jdbcTemplate;public void executeSqlOnDatabase(int instanceId, String sql) {// 根据实例ID获取数据库实例信息DatabaseInstance instance = databaseInstanceService.getInstanceById(instanceId);if (instance == null) {throw new IllegalArgumentException("Invalid database instance ID: " + instanceId);}// 构建数据源配置HikariConfig config = new HikariConfig();config.setJdbcUrl(generateJdbcUrl(instance));config.setUsername(instance.getUsername());config.setPassword(instance.getPassword());config.setDriverClassName(getDriverClassName(instance.getDatabaseType()));// 创建数据源HikariDataSource dataSource = new HikariDataSource(config);// 动态添加数据源String dataSourceKey = instance.getIp() + ":" + instance.getPort();dynamicDataSource.addTargetDataSource(dataSourceKey, dataSource);try {// 切换到新的数据源DynamicDataSourceContextHolder.setDataSourceKey(dataSourceKey);// 执行SQLjdbcTemplate.execute(sql);} catch (DataAccessException e) {// 处理异常e.printStackTrace();} finally {// 操作完成后，切换回默认数据源或清理当前数据源DynamicDataSourceContextHolder.clearDataSourceKey();dynamicDataSource.removeTargetDataSource(dataSourceKey);// 关闭数据源dataSource.close();}}private String generateJdbcUrl(DatabaseInstance instance) {String databaseType = instance.getDatabaseType();String ip = instance.getIp();int port = instance.getPort();String databaseName = instance.getDatabaseName();switch (databaseType.toLowerCase()) {case "mysql":return "jdbc:mysql://" + ip + ":" + port + "/" + databaseName;case "oracle":return "jdbc:oracle:thin:@" + ip + ":" + port + ":" + databaseName;case "sqlserver":return "jdbc:sqlserver://" + ip + ":" + port + ";databaseName=" + databaseName;default:throw new IllegalArgumentException("Unsupported database type: " + databaseType);}}private String getDriverClassName(String databaseType) {switch (databaseType.toLowerCase()) {case "mysql":return "com.mysql.cj.jdbc.Driver";case "oracle":return "oracle.jdbc.driver.OracleDriver";case "sqlserver":return "com.microsoft.sqlserver.jdbc.SQLServerDriver";default:throw new IllegalArgumentException("Unsupported database type: " + databaseType);}}
}

public class DynamicDataSourceContextHolder {private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();public static void setDataSourceKey(String key) {CONTEXT_HOLDER.set(key);}public static String getDataSourceKey() {return CONTEXT_HOLDER.get();}public static void clearDataSourceKey() {CONTEXT_HOLDER.remove();}
}