001: /*
002: * Copyright 2000-2005 by Mark A. Kobold
003: *
004: * The contents of this file are subject to the Mozilla Public License Version
005: * 1.1 (the "License"); you may not use this file except in compliance with the
006: * License. You may obtain a copy of the License at http://www.mozilla.org/MPL/
007: *
008: * Software distributed under the License is distributed on an "AS IS" basis,
009: * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
010: * the specific language governing rights and limitations under the License.
011: *
012: * The Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
013: *
014: * The Initial Developer of the Original Code is Markus A. Kobold.
015: *
016: * Portions created by Mark A. Kobold are Copyright (C)
017: * 2000-2005 Mark A. Kobold. All Rights Reserved.
018: *
019: * Contributor(s):
020: * Mark A. Kobold <mkobold@isqlviewer.com>.
021: *
022: * Contributor(s): all the names of the contributors are added in the source
023: * code where applicable.
024: *
025: * If you didn't download this code from the following link, you should check
026: * if you aren't using an obsolete version: http://isql.sourceforge.net/
027: */
028: package org.isqlviewer.util;
029:
030: import java.lang.management.ManagementFactory;
031: import java.lang.management.MemoryMXBean;
032: import java.lang.management.ThreadMXBean;
033: import java.sql.Connection;
034: import java.sql.DatabaseMetaData;
035: import java.sql.ResultSet;
036: import java.sql.SQLException;
037: import java.sql.Statement;
038: import java.text.NumberFormat;
039: import java.text.ParseException;
040: import java.util.Date;
041: import java.util.concurrent.TimeUnit;
042:
043: import org.isqlviewer.JdbcCommandLogger;
044: import org.isqlviewer.history.CommandType;
045: import org.isqlviewer.history.HistoricalCommand;
046: import org.isqlviewer.sql.ConnectionProfile;
047: import org.isqlviewer.sql.JdbcService;
048: import org.isqlviewer.sql.JdbcUtilities;
049: import org.isqlviewer.sql.ResultSetRenderer;
050:
051: /**
052: * TODO Add QueryExecutor Overview JavaDoc.
053: * <p>
054: * TODO Finish this class.
055: *
056: * @author Mark A. Kobold <mkobold at isqlviewer dot com>
057: * @version 1.0
058: */
059: public class QueryExecutor extends LoggableObject implements Runnable {
060:
061: private static final String DELIM_START = "${";
062: private static final char DELIM_STOP = '}';
063: private static final int DELIM_START_LEN = 2;
064: private static final int DELIM_STOP_LEN = 1;
065: private static final String RESOURCE_BUNDLE = "org.isqlviewer.util.ResourceBundle";
066: private static final LocalMessages messages = new LocalMessages(
067: RESOURCE_BUNDLE);
068:
069: private ResultSetRenderer renderer = null;
070: private String statement = null;
071: private JdbcService service = null;
072:
073: public QueryExecutor(JdbcService service,
074: ResultSetRenderer renderer, String statement) {
075:
076: if (renderer == null) {
077: this .renderer = new NullResultSetViewer();
078: } else {
079: this .renderer = renderer;
080: }
081: this .service = service;
082: this .statement = statement;
083: }
084:
085: public void run() {
086:
087: if (service == null) {
088: return;
089: }
090:
091: Connection connection = null;
092: try {
093: try {
094: connection = service.getConnection();
095: connection.clearWarnings();
096: } catch (SQLException sqle) {
097: renderer.handleSQLException(sqle);
098: return;
099: }
100:
101: String localID = Long.toHexString(System
102: .currentTimeMillis());
103: StringTokenizer st = new StringTokenizer(statement, ";",
104: false);
105: st.setQuotesEnabled(true);
106:
107: String[] statements = st.toArray();
108: JdbcCommandLogger commandLogger = service
109: .getCommandLogger();
110: HistoricalCommand executionHistory = new HistoricalCommand();
111: executionHistory.setCommandText("");
112: executionHistory.setQueryTime(new Date());
113: executionHistory.setTransactionId(System
114: .currentTimeMillis());
115: executionHistory.setService(service.getName());
116: executionHistory.setType(CommandType.BATCH_QUERY);
117: boolean logBatch = statements.length > 1;
118: for (int i = 0; i < statements.length; i++) {
119: renderer.initialize();
120: String userQuery = statements[i].trim();
121: if (userQuery.length() > 0) {
122: try {
123: String substitutedQuery = doVariableSubstitutions(userQuery);
124: String nativeSQL = getNativeSql(connection,
125: substitutedQuery);
126: Date queryTime = new Date();
127: doQuery(connection, localID, nativeSQL);
128: if (logBatch && commandLogger != null) {
129: HistoricalCommand subCommand = new HistoricalCommand();
130: subCommand.setCommandText(userQuery);
131: subCommand.setQueryTime(queryTime);
132: subCommand.setService(service.getName());
133: subCommand
134: .setTransactionId(executionHistory
135: .getTransactionId());
136: getTypeForQuery(subCommand, userQuery);
137: executionHistory.addSubcommand(subCommand);
138: } else {
139: logCommandHistory(commandLogger, userQuery,
140: queryTime);
141: }
142: } catch (ParseException e) {
143: error("ERR:", e);
144: } catch (SQLException sqle) {
145: Object[] arguments = new Object[3];
146: arguments[0] = sqle.getMessage();
147: arguments[1] = Integer.toString(sqle
148: .getErrorCode());
149: arguments[2] = sqle.getSQLState();
150: error(messages.format(
151: "queryexecutor.sql_execption",
152: arguments));
153: renderer.handleSQLException(sqle);
154: } catch (Exception e) {
155: error("ERR:", e);
156: } finally {
157: renderer.finalizeStatement(localID);
158: }
159: }
160: }
161: if (commandLogger != null && logBatch) {
162: commandLogger.logCommand(executionHistory);
163: }
164: } finally {
165: info(messages.getMessage("queryexecutor.finished"));
166: System.gc();
167: }
168:
169: }
170:
171: private void logCommandHistory(JdbcCommandLogger commandLogger,
172: String userQuery, Date queryTime) {
173:
174: if (commandLogger != null) {
175: HistoricalCommand command = new HistoricalCommand();
176: command.setCommandText(userQuery);
177: command.setQueryTime(queryTime);
178: command.setService(service.getName());
179: getTypeForQuery(command, userQuery);
180: commandLogger.logCommand(command);
181: }
182: }
183:
184: private void getTypeForQuery(HistoricalCommand command,
185: String userQuery) {
186:
187: StringBuilder builder = new StringBuilder("");
188: for (int i = 0; i < userQuery.length(); i++) {
189: char c = userQuery.charAt(i);
190: if (Character.isWhitespace(c)) {
191: break;
192: }
193: builder.append(c);
194: }
195:
196: String firstWord = builder.toString().toUpperCase();
197: if ("UPDATE".equals(firstWord)) {
198: command.setType(CommandType.UPDATE_QUERY);
199: } else if ("INSERT".equalsIgnoreCase(firstWord)) {
200: command.setType(CommandType.INSERT_QUERY);
201: } else if ("DELETE".equalsIgnoreCase(firstWord)) {
202: command.setType(CommandType.DELETE_QUERY);
203: } else if ("CREATE".equalsIgnoreCase(firstWord)) {
204: command.setType(CommandType.INSERT_QUERY);
205: } else if ("DROP".equalsIgnoreCase(firstWord)) {
206: command.setType(CommandType.DELETE_QUERY);
207: } else {
208: command.setType(CommandType.QUERY);
209: }
210: }
211:
212: private void doQuery(Connection connection, String localID,
213: String nativeSQL) throws SQLException {
214:
215: boolean results = false;
216:
217: ConnectionProfile profile = service.getProfile();
218: DatabaseMetaData jdbcMetaData = connection.getMetaData();
219: Statement sqlStatement = connection.createStatement();
220: renderer.statementInitialized(localID, sqlStatement);
221:
222: MemoryMXBean memoryMBean = ManagementFactory.getMemoryMXBean();
223: ThreadMXBean threadMBean = ManagementFactory.getThreadMXBean();
224: long cpuTime = 0;
225: long vmMemory = 0;
226:
227: boolean returnKeys = false;
228: boolean generateKeys = profile.isResultsetKeys();
229: boolean supportsMultiResults = jdbcMetaData
230: .supportsMultipleResultSets();
231: // if escape processing is enable it now otherwise leave with driver default //
232: if (!profile.isEscapeProcessing()) {
233: sqlStatement.setEscapeProcessing(profile
234: .isEscapeProcessing());
235: }
236:
237: if (generateKeys) {
238: if (jdbcMetaData.supportsGetGeneratedKeys()) {
239: info(messages
240: .format("queryexecutor.generated_keys_query",
241: nativeSQL));
242: cpuTime = threadMBean.getCurrentThreadUserTime();
243: vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
244: results = sqlStatement.execute(nativeSQL,
245: Statement.RETURN_GENERATED_KEYS);
246: vmMemory = vmMemory
247: - memoryMBean.getHeapMemoryUsage().getUsed();
248: cpuTime = threadMBean.getCurrentThreadUserTime()
249: - cpuTime;
250: returnKeys = true;
251: } else {
252: info(messages
253: .format(
254: "queryexecutor.generated_keys_query_not_supported",
255: nativeSQL));
256: cpuTime = threadMBean.getCurrentThreadUserTime();
257: vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
258: results = sqlStatement.execute(nativeSQL,
259: Statement.NO_GENERATED_KEYS);
260: vmMemory = vmMemory
261: - memoryMBean.getHeapMemoryUsage().getUsed();
262: cpuTime = threadMBean.getCurrentThreadUserTime()
263: - cpuTime;
264: }
265: } else {
266: info(messages.format("queryexecutor.executing_query",
267: nativeSQL));
268: cpuTime = threadMBean.getCurrentThreadUserTime();
269: vmMemory = memoryMBean.getHeapMemoryUsage().getUsed();
270: results = sqlStatement.execute(nativeSQL);
271: vmMemory = vmMemory
272: - memoryMBean.getHeapMemoryUsage().getUsed();
273: cpuTime = threadMBean.getCurrentThreadUserTime() - cpuTime;
274: }
275:
276: String execTime = StringUtilities
277: .getFullHumanReadableTime(TimeUnit.NANOSECONDS
278: .toMillis(cpuTime));
279: info(messages.format("queryexecutor.execution_time", execTime));
280: info(messages.format("queryexecutor.execution_memory_profile",
281: StringUtilities.getHumanReadableSize(vmMemory)));
282: int rowsAffected = -1;
283: if (!results) {
284: rowsAffected = sqlStatement.getUpdateCount();
285: NumberFormat nf = NumberFormat.getIntegerInstance();
286: info(messages.format("queryexecutor.rows_affected", nf
287: .format(rowsAffected)));
288: } else {
289: rowsAffected = -1;
290: }
291:
292: int rsCount = 0;
293:
294: do {
295: ResultSet rs = null;
296: if (results) {
297: rs = sqlStatement.getResultSet();
298: try {
299: cpuTime = threadMBean.getCurrentThreadUserTime();
300: vmMemory = memoryMBean.getHeapMemoryUsage()
301: .getUsed();
302: long rowsProcessed = renderer.processResultSet(
303: localID, rs, nativeSQL, rsCount);
304: vmMemory = vmMemory
305: - memoryMBean.getHeapMemoryUsage()
306: .getUsed();
307: cpuTime = threadMBean.getCurrentThreadUserTime()
308: - cpuTime;
309:
310: String time = StringUtilities
311: .getFullHumanReadableTime(TimeUnit.NANOSECONDS
312: .toMillis(cpuTime));
313: String memory = StringUtilities
314: .getHumanReadableSize(vmMemory);
315: info(messages.format(
316: "queryexecutor.records_processed",
317: new Long(rowsProcessed)));
318: info(messages.format("queryexecutor.render_time",
319: time));
320: info(messages.format(
321: "queryexecutor.render_memory_profile",
322: memory));
323: } catch (Throwable error) {
324: error(
325: messages
326: .format("queryexecutor.general_render_error"),
327: error);
328: }
329: } else if (rowsAffected >= 0) {
330: try {
331: renderer.processRowUpdates(localID, rowsAffected,
332: nativeSQL);
333: } catch (Throwable error) {
334: error(
335: messages
336: .format("queryexecutor.general_render_error"),
337: error);
338: }
339: }
340:
341: if (!renderer.isCancelled()) {
342: if (rs != null) {
343: renderer.recieveResultsetWarnings(rs, rs
344: .getWarnings());
345: }
346: renderer.recieveStatementWarnings(sqlStatement,
347: sqlStatement.getWarnings());
348: if (renderer.supportsGeneratedKeys() && returnKeys) {
349: try {
350: rs = sqlStatement.getGeneratedKeys();
351: renderer.processGeneratedKeys(localID, rs,
352: nativeSQL);
353: } catch (Throwable error) {
354: error(messages.format(
355: "queryexecutor.general_render_error",
356: error));
357: } finally {
358: try {
359: if (rs != null) {
360: rs.close();
361: }
362: } catch (Throwable ignored) {
363: }
364: }
365: }
366: }
367:
368: if (!supportsMultiResults) {
369: boolean autoClose = !renderer
370: .supportsUpdateableResultSets();
371: if (!JdbcUtilities.isUpdatable(rs) || autoClose) {
372: try {
373: if (rs != null) {
374: // logEvent(BasicUtilities.getString(resourceBundle, "ResultSet_Closing"));
375: rs.close();
376: }
377: } catch (SQLException sqle) {
378: sqle.printStackTrace();
379: // HandleSQLException(sqle);
380: }
381: } else {
382: // signals member value for closing statement after
383: // this method.
384: // closeStatement = false;
385: // logEvent(BasicUtilities.getString(resourceBundle, "ResultSet_Closing_Skip"));
386: }
387: break;
388: }
389: results = sqlStatement.getMoreResults();
390: if (rs != null) {
391: if (results) {
392: try {
393: rs.close();
394: } catch (SQLException ignored) {
395: }
396: }
397: }
398:
399: if (!results) {
400: try {
401: rowsAffected = sqlStatement.getUpdateCount();
402: } catch (SQLException sqle) {
403: rowsAffected = -1;
404: } catch (Throwable t) {
405: rowsAffected = -1;
406: }
407: } else {
408: rowsAffected = -1;
409: }
410: } while (results || rowsAffected != -1);
411: }
412:
413: private String getNativeSql(Connection connection, String query) {
414:
415: try {
416: return connection.nativeSQL((query == null ? "" : query));
417: } catch (SQLException ignored) {
418: return (query == null ? "" : query);
419: }
420: }
421:
422: private String doVariableSubstitutions(final String source)
423: throws ParseException {
424:
425: StringBuffer sbuf = new StringBuffer();
426: int i = 0;
427: int j, k;
428: while (true) {
429: j = source.indexOf(DELIM_START, i);
430: if (j == -1) {
431: // no more variables
432: if (i == 0) { // this is a simple string
433: return source;
434: }
435: // add the tail string which contains no variables and return the result.
436: sbuf.append(source.substring(i, source.length()));
437: return sbuf.toString();
438: }
439: sbuf.append(source.substring(i, j));
440: k = source.indexOf(DELIM_STOP, j);
441: if (k == -1) {
442: throw new ParseException(messages
443: .format("StringUtilities.bad_variable_format"),
444: j);
445: }
446:
447: j += DELIM_START_LEN;
448: String key = source.substring(j, k);
449: // first try in System properties
450: String replacement = renderer.substituteVariable(key);
451: // then try props parameter
452: if (replacement != null) {
453: // Do variable substitution on the replacement string
454: // such that we can solve "Hello ${x2}" as "Hello p1"
455: // the where the properties are
456: // x1=p1
457: // x2=${x1}
458: String recursiveReplacement = doVariableSubstitutions(replacement);
459: sbuf.append(recursiveReplacement);
460: } else {
461: sbuf.append(DELIM_START);
462: sbuf.append(key);
463: sbuf.append(DELIM_STOP);
464: }
465: i = k + DELIM_STOP_LEN;
466: }
467: }
468:
469: }
|