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.SQLException;
007: import java.sql.Statement;
008: import java.util.ArrayList;
009: import java.util.List;
010: import org.dbbrowser.db.engine.exception.DBEngineException;
011: import org.dbbrowser.db.engine.model.ColumnInfo;
012: import org.dbbrowser.db.engine.model.DBRow;
013: import org.dbbrowser.db.engine.model.DBTable;
014: import org.dbbrowser.db.engine.model.DBTableCell;
015: import org.dbbrowser.db.engine.model.filter.Filter;
016: import org.dbbrowser.db.engine.SQLLog;
017:
018: /**
019: * A DB Query engine which handles some of the common/generic behaviour
020: */
021: public abstract class AbstractDBQueryEngine implements DBQueryEngine {
022: private Statement statement = null;
023:
024: /**
025: * Constructer
026: * @param statement
027: */
028: public AbstractDBQueryEngine(Statement statement) {
029: this .statement = statement;
030: }
031:
032: /**
033: * Returns the statememnt used to run the queries
034: * @return
035: */
036: public Statement getStatement() {
037: return this .statement;
038: }
039:
040: /**
041: * Return the number of rows in a table
042: * @param schemaName
043: * @param tableName
044: * @return
045: * @throws DBEngineException
046: */
047: public Integer getRowCount(String schemaName, String tableName)
048: throws DBEngineException {
049: Log.getInstance().infoMessage(
050: "Getting row count for " + schemaName + "." + tableName
051: + "...", this .getClass().getName());
052:
053: Integer rowCount = null;
054: try {
055: //String sql = "select count(*) from " + tableName;
056: String sql = "select count(*) from " + schemaName + "."
057: + tableName;
058:
059: //Log the SQL
060: SQLLog.getInstance().logSQLStatement(sql);
061:
062: //Run the SQL
063: ResultSet rs = this .statement.executeQuery(sql);
064: if (rs.next()) {
065: rowCount = new Integer(rs.getInt("count(*)"));
066: }
067: rs.close();
068: } catch (SQLException exc) {
069: throw new DBEngineException(exc.getMessage());
070: }
071:
072: Log.getInstance().infoMessage(
073: "Row count for " + schemaName + "." + tableName
074: + " is " + rowCount, this .getClass().getName());
075: return rowCount;
076: }
077:
078: /**
079: * Returns a list of table spaces in the Database. It is empty if there are no table spaces for the database.
080: * @return - a list of Strings
081: * @throws DBEngineException
082: */
083: public abstract List listSchemas() throws DBEngineException;
084:
085: /**
086: * Returns a list of views accessible to the user. It is empty if there are no views for the user.
087: * @return - a list of Strings
088: * @throws DBEngineException
089: */
090: public abstract List listViews() throws DBEngineException;
091:
092: /**
093: * Returns a list of tables in the table space
094: * @param schemaName
095: * @return - a list of Strings
096: * @throws DBEngineException
097: */
098: public abstract List listTablesInSchema(String schemaName)
099: throws DBEngineException;
100:
101: /**
102: * Returns a list of columns in the table
103: * @param schemaName
104: * @param tableName
105: * @return - a list of ColumnInfo objects
106: * @throws DBEngineException
107: */
108: public abstract List listColumnsInATable(String schemaName,
109: String tableName) throws DBEngineException;
110:
111: /**
112: * Lists the indexes in the schema
113: * @return
114: * @throws DBEngineException
115: */
116: public abstract DBTable listIndexes() throws DBEngineException;
117:
118: /**
119: * Lists the constraints
120: * @return
121: * @throws DBEngineException
122: */
123: public abstract DBTable listConstraints() throws DBEngineException;
124:
125: /**
126: * Get all the data in a table
127: * @param schemaName
128: * @param tableName
129: * @return
130: * @throws DBEngineException
131: */
132: public abstract DBTable getAllDataInATable(String schemaName,
133: String tableName, Integer offset,
134: Integer numberOfRowsToReturn) throws DBEngineException;
135:
136: /**
137: * Returns the filtered data in the table
138: * @param schemaName
139: * @param tableName
140: * @param filter
141: * @return
142: * @throws DBEngineException
143: */
144: public DBTable getFilteredDataInATable(String schemaName,
145: String tableName, Filter filter) throws DBEngineException {
146: Log.getInstance().infoMessage(
147: "Getting filtered data for " + schemaName + "."
148: + tableName + "...", this .getClass().getName());
149: Log.getInstance().infoMessage(
150: "Filter: " + filter.getSQLString(),
151: this .getClass().getName());
152: DBTable dbTable = null;
153: List rows = new ArrayList();
154:
155: //Get the columns in the table
156: List listOfColumnsInATable = listColumnsInATable(schemaName,
157: tableName);
158:
159: try {
160: //String sql = "select count(*) from " + tableName;
161: String sql = "select * from " + tableName + " where "
162: + filter.getSQLString();
163:
164: //Log the SQL
165: SQLLog.getInstance().logSQLStatement(sql);
166: Log.getInstance().infoMessage(
167: "SQL to get filtered data is: " + sql,
168: this .getClass().getName());
169:
170: //Run the SQL
171: ResultSet rs = this .statement.executeQuery(sql);
172:
173: //Get the data from the result set and build a DBTable
174: while (rs.next()) {
175: //Get the data for every column
176: List listOfRowData = new ArrayList();
177: for (int i = 0; i < listOfColumnsInATable.size(); i++) {
178: ColumnInfo columnInfo = (ColumnInfo) listOfColumnsInATable
179: .get(i);
180: String columnName = columnInfo.getColumnName();
181: Object o = rs.getObject(columnName);
182:
183: //if it is an oracle timestamp, get the timestamp
184: if ((o != null)
185: && ("oracle.sql.TIMESTAMP".equals(o
186: .getClass().getName()))) {
187: o = rs.getTimestamp(columnName);
188: }
189:
190: DBTableCell dbTableCell = new DBTableCell(
191: columnInfo, o, Boolean.FALSE);
192:
193: listOfRowData.add(dbTableCell);
194: }
195: DBRow dbRow = new DBRow(listOfRowData);
196: rows.add(dbRow);
197: }
198:
199: if (rows.isEmpty()) {
200: dbTable = new DBTable(schemaName, tableName, rows,
201: new Integer(0), new Integer(rows.size()),
202: listOfColumnsInATable);
203: } else {
204: dbTable = new DBTable(schemaName, tableName, rows,
205: new Integer(0), new Integer(rows.size()),
206: new Integer(rows.size()));
207: }
208: rs.close();
209: } catch (SQLException exc) {
210: throw new DBEngineException(exc.getMessage());
211: }
212:
213: Log.getInstance().infoMessage(
214: "Row count for " + schemaName + "." + tableName
215: + " is " + rows.size(),
216: this .getClass().getName());
217: return dbTable;
218: }
219:
220: /**
221: * Returns the list of column names which are primary keys for the table
222: * @param schemaName
223: * @param tableName
224: * @return a list of strings - names of primary key columns
225: * @throws DBEngineException
226: */
227: public List getPrimaryKeyColumnNames(String schemaName,
228: String tableName) throws DBEngineException {
229: Log.getInstance().infoMessage("Listing primary key columns...",
230: this .getClass().getName());
231:
232: List listOfPrimaryKeyColumnNames = new ArrayList();
233: try {
234: DatabaseMetaData databaseMetaData = this .getStatement()
235: .getConnection().getMetaData();
236: ResultSet rs = databaseMetaData.getPrimaryKeys(null, null,
237: tableName);
238:
239: while (rs.next()) {
240: String primaryKeyColumnNameForTable = rs
241: .getString("COLUMN_NAME");
242: String tableNameOfPrimaryKey = rs
243: .getString("TABLE_NAME");
244: String schemaNameForTable = rs.getString("TABLE_SCHEM");
245:
246: //if the primary key is for this table, add it
247: if (tableName.equals(tableNameOfPrimaryKey)
248: && schemaName.equals(schemaNameForTable)) {
249: listOfPrimaryKeyColumnNames
250: .add(primaryKeyColumnNameForTable);
251: }
252: }
253: rs.close();
254: } catch (SQLException exc) {
255: throw new DBEngineException(exc.getMessage());
256: }
257:
258: Log.getInstance().infoMessage(
259: "Found " + listOfPrimaryKeyColumnNames.size()
260: + " primary key columns",
261: this.getClass().getName());
262:
263: return listOfPrimaryKeyColumnNames;
264: }
265: }
|