package com.zte.runaqlscript;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
/**
* 调用 ant.jar 的 SQLExec 执行 SQL 脚本文件
*
* @author Unmi
*/
public class AntExecSql {
/**
* @param args
*/
public static final String driverClassName = "com.mysql.jdbc.Driver";
public static final String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&noAccessToProcedureBodies=true&characterEncoding=utf8";
// public static final String url = "jdbc:mysql://localhost:3306/test";
public static final String name = "root";
public static final String pwd = "root";
public static final String sqlFileName = "src/sql.sql";
public static void main(String[] args) {
try {
SQLExec sqlExec = new SQLExec();
// 设置数据库参数
sqlExec.setDriver(driverClassName);
sqlExec.setUrl(url);
sqlExec.setUserid(name);
sqlExec.setPassword(pwd);
// 要执行的脚本
sqlExec.setSrc(new File(sqlFileName));
// 有出错的语句该如何处理
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();
System.out.println("ok!!!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package com.unmi.db;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* 读取 SQL 脚本并执行
* @author Unmi
*/
public class SqlFileExecutor {
/**
* 读取 SQL 文件,获取 SQL 语句
* @param sqlFile SQL 脚本文件
* @return List<sql> 返回所有 SQL 语句的 List
* @throws Exception
*/
private List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
}
// Windows 下换行是 /r/n, Linux 下是 /n
String[] sqlArr = sqlSb.toString().split("(;//s*//r//n)|(;//s*//n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
/**
* 传入连接来执行 SQL 脚本文件,这样可与其外的数据库操作同处一个事物中
* @param conn 传入数据库连接
* @param sqlFile SQL 脚本文件
* @throws Exception
*/
public void execute(Connection conn, String sqlFile) throws Exception {
Statement stmt = null;
List<String> sqlList = loadSql(sqlFile);
stmt = conn.createStatement();
for (String sql : sqlList) {
stmt.addBatch(sql);
}
int[] rows = stmt.executeBatch();
System.out.println("Row count:" + Arrays.toString(rows));
}
/**
* 自建连接,独立事物中执行 SQL 文件
* @param sqlFile SQL 脚本文件
* @throws Exception
*/
public void execute(String sqlFile) throws Exception {
Connection conn = DBCenter.getConnection();
Statement stmt = null;
List<String> sqlList = loadSql(sqlFile);
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
for (String sql : sqlList) {
stmt.addBatch(sql);
}
int[] rows = stmt.executeBatch();
System.out.println("Row count:" + Arrays.toString(rows));
DBCenter.commit(conn);
} catch (Exception ex) {
DBCenter.rollback(conn);
throw ex;
} finally {
DBCenter.close(null, stmt, conn);
}
}
public static void main(String[] args) throws Exception {
List<String> sqlList = new SqlFileExecutor().loadSql(args[0]);
System.out.println("size:" + sqlList.size());
for (String sql : sqlList) {
System.out.println(sql);
}
}
}