从文件中读取SQL语句并批量执行（从ant的taskdefs中SQLExec类提取代码）
简述：同时使用spring的jdbcTemplate和ant的taskdefs来执行SQL语句，但是这样不能把所有操作放在一个事务中。所以把ant的taskdefs中解析SQL语句的代码提取出来，再使用jdbcTemplate来指执行。
现有批量执行文件中的SQL语句可以使用Ant的TaskDefs中的SQLExec类。如：
http://blog.csdn.net/scorpio3k/article/details/4024983 （个人觉得完全没有必要去把这个类提取出来，不值得花那个时间，一个JAR包也就1M到2M。直接用就行。）
http://ant.apache.org/manual/Tasks/sql.html
单独使用这个方法时没问题，但是如果和其他事务集成时就有问题了。
比如，有的SQL语句放在XML里，有的放在.SQL文件里 ，总之很多SQL语句放在不同的地方。而且这些SQL都需要放一个事务里执行。
假定使用spring的DataSourceTransactionManager，spring会管理创建并管理连接。
由于SQLExec是自己管理创建并管理连接的，而spring也是这样。同时使用的时候，其实是通过他们各自的连接更新数据库，所以并不能放到一个数据库事务里。
注：我的理解为，事务通过数据库连接来管理，不同的连接不能放在同一个事务里。还没有做深入研究，现在看来是这样。有待深入研究。
那么实现同时使用jdbcTemplate和SQLExec来执行SQL，并且要把所有操作放在一个事务里是不现实的。
既然SQLExec能以文件为单位执行SQL，那么里面一定有读取文件中所有SQL的方法。把这个方法提取出来，然后再用jdbcTemplate执行SQL，就可以把所有SQL放在一个事务里了。
代码如下：
1 public class SQLExtractor {
2
3 private static SQLExtractor instance;
4
5 /**
6 * Encoding to use when reading SQL statements from a file
7 */
8 private String encoding = null;
9
10 /**
11 * Keep the format of a sql block?
12 */
13 private boolean keepformat = false;
14
15 /**
16 * delimiters must match in case and whitespace is significant.
17 */
18 private boolean strictDelimiterMatching = true;
19
20 /**
21 * SQL Statement delimiter
22 */
23 private String delimiter = ";";
24
25 /**
26 * keep the delimiter in sql or not
27 */
28 private boolean keepDelimiter = true;
29
30 /**
31 * The delimiter type indicating whether the delimiter will only be recognized on a line by itself
32 */
33 private String delimiterType = DelimiterType.NORMAL;
34
35 /**
36 * delimiters we support, "normal" and "row"
37 */
38 public static class DelimiterType extends EnumeratedAttribute {
39 /** The enumerated strings */
40 public static final String NORMAL = "normal", ROW = "row";
41
42 /** @return the enumerated strings */
43 public String[] getValues() {
44 return new String[] { NORMAL, ROW };
45 }
46 }
47
48 private SQLExtractor() {
49
50 }
51
52 public static synchronized SQLExtractor getInstance() {
53 if (instance == null) {
54 instance = new SQLExtractor();
55 }
56 return instance;
57 }
58
59 public List<String> extract(InputStream is) throws IOException {
60 Reader reader = null;
61 try {
62 reader = (encoding == null) ? new InputStreamReader(is) : new InputStreamReader(is, encoding);
63 return extract(reader);
64 } finally {
65 FileUtils.close(is);
66 FileUtils.close(reader);
67 }
68 }
69
70 public List<String> extract(Reader reader) throws IOException {
71 List<String> sqlList = new ArrayList<String>();
72 StringBuffer sql = new StringBuffer();
73 String line;
74
75 BufferedReader in = new BufferedReader(reader);
76
77 while ((line = in.readLine()) != null) {
78 if (!keepformat) {
79 line = line.trim();
80 }
81 // if (expandProperties) {
82 // line = getProject().replaceProperties(line);
83 // }
84
85 if (!keepformat) {
86 if (line.startsWith("//")) {
87 continue;
88 }
89 if (line.startsWith("--")) {
90 continue;
91 }
92 StringTokenizer st = new StringTokenizer(line);
93 if (st.hasMoreTokens()) {
94 String token = st.nextToken();
95 if ("REM".equalsIgnoreCase(token)) {
96 continue;
97 }
98 }
99 }
100
101 sql.append(keepformat ? "\n" : " ").append(line);
102
103 // SQL defines "--" as a comment to EOL
104 // and in Oracle it may contain a hint
105 // so we cannot just remove it, instead we must end it
106 if (!keepformat && line.indexOf("--") >= 0) {
107 sql.append("\n");
108 }
109 int lastDelimPos = lastDelimiterPosition(sql, line);
110 if (lastDelimPos > -1) {
111 // execSQL(sql.substring(0, lastDelimPos), out);
112 String sqlStr = keepDelimiter ? sql.toString() : eliminateLastColon(sql.toString());
113 sqlList.add(sqlStr);
114 sql.replace(0, sql.length(), "");
115 }
116 }
117
118 // Catch any statements not followed by ;
119 if (sql.length() > 0) {
120 // execSQL(sql.toString(), out);
121 sqlList.add(sql.toString());
122 }
123
124 return sqlList;
125 }
126
127 private String eliminateLastColon(String sql) {
128 sql = sql.trim();
129 if (org.apache.commons.lang.StringUtils.endsWith(sql, ";")) {
130 return sql.substring(0, sql.length() - 1);
131 }
132 return sql;
133 }
134
135 private int lastDelimiterPosition(StringBuffer buf, String currentLine) {
136 if (strictDelimiterMatching) {
137 if ((delimiterType.equals(DelimiterType.NORMAL) && StringUtils.endsWith(buf, delimiter))
138 || (delimiterType.equals(DelimiterType.ROW) && currentLine.equals(delimiter))) {
139 return buf.length() - delimiter.length();
140 }
141 // no match
142 return -1;
143 } else {
144 String d = delimiter.trim().toLowerCase(Locale.ENGLISH);
145 if (delimiterType.equals(DelimiterType.NORMAL)) {
146 // still trying to avoid wasteful copying, see
147 // StringUtils.endsWith
148 int endIndex = delimiter.length() - 1;
149 int bufferIndex = buf.length() - 1;
150 while (bufferIndex >= 0 && Character.isWhitespace(buf.charAt(bufferIndex))) {
151 --bufferIndex;
152 }
153 if (bufferIndex < endIndex) {
154 return -1;
155 }
156 while (endIndex >= 0) {
157 if (buf.substring(bufferIndex, bufferIndex + 1).toLowerCase(Locale.ENGLISH).charAt(0) != d.charAt(endIndex)) {
158 return -1;
159 }
160 bufferIndex--;
161 endIndex--;
162 }
163 return bufferIndex + 1;
164 } else {
165 return currentLine.trim().toLowerCase(Locale.ENGLISH).equals(d) ? buf.length() - currentLine.length() : -1;
166 }
167 }
168 }
169
170 // --------------------------------------------------------------------------------
171 //
172 // getter and setter
173 //
174 // --------------------------------------------------------------------------------
175
176 public String getEncoding() {
177 return encoding;
178 }
179
180 public void setEncoding(String encoding) {
181 this.encoding = encoding;
182 }
183
184 public boolean isKeepformat() {
185 return keepformat;
186 }
187
188 public void setKeepformat(boolean keepformat) {
189 this.keepformat = keepformat;
190 }
191
192 public boolean isStrictDelimiterMatching() {
193 return strictDelimiterMatching;
194 }
195
196 public void setStrictDelimiterMatching(boolean strictDelimiterMatching) {
197 this.strictDelimiterMatching = strictDelimiterMatching;
198 }
199
200 public String getDelimiter() {
201 return delimiter;
202 }
203
204 public void setDelimiter(String delimiter) {
205 this.delimiter = delimiter;
206 }
207
208 public boolean isKeepDelimiter() {
209 return keepDelimiter;
210 }
211
212 public void setKeepDelimiter(boolean keepDelimiter) {
213 this.keepDelimiter = keepDelimiter;
214 }
215
2
3 private static SQLExtractor instance;
4
5 /**
6 * Encoding to use when reading SQL statements from a file
7 */
8 private String encoding = null;
9
10 /**
11 * Keep the format of a sql block?
12 */
13 private boolean keepformat = false;
14
15 /**
16 * delimiters must match in case and whitespace is significant.
17 */
18 private boolean strictDelimiterMatching = true;
19
20 /**
21 * SQL Statement delimiter
22 */
23 private String delimiter = ";";
24
25 /**
26 * keep the delimiter in sql or not
27 */
28 private boolean keepDelimiter = true;
29
30 /**
31 * The delimiter type indicating whether the delimiter will only be recognized on a line by itself
32 */
33 private String delimiterType = DelimiterType.NORMAL;
34
35 /**
36 * delimiters we support, "normal" and "row"
37 */
38 public static class DelimiterType extends EnumeratedAttribute {
39 /** The enumerated strings */
40 public static final String NORMAL = "normal", ROW = "row";
41
42 /** @return the enumerated strings */
43 public String[] getValues() {
44 return new String[] { NORMAL, ROW };
45 }
46 }
47
48 private SQLExtractor() {
49
50 }
51
52 public static synchronized SQLExtractor getInstance() {
53 if (instance == null) {
54 instance = new SQLExtractor();
55 }
56 return instance;
57 }
58
59 public List<String> extract(InputStream is) throws IOException {
60 Reader reader = null;
61 try {
62 reader = (encoding == null) ? new InputStreamReader(is) : new InputStreamReader(is, encoding);
63 return extract(reader);
64 } finally {
65 FileUtils.close(is);
66 FileUtils.close(reader);
67 }
68 }
69
70 public List<String> extract(Reader reader) throws IOException {
71 List<String> sqlList = new ArrayList<String>();
72 StringBuffer sql = new StringBuffer();
73 String line;
74
75 BufferedReader in = new BufferedReader(reader);
76
77 while ((line = in.readLine()) != null) {
78 if (!keepformat) {
79 line = line.trim();
80 }
81 // if (expandProperties) {
82 // line = getProject().replaceProperties(line);
83 // }
84
85 if (!keepformat) {
86 if (line.startsWith("//")) {
87 continue;
88 }
89 if (line.startsWith("--")) {
90 continue;
91 }
92 StringTokenizer st = new StringTokenizer(line);
93 if (st.hasMoreTokens()) {
94 String token = st.nextToken();
95 if ("REM".equalsIgnoreCase(token)) {
96 continue;
97 }
98 }
99 }
100
101 sql.append(keepformat ? "\n" : " ").append(line);
102
103 // SQL defines "--" as a comment to EOL
104 // and in Oracle it may contain a hint
105 // so we cannot just remove it, instead we must end it
106 if (!keepformat && line.indexOf("--") >= 0) {
107 sql.append("\n");
108 }
109 int lastDelimPos = lastDelimiterPosition(sql, line);
110 if (lastDelimPos > -1) {
111 // execSQL(sql.substring(0, lastDelimPos), out);
112 String sqlStr = keepDelimiter ? sql.toString() : eliminateLastColon(sql.toString());
113 sqlList.add(sqlStr);
114 sql.replace(0, sql.length(), "");
115 }
116 }
117
118 // Catch any statements not followed by ;
119 if (sql.length() > 0) {
120 // execSQL(sql.toString(), out);
121 sqlList.add(sql.toString());
122 }
123
124 return sqlList;
125 }
126
127 private String eliminateLastColon(String sql) {
128 sql = sql.trim();
129 if (org.apache.commons.lang.StringUtils.endsWith(sql, ";")) {
130 return sql.substring(0, sql.length() - 1);
131 }
132 return sql;
133 }
134
135 private int lastDelimiterPosition(StringBuffer buf, String currentLine) {
136 if (strictDelimiterMatching) {
137 if ((delimiterType.equals(DelimiterType.NORMAL) && StringUtils.endsWith(buf, delimiter))
138 || (delimiterType.equals(DelimiterType.ROW) && currentLine.equals(delimiter))) {
139 return buf.length() - delimiter.length();
140 }
141 // no match
142 return -1;
143 } else {
144 String d = delimiter.trim().toLowerCase(Locale.ENGLISH);
145 if (delimiterType.equals(DelimiterType.NORMAL)) {
146 // still trying to avoid wasteful copying, see
147 // StringUtils.endsWith
148 int endIndex = delimiter.length() - 1;
149 int bufferIndex = buf.length() - 1;
150 while (bufferIndex >= 0 && Character.isWhitespace(buf.charAt(bufferIndex))) {
151 --bufferIndex;
152 }
153 if (bufferIndex < endIndex) {
154 return -1;
155 }
156 while (endIndex >= 0) {
157 if (buf.substring(bufferIndex, bufferIndex + 1).toLowerCase(Locale.ENGLISH).charAt(0) != d.charAt(endIndex)) {
158 return -1;
159 }
160 bufferIndex--;
161 endIndex--;
162 }
163 return bufferIndex + 1;
164 } else {
165 return currentLine.trim().toLowerCase(Locale.ENGLISH).equals(d) ? buf.length() - currentLine.length() : -1;
166 }
167 }
168 }
169
170 // --------------------------------------------------------------------------------
171 //
172 // getter and setter
173 //
174 // --------------------------------------------------------------------------------
175
176 public String getEncoding() {
177 return encoding;
178 }
179
180 public void setEncoding(String encoding) {
181 this.encoding = encoding;
182 }
183
184 public boolean isKeepformat() {
185 return keepformat;
186 }
187
188 public void setKeepformat(boolean keepformat) {
189 this.keepformat = keepformat;
190 }
191
192 public boolean isStrictDelimiterMatching() {
193 return strictDelimiterMatching;
194 }
195
196 public void setStrictDelimiterMatching(boolean strictDelimiterMatching) {
197 this.strictDelimiterMatching = strictDelimiterMatching;
198 }
199
200 public String getDelimiter() {
201 return delimiter;
202 }
203
204 public void setDelimiter(String delimiter) {
205 this.delimiter = delimiter;
206 }
207
208 public boolean isKeepDelimiter() {
209 return keepDelimiter;
210 }
211
212 public void setKeepDelimiter(boolean keepDelimiter) {
213 this.keepDelimiter = keepDelimiter;
214 }
215
216 }
注：这里添加了一个选项，keepDelimite。SQL语句中是否保存结束标记。
比如有的数据库驱动执行结尾带";"的SQL语句会报错（我碰到的是ojdbc6的oracle thin驱动连接oracle 11g r2），必须要把";"去掉。
测试代码：
public static void main(String[] args) throws Exception {
ClassPathResource res = new ClassPathResource("test.sql");
SQLExtractor ext = SQLExtractor.getInstance();
List<String> list = ext.extract(res.getInputStream());
for (String s : list) {
System.out.println(s);
}
ClassPathResource res = new ClassPathResource("test.sql");
SQLExtractor ext = SQLExtractor.getInstance();
List<String> list = ext.extract(res.getInputStream());
for (String s : list) {
System.out.println(s);
}
}
批量执行SQL的代码（性能有待优化）：
/**
* batch execute sql
*
* @param sqlList sql statement list
* @param batch sql count in each batch
*/
private void batchUpdate(List<String> sqlList, int batch) throws Exception {
List<String> sqls = new LinkedList<String>();
int index = 0;
int i = 0;
while (index < sqlList.size()) {
if (i == batch) {
jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
i = 0;
sqls = new LinkedList<String>();
}
sqls.add(sqlList.get(index));
if (index == sqlList.size() - 1) {
jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
}
++index;
++i;
}
* batch execute sql
*
* @param sqlList sql statement list
* @param batch sql count in each batch
*/
private void batchUpdate(List<String> sqlList, int batch) throws Exception {
List<String> sqls = new LinkedList<String>();
int index = 0;
int i = 0;
while (index < sqlList.size()) {
if (i == batch) {
jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
i = 0;
sqls = new LinkedList<String>();
}
sqls.add(sqlList.get(index));
if (index == sqlList.size() - 1) {
jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
}
++index;
++i;
}
}
所有文件：SQLExtractor.zip
