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, 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语句中的创建存储过程和触发器语句
最新发布
本文深入探讨了HTTP缓存的工作原理，包括强缓存与协商缓存的区别，以及如何通过设置响应头来控制缓存行为，旨在帮助开发者更好地理解并利用HTTP缓存提高网站性能。
扫一扫
1156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
