001: package org.dbbrowser.db.engine.queryengine;
002:
003: import infrastructure.logging.Log;
004: import java.sql.DatabaseMetaData;
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 org.dbbrowser.db.engine.SQLLog;
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.model.View;
018: import org.dbbrowser.db.engine.model.filter.Filter;
019:
020: /**
021: * DBQuery Engine which gets data from a MsSQL DBMS
022: */
023: public class MsSQLDBQueryEngine extends AbstractDBQueryEngine implements
024: DBQueryEngine {
025: /**
026: * Constructer
027: *
028: * @param statement
029: */
030: public MsSQLDBQueryEngine(Statement statement) {
031: super (statement);
032: }
033:
034: /**
035: * Returns a list of schemas in the Database.
036: *
037: * @return - a list of Strings. It is empty if there are no table spaces for
038: * the database.
039: * @throws DBEngineException
040: */
041: public List listSchemas() throws DBEngineException {
042: Log.getInstance().infoMessage(
043: "Listing database instances in MsSQL...",
044: this .getClass().getName());
045:
046: List listOfDatabaseInstances = new ArrayList();
047: try {
048: String sql = "exec sp_databases";
049:
050: Log.getInstance().debugMessage("SQL is: " + sql,
051: this .getClass().getName());
052:
053: ResultSet rs = this .getStatement().executeQuery(sql);
054:
055: while (rs.next()) {
056: String tablename = rs.getString(1);
057: listOfDatabaseInstances.add(tablename);
058: }
059:
060: Log.getInstance().debugMessage(
061: "Finished listing schemas in MsSQLDBQueryEngine",
062: this .getClass().getName());
063: rs.close();
064: } catch (SQLException exc) {
065:
066: throw new DBEngineException(exc.getMessage());
067: }
068:
069: Log.getInstance().infoMessage(
070: "Found " + listOfDatabaseInstances.size()
071: + " database instances in MsSQL",
072: this .getClass().getName());
073:
074: return listOfDatabaseInstances;
075: }
076:
077: /**
078: * Returns a list of views accessible to the user. It is empty if there are
079: * no views for the user.
080: *
081: * @return - a list of Strings
082: * @throws DBEngineException
083: */
084: public List listViews() throws DBEngineException {
085: return new ArrayList();
086: }
087:
088: /**
089: * Returns the SQL used to create a view
090: *
091: * @return - a String
092: * @throws DBEngineException
093: */
094: public String getSQLForView(View view) throws DBEngineException {
095: return "";
096: }
097:
098: /**
099: * Get all the data in a table
100: *
101: * @param schemaName
102: * @param tableName
103: * @return
104: * @throws DBEngineException
105: */
106: public DBTable getAllDataInATable(String schemaName,
107: String tableName, Integer offset,
108: Integer numberOfRowsToReturn) throws DBEngineException {
109: Log.getInstance().infoMessage(
110: "Listing all data in " + schemaName + "." + tableName
111: + "...", this .getClass().getName());
112:
113: // Get the number of rows in the table
114: Integer rowCount = this .getRowCount(schemaName, tableName);
115:
116: List listOfColumnsInATable = listColumnsInATable(schemaName,
117: tableName);
118: DBTable dbTable = null;
119: List rows = new ArrayList();
120: try {
121:
122: // Change the schema
123: String sql = "use " + schemaName;
124: // this.getStatement().execute(sql);
125:
126: // Execute the statement to get the data in the table
127: sql = "select * from " + tableName;
128:
129: Log.getInstance().debugMessage("SQL is: " + sql,
130: this .getClass().getName());
131:
132: ResultSet rs = this .getStatement().executeQuery(sql);
133:
134: // Get the data from the result set and build a DBTable
135: while (rs.next()) {
136: // Get the data for every column
137: List listOfRowData = new ArrayList();
138: for (int i = 0; i < listOfColumnsInATable.size(); i++) {
139: ColumnInfo columnInfo = (ColumnInfo) listOfColumnsInATable
140: .get(i);
141: String columnName = columnInfo.getColumnName();
142: Object o = rs.getObject(columnName);
143: DBTableCell dbTableCell = null;
144:
145: dbTableCell = new DBTableCell(columnInfo, o,
146: Boolean.FALSE);
147:
148: listOfRowData.add(dbTableCell);
149: }
150: DBRow dbRow = new DBRow(listOfRowData);
151: rows.add(dbRow);
152: }
153:
154: if (rows.isEmpty()) {
155: dbTable = new DBTable(schemaName, tableName, rows,
156: offset, numberOfRowsToReturn,
157: listOfColumnsInATable);
158: } else {
159: dbTable = new DBTable(schemaName, tableName, rows,
160: offset, numberOfRowsToReturn,
161: numberOfRowsToReturn);
162: }
163:
164: Log.getInstance().debugMessage(
165: "Returning all data for " + schemaName + "."
166: + tableName + " in MsSQLDBQueryEngine",
167: this .getClass().getName());
168:
169: rs.close();
170: } catch (SQLException exc) {
171:
172: throw new DBEngineException(exc.getMessage());
173: }
174:
175: Log.getInstance().infoMessage(
176: "Found " + rows.size() + " rows in " + schemaName + "."
177: + tableName, this .getClass().getName());
178:
179: return dbTable;
180: }
181:
182: /**
183: * Returns a list of tables in the schema
184: *
185: * @param schemaName
186: * @return - a list of Strings
187: * @throws DBEngineException
188: */
189: public List listTablesInSchema(String databaseInstanceName)
190: throws DBEngineException {
191: Log.getInstance().infoMessage(
192: "Listing tables in MsSQL database instance "
193: + databaseInstanceName + "...",
194: this .getClass().getName());
195:
196: List listOfTables = new ArrayList();
197: try {
198: // Change the tablespace
199: String sql = "use " + databaseInstanceName;
200: // System.out.println(sql);
201: this .getStatement().execute(sql);
202:
203: // Select the list of tables
204: sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
205:
206: Log.getInstance().debugMessage("SQL is: " + sql,
207: this .getClass().getName());
208:
209: ResultSet rs = this .getStatement().executeQuery(sql);
210: while (rs.next()) {
211: String tablename = rs.getString(1);
212: listOfTables.add(tablename);
213: }
214:
215: Log.getInstance().debugMessage(
216: "Finished listing all schemas for "
217: + databaseInstanceName
218: + " in MsSQLDBQueryEngine",
219: this .getClass().getName());
220:
221: rs.close();
222: } catch (SQLException exc) {
223:
224: throw new DBEngineException(exc.getMessage());
225: }
226:
227: Log.getInstance().infoMessage(
228: "Found " + listOfTables.size()
229: + " tables in MsSQL database instance "
230: + databaseInstanceName,
231: this .getClass().getName());
232:
233: return listOfTables;
234: }
235:
236: /**
237: * Returns a list of columns in the table
238: *
239: * @param databaseInstanceName
240: * @param tableName
241: * @return - a list of ColumnInfo objects
242: * @throws DBEngineException
243: */
244: public List listColumnsInATable(String databaseInstanceName,
245: String tableName) throws DBEngineException {
246: Log.getInstance().infoMessage(
247: "Listing columns in table " + databaseInstanceName
248: + "." + tableName + "...",
249: this .getClass().getName());
250:
251: // Get the list of primary key column names
252: List primaryKeyColumnNames = this .getPrimaryKeyColumnNames(
253: databaseInstanceName, tableName);
254:
255: List listOfColumns = new ArrayList();
256: try {
257: String sql = "select * from " + tableName;
258:
259: Log.getInstance().debugMessage("SQL is: " + sql,
260: this .getClass().getName());
261:
262: ResultSet rs = this .getStatement().executeQuery(sql);
263: ResultSetMetaData rsmd = rs.getMetaData();
264:
265: int numberOfColumns = rsmd.getColumnCount();
266: for (int i = 1; i < numberOfColumns + 1; i++) {
267: // Get the values from the ResultSetMetaData
268: String columnName = rsmd.getColumnName(i);
269: String equivalentJavaClass = rsmd.getColumnClassName(i);
270: String columnTypeName = rsmd.getColumnTypeName(i);
271: Integer columnDisplaysize = new Integer(rsmd
272: .getColumnDisplaySize(i));
273: int nullable = rsmd.isNullable(i);
274:
275: // Check if this column is a primary key column
276: boolean isPrimaryKeyColumn = primaryKeyColumnNames
277: .contains(columnName);
278: Boolean isPrimaryKeyColumnBoolean = new Boolean(
279: isPrimaryKeyColumn);
280:
281: // Build the column info
282: String nullableNature = ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN;
283: if (nullable == ResultSetMetaData.columnNullable) {
284: nullableNature = ColumnInfo.COLUMN_NULLABLE;
285: }
286:
287: if (nullable == ResultSetMetaData.columnNoNulls) {
288: nullableNature = ColumnInfo.COLUMN_NOT_NULLABLE;
289: }
290:
291: // Check if this column is editable/writable
292: // boolean isEditable = rsmd.isDefinitelyWritable( i );
293: // Boolean isEditableBoolean = new Boolean( isEditable );
294:
295: int columnType = rsmd.getColumnType(i);
296:
297: Boolean isAutoIncrement = new Boolean(rsmd
298: .isAutoIncrement(i));
299:
300: // System.out.println("columntypename " + columnTypeName
301: // + " java class " + equivalentJavaClass + " "+columnType);
302:
303: ColumnInfo columnInfo = new ColumnInfo(columnName,
304: columnTypeName, equivalentJavaClass,
305: columnDisplaysize, nullableNature,
306: isAutoIncrement, isPrimaryKeyColumnBoolean,
307: Boolean.TRUE, new Integer(columnType));
308: listOfColumns.add(columnInfo);
309: }
310:
311: Log.getInstance().debugMessage(
312: "Finished listing all columns in for "
313: + databaseInstanceName + "." + tableName
314: + " in MsSQLDBQueryEngine",
315: this .getClass().getName());
316:
317: rs.close();
318: } catch (SQLException exc) {
319:
320: throw new DBEngineException(exc.getMessage());
321: }
322:
323: Log.getInstance().infoMessage(
324: "Found " + listOfColumns.size() + " columns in table "
325: + databaseInstanceName + "." + tableName,
326: this .getClass().getName());
327:
328: return listOfColumns;
329: }
330:
331: public DBTable getFilteredDataInATable(String schemaName,
332: String tableName, Filter filter) throws DBEngineException {
333: Log.getInstance().infoMessage(
334: "Getting filtered data for " + schemaName + "."
335: + tableName + "...", this .getClass().getName());
336: Log.getInstance().infoMessage(
337: "Filter: " + filter.getSQLString(),
338: this .getClass().getName());
339: DBTable dbTable = null;
340: List rows = new ArrayList();
341:
342: // Get the columns in the table
343: List listOfColumnsInATable = listColumnsInATable(schemaName,
344: tableName);
345:
346: try {
347: // String sql = "select count(*) from " + tableName;
348: String sql = "select * from " + tableName + " where "
349: + filter.getSQLString();
350:
351: // Log the SQL
352: SQLLog.getInstance().logSQLStatement(sql);
353: Log.getInstance().infoMessage(
354: "SQL to get filtered data is: " + sql,
355: this .getClass().getName());
356:
357: // Run the SQL
358: System.out.println(sql);
359: ResultSet rs = getStatement().executeQuery(sql);
360:
361: // Get the data from the result set and build a DBTable
362: while (rs.next()) {
363: // Get the data for every column
364: List listOfRowData = new ArrayList();
365: for (int i = 0; i < listOfColumnsInATable.size(); i++) {
366: ColumnInfo columnInfo = (ColumnInfo) listOfColumnsInATable
367: .get(i);
368: String columnName = columnInfo.getColumnName();
369: Object o = rs.getObject(columnName);
370:
371: // // if it is an oracle timestamp, get the timestamp
372: // if ((o != null)
373: // && ((o.getClass().getName()).endsWith("TIMESTAMP"))) {
374: // o = rs.getTimestamp(columnName);
375: // } else {
376: // if ((o != null)
377: // && ((o.getClass().getName())
378: // .endsWith("TimeStamp"))) {
379: // o = rs.getTimestamp(columnName);
380: // } else {
381: // if ((o != null)
382: // && ((o.getClass().getName())
383: // .endsWith("Timestamp"))) {
384: // o = rs.getTimestamp(columnName);
385: // }
386: // }
387: // }
388:
389: DBTableCell dbTableCell = new DBTableCell(
390: columnInfo, o, Boolean.FALSE);
391:
392: listOfRowData.add(dbTableCell);
393: }
394: DBRow dbRow = new DBRow(listOfRowData);
395: rows.add(dbRow);
396: }
397:
398: if (rows.isEmpty()) {
399: dbTable = new DBTable(schemaName, tableName, rows,
400: new Integer(0), new Integer(rows.size()),
401: listOfColumnsInATable);
402: } else {
403: dbTable = new DBTable(schemaName, tableName, rows,
404: new Integer(0), new Integer(rows.size()),
405: new Integer(rows.size()));
406: }
407: rs.close();
408: } catch (SQLException exc) {
409:
410: throw new DBEngineException(exc.getMessage());
411: }
412:
413: Log.getInstance().infoMessage(
414: "Row count for " + schemaName + "." + tableName
415: + " is " + rows.size(),
416: this .getClass().getName());
417: return dbTable;
418: }
419:
420: public List getPrimaryKeyColumnNames(String schemaName,
421: String tableName) throws DBEngineException {
422:
423: Log.getInstance().infoMessage("Listing primary key columns...",
424: this .getClass().getName());
425:
426: List listOfPrimaryKeyColumnNames = new ArrayList();
427: try {
428: DatabaseMetaData databaseMetaData = this .getStatement()
429: .getConnection().getMetaData();
430: ResultSet rs = databaseMetaData.getPrimaryKeys(null, null,
431: tableName);
432:
433: while (rs.next()) {
434: String primaryKeyColumnNameForTable = rs
435: .getString("COLUMN_NAME");
436: String tableNameOfPrimaryKey = rs
437: .getString("TABLE_NAME");
438: String schemaNameForTable = rs.getString("TABLE_CAT");
439:
440: // if the primary key is for this table, add it
441: if (tableName.equals(tableNameOfPrimaryKey)
442: && schemaName.equals(schemaNameForTable)) {
443: listOfPrimaryKeyColumnNames
444: .add(primaryKeyColumnNameForTable);
445: }
446: }
447: rs.close();
448: } catch (SQLException exc) {
449:
450: throw new DBEngineException(exc.getMessage());
451: }
452:
453: Log.getInstance().infoMessage(
454: "Found " + listOfPrimaryKeyColumnNames.size()
455: + " primary key columns",
456: this .getClass().getName());
457:
458: return listOfPrimaryKeyColumnNames;
459: }
460:
461: public Integer getRowCount(String schemaName, String tableName)
462: throws DBEngineException {
463:
464: Log.getInstance().infoMessage(
465: "Getting row count for " + schemaName + "." + tableName
466: + "...", this .getClass().getName());
467:
468: Integer rowCount = null;
469: try {
470: // String sql = "select count(*) from " + tableName;
471: String sql = "select count(*) from " + tableName;
472:
473: // Log the SQL
474: SQLLog.getInstance().logSQLStatement(sql);
475:
476: // Run the SQL
477: ResultSet rs = getStatement().executeQuery(sql);
478: if (rs.next()) {
479: rowCount = new Integer(rs.getInt(1));
480: }
481: rs.close();
482: } catch (SQLException exc) {
483:
484: throw new DBEngineException(exc.getMessage());
485: }
486:
487: Log.getInstance().infoMessage(
488: "Row count for " + schemaName + "." + tableName
489: + " is " + rowCount, this .getClass().getName());
490: return rowCount;
491: }
492:
493: /**
494: * Lists the indexes
495: * @return
496: * @throws DBEngineException
497: */
498: public DBTable listIndexes() throws DBEngineException {
499: throw new UnsupportedOperationException();
500: }
501:
502: /**
503: * Lists the constraints
504: * @return
505: * @throws DBEngineException
506: */
507: public DBTable listConstraints() throws DBEngineException {
508: throw new UnsupportedOperationException();
509: }
510: }
|