老系统改造增加初始化，自动化数据源配置
一、前言
在技术飞速迭代的当下，许多老旧项目因未及时升级，陷入了部署困境。这类系统往往缺乏标准化配置，依赖特殊运行环境，加之团队中运维角色的缺失，每次上线部署都得劳烦开发人员远程操作。繁琐的手动配置、环境依赖冲突、版本兼容问题层出不穷，不仅占用开发精力，还常因操作失误导致部署失败，拖慢业务推进节奏。为此，亟需一套极简的部署方案
—— 无需专业技术背景，让普通员工通过点击几次鼠标，就能完成系统的安装与上线，彻底摆脱对开发人员的依赖，化解老系统部署的效率瓶颈。
二、改造描述
1、环境说明
springmvc + mybatis plus + jsp + maven 的 javaweb 项目
2、实现步骤简要思考
- 准备初始化sql结构文件 定义绕过权限的接口，访问初始化配置页面
- 启动时自动读取jdbc文件，创建数据源，如未配置，需要一个默认的临时数据源
- 编辑jdbc配置，保存配置，根据配置创建新的数据源，并销毁就的数据源，同时改变新数据源的SqlSessionFactory，执行初始化脚本
- 正常访问系统
三、开始改造
1、准备sql初始化文件
不涉及代码，不过多介绍，把准备好的sql脚本放到资源文件下，新建一个sql目录，注意后续增量sql继续增加后面，按照增量时间改名。后续初始化的时候，会按照时间顺序执行初始化脚本
注意：1、调整maven的pom.xml文件，让maven能把sql脚本文件编译到class里面去，不然后面执行，找不到文件
2、启动时自动读取jdbc文件，创建数据源，如未配置，需要一个默认的临时数据源
2.1去掉sping mvc原本配置的固定dataSource，改为动态dataSource
注意：需要添加包扫描，保证动态dataSource那部分bean能被正常扫描
2.2 代码类，这里是示例，我就不管规范了，放到一起
2.2.1 DynamicDataSourceConfig.java
package com.luozc.config.jdbc;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope; // 添加缺失的Scope导入
import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
public class DynamicDataSourceConfig {
@Autowired
private JdbcConfig jdbcConfig;
@Bean(name = "dataSource")
@Scope("prototype")
public DataSource dataSource() throws SQLException {
System.out.println("[动态数据源] 创建新数据源...");
jdbcConfig.loadConfig();
String driver = jdbcConfig.getDriverClassName();
String url = jdbcConfig.getUrl();
String username = jdbcConfig.getUsername();
String password = jdbcConfig.getPassword();
System.out.println("[动态数据源] 使用以下配置创建数据源:");
System.out.println("driver: " + driver);
System.out.println("url: " + url);
System.out.println("username: " + username);
System.out.println("password: " + (password != null ? "******" : "null"));
if (driver == null || url == null) {
System.err.println("[动态数据源] 配置为空，返回代理数据源");
return new ProxyDataSource(jdbcConfig);
}
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 验证数据库连接
try {
// 获取并关闭连接，验证连接有效性
dataSource.getConnection().close();
System.out.println("[动态数据源] 新数据源已成功创建并验证。");
} catch (SQLException e) {
System.err.println("[动态数据源] 数据库连接验证失败: " + e.getMessage());
// 记录异常堆栈信息
e.printStackTrace();
throw e;
}
return dataSource;
}
}
2.2.2 JdbcConfig.java
package com.luozc.config.jdbc;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Component;
import java.io.*;
import java.nio.file.Files;
import java.util.Properties;
@Component
public class JdbcConfig {
private static final String JDBC_CONFIG_FILE = "jdbc.properties";
private String driverClassName; // 默认驱动
private String url; // 默认URL
private String username;
private String password;
private boolean initialized = false;
public void init() {
System.out.println("JdbcConfig loaded:");
System.out.println("driverClassName: " + driverClassName);
System.out.println("url: " + url);
System.out.println("username: " + username);
}
// 加载配置
public void loadConfig() {
Properties props = new Properties();
String userDir = System.getProperty("user.dir");
File file = new File(userDir + File.separator + JDBC_CONFIG_FILE);
if(file.exists()){
try (InputStream is = Files.newInputStream(file.toPath())) {
props.load(is);
driverClassName = props.getProperty("jdbc.driverClassName");
url = props.getProperty("jdbc.url");
username = props.getProperty("jdbc.username");
password = props.getProperty("jdbc.password");
initialized = Boolean.parseBoolean(props.getProperty("jdbc.initialized", "false"));
} catch (IOException e) {
e.printStackTrace();
}
}
}
// 验证配置是否有效
public boolean isValid() {
return driverClassName != null && !driverClassName.isEmpty() &&
url != null && !url.isEmpty() &&
username != null && !username.isEmpty();
}
// 保存配置到属性文件
public void saveConfig(boolean persist) {
Properties props = new Properties();
String userDir = System.getProperty("user.dir");
props.setProperty("jdbc.driverClassName", driverClassName);
props.setProperty("jdbc.url", url);
props.setProperty("jdbc.username", username);
props.setProperty("jdbc.password", password);
initialized = persist;
props.setProperty("jdbc.initialized", String.valueOf(persist));
File file = new File(userDir + File.separator + JDBC_CONFIG_FILE);
try (OutputStream os = new FileOutputStream(file)) {
props.store(os, "Sys Configuration");
} catch (IOException e) {
e.printStackTrace();
}
}
// Getters and Setters
public String getDriverClassName() { return driverClassName; }
public void setDriverClassName(String driverClassName) { this.driverClassName = driverClassName; }
public String getUrl() { return url; }
public void setUrl(String url) { this.url = url; }
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 boolean isInitialized() { return initialized; }
public void setInitialized(boolean initialized) { this.initialized = initialized; }
}
2.2.3 ProxyDataSource.java
package com.luozc.config.jdbc;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;
/**
* 代理数据源，在数据库配置完成前处理请求
*/
@Component
public class ProxyDataSource implements DataSource {
private final JdbcConfig jdbcConfig;
private DataSource fallbackDataSource; // 新增备用数据源
public ProxyDataSource(JdbcConfig jdbcConfig) {
this.jdbcConfig = jdbcConfig;
this.fallbackDataSource = createFallbackDataSource();
}
private DataSource createFallbackDataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setDriverClassName("org.h2.Driver");
ds.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
ds.setUsername("sa");
ds.setPassword("");
ds.setInitialSize(1);
ds.setMinIdle(1);
ds.setMaxActive(5);
return ds;
}
@Override
public Connection getConnection() throws SQLException {
if (!jdbcConfig.isInitialized()) {
return fallbackDataSource.getConnection();
}
throw new UnsupportedOperationException("代理数据源不支持直接获取连接");
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return getConnection();
}
// 实现其他 DataSource 方法...
@Override public <T> T unwrap(Class<T> iface) throws SQLException { return null; }
@Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; }
@Override public PrintWriter getLogWriter() throws SQLException { return null; }
@Override public void setLogWriter(PrintWriter out) throws SQLException {}
@Override public void setLoginTimeout(int seconds) throws SQLException {}
@Override public int getLoginTimeout() throws SQLException { return 0; }
@Override public Logger getParentLogger() throws SQLFeatureNotSupportedException { return null; }
}
注意：这里的备用数据源，需要引入包
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>
3. 编辑jdbc配置，保存配置，根据配置创建新的数据源，并销毁就的数据源，同时改变新数据源的SqlSessionFactory
3.1放开登录拦截
代码如下
3.1.1 登录拦截器添加代码
// 获取JdbcConfig Bean
ServletContext context = request.getServletContext();
JdbcConfig jdbcConfig = (JdbcConfig) context.getAttribute("jdbcConfig");
// 如果配置为空，先加载配置
if (jdbcConfig == null) {
jdbcConfig = new JdbcConfig();
jdbcConfig.loadConfig();
if(jdbcConfig.isValid()){
context.setAttribute("jdbcConfig", jdbcConfig);
}
}
// 排除配置相关的URL
String requestURI = request.getRequestURI();
if (requestURI.contains("/setup") || requestURI.contains("/init-db")
|| requestURI.startsWith("/resources")) {
return true;
}
// 如果未初始化或配置无效，重定向到配置页面
if (!jdbcConfig.isInitialized() || !jdbcConfig.isValid()) {
response.sendRedirect(request.getContextPath() + "/setup");
return false;
}
3.2 控制器实现，和前端页面代码
3.2.1 SetupController.java
package com.luozc.config.jdbc;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.util.Base64;
import java.util.HashMap;
import java.util.Map;
@Controller
public class SetupController {
@Autowired
private JdbcConfig jdbcConfig;
@Autowired
private DatabaseInitService initService;
@Autowired
private ApplicationContext applicationContext; // 注入上下文
@Autowired
private SqlSessionFactory sqlSessionFactory; // 新增注入
@GetMapping("/setup")
public String showSetupForm(Model model) {
return "setup"; // 返回配置页面
}
@PostMapping("/setup")
public String saveConfig(
@RequestParam("driverClassName") String driverClassName,
@RequestParam("url") String url,
@RequestParam("username") String username,
@RequestParam("password") String password,
@RequestParam(name="sfInit",required = false) String sfInit,
Model model) {
// 保存数据库配置
jdbcConfig.setDriverClassName(driverClassName);
jdbcConfig.setUrl(url);
jdbcConfig.setUsername(username);
jdbcConfig.setPassword(password);
if(StringUtils.isNotBlank(sfInit)){
jdbcConfig.saveConfig(false);
return "redirect:/init-db";
}else{
jdbcConfig.saveConfig(true);
// 可选：手动刷新数据源
try {
// 获取当前数据源并尝试关闭（如果是DruidDataSource）
DataSource currentDataSource = (DataSource) applicationContext.getBean("dataSource");
if (currentDataSource instanceof DruidDataSource) {
((DruidDataSource) currentDataSource).close();
System.out.println("[数据源切换] 旧数据源已关闭。");
}
// 获取Bean工厂并移除旧的数据源定义
DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getAutowireCapableBeanFactory();
if (beanFactory.containsBeanDefinition("dataSource")) {
beanFactory.removeBeanDefinition("dataSource");
}
// 获取新的数据源实例
DataSource newDataSource = (DataSource) applicationContext.getBean("dataSource");
// 正确刷新 MyBatis 的 SqlSessionFactory，确保 TransactionFactory 不为空
TransactionFactory transactionFactory = sqlSessionFactory.getConfiguration().getEnvironment().getTransactionFactory();
if (transactionFactory == null) {
transactionFactory = new JdbcTransactionFactory(); // 使用默认事务工厂
}
sqlSessionFactory.getConfiguration().setEnvironment(
new org.apache.ibatis.mapping.Environment("default", transactionFactory, newDataSource)
);
System.out.println("[MyBatis] 环境已刷新，使用新数据源。");
// 验证新数据源连接
try (Connection conn = newDataSource.getConnection()) {
System.out.println("[数据源切换] 新数据源验证成功，当前数据库: " + conn.getCatalog());
}
System.out.println("[数据源切换] 新数据源已激活。");
} catch (Exception e) {
e.printStackTrace();
System.err.println("[数据源切换] 切换失败: " + e.getMessage());
}
return "redirect:/"; // 已初始化，重定向到首页
}
}
@GetMapping("/init-db")
public String showInitPage(Model model) {
if (jdbcConfig.isInitialized()) {
return "redirect:/"; // 已初始化，重定向到首页
}
return "init-db"; // 返回初始化页面
}
@PostMapping("/init-db")
public String initDatabase(Model model) {
try {
initService.initDatabase();
// 可选：手动刷新数据源
try {
// 获取当前数据源并尝试关闭（如果是DruidDataSource）
DataSource currentDataSource = (DataSource) applicationContext.getBean("dataSource");
if (currentDataSource instanceof DruidDataSource) {
((DruidDataSource) currentDataSource).close();
System.out.println("[数据源切换] 旧数据源已关闭。");
}
// 获取Bean工厂并移除旧的数据源定义
DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getAutowireCapableBeanFactory();
if (beanFactory.containsBeanDefinition("dataSource")) {
beanFactory.removeBeanDefinition("dataSource");
}
// 获取新的数据源实例
DataSource newDataSource = (DataSource) applicationContext.getBean("dataSource");
// 正确刷新 MyBatis 的 SqlSessionFactory，确保 TransactionFactory 不为空
TransactionFactory transactionFactory = sqlSessionFactory.getConfiguration().getEnvironment().getTransactionFactory();
if (transactionFactory == null) {
transactionFactory = new JdbcTransactionFactory(); // 使用默认事务工厂
}
sqlSessionFactory.getConfiguration().setEnvironment(
new org.apache.ibatis.mapping.Environment("default", transactionFactory, newDataSource)
);
System.out.println("[MyBatis] 环境已刷新，使用新数据源。");
// 验证新数据源连接
try (Connection conn = newDataSource.getConnection()) {
System.out.println("[数据源切换] 新数据源验证成功，当前数据库: " + conn.getCatalog());
}
System.out.println("[数据源切换] 新数据源已激活。");
} catch (Exception e) {
e.printStackTrace();
System.err.println("[数据源切换] 切换失败: " + e.getMessage());
}
model.addAttribute("message", "数据库初始化成功！请重启应用服务器。");
} catch (Exception e) {
e.printStackTrace();
model.addAttribute("error", "初始化失败: " + e.getMessage());
return "init-db";
}
return "message"; // 返回成功消息页面
}
}
3.2.2 DatabaseInitService.java
package com.luozc.config.jdbc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.stereotype.Service;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Service
public class DatabaseInitService {
@Autowired
private JdbcConfig jdbcConfig;
public void initDatabase() throws Exception {
// 加载数据库驱动
Class.forName(jdbcConfig.getDriverClassName());
// 建立数据库连接
try (Connection conn = DriverManager.getConnection(
jdbcConfig.getUrl(),
jdbcConfig.getUsername(),
jdbcConfig.getPassword())) {
// 执行初始化SQL脚本
executeSqlScript(conn, "/sql/init.sql");
List<String> sortedSqlFiles = getSortedSqlFiles();
for (int i = 0; i < sortedSqlFiles.size(); i++) {
// 执行初始化SQL脚本
executeSqlScript(conn, "/sql/"+sortedSqlFiles.get(i));
}
// 标记数据库已初始化
jdbcConfig.saveConfig(true);
}
}
private void executeSqlScript(Connection conn, String sqlResourcePath) throws IOException, SQLException {
InputStream is = getClass().getResourceAsStream(sqlResourcePath);
if (is == null) {
throw new IOException("SQL脚本文件未找到: " + sqlResourcePath);
}
try (BufferedReader reader = new BufferedReader(new InputStreamReader(is))) {
StringBuilder sqlStatement = new StringBuilder();
String line;
while ((line = reader.readLine()) != null) {
// 跳过注释
if (line.startsWith("--") || line.trim().isEmpty()) {
continue;
}
sqlStatement.append(line);
// 如果是完整的SQL语句
if (line.trim().endsWith(";")) {
String sql = sqlStatement.toString().replace(";", "");
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
}
sqlStatement.setLength(0); // 清空
}
}
}
}
// 修改getSortedSqlFiles方法，使用Spring的ResourceUtils读取资源文件
public List<String> getSortedSqlFiles() {
List<String> sqlFiles = new ArrayList<>();
try {
// 使用Spring的ResourceUtils获取资源目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] resources = resolver.getResources("classpath:sql/*.sql");
// 提取文件名并添加到列表
for (Resource resource : resources) {
if (resource.isReadable()) {
String filename = resource.getFilename();
if (filename != null && filename.endsWith(".sql")&&!filename.contains("init")) {
sqlFiles.add(filename);
}
}
}
// 按文件名中的数字排序
sqlFiles.sort((f1, f2) -> {
// 提取文件名中的数字部分
int num1 = extractNumber(f1);
int num2 = extractNumber(f2);
return Integer.compare(num1, num2);
});
} catch (Exception e) {
e.printStackTrace();
}
return sqlFiles;
}
// 从文件名中提取数字部分
private int extractNumber(String filename) {
// 去掉.sql扩展名
String name = filename.substring(0, filename.lastIndexOf("."));
// 提取数字后缀
int i = name.length() - 1;
while (i >= 0 && Character.isDigit(name.charAt(i))) {
i--;
}
// 如果文件名以数字结尾，则返回对应的数字，否则返回0
if (i < name.length() - 1) {
return Integer.parseInt(name.substring(i + 1));
} else {
return 0; // 对于没有数字后缀的文件，默认为0
}
}
}
jsp代码如下，注意放的目录为你配置spring mvc的上下文目录
3.2.3 setup.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>数据库配置</title>
<style>
body { font-family: Arial, sans-serif; }
.container { max-width: 800px; margin: 0 auto; padding: 10px; }
.form-group { margin-bottom: 15px; }
label { display: block; margin-bottom: 5px; }
input { width: 100%; padding: 8px; box-sizing: border-box; }
button { padding: 10px 20px; background-color: #4CAF50; color: white; border: none; }
</style>
</head>
<body>
<div class="container">
<h2>创建数据库用户sql示例:</h2>
<form >
<div class="form-group">
<label >查询表空间地址:</label>
<div style="color: red;">
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME, FILE_NAME;
</div>
</div>
<div class="form-group">
<label >创建永久表空间，初始大小500MB，自动扩展每次50MB，最大2GB:</label>
<div style="color: red;">
CREATE TABLESPACE xxx DATAFILE '/usr/local/oracle/oradata/orcl/xxx.dbf'
SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
</div>
</div>
<div class="form-group">
<label >创建用户并分配表空间:</label>
<div style="color: red;">
CREATE USER xxx IDENTIFIED BY xxx DEFAULT TABLESPACE xxx ;
</div>
</div>
<div class="form-group">
<label >允许用户无限制使用表空间:</label>
<div style="color: red;">
ALTER USER xxx QUOTA UNLIMITED ON xxx;
</div>
</div>
<div class="form-group">
<label >基础权限（登录、建表）:</label>
<div style="color: red;">
GRANT CREATE SESSION, CREATE TABLE TO xxx;
</div>
</div>
<div class="form-group">
<label >高级权限（建视图、序列、存储过程）:</label>
<div style="color: red;">
GRANT CREATE VIEW, CREATE SEQUENCE TO xxx;
</div>
</div>
</form>
<h2>数据库配置</h2>
<form action="setup" method="post">
<div class="form-group">
<label for="sfInit">是否初始化:</label>
<input type="checkbox" id="sfInit" name="sfInit" value="true" style="width: auto">
</div>
<div class="form-group">
<label for="driverClassName">driverClassName:</label>
<input type="text" id="driverClassName" name="driverClassName" required>
</div>
<div class="form-group">
<label for="url">JDBC URL:</label>
<input type="text" id="url" name="url" required>
</div>
<div class="form-group">
<label for="username">用户名:</label>
<input type="text" id="username" name="username" required>
</div>
<div class="form-group">
<label for="password">密码:</label>
<input type="password" id="password" name="password" required>
</div>
<button type="submit">保存配置</button>
</form>
</div>
</body>
</html>
3.2.4 init-db.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>初始化数据库</title>
<style>
body { font-family: Arial, sans-serif; }
.container { max-width: 500px; margin: 0 auto; padding: 20px; }
.error { color: red; }
</style>
</head>
<body>
<div class="container">
<h2>初始化数据库</h2>
<p>检测到新的数据库配置，需要初始化数据库。</p>
<% if (request.getAttribute("error") != null) { %>
<p class="error"><%= request.getAttribute("error") %></p>
<% } %>
<form action="init-db" method="post">
<button type="submit">开始初始化</button>
</form>
</div>
</body>
</html>
3.2.5 message.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>操作结果</title>
<style>
body { font-family: Arial, sans-serif; }
.container { max-width: 500px; margin: 0 auto; padding: 20px; }
</style>
</head>
<body>
<div class="container">
<h2>操作结果</h2>
<p><%= request.getAttribute("message") %></p>
<p>点击下面的链接返回首页：</p>
<a href="<%= request.getContextPath() %>/">首页</a>
</div>
</body>
</html>
4、正常访问系统
至此系统正常访问
注意：此系统可随时切换数据源，直接访问地址 /setup 重新配置即可
四、结语
此方法使用于老项目，并且不会做升级的老项目。示例代码不一定规范，各位可按照逻辑做调整。页面美化，可自行根据需要调整