1、导入架包:ant-1.8.4.jar
ojdbc5-11.1.0.6.0.jar
2、源码:
import java.io.BufferedReader;
import java.io.BufferedWriter;import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;
import org.apache.tools.ant.types.EnumeratedAttribute;
public class AutoExeSql {
public static void main(String[] args) {
String driverClassName = "";
String jdbcUrl = "";
String userName = "";
String userPassWord = "";
List<String> pathLists = new ArrayList<String>();
Properties pps = new Properties();
try {
pps.load(new FileInputStream("src\\module.properties"));
} catch (Exception e) {
e.printStackTrace();
}
Enumeration enum1 = pps.keys();// 得到配置文件的名字
while (enum1.hasMoreElements()) {
String strKey = (String) enum1.nextElement();
String strValue = pps.getProperty(strKey);
if ("jdbc.driverClassName".equals(strKey)) {
driverClassName = strValue;
} else if ("jdbc.url".equals(strKey)) {
jdbcUrl = strValue;
} else if ("jdbc.username".equals(strKey)) {
userName = strValue;
} else if ("jdbc.password".equals(strKey)) {
userPassWord = strValue;
} else {
pathLists.add(strValue);
}
}
for (String pathList : pathLists) {
File fileRoot = new File(pathList);
try {
getExecuteFile(fileRoot, driverClassName, jdbcUrl, userName, userPassWord);
} catch (Exception e) {
// 输出异常日志
e.printStackTrace();
writeExceptionFileString(e, fileRoot);
continue;
}
}
}
private static void getExecuteFile(File fileRoot, String driverClassName, String jdbcUrl, String userName, String userPassWord)
throws Exception {
if (fileRoot.isDirectory()) {
File[] filelist = fileRoot.listFiles();
for (File file : filelist) {
if (file.isDirectory()) {
// 递归执行文件夹下所有文件
getExecuteFile(file, driverClassName, jdbcUrl, userName, userPassWord);
} else {
System.out.println(file);
try {
getExecuteSql(file, driverClassName, jdbcUrl, userName, userPassWord);
} catch (Exception e) {
e.printStackTrace();
writeExceptionFileString(e, file);
continue;
}
}
}
} else {
getExecuteSql(fileRoot, driverClassName, jdbcUrl, userName, userPassWord);
}
}
private static void getExecuteSql(File file, String driverClassName, String jdbcUrl, String userName, String userPassWord)
throws Exception {
String fileString = getFileString(file);
// 删除/**/的注释
fileString = fileString.replaceAll("/\\*[\\s\\S]*?\\*/", "");
checkSql(fileString);
writeFileString(file, fileString);
SQLExec sqlExec = getSQLExec(driverClassName, jdbcUrl, userName, userPassWord);
// 要执行的脚本
sqlExec.setSrc(file);
// 有出错的语句该如何处理
sqlExec.setOnerror((SQLExec.OnError) (EnumeratedAttribute.getInstance(SQLExec.OnError.class, "abort")));
sqlExec.setPrint(true); // 设置是否输出
// 输出到文件 sql.out 中;不设置该属性,默认输出到控制台
sqlExec.setOutput(new File("src/sql.out"));
sqlExec.setProject(new Project()); // 要指定这个属性,不然会出错
sqlExec.execute();
}
// 设置数据库参数
private static SQLExec getSQLExec(String driverClassName, String jdbcUrl, String userName, String userPassWord) {
SQLExec sqlExec = new SQLExec();
sqlExec.setDriver(driverClassName);
sqlExec.setUrl(jdbcUrl);
sqlExec.setUserid(userName);
sqlExec.setPassword(userPassWord);
return sqlExec;
}
private static String getFileString(File file) {
String str = "";
try {
FileInputStream fis = new FileInputStream(file);
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
BufferedReader reader = new BufferedReader(isr);
String tempString = null;
// 一次读入一行,直到读入null为文件结束
while ((tempString = reader.readLine()) != null) {
str = str + tempString + "\n";
}
} catch (Exception e) {
e.printStackTrace();
}
return str;
}
private static void writeFileString(File file, String fileString) {
try {
FileWriter fw = new FileWriter(file);
BufferedWriter output = new BufferedWriter(fw);
output.write(fileString);
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
private static void writeExceptionFileString(Exception e, File exceptionfile) {
try {
String exceptionString = "\n\n" + new Date() + "----" + exceptionfile.getAbsolutePath() + "\n" + e.toString();
File file = new File("src/exception.txt");
FileWriter writer = new FileWriter(file, true);
writer.write(exceptionString);
writer.close();
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
private static void checkSql(String sqlString) {
// 打印出‘ -- ’的代码,Ant自动执行脚本不能识别
String replaceRegx = "\'[^\n|^\']*?--[^\n|^\']*?\'";
Pattern p = Pattern.compile(replaceRegx);
Matcher m = p.matcher(sqlString);
while (m.find()) {
String str = sqlString.substring(m.start(), m.end());
System.out.println(str + "\n--------");
}
}
}