1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.common.jdbc;
17
18 import java.io.BufferedReader;
19 import java.io.IOException;
20 import java.sql.Connection;
21 import java.sql.DatabaseMetaData;
22 import java.sql.SQLException;
23 import java.sql.Statement;
24 import java.util.ArrayList;
25 import java.util.Collections;
26 import java.util.List;
27
28 import javax.sql.DataSource;
29
30 import org.apache.commons.io.IOUtils;
31 import org.kuali.common.jdbc.context.JdbcContext;
32 import org.kuali.common.jdbc.context.ProgressContext;
33 import org.kuali.common.jdbc.context.SqlContext;
34 import org.kuali.common.jdbc.context.SqlSourceExecutionContext;
35 import org.kuali.common.util.SimpleFormatter;
36 import org.kuali.common.util.Str;
37 import org.slf4j.Logger;
38 import org.slf4j.LoggerFactory;
39 import org.springframework.jdbc.datasource.DataSourceUtils;
40
41 public class DefaultJdbcService implements JdbcService {
42
43 private static final Logger logger = LoggerFactory.getLogger(DefaultJdbcService.class);
44 protected SimpleFormatter formatter = new SimpleFormatter();
45
46 @Override
47 public JdbcMetaData getJdbcMetaData(DataSource dataSource) {
48 Connection conn = null;
49 try {
50 conn = DataSourceUtils.doGetConnection(dataSource);
51 DatabaseMetaData dbmd = conn.getMetaData();
52 return getJdbcMetaData(dbmd);
53 } catch (Exception e) {
54 throw new JdbcException(e);
55 } finally {
56 JdbcUtils.closeQuietly(dataSource, conn);
57 }
58 }
59
60 @Override
61 public SqlMetaData executeSql(JdbcContext context, String location) {
62 return executeSql(context, location, null);
63 }
64
65 @Override
66 public SqlMetaData executeSql(JdbcContext context, String location, String encoding) {
67 return executeSql(context, Collections.singletonList(location), encoding).get(0);
68 }
69
70 @Override
71 public SqlMetaData executeSqlString(JdbcContext context, String sql) {
72 return executeSqlStrings(context, Collections.singletonList(sql)).get(0);
73 }
74
75 protected SqlMetaDataList executeSources(JdbcContext context, List<SqlSource> sources) {
76 Connection conn = null;
77 Statement statement = null;
78 long count = 0;
79 long start = System.currentTimeMillis();
80 try {
81 conn = DataSourceUtils.doGetConnection(context.getDataSource());
82 boolean originalAutoCommitSetting = conn.getAutoCommit();
83 conn.setAutoCommit(false);
84 statement = conn.createStatement();
85 SqlMetaDataList smdl = new SqlMetaDataList();
86 logger.info("Executing SQL from {} sources", sources.size());
87 for (int i = 0; i < sources.size(); i++) {
88 SqlSource source = sources.get(i);
89 SqlSourceExecutionContext sec = getSourceSqlExecutionContext(context, conn, statement, source, count, i, sources.size());
90 SqlMetaData smd = executeSqlFromSource(sec);
91 smdl.add(smd);
92 count += smd.getCount();
93 afterExecuteSqlFromSource(sec, smd);
94 }
95 conn.setAutoCommit(originalAutoCommitSetting);
96 smdl.setExecutionTime(System.currentTimeMillis() - start);
97 smdl.setCount(count);
98 afterExecuteSources(context, conn);
99 return smdl;
100 } catch (Exception e) {
101 throw new JdbcException(e);
102 } finally {
103 JdbcUtils.closeQuietly(context.getDataSource(), conn, statement);
104 }
105 }
106
107 @Override
108 public SqlMetaData getMetaData(SqlContext context, String location) {
109 return getMetaData(context, Collections.singletonList(location), null).get(0);
110 }
111
112 @Override
113 public SqlMetaDataList getMetaData(SqlContext context, List<String> locations) {
114 return getMetaData(context, locations, null);
115 }
116
117 @Override
118 public SqlMetaData getMetaDataFromString(SqlContext context, String sql) {
119 return getMetaDataFromStrings(context, Collections.singletonList(sql)).get(0);
120 }
121
122 protected SqlMetaData getSqlMetaData(SqlContext context, SqlSource source) {
123 return getSqlMetaDataList(context, Collections.singletonList(source)).get(0);
124 }
125
126 protected SqlMetaDataList getSqlMetaDataList(SqlContext context, List<SqlSource> sources) {
127 SqlMetaDataList smdl = new SqlMetaDataList();
128 long count = 0;
129 for (int i = 0; i < sources.size(); i++) {
130 SqlSource source = sources.get(i);
131
132 SqlMetaData smd = getSqlMetaDataFromSource(context, source);
133 count += smd.getCount();
134 smdl.add(smd);
135 }
136 smdl.setCount(count);
137 return smdl;
138 }
139
140 protected SqlMetaData getSqlMetaDataFromSource(SqlContext context, SqlSource source) {
141 long count = 0;
142 BufferedReader in = null;
143 try {
144 in = JdbcUtils.getBufferedReader(source);
145 String sql = context.getReader().getSqlStatement(in);
146 while (sql != null) {
147 logger.debug("{} - {}", ++count, Str.flatten(sql));
148 sql = context.getReader().getSqlStatement(in);
149 }
150 SqlMetaData metadata = new SqlMetaData();
151 metadata.setCount(count);
152 metadata.setSource(source);
153 metadata.setReader(context.getReader());
154 return metadata;
155 } catch (IOException e) {
156 throw new JdbcException("Unexpected IO error", e);
157 } finally {
158 IOUtils.closeQuietly(in);
159 }
160 }
161
162 public List<String> getSqlStatements(SqlContext context, List<SqlSource> sources) {
163 List<String> sql = new ArrayList<String>();
164 for (SqlSource source : sources) {
165 sql.addAll(getSqlStatements(context, source));
166 }
167 return sql;
168 }
169
170 public List<String> getSqlStatements(SqlContext context, SqlSource source) {
171 List<String> list = new ArrayList<String>();
172 BufferedReader in = null;
173 try {
174 in = JdbcUtils.getBufferedReader(source);
175 String sql = context.getReader().getSqlStatement(in);
176 while (sql != null) {
177 list.add(sql);
178 sql = context.getReader().getSqlStatement(in);
179 }
180 return list;
181 } catch (IOException e) {
182 throw new JdbcException("Unexpected IO error", e);
183 } finally {
184 IOUtils.closeQuietly(in);
185 }
186 }
187
188 protected void logSource(String prefix, SqlSource source, int index, int size) {
189 SqlStringType type = source.getType();
190 String string = source.getString();
191 switch (type) {
192 case SQL:
193 logger.info((index + 1) + " of " + size + " - " + prefix + " SQL [{} characters]", formatter.getCount(string.length()));
194 return;
195 case LOCATION:
196 logger.info((index + 1) + " of " + size + " - " + prefix + " [{}]", string);
197 return;
198 default:
199 throw new IllegalArgumentException("SQL string type '" + type + "' is unknown");
200 }
201 }
202
203 protected ProgressContext getProgressContext(SqlSourceExecutionContext context) {
204 ProgressContext pc = new ProgressContext();
205 boolean showProgress = context.getJdbcContext().isShowProgress();
206 pc.setShowProgress(showProgress);
207 if (!showProgress) {
208 return pc;
209 }
210 SqlMetaData metaData = getSqlMetaData(context.getJdbcContext(), context.getSource());
211 int min = context.getJdbcContext().getShowProgressMin();
212 int divisor = context.getJdbcContext().getShowProgressDivisor();
213 long progress = Math.max(min, metaData.getCount() / divisor);
214 pc.setMin(min);
215 pc.setDivisor(divisor);
216 pc.setTotalCount(metaData.getCount());
217 pc.setProgress(progress);
218 return pc;
219 }
220
221 protected void beforeExecuteSqlFromSource(SqlSourceExecutionContext context, ProgressContext pc) {
222 int min = pc.getMin();
223 long count = pc.getTotalCount();
224 boolean showProgress = pc.isShowProgress() && count > min;
225 if (showProgress) {
226 logger.info("Executing {} SQL statements", formatter.getCount(pc.getTotalCount()));
227 }
228 }
229
230 protected SqlMetaData executeSqlFromSource(SqlSourceExecutionContext context) {
231 logSource("Executing", context.getSource(), context.getSourceIndex(), context.getSourcesCount());
232 ProgressContext pc = getProgressContext(context);
233 beforeExecuteSqlFromSource(context, pc);
234 long count = 0;
235 BufferedReader in = null;
236 try {
237 in = JdbcUtils.getBufferedReader(context.getSource());
238 SqlReader reader = context.getJdbcContext().getReader();
239 long start = System.currentTimeMillis();
240 String sql = reader.getSqlStatement(in);
241 while (sql != null) {
242 logger.debug("{} - [{}]", ++count, Str.flatten(sql));
243 executeSqlStatement(context, sql);
244 afterExecuteSqlStatement(context, count, pc);
245 sql = reader.getSqlStatement(in);
246 }
247 return getSqlMetaData(start, count, context);
248 } catch (Exception e) {
249 throw new JdbcException(e);
250 } finally {
251 IOUtils.closeQuietly(in);
252 }
253 }
254
255 protected void executeSqlStatement(SqlSourceExecutionContext context, String sql) throws SQLException {
256 try {
257 context.getStatement().execute(sql);
258 } catch (SQLException e) {
259 throw new SQLException("Error executing SQL [" + Str.flatten(sql) + "]", e);
260 }
261 }
262
263 protected void afterExecuteSources(JdbcContext context, Connection conn) throws SQLException {
264 if (CommitMode.PER_EXECUTION.equals(context.getCommitMode())) {
265 conn.commit();
266 }
267 }
268
269 protected void afterExecuteSqlFromSource(SqlSourceExecutionContext context, SqlMetaData metaData) throws SQLException {
270 if (CommitMode.PER_SOURCE.equals(context.getJdbcContext().getCommitMode())) {
271 context.getConnection().commit();
272 }
273 JdbcContext jdbcContext = context.getJdbcContext();
274 long count = metaData.getCount();
275 int min = jdbcContext.getShowProgressMin();
276 boolean showProgress = jdbcContext.isShowProgress() && count > min;
277 if (showProgress) {
278 String s = formatter.getCount(metaData.getCount());
279 logger.info("Executed {} of {} SQL statements", s, s);
280 }
281 }
282
283 protected void afterExecuteSqlStatement(SqlSourceExecutionContext context, long count, ProgressContext pc) throws SQLException {
284 if (CommitMode.PER_STATEMENT.equals(context.getJdbcContext().getCommitMode())) {
285 context.getConnection().commit();
286 }
287 if (pc.isShowProgress() && count % pc.getProgress() == 0) {
288 String count1 = formatter.getCount(count);
289 String count2 = formatter.getCount(pc.getTotalCount());
290 logger.info("Executed {} of {} SQL statements", count1, count2);
291 }
292 }
293
294 @Override
295 public SqlMetaData getMetaData(SqlContext context, String location, String encoding) {
296 return getMetaData(context, Collections.singletonList(location), encoding).get(0);
297 }
298
299 @Override
300 public SqlMetaDataList executeSql(JdbcContext context, List<String> locations) {
301 return executeSql(context, locations, null);
302 }
303
304 @Override
305 public SqlMetaDataList getMetaData(SqlContext context, List<String> locations, String encoding) {
306 List<SqlSource> sources = JdbcUtils.getSqlSources(locations, encoding);
307 return getSqlMetaDataList(context, sources);
308 }
309
310 @Override
311 public SqlMetaDataList getMetaDataFromStrings(SqlContext context, List<String> sql) {
312 List<SqlSource> sources = JdbcUtils.getSqlSourcesFromStrings(sql);
313 return getSqlMetaDataList(context, sources);
314 }
315
316 @Override
317 public SqlMetaDataList executeSql(JdbcContext context, List<String> locations, String encoding) {
318 List<SqlSource> sources = JdbcUtils.getSqlSources(locations, encoding);
319 return executeSources(context, sources);
320 }
321
322 @Override
323 public SqlMetaDataList executeSqlStrings(JdbcContext context, List<String> sql) {
324 List<SqlSource> sources = JdbcUtils.getSqlSourcesFromStrings(sql);
325 return executeSources(context, sources);
326 }
327
328 protected JdbcMetaData getJdbcMetaData(DatabaseMetaData dbmd) throws SQLException {
329 JdbcMetaData md = new JdbcMetaData();
330 md.setDatabaseProductName(dbmd.getDatabaseProductName());
331 md.setDatabaseProductVersion(dbmd.getDatabaseProductVersion());
332 md.setDriverName(dbmd.getDriverName());
333 md.setDriverVersion(dbmd.getDriverVersion());
334 md.setUrl(dbmd.getURL());
335 md.setUsername(dbmd.getUserName());
336 return md;
337 }
338
339 protected SqlMetaData getSqlMetaData(long start, long count, SqlSourceExecutionContext context) {
340 SqlMetaData ssm = new SqlMetaData();
341 ssm.setExecutionTime(System.currentTimeMillis() - start);
342 ssm.setCount(count);
343 ssm.setReader(context.getJdbcContext().getReader());
344 ssm.setSource(context.getSource());
345 return ssm;
346 }
347
348 protected SqlSourceExecutionContext getSourceSqlExecutionContext(JdbcContext context, Connection conn, Statement stmt, SqlSource source, long runningCount, int index,
349 int sourcesCount) {
350 SqlSourceExecutionContext sec = new SqlSourceExecutionContext();
351 sec.setSourceIndex(index);
352 sec.setJdbcContext(context);
353 sec.setConnection(conn);
354 sec.setStatement(stmt);
355 sec.setSource(source);
356 sec.setRunningCount(runningCount);
357 sec.setSourcesCount(sourcesCount);
358 return sec;
359 }
360
361 }