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