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

需求

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

实现

@Service
public class DatabaseService {
    @Autowired
    private DynamicDataSource dynamicDataSource;
    @Autowired
    private 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);
            // 执行SQL
            jdbcTemplate.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();
    }
}