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
() == 1350
) { // 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 == 1050 || errorCode == 1051 || errorCode == 1060 ||
errorCode == 1061 || errorCode == 1062 || errorCode == 1217 ||
errorCode == 1304 || errorCode == 1350
) {
System
.out
.println
("数据库对象已存在，继续执行后续SQL"
);
} else {
logSQLException
(e
);
}
}
// 保留其他原有方法（executeSQL, executeQuery, executeUp
date, 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, UP
DATE, DELETE）
*/
public static int executeUp
date(String sql
) {
Connection conn =
null;
Statement stmt =
null;
try {
conn = DatabaseUtil
.getConnection
();
stmt = conn
.createStatement
();
return stmt
.executeUp
date(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 IN
FORMATION_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 IN
FORMATION_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 UP
DATE;
-- 检查读者是否存在
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 UP
DATE;
-- 检查图书是否存在和状态
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);
-- 更新图书状态
UP
DATE books SET status = '借出' WHERE book_id = v_book_id;
SET v_success_count = v_success_count + 1;
END IF;
END IF;
END WHILE;
-- 更新读者借阅数量（只增加成功借阅的数量）
UP
DATE 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>
本文深入探讨了DATE_FORMAT函数在处理日期格式转换时的常见错误，特别是年-月-日与年-日-月格式的区别。通过具体案例分析，指出了在Java环境中从String类型转换为Date类型时可能遇到的问题，并提供了正确的解决方案。
扫一扫
1万+
到【灌水乐园】发言
