package Util;
import java
.sql
.*;
import java
.io
.BufferedReader;
import java
.io
.InputStream;
import java
.io
.InputStreamReader;
import java
.nio
.charset
.StandardCharsets;
import java
.util
.ArrayList;
import java
.util
.List;
public class SQLFileExecutor {
/**
* 执行SQL文件，支持存储过程和触发器
*/
public static void executeSQLFile(String sqlFilePath) {
Connection conn = null;
Statement stmt = null;
try {
conn = DatabaseUtil
.getConnection();
stmt = conn
.createStatement();
// 读取SQL文件
InputStream inputStream = SQLFileExecutor
.class
.getClassLoader()
.getResourceAsStream(sqlFilePath);
if (inputStream == null) {
throw new RuntimeException("SQL文件未找到: " + sqlFilePath);
}
BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets
.UTF_8));
StringBuilder sqlBuilder = new StringBuilder();
String line;
int sqlCount = 0;
System
.out
.println("开始读取SQL文件: " + sqlFilePath);
// 读取文件内容
while ((line = reader
.readLine()) != null) {
// 跳过注释和空行
line = line
.trim();
if (line
.isEmpty()
|| line
.startsWith("--")
|| line
.startsWith("#")) {
continue;
}
sqlBuilder
.append(line)
.append("\n");
// 如果行以分号结束，表示一个完整的SQL语句
if (line
.endsWith(";")) {
String sql = sqlBuilder
.toString()
.trim();
if (!sql
.isEmpty()) {
sqlCount++;
try {
// 移除末尾的分号
if (sql
.endsWith(";")) {
sql = sql
.substring(0, sql
.length() - 1);
}
// 检查是否是存储过程或触发器
if (isProcedureOrTrigger(sql)) {
System
.out
.println("创建程序对象 [" + sqlCount + "]: " + getObjectName(sql));
executeWithDelimiter(conn, sql);
} else {
System
.out
.println("执行SQL [" + sqlCount + "]: " +
(sql
.length() > 100 ? sql
.substring(0, 100) + "
..." : sql));
stmt
.execute(sql);
}
System
.out
.println("SQL执行成功");
} catch (SQLException e) {
handleSQLException(e, sql);
}
}
sqlBuilder
.setLength(0); // 清空StringBuilder
}
}
reader
.close();
System
.out
.println("SQL文件执行完成: " + sqlFilePath + "，共执行 " + sqlCount + " 条SQL语句");
} catch (Exception e) {
System
.err
.println("执行SQL文件时出错: " + e
.getMessage());
e
.printStackTrace();
} finally {
DatabaseUtil
.closeResources(conn, stmt, null);
}
}
/**
* 使用自定义分隔符执行存储过程/触发器
*/
private static void executeWithDelimiter(Connection conn, String sql) throws SQLException {
// 临时改变分隔符来执行存储过程
try (Statement stmt = conn
.createStatement()) {
// 设置分隔符为 //
stmt
.execute("DELIMITER //");
stmt
.execute(sql + " //");
// 恢复分隔符为 ;
stmt
.execute("DELIMITER ;");
}
}
/**
* 专门处理存储过程和触发器文件的方法
*/
public static void executeProcedureFile(String sqlFilePath) {
Connection conn = null;
try {
conn = DatabaseUtil
.getConnection();
// 读取整个文件内容
String fileContent = readEntireFile(sqlFilePath);
if (fileContent == null) {
throw new RuntimeException("无法读取SQL文件: " + sqlFilePath);
}
System
.out
.println("开始执行存储过程文件: " + sqlFilePath);
// 分割SQL语句（处理DELIMITER命令）
List<String> sqlStatements = parseSQLStatements(fileContent);
int successCount = 0;
int totalCount = sqlStatements
.size();
for (int i = 0; i < sqlStatements
.size(); i++) {
String sql = sqlStatements
.get(i)
.trim();
if (sql
.isEmpty()) continue;
try {
System
.out
.println("执行语句 [" + (i + 1) + "/" + totalCount + "]: " +
(sql
.length() > 100 ? sql
.substring(0, 100) + "
..." : sql));
try (Statement stmt = conn
.createStatement()) {
stmt
.execute(sql);
successCount++;
System
.out
.println("语句执行成功");
}
} catch (SQLException e) {
System
.err
.println("语句执行失败: " + e
.getMessage());
// 如果是对象已存在的错误，继续执行
if (e
.getErrorCode() == 1304
|| e
.getErrorCode() == 13
50) { // PROCEDURE/TRIGGER already exists
System
.out
.println("程序对象已存在，继续执行");
successCount++;
} else {
logSQLException(e);
}
}
}
System
.out
.println("存储过程文件执行完成: " + sqlFilePath +
"，成功 " + successCount + "/" + totalCount + " 条语句");
} catch (Exception e) {
System
.err
.println("执行存储过程文件时出错: " + e
.getMessage());
e
.printStackTrace();
} finally {
DatabaseUtil
.closeResources(conn, null, null);
}
}
/**
* 读取整个文件内容
*/
private static String readEntireFile(String sqlFilePath) {
try {
InputStream inputStream = SQLFileExecutor
.class
.getClassLoader()
.getResourceAsStream(sqlFilePath);
if (inputStream == null) {
return null;
}
BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets
.UTF_8));
StringBuilder content = new StringBuilder();
String line;
while ((line = reader
.readLine()) != null) {
content
.append(line)
.append("\n");
}
reader
.close();
return content
.toString();
} catch (Exception e) {
System
.err
.println("读取文件失败: " + e
.getMessage());
return null;
}
}
/**
* 解析SQL语句，处理DELIMITER命令
*/
private static List<String> parseSQLStatements(String fileContent) {
List<String> statements = new ArrayList<>();
String[] lines = fileContent
.split("\n");
StringBuilder currentStatement = new StringBuilder();
String delimiter = ";";
boolean inDelimiterCommand = false;
for (String line : lines) {
line = line
.trim();
// 跳过注释
if (line
.startsWith("--")
|| line
.startsWith("#")
|| line
.isEmpty()) {
continue;
}
// 处理DELIMITER命令
if (line
.toUpperCase()
.startsWith("DELIMITER ")) {
if (!currentStatement
.toString()
.trim()
.isEmpty()) {
statements
.add(currentStatement
.toString());
currentStatement
.setLength(0);
}
delimiter = line
.substring(10)
.trim();
System
.out
.println("设置分隔符为: " + delimiter);
inDelimiterCommand = true;
continue;
}
currentStatement
.append(line)
.append("\n");
// 检查是否以当前分隔符结束
if (line
.endsWith(delimiter) && !inDelimiterCommand) {
String statement = currentStatement
.toString()
.trim();
if (!statement
.isEmpty()) {
// 移除末尾的分隔符
if (statement
.endsWith(delimiter)) {
statement = statement
.substring(0, statement
.length() - delimiter
.length());
}
statements
.add(statement);
}
currentStatement
.setLength(0);
}
inDelimiterCommand = false;
}
// 添加最后一个语句（如果没有以分隔符结束）
String lastStatement = currentStatement
.toString()
.trim();
if (!lastStatement
.isEmpty()) {
statements
.add(lastStatement);
}
return statements;
}
/**
* 检查是否为存储过程或触发器SQL
*/
private static boolean isProcedureOrTrigger(String sql) {
String upperSql = sql
.toUpperCase();
return upperSql
.contains("CREATE PROCEDURE")
||
upperSql
.contains("CREATE FUNCTION")
||
upperSql
.contains("CREATE TRIGGER")
||
upperSql
.contains("DROP PROCEDURE")
||
upperSql
.contains("DROP FUNCTION")
||
upperSql
.contains("DROP TRIGGER");
}
/**
* 从SQL中提取对象名称
*/
private static String getObjectName(String sql) {
String[] parts = sql
.split("\\s+");
for (int i = 0; i < parts
.length; i++) {
if (parts[i]
.equalsIgnoreCase("PROCEDURE")
||
parts[i]
.equalsIgnoreCase("FUNCTION")
||
parts[i]
.equalsIgnoreCase("TRIGGER")) {
if (i + 1 < parts
.length) {
return parts[i + 1]
.replace("`", "")
.split("\\(")[0];
}
}
}
return "未知对象";
}
/**
* 处理SQL异常
*/
private static void handleSQLException(SQLException e, String sql) {
System
.err
.println("SQL执行失败: " + e
.getMessage());
System
.err
.println("失败SQL: " + (sql
.length() > 200 ? sql
.substring(0, 200) + "
..." : sql));
// 如果是对象已存在的错误，可以继续执行
int errorCode = e
.getErrorCode();
if (errorCode == 10
50 || errorCode == 1051
|| errorCode == 1060
||
errorCode == 1061
|| errorCode == 1062
|| errorCode == 1217
||
errorCode == 1304
|| errorCode == 13
50) {
System
.out
.println("数据库对象已存在，继续执行后续SQL");
} else {
logSQLException(e);
}
}
// 保留其他原有方法（executeSQL, executeQuery, executeUpdate, executeBatch, checkTableExists等）
/**
* 执行单条SQL语句
*/
public static boolean executeSQL(String sql) {
Connection conn = null;
Statement stmt = null;
try {
conn = DatabaseUtil
.getConnection();
stmt = conn
.createStatement();
return stmt
.execute(sql);
} catch (SQLException e) {
System
.err
.println("执行SQL语句失败: " + sql);
logSQLException(e);
return false;
} finally {
DatabaseUtil
.closeResources(conn, stmt, null);
}
}
/**
* 执行查询SQL语句
*/
public static ResultSet executeQuery(String sql) {
Connection conn = null;
Statement stmt = null;
try {
conn = DatabaseUtil
.getConnection();
stmt = conn
.createStatement();
return stmt
.executeQuery(sql);
} catch (SQLException e) {
System
.err
.println("执行查询SQL失败: " + sql);
logSQLException(e);
return null;
}
// 注意：调用者需要手动关闭ResultSet和Statement
}
/**
* 执行更新SQL语句（INSERT, UPDATE, DELETE）
*/
public static int executeUpdate(String sql) {
Connection conn = null;
Statement stmt = null;
try {
conn = DatabaseUtil
.getConnection();
stmt = conn
.createStatement();
return stmt
.executeUpdate(sql);
} catch (SQLException e) {
System
.err
.println("执行更新SQL失败: " + sql);
logSQLException(e);
return -1;
} finally {
DatabaseUtil
.closeResources(conn, stmt, null);
}
}
/**
* 批量执行SQL语句
*/
public static int[] executeBatch(String[] sqlArray) {
Connection conn = null;
Statement stmt = null;
try {
conn = DatabaseUtil
.getConnection();
stmt = conn
.createStatement();
for (String sql : sqlArray) {
stmt
.addBatch(sql);
}
return stmt
.executeBatch();
} catch (SQLException e) {
System
.err
.println("批量执行SQL失败");
logSQLException(e);
return null;
} finally {
DatabaseUtil
.closeResources(conn, stmt, null);
}
}
/**
* 检查表是否存在
*/
public static boolean checkTableExists(String tableName) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DatabaseUtil
.getConnection();
// 先切换到目标数据库
conn
.setCatalog("Library_Management");
stmt = conn
.createStatement();
rs = stmt
.executeQuery("SHOW TABLES LIKE '" + tableName + "'");
return rs
.next();
} catch (Exception e) {
System
.err
.println("检查表存在性时出错: " + e
.getMessage());
return false;
} finally {
DatabaseUtil
.closeResources(conn, stmt, rs);
}
}
/**
* 检查存储过程是否存在
*/
public static boolean checkProcedureExists(String procedureName) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DatabaseUtil
.getConnection();
String sql = "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA
.ROUTINES " +
"WHERE ROUTINE_SCHEMA = ? AND ROUTINE_NAME = ? AND ROUTINE_TYPE = 'PROCEDURE'";
stmt = conn
.prepareStatement(sql);
stmt
.setString(1, "Library_management");
stmt
.setString(2, procedureName);
rs = stmt
.executeQuery();
return rs
.next();
} catch (Exception e) {
System
.err
.println("检查存储过程存在性时出错: " + e
.getMessage());
return false;
} finally {
DatabaseUtil
.closeResources(conn, stmt, rs);
}
}
/**
* 检查触发器是否存在
*/
public static boolean checkTriggerExists(String triggerName) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DatabaseUtil
.getConnection();
String sql = "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA
.TRIGGERS " +
"WHERE TRIGGER_SCHEMA = ? AND TRIGGER_NAME = ?";
stmt = conn
.prepareStatement(sql);
stmt
.setString(1, "Library_management");
stmt
.setString(2, triggerName);
rs = stmt
.executeQuery();
return rs
.next();
} catch (Exception e) {
System
.err
.println("检查触发器存在性时出错: " + e
.getMessage());
return false;
} finally {
DatabaseUtil
.closeResources(conn, stmt, rs);
}
}
/**
* 记录SQL异常详细信息
*/
private static void logSQLException(SQLException e) {
System
.err
.println("SQL错误: " + e
.getMessage());
System
.err
.println("SQL状态: " + e
.getSQLState());
System
.err
.println("错误代码: " + e
.getErrorCode());
}
}
优化代码要求可以实现sql语句中的创建存储过程和触发器语句
a
.sql文件
-- 创建借阅图书的存储过程（MySQL 8
.0 格式规范）
DROP PROCEDURE IF EXISTS BorrowBooks;
DELIMITER // -- 分隔符后无空格，单独一行
CREATE PROCEDURE BorrowBooks(
IN p_reader_id INT, -- 必传：读者ID
IN p_book_ids TEXT, -- 必传：图书ID列表，逗号分隔，如 '1,2,3,4'
IN p_borrow_date DATETIME, -- 可选：借阅日期（NULL则使用当前时间）
OUT p_result VARCHAR(
500) -- 输出：操作结果
)
BEGIN
-- 声明变量
DECLARE v_book_status ENUM('可借','借出','维修中','下架','遗失');
DECLARE v_reader_status ENUM('正常','异常','冻结','注销');
DECLARE v_current_borrow_count INT;
DECLARE v_max_borrow_count INT;
DECLARE v_due_date DATETIME;
DECLARE v_actual_borrow_date DATETIME;
DECLARE v_book_id INT;
-- 用于循环处理图书ID的变量
DECLARE v_book_ids_temp TEXT;
DECLARE v_current_book_id TEXT;
DECLARE v_comma_pos INT;
DECLARE v_success_count INT DEFAULT 0;
DECLARE v_fail_count INT DEFAULT 0;
DECLARE v_error_messages TEXT DEFAULT '';
DECLARE v_total_books INT DEFAULT 0;
DECLARE v_remaining_quota INT DEFAULT 0; -- 剩余借阅额度
DECLARE v_can_borrow_count INT DEFAULT 0; -- 实际可借数量
DECLARE v_processed_count INT DEFAULT 0; -- 已处理图书数量
-- 异常处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET p_result = CONCAT('错误: ', @text);
END;
-- 开始事务
START TRANSACTION;
-- 确定实际借阅日期：如果传入NULL则使用当前时间
SET v_actual_borrow_date = COALESCE(p_borrow_date, NOW());
-- 1
. 锁定并查询读者信息
SELECT status, current_borrow_count, max_borrow_count
INTO v_reader_status, v_current_borrow_count, v_max_borrow_count
FROM readers
WHERE reader_id = p_reader_id
FOR UPDATE;
-- 检查读者是否存在
IF v_reader_status IS NULL THEN
SET p_result = '借阅失败: 读者不存在';
ROLLBACK;
ELSE
-- 检查读者状态
IF v_reader_status != '正常' THEN
SET p_result = CONCAT('借阅失败: 读者账户状态为"', v_reader_status, '"');
ROLLBACK;
ELSE
-- 计算剩余借阅额度和要借阅的图书总数
SET v_remaining_quota = v_max_borrow_count - v_current_borrow_count;
SET v_total_books = (LENGTH(p_book_ids) - LENGTH(REPLACE(p_book_ids, ',', '')) + 1);
-- 如果剩余额度为0，直接返回
IF v_remaining_quota <= 0 THEN
SET p_result = CONCAT('借阅失败: 借阅数量已达上限。当前借阅:', v_current_borrow_count, '/最大借阅:', v_max_borrow_count);
ROLLBACK;
ELSE
-- 计算实际可借数量（取剩余额度和请求数量的较小值）
SET v_can_borrow_count = LEAST(v_remaining_quota, v_total_books);
-- 初始化循环变量
SET v_book_ids_temp = p_book_ids;
SET v_success_count = 0;
SET v_processed_count = 0;
-- 循环处理每个图书ID（最多处理v_can_borrow_count本）
WHILE LENGTH(v_book_ids_temp) > 0 AND v_processed_count < v_total_books DO
-- 获取第一个图书ID
SET v_comma_pos = LOCATE(',', v_book_ids_temp);
IF v_comma_pos = 0 THEN
SET v_current_book_id = v_book_ids_temp;
SET v_book_ids_temp = '';
ELSE
SET v_current_book_id = SUBSTRING(v_book_ids_temp, 1, v_comma_pos - 1);
SET v_book_ids_temp = SUBSTRING(v_book_ids_temp, v_comma_pos + 1);
END IF;
-- 转换为整数并去除可能的空格
SET v_book_id = CAST(TRIM(v_current_book_id) AS UNSIGNED);
SET v_processed_count = v_processed_count + 1;
-- 如果已经达到可借数量上限，记录额度不足
IF v_success_count >= v_can_borrow_count THEN
SET v_fail_count = v_fail_count + 1;
SET v_error_messages = CONCAT(v_error_messages, ' 图书', v_book_id, '因借阅额度不足未能借阅;');
ELSE
-- 锁定并查询当前图书状态
SELECT status INTO v_book_status
FROM books
WHERE book_id = v_book_id
FOR UPDATE;
-- 检查图书是否存在和状态
IF v_book_status IS NULL THEN
SET v_fail_count = v_fail_count + 1;
SET v_error_messages = CONCAT(v_error_messages, ' 图书', v_book_id, '不存在;');
ELSEIF v_book_status != '可借' THEN
SET v_fail_count = v_fail_count + 1;
SET v_error_messages = CONCAT(v_error_messages, ' 图书', v_book_id, '状态为"', v_book_status, '";');
ELSE
-- 计算应还日期（基于实际借阅日期）
SET v_due_date = DATE_FORMAT(
DATE_ADD(v_actual_borrow_date, INTERVAL 30
DAY),
'%Y-%m-%d 18:00:00'
);
-- 插入借阅记录
INSERT INTO borrow_records (reader_id, book_id, borrow_date, due_date)
VALUES (p_reader_id, v_book_id, v_actual_borrow_date, v_due_date);
-- 更新图书状态
UPDATE books SET status = '借出' WHERE book_id = v_book_id;
SET v_success_count = v_success_count + 1;
END IF;
END IF;
END WHILE;
-- 更新读者借阅数量（只增加成功借阅的数量）
UPDATE readers
SET current_borrow_count = current_borrow_count + v_success_count
WHERE reader_id = p_reader_id;
-- 提交事务
COMMIT;
-- 构造结果信息
IF v_fail_count = 0 THEN
SET p_result = CONCAT('借阅成功: 成功借阅', v_success_count, '本书，应还日期:', DATE_FORMAT(v_due_date, '%Y-%m-%d'));
ELSE
SET p_result = CONCAT('部分成功: 成功借阅', v_success_count, '本，失败', v_fail_count, '本。失败原因:', v_error_messages);
END IF;
END IF;
END IF;
END IF;
END // -- 关键：END和 //在同一行，//后无空格（避免工具类无法识别）
DELIMITER ; -- 切回默认分隔符，单独一行
可以考虑使用成熟的SQL解析库
<dependency>
<groupId>com
.github
.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4
.5</version>
</dependency>
扫一扫
1443
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
