java代码执行脚本

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);

}

}

}