001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * The Original Software is NetBeans. The Initial Developer of the Original
027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2006 Sun
028: * Microsystems, Inc. All Rights Reserved.
029: *
030: * If you wish your version of this file to be governed by only the CDDL
031: * or only the GPL Version 2, indicate your decision by adding
032: * "[Contributor] elects to include this software in this distribution
033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
034: * single choice of license, a recipient has the option to distribute
035: * your version of this file under either the CDDL, the GPL Version 2 or
036: * to extend the choice of license to its licensees as provided above.
037: * However, if you add GPL Version 2 code and therefore, elected the GPL
038: * Version 2 license, then the option applies only if the new code is
039: * made subject to such option by the copyright holder.
040: */
041:
042: package org.netbeans.modules.db.sql.execute;
043:
044: import java.sql.Connection;
045: import java.sql.DatabaseMetaData;
046: import java.sql.PreparedStatement;
047: import java.sql.ResultSet;
048: import java.sql.SQLException;
049: import java.sql.Statement;
050: import java.util.ArrayList;
051: import java.util.Collections;
052: import java.util.Iterator;
053: import java.util.List;
054: import java.util.logging.Level;
055: import java.util.logging.Logger;
056: import org.netbeans.api.progress.ProgressHandle;
057:
058: /**
059: * Support class for executing SQL statements.
060: *
061: * @author Andrei Badea
062: */
063: public final class SQLExecuteHelper {
064:
065: private static final Logger LOGGER = Logger
066: .getLogger(SQLExecuteHelper.class.getName());
067: private static final boolean LOG = LOGGER.isLoggable(Level.FINE);
068:
069: /**
070: * Executes a SQL string, possibly containing multiple statements. Returns the execution
071: * result, but only if the string contained a single statement.
072: *
073: * @param sqlScript the SQL script to execute. If it contains multiple lines
074: * they have to be delimited by '\n' characters.
075: */
076: public static SQLExecutionResults execute(String sqlScript,
077: int startOffset, int endOffset, Connection conn,
078: ProgressHandle progressHandle,
079: SQLExecutionLogger executionLogger) {
080:
081: boolean cancelled = false;
082:
083: List<StatementInfo> statements = getStatements(sqlScript,
084: startOffset, endOffset);
085: boolean computeResults = statements.size() == 1;
086:
087: List<SQLExecutionResult> resultList = new ArrayList<SQLExecutionResult>();
088: long totalExecutionTime = 0;
089:
090: for (Iterator i = statements.iterator(); i.hasNext();) {
091:
092: cancelled = Thread.currentThread().isInterrupted();
093: if (cancelled) {
094: break;
095: }
096:
097: StatementInfo info = (StatementInfo) i.next();
098: String sql = info.getSQL();
099:
100: if (LOG) {
101: LOGGER.log(Level.FINE, "Executing: " + sql);
102: }
103:
104: SQLExecutionResult result = null;
105: Statement stmt = null;
106:
107: try {
108: if (sql.startsWith("{")) { // NOI18N
109: stmt = conn.prepareCall(sql);
110: } else {
111: stmt = conn.createStatement();
112: }
113:
114: boolean isResultSet = false;
115: long startTime = System.currentTimeMillis();
116: if (stmt instanceof PreparedStatement) {
117: isResultSet = ((PreparedStatement) stmt).execute();
118: } else {
119: isResultSet = stmt.execute(sql);
120: }
121: long executionTime = System.currentTimeMillis()
122: - startTime;
123: totalExecutionTime += executionTime;
124:
125: if (isResultSet) {
126: result = new SQLExecutionResult(info, stmt, stmt
127: .getResultSet(), executionTime);
128: } else {
129: result = new SQLExecutionResult(info, stmt, stmt
130: .getUpdateCount(), executionTime);
131: }
132: } catch (SQLException e) {
133: result = new SQLExecutionResult(info, stmt, e);
134: }
135: assert result != null;
136:
137: executionLogger.log(result);
138:
139: if (LOG) {
140: LOGGER.log(Level.FINE, "Result: " + result);
141: }
142:
143: if (computeResults || result.getException() != null) {
144: resultList.add(result);
145: } else {
146: try {
147: result.close();
148: } catch (SQLException e) {
149: Logger.getLogger("global").log(Level.INFO, null, e);
150: }
151: }
152: }
153:
154: if (!cancelled) {
155: executionLogger.finish(totalExecutionTime);
156: } else {
157: if (LOG) {
158: LOGGER.log(Level.FINE, "Execution cancelled"); // NOI18N
159: }
160: executionLogger.cancel();
161: }
162:
163: SQLExecutionResults results = new SQLExecutionResults(
164: resultList);
165: if (!cancelled) {
166: return results;
167: } else {
168: results.close();
169: return null;
170: }
171: }
172:
173: private static int[] getSupportedResultSetTypeConcurrency(
174: Connection conn) throws SQLException {
175: // XXX some drivers don't implement the DMD.supportsResultSetConcurrency() method
176: // for example the MSSQL WebLogic driver 4v70rel510 always throws AbstractMethodError
177:
178: DatabaseMetaData dmd = conn.getMetaData();
179:
180: int type = ResultSet.TYPE_SCROLL_INSENSITIVE;
181: int concurrency = ResultSet.CONCUR_UPDATABLE;
182: if (!dmd.supportsResultSetConcurrency(type, concurrency)) {
183: concurrency = ResultSet.CONCUR_READ_ONLY;
184: if (!dmd.supportsResultSetConcurrency(type, concurrency)) {
185: type = ResultSet.TYPE_FORWARD_ONLY;
186: }
187: }
188: return new int[] { type, concurrency };
189: }
190:
191: private static List<StatementInfo> getStatements(String script,
192: int startOffset, int endOffset) {
193: List<StatementInfo> allStatements = split(script);
194: if (startOffset == 0 && endOffset == script.length()) {
195: return allStatements;
196: }
197: List<StatementInfo> statements = new ArrayList<StatementInfo>();
198: for (Iterator i = allStatements.iterator(); i.hasNext();) {
199: StatementInfo stmt = (StatementInfo) i.next();
200: if (startOffset == endOffset) {
201: // only find the statement at offset startOffset
202: if (stmt.getRawStartOffset() <= startOffset
203: && stmt.getRawEndOffset() >= endOffset) {
204: statements.add(stmt);
205: }
206: } else {
207: // find the statements between startOffset and endOffset
208: if (stmt.getStartOffset() >= startOffset
209: && stmt.getEndOffset() <= endOffset) {
210: statements.add(stmt);
211: }
212: }
213: }
214: return Collections.unmodifiableList(statements);
215: }
216:
217: static List<StatementInfo> split(String script) {
218: return new SQLSplitter(script).getStatements();
219: }
220:
221: private static final class SQLSplitter {
222:
223: private static final int STATE_MEANINGFUL_TEXT = 0;
224: private static final int STATE_MAYBE_LINE_COMMENT = 1;
225: private static final int STATE_LINE_COMMENT = 2;
226: private static final int STATE_MAYBE_BLOCK_COMMENT = 3;
227: private static final int STATE_BLOCK_COMMENT = 4;
228: private static final int STATE_MAYBE_END_BLOCK_COMMENT = 5;
229: private static final int STATE_STRING = 6;
230:
231: private String sql;
232: private int sqlLength;
233:
234: private StringBuffer statement = new StringBuffer();
235: private List<StatementInfo> statements = new ArrayList<StatementInfo>();
236:
237: private int pos = 0;
238: private int line = -1;
239: private int column;
240: private boolean wasEOL = true;
241:
242: private int rawStartOffset;
243: private int startOffset;
244: private int startLine;
245: private int startColumn;
246: private int endOffset;
247: private int rawEndOffset;
248:
249: private int state = STATE_MEANINGFUL_TEXT;
250:
251: /**
252: * @param sql the SQL string to parse. If it contains multiple lines
253: * they have to be delimited by '\n' characters.
254: */
255: public SQLSplitter(String sql) {
256: assert sql != null;
257: this .sql = sql;
258: sqlLength = sql.length();
259: parse();
260: }
261:
262: private void parse() {
263: while (pos < sqlLength) {
264: char ch = sql.charAt(pos);
265:
266: if (ch == '\r') { // NOI18N
267: // the string should not contain these
268: if (LOG) {
269: LOGGER
270: .log(Level.FINE,
271: "The SQL string contained non-supported \r characters."); // NOI18N
272: }
273: continue;
274: }
275:
276: if (wasEOL) {
277: line++;
278: column = 0;
279: wasEOL = false;
280: } else {
281: column++;
282: }
283:
284: if (ch == '\n') {
285: wasEOL = true;
286: }
287:
288: switch (state) {
289: case STATE_MEANINGFUL_TEXT:
290: if (ch == '-') {
291: state = STATE_MAYBE_LINE_COMMENT;
292: }
293: if (ch == '/') {
294: state = STATE_MAYBE_BLOCK_COMMENT;
295: }
296: if (ch == '\'') {
297: state = STATE_STRING;
298: }
299: break;
300:
301: case STATE_MAYBE_LINE_COMMENT:
302: if (ch == '-') {
303: state = STATE_LINE_COMMENT;
304: } else {
305: state = STATE_MEANINGFUL_TEXT;
306: statement.append('-'); // previous char
307: endOffset = pos;
308: }
309: break;
310:
311: case STATE_LINE_COMMENT:
312: if (ch == '\n') {
313: state = STATE_MEANINGFUL_TEXT;
314: // avoid appending the final \n to the result
315: pos++;
316: continue;
317: }
318: break;
319:
320: case STATE_MAYBE_BLOCK_COMMENT:
321: if (ch == '*') {
322: state = STATE_BLOCK_COMMENT;
323: } else {
324: statement.append('/'); // previous char
325: endOffset = pos;
326: if (ch != '/') {
327: state = STATE_MEANINGFUL_TEXT;
328: }
329: }
330: break;
331:
332: case STATE_BLOCK_COMMENT:
333: if (ch == '*') {
334: state = STATE_MAYBE_END_BLOCK_COMMENT;
335: }
336: break;
337:
338: case STATE_MAYBE_END_BLOCK_COMMENT:
339: if (ch == '/') {
340: state = STATE_MEANINGFUL_TEXT;
341: // avoid writing the final / to the result
342: pos++;
343: continue;
344: } else if (ch != '*') {
345: state = STATE_BLOCK_COMMENT;
346: }
347: break;
348:
349: case STATE_STRING:
350: if (ch == '\n' || ch == '\'') {
351: state = STATE_MEANINGFUL_TEXT;
352: }
353: break;
354:
355: default:
356: assert false;
357: }
358:
359: if (state == STATE_MEANINGFUL_TEXT && ch == ';') {
360: rawEndOffset = pos;
361: addStatement();
362: statement.setLength(0);
363: rawStartOffset = pos + 1; // skip the semicolon
364: } else {
365: if (state == STATE_MEANINGFUL_TEXT
366: || state == STATE_STRING) {
367: // don't append leading whitespace
368: if (statement.length() > 0
369: || !Character.isWhitespace(ch)) {
370: // remember the position of the first appended char
371: if (statement.length() == 0) {
372: startOffset = pos;
373: endOffset = pos;
374: startLine = line;
375: startColumn = column;
376: }
377: statement.append(ch);
378: // the end offset is the character after the last non-whitespace character
379: if (state == STATE_STRING
380: || !Character.isWhitespace(ch)) {
381: endOffset = pos + 1;
382: }
383: }
384: }
385: }
386:
387: pos++;
388: }
389:
390: rawEndOffset = pos;
391: addStatement();
392: }
393:
394: private void addStatement() {
395: // PENDING since startOffset is the first non-whitespace char and
396: // endOffset is the offset after the last non-whitespace char,
397: // the trim() call could be replaced with statement.substring(startOffset, endOffset)
398: String sql = statement.toString().trim();
399: if (sql.length() <= 0) {
400: return;
401: }
402:
403: StatementInfo info = new StatementInfo(sql, rawStartOffset,
404: startOffset, startLine, startColumn, endOffset,
405: rawEndOffset);
406: statements.add(info);
407: }
408:
409: public List<StatementInfo> getStatements() {
410: return Collections.unmodifiableList(statements);
411: }
412: }
413: }
|