转自：
1 package com.unmi.db; 2 3 import java.io.FileInputStream; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.Statement; 7 import java.util.ArrayList; 8 import java.util.Arrays; 9 import java.util.List; 10 11 /** 12 * 读取 SQL 脚本并执行 13 * @author Unmi 14 */ 15 public class SqlFileExecutor { 16 17 /** 18 * 读取 SQL 文件，获取 SQL 语句 19 * @param sqlFile SQL 脚本文件 20 * @return List 返回所有 SQL 语句的 List 21 * @throws Exception 22 */ 23 private List loadSql(String sqlFile) throws Exception { 24 List sqlList = new ArrayList (); 25 26 try { 27 InputStream sqlFileIn = new FileInputStream(sqlFile); 28 29 StringBuffer sqlSb = new StringBuffer(); 30 byte[] buff = new byte[1024]; 31 int byteRead = 0; 32 while ((byteRead = sqlFileIn.read(buff)) != -1) { 33 sqlSb.append(new String(buff, 0, byteRead)); 34 } 35 36 // Windows 下换行是 /r/n, Linux 下是 /n 37 String[] sqlArr = sqlSb.toString().split("(;//s*//r//n)|(;//s*//n)"); 38 for (int i = 0; i < sqlArr.length; i++) { 39 String sql = sqlArr[i].replaceAll("--.*", "").trim(); 40 if (!sql.equals("")) { 41 sqlList.add(sql); 42 } 43 } 44 return sqlList; 45 } catch (Exception ex) { 46 throw new Exception(ex.getMessage()); 47 } 48 } 49 50 /** 51 * 传入连接来执行 SQL 脚本文件，这样可与其外的数据库操作同处一个事物中 52 * @param conn 传入数据库连接 53 * @param sqlFile SQL 脚本文件 54 * @throws Exception 55 */ 56 public void execute(Connection conn, String sqlFile) throws Exception { 57 Statement stmt = null; 58 List sqlList = loadSql(sqlFile); 59 stmt = conn.createStatement(); 60 for (String sql : sqlList) { 61 stmt.addBatch(sql); 62 } 63 int[] rows = stmt.executeBatch(); 64 System.out.println("Row count:" + Arrays.toString(rows)); 65 } 66 67 /** 68 * 自建连接，独立事物中执行 SQL 文件 69 * @param sqlFile SQL 脚本文件 70 * @throws Exception 71 */ 72 public void execute(String sqlFile) throws Exception { 73 Connection conn = DBCenter.getConnection(); 74 Statement stmt = null; 75 List sqlList = loadSql(sqlFile); 76 try { 77 conn.setAutoCommit(false); 78 stmt = conn.createStatement(); 79 for (String sql : sqlList) { 80 stmt.addBatch(sql); 81 } 82 int[] rows = stmt.executeBatch(); 83 System.out.println("Row count:" + Arrays.toString(rows)); 84 DBCenter.commit(conn); 85 } catch (Exception ex) { 86 DBCenter.rollback(conn); 87 throw ex; 88 } finally { 89 DBCenter.close(null, stmt, conn); 90 } 91 } 92 93 public static void main(String[] args) throws Exception { 94 List sqlList = new SqlFileExecutor().loadSql(args[0]); 95 System.out.println("size:" + sqlList.size()); 96 for (String sql : sqlList) { 97 System.out.println(sql); 98 } 99 }100 }