转自:http://blog.csdn.net/hongmin118/article/details/4588941
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> 返回所有 SQL 语句的 List
21 * @throws Exception
22 */
23 private List<String> loadSql(String sqlFile) throws Exception {
24 List<String> sqlList = new ArrayList<String>();
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<String> 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<String> 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<String> 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 }