Java 执行 SQL 脚本文件

Java 执行 SQL 脚本文件 收藏
是拷贝的别人的，以备学习

view plaincopy to clipboardprint?
01.package com.unmi.db;
02.
03.import java.io.FileInputStream;
04.import java.io.InputStream;
05.import java.sql.Connection;
06.import java.sql.Statement;
07.import java.util.ArrayList;
08.import java.util.Arrays;
09.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.}


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hongmin118/archive/2009/09/24/4588941.aspx