SQL脚本文件执行器

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

处于项目需求，需要能够批量执行SQL脚本文件，需要由前台页面操作触发执行。

查找相关资料，发现 Ant 提供了 SQLExec 组件可以支持SQL文件的执行，测试效果不错。

以下是对 SQLExec 封装后支持批量执行的执行器组件。

package com.ge.digital.spo.sql.execute.ant;
import java.io.File;import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;
import org.apache.tools.ant.types.EnumeratedAttribute; public class AntSqlRunner { public void runSqlScript() {
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "mysql";
String targetSqlFolder = "/home/ca6417/upload/RecoverySQL/"; // 存放SQL脚本的目录
String logOutputFolder = "/home/ca6417/upload/RecoverySQL/"; // 执行结果输出目录，出错的时候才会生成错误报告
runSqlScriptFiles(driverClass, url, username, password, targetSqlFolder, logOutputFolder);
} public static void runSqlScriptFiles(String driverClass, String url, String username, String password, String targetSqlFolder,
String logOutputFolder) {
SQLExec sqlExec = new SQLExec();
sqlExec.setDriver(driverClass);
sqlExec.setUrl(url);
sqlExec.setUserid(username);
sqlExec.setPassword(password);
// 如果执行过程有语句出错时如何处理，可以选择继续执行或停止.
sqlExec.setOnerror((SQLExec.OnError) (EnumeratedAttribute.getInstance(SQLExec.OnError.class, "stop")));
sqlExec.setPrint(true);
sqlExec.setProject(new Project());
String targetSqlFile = targetSqlFolder;
if(!new File(targetSqlFolder).isDirectory()) {
targetSqlFolder = new File(targetSqlFolder).getParentFile().getAbsolutePath();
}
runSqlScript(sqlExec, targetSqlFile, targetSqlFolder, logOutputFolder);
} private static void runSqlScript(SQLExec sqlExec, String targetFile, String targetSqlFolder, String logOutputFolder) {
File file = new File(targetFile);
if(file.isDirectory()) {
// 如果是目录，遍历执行所有脚本
for(File f :file.listFiles()) {
String fileStr = f.getAbsolutePath();
runSqlScript(sqlExec, fileStr, file.getAbsolutePath(), logOutputFolder);
}
} else {
// 如果是脚本文件，执行SQL脚本
String targetSqlFile = targetFile;
String logOutputFile = getLogOutputFile(logOutputFolder, targetSqlFolder, targetSqlFile);
runSqlScriptFile(sqlExec, targetSqlFile, logOutputFile);
}
} private static void runSqlScriptFile(SQLExec sqlExec, String targetSqlFile, String logOutputFile) {
sqlExec.setSrc(new File(targetSqlFile));
sqlExec.setOutput(new File(logOutputFile));
sqlExec.execute();
System.out.println(targetSqlFile + " execute success.");
} private static String getLogOutputFile(String logOutputFolder, String targetSqlFolder, String targetSqlFile) {
String logFileName = targetSqlFile.substring(, targetSqlFile.indexOf(".")) + ".txt";
logFileName = logFileName.replace(targetSqlFolder, logOutputFolder);
return logFileName;
}
}