001: package org.dbbrowser.db.engine.rawsqlengine;
002:
003: import infrastructure.logging.Log;
004: import infrastructure.propertymanager.PropertyManager;
005: import java.sql.ResultSet;
006: import java.sql.ResultSetMetaData;
007: import java.sql.SQLException;
008: import java.sql.Statement;
009: import java.util.ArrayList;
010: import java.util.List;
011: import java.util.StringTokenizer;
012: import org.dbbrowser.db.engine.exception.DBEngineException;
013: import org.dbbrowser.db.engine.model.ColumnInfo;
014: import org.dbbrowser.db.engine.model.DBRow;
015: import org.dbbrowser.db.engine.model.DBTable;
016: import org.dbbrowser.db.engine.model.DBTableCell;
017: import org.dbbrowser.db.engine.SQLLog;
018:
019: /**
020: * Runs raw SQL statement entered by the user
021: */
022: public class GenericRawSQLEngine implements DBRawSQLEngine {
023: private static final String SELECT = "SELECT";
024: private static final String DESC = "DESC";
025: private static final String DESCRIBE = "DESCRIBE";
026:
027: private static final String DELETE = "DELETE";
028: private static final String UPDATE = "UPDATE";
029: private static final String INSERT = "INSERT";
030: private static final String DROP = "DROP";
031:
032: private Statement statement = null;
033:
034: /**
035: * Constructer
036: * @param statement
037: */
038: public GenericRawSQLEngine(Statement statement) {
039: this .statement = statement;
040: }
041:
042: /**
043: * Returns the statement used to run the SQL statement
044: * @return
045: */
046: public Statement getStatement() {
047: return this .statement;
048: }
049:
050: /**
051: * Run the SQL statement and return the result if any.
052: * @param sql
053: * @return - DBTable if there is any result or null
054: * @throws DBEngineException
055: */
056: public DBTable runRawSQL(String sql) throws DBEngineException {
057: Log.getInstance().infoMessage("Running raw SQL: " + sql,
058: this .getClass().getName());
059:
060: //Tokenize the string using the sql delimiter
061: String delimiter = PropertyManager.getInstance().getProperty(
062: "dbbrowser-sql-statement-terminator");
063: DBTable dbTable = null;
064: StringTokenizer st = new StringTokenizer(sql, delimiter);
065:
066: //If the sql has only one token, run the sql
067: if (st.countTokens() == 1) {
068: dbTable = runSingleSQLStatement(sql);
069: } else {
070: while (st.hasMoreTokens()) {
071: String token = st.nextToken();
072: runSingleSQLStatement(token);
073: }
074: }
075: return dbTable;
076: }
077:
078: /**
079: * Strips the comments from an SQL statement.
080: * @param sql
081: * @return
082: */
083: public String stripComments(String sql) {
084: Log.getInstance().infoMessage(
085: "Stripping comments from SQL: " + sql,
086: this .getClass().getName());
087:
088: //Are there any comments
089: int commentsStartPosition = sql.indexOf("/*");
090:
091: //if there are any comments
092: if (commentsStartPosition != -1) {
093: int commentsEndPosition = sql.indexOf("*/",
094: commentsStartPosition);
095:
096: //if comments have been ended
097: if (commentsEndPosition != -1) {
098: //Strip the comments
099: String sql1 = sql.substring(0, commentsStartPosition);
100: String sql2 = sql.substring(commentsEndPosition + 2,
101: sql.length());
102:
103: sql = sql1 + sql2;
104:
105: //Recursively call this method to strip all strings
106: sql = stripComments(sql);
107: }
108: }
109:
110: return sql;
111: }
112:
113: private DBTable runSingleSQLStatement(String sql)
114: throws DBEngineException {
115: Log.getInstance().infoMessage("Running SQL: " + sql,
116: this .getClass().getName());
117:
118: DBTable dbTable = null;
119: StringTokenizer st = new StringTokenizer(sql, " ");
120: if (st.hasMoreTokens()) {
121: String firstToken = st.nextToken();
122:
123: //Remove the ; token
124: sql = sql.replaceAll(PropertyManager.getInstance()
125: .getProperty("dbbrowser-sql-statement-terminator"),
126: "");
127:
128: if (SELECT.equalsIgnoreCase(firstToken)
129: || DESC.equalsIgnoreCase(firstToken)
130: || DESCRIBE.equalsIgnoreCase(firstToken)) {
131: dbTable = runRawSelectStatement(sql);
132: } else if (DELETE.equalsIgnoreCase(firstToken)
133: || UPDATE.equalsIgnoreCase(firstToken)
134: || INSERT.equalsIgnoreCase(firstToken)
135: || DROP.equalsIgnoreCase(firstToken)) {
136: runRawUpdateStatement(sql);
137: } else {
138: //Run unknown SQL statement
139: dbTable = runRawUnknownSQLStatement(sql);
140: }
141: } else {
142: //if the sql is not a blank string, then it is an invalid sql
143: sql = sql.trim();
144: if (!"".equals(sql)) {
145: throw new DBEngineException("Invalid SQL");
146: }
147: }
148:
149: return dbTable;
150: }
151:
152: private DBTable runRawSelectStatement(String sql)
153: throws DBEngineException {
154: Log.getInstance().infoMessage(
155: "Running select/describe statement: " + sql,
156: this .getClass().getName());
157:
158: //Split the ';' from the sql query
159: sql = sql.replaceAll(PropertyManager.getInstance().getProperty(
160: "dbbrowser-sql-statement-terminator"), "");
161:
162: //Log the SQL
163: SQLLog.getInstance().logSQLStatement(sql);
164:
165: //Run the SQL
166: ResultSet rs = null;
167: DBTable dbTable = null;
168: try {
169: rs = this .statement.executeQuery(sql);
170:
171: //Get the resultset metadata
172: ResultSetMetaData rsmd = rs.getMetaData();
173:
174: //Get the data from the result set and build a DBTable
175: List rows = new ArrayList();
176: List listOfColumnInfos = new ArrayList();
177: while (rs.next()) {
178: //Get the data for every column
179: List listOfRowData = new ArrayList();
180: int columnCount = rsmd.getColumnCount();
181: for (int i = 0; i < columnCount; i++) {
182: //Build the column info object
183: String columnName = rsmd.getColumnName(i + 1);
184: String equivalentJavaClass = rsmd
185: .getColumnClassName(i + 1);
186: String columnTypeName = rsmd
187: .getColumnTypeName(i + 1); //e.g. NUMBER, VARCHAR2
188: Integer columnDisplaysize = new Integer(rsmd
189: .getColumnDisplaySize(i + 1));
190: int columnType = rsmd.getColumnType(i + 1); //From java.sql.Types
191: ColumnInfo columnInfo = new ColumnInfo(columnName,
192: columnTypeName, equivalentJavaClass,
193: columnDisplaysize,
194: ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN,
195: Boolean.FALSE, Boolean.FALSE,
196: Boolean.FALSE, new Integer(columnType));
197: listOfColumnInfos.add(columnInfo);
198:
199: //Get the data
200: Object o = rs.getObject(i + 1);
201:
202: //if it is an oracle timestamp, get the timestamp
203: if ((o != null)
204: && ("oracle.sql.TIMESTAMP".equals(o
205: .getClass().getName()))) {
206: o = rs.getTimestamp(i + 1);
207: }
208:
209: DBTableCell dbTableCell = new DBTableCell(
210: columnInfo, o, Boolean.FALSE);
211:
212: listOfRowData.add(dbTableCell);
213: }
214: DBRow dbRow = new DBRow(listOfRowData);
215: rows.add(dbRow);
216: }
217:
218: dbTable = new DBTable(null, null, rows, null, new Integer(
219: rows.size()), new Integer(rows.size()));
220: rs.close();
221: } catch (SQLException exc) {
222: Log.getInstance().warnMessage(
223: "Exception while running select/describe statement: "
224: + sql, this .getClass().getName());
225: throw new DBEngineException(exc.getMessage());
226: }
227: return dbTable;
228: }
229:
230: private void runRawUpdateStatement(String sql)
231: throws DBEngineException {
232: Log.getInstance().infoMessage(
233: "Running raw update/delete/insert/alter SQL: " + sql,
234: this .getClass().getName());
235:
236: //Log the SQL
237: SQLLog.getInstance().logSQLStatement(sql);
238:
239: //Run the SQL
240: try {
241: this .statement.executeUpdate(sql);
242: } catch (SQLException exc) {
243: Log.getInstance().warnMessage(
244: "Exception while running update/delete/insert/alter SQL: "
245: + sql, this .getClass().getName());
246: throw new DBEngineException(exc.getMessage());
247: }
248: }
249:
250: private DBTable runRawUnknownSQLStatement(String sql)
251: throws DBEngineException {
252: Log.getInstance().infoMessage(
253: "Running raw unknown SQL: " + sql,
254: this .getClass().getName());
255:
256: DBTable dbTable = null;
257:
258: //Log the SQL
259: SQLLog.getInstance().logSQLStatement(sql);
260:
261: try {
262: //Run the SQL
263: boolean areThereResults = this .statement.execute(sql);
264:
265: //if there are any results
266: if (areThereResults) {
267: ResultSet rs = this .statement.getResultSet();
268:
269: //Get the resultset metadata
270: ResultSetMetaData rsmd = rs.getMetaData();
271:
272: //Get the data from the result set and build a DBTable
273: List rows = new ArrayList();
274: List listOfColumnInfos = new ArrayList();
275: while (rs.next()) {
276: //Get the data for every column
277: List listOfRowData = new ArrayList();
278: int columnCount = rsmd.getColumnCount();
279: for (int i = 0; i < columnCount; i++) {
280: //Build the column info object
281: String columnName = rsmd.getColumnName(i + 1);
282: String equivalentJavaClass = rsmd
283: .getColumnClassName(i + 1);
284: String columnTypeName = rsmd
285: .getColumnTypeName(i + 1); //e.g. NUMBER, VARCHAR2
286: Integer columnDisplaysize = new Integer(rsmd
287: .getColumnDisplaySize(i + 1));
288: int columnType = rsmd.getColumnType(i + 1); //From java.sql.Types
289: ColumnInfo columnInfo = new ColumnInfo(
290: columnName,
291: columnTypeName,
292: equivalentJavaClass,
293: columnDisplaysize,
294: ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN,
295: Boolean.FALSE, Boolean.FALSE,
296: Boolean.FALSE, new Integer(columnType));
297: listOfColumnInfos.add(columnInfo);
298:
299: //Get the data
300: Object o = rs.getObject(columnName);
301:
302: //if it is an oracle timestamp, get the timestamp
303: if ((o != null)
304: && ("oracle.sql.TIMESTAMP".equals(o
305: .getClass().getName()))) {
306: o = rs.getTimestamp(columnName);
307: }
308:
309: DBTableCell dbTableCell = new DBTableCell(
310: columnInfo, o, Boolean.FALSE);
311:
312: listOfRowData.add(dbTableCell);
313: }
314: DBRow dbRow = new DBRow(listOfRowData);
315: rows.add(dbRow);
316: }
317:
318: dbTable = new DBTable(null, null, rows, new Integer(0),
319: new Integer(rows.size()), new Integer(rows
320: .size()));
321: rs.close();
322: }
323: } catch (SQLException exc) {
324: Log.getInstance().warnMessage(
325: "Exception while running unknown SQL: " + sql,
326: this .getClass().getName());
327: throw new DBEngineException(exc.getMessage());
328: }
329:
330: return dbTable;
331: }
332: }
|