1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.jumpmind.symmetric.db;
22
23 import java.io.BufferedReader;
24 import java.io.InputStreamReader;
25 import java.net.URL;
26 import java.sql.Connection;
27 import java.sql.SQLException;
28 import java.sql.Statement;
29 import java.util.Map;
30
31 import javax.sql.DataSource;
32
33 import org.apache.commons.logging.Log;
34 import org.apache.commons.logging.LogFactory;
35 import org.springframework.dao.DataAccessException;
36 import org.springframework.jdbc.core.ConnectionCallback;
37 import org.springframework.jdbc.core.JdbcTemplate;
38
39 /***
40 * This class is for running SQL scripts against a DataSource.
41 */
42 public class SqlScript {
43
44 static final String COMMENT_CHARS_1 = "--";
45 static final String COMMENT_CHARS_2 = "#";
46
47 static final Log logger = LogFactory.getLog(SqlScript.class);
48
49 public final static char QUERY_ENDS = ';';
50
51 private char delimiter = QUERY_ENDS;
52
53 private URL script;
54
55 private DataSource dataSource;
56
57 private int commitRate = 10000;
58
59 private boolean failOnError = true;
60
61 private Map<String, String> replacementTokens;
62
63 public SqlScript(URL url, DataSource ds) {
64 this(url, ds, true, QUERY_ENDS, null);
65 }
66
67 public SqlScript(URL url, DataSource ds, boolean failOnError) {
68 this(url, ds, failOnError, QUERY_ENDS, null);
69 }
70
71 public SqlScript(URL url, DataSource ds, char delimiter) {
72 this(url, ds, true, delimiter, null);
73 }
74
75 public SqlScript(URL url, DataSource ds, boolean failOnError, char delimiter, Map<String, String> replacementTokens) {
76 this.script = url;
77 this.dataSource = ds;
78 this.failOnError = failOnError;
79 this.delimiter = delimiter;
80 this.replacementTokens = replacementTokens;
81 }
82
83 private void closeQuietly(Statement stmt) {
84 if (stmt != null) {
85 try {
86 stmt.close();
87 } catch (SQLException e) {
88 logger.error(e, e);
89 }
90 }
91 }
92
93 protected String replaceTokens(String original) {
94 if (this.replacementTokens != null) {
95 for (Object key : this.replacementTokens.keySet()) {
96 original = original.replaceAll("//%" + key + "//%", this.replacementTokens.get((String) key));
97 }
98 }
99 return original;
100 }
101
102 public void execute() {
103 JdbcTemplate template = new JdbcTemplate(this.dataSource);
104 template.execute(new ConnectionCallback() {
105 public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
106 Statement st = null;
107 String fileName = script.getFile();
108 fileName = fileName.substring(fileName.lastIndexOf("/") + 1);
109 logger.info("Running " + fileName);
110 int lineCount = 0;
111
112 try {
113 connection.setAutoCommit(false);
114 st = connection.createStatement();
115 BufferedReader reader = new BufferedReader(new InputStreamReader(script.openStream()));
116 String line;
117 StringBuilder sql = new StringBuilder();
118 int count = 0;
119 int notFoundCount = 0;
120 while ((line = reader.readLine()) != null) {
121 lineCount++;
122 line = trimComments(line);
123 if (line.length() > 0) {
124 if (checkStatementEnds(line)) {
125 sql.append(" ");
126 sql.append(line.substring(0, line.lastIndexOf(delimiter)));
127 if (logger.isDebugEnabled()) {
128 logger.debug("query->" + sql);
129 }
130 try {
131 st.execute(replaceTokens(sql.toString()));
132 count++;
133 if (count % commitRate == 0) {
134 connection.commit();
135 }
136 } catch (SQLException e) {
137 if (failOnError) {
138 logger.error(sql.toString() + " failed to execute.", e);
139 throw e;
140 } else {
141 if (e.getErrorCode() != 942 && e.getErrorCode() != 2289) {
142 logger.warn(e.getMessage() + ": " + sql.toString());
143 } else if (sql.toString().toLowerCase().startsWith("drop")) {
144 notFoundCount++;
145 }
146 }
147 }
148 sql.setLength(0);
149 } else {
150 sql.append(" ");
151 sql.append(line);
152 }
153 }
154 }
155
156 connection.commit();
157
158 logger.info("Ran " + count + " sql statements in " + fileName);
159 if (notFoundCount > 0) {
160 logger.info("Could not drop a total of " + notFoundCount
161 + " database object because they were not found");
162 }
163 } catch (Exception e) {
164 logger.info("Error on line " + lineCount + " of " + fileName);
165 throw new RuntimeException(e);
166 } finally {
167 closeQuietly(st);
168 }
169 return null;
170 }
171 });
172 }
173
174 private String trimComments(String line) {
175 int index = line.indexOf(COMMENT_CHARS_1);
176 if (index >= 0) {
177 line = line.substring(0, index);
178 }
179 index = line.indexOf(COMMENT_CHARS_2);
180 if (index >= 0) {
181 line = line.substring(0, index);
182 }
183 return line.trim();
184 }
185
186 private boolean checkStatementEnds(String s) {
187 return s.trim().endsWith("" + delimiter);
188 }
189
190 public int getCommitRate() {
191 return commitRate;
192 }
193
194 public void setCommitRate(int commitRate) {
195 this.commitRate = commitRate;
196 }
197 }