001: /*
002: * Copyright 2007 Roy van der Kuil (roy@vanderkuil.nl) and Stefan Rotman (stefan@rotman.net)
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package nl.improved.sqlclient;
017:
018: import java.sql.Date;
019: import java.sql.ResultSet;
020: import java.sql.ResultSetMetaData;
021: import java.sql.SQLException;
022: import java.sql.Time;
023: import java.sql.Timestamp;
024: import java.sql.Types;
025: import java.util.List;
026: import java.util.ArrayList;
027: import java.text.DateFormat;
028: import java.text.SimpleDateFormat;
029: import java.util.logging.Level;
030: import java.util.logging.Logger;
031: import nl.improved.sqlclient.util.ResultBuilder;
032:
033: /**
034: * Class to execute SQL Queries.
035: * Typical examples would be:
036: * <code>
037: * QueryExecutor qe = new QueryExecutor();
038: * qe.executeQuery("select * from test where id=12;");
039: * </code>
040: * which would then print the resulting row with id 12.
041: * If the table <tt>test</tt> does not exist, or doesn not have an <tt>id</tt> column,
042: * a {@link java.sql.SQLException} is being thrown.
043: *
044: * @see StatementExecutor
045: */
046: public class QueryExecutor {
047:
048: /**
049: * Used to format dates with unspecified patterns.
050: */
051: private DateFormat defaultDateFormat, defaultTimeFormat,
052: defaultTimestampFormat;
053:
054: private String timeFormat;
055: private String timestampFormat;
056: private String dateFormat;
057: private boolean cancelled = false;
058:
059: /**
060: * Constructor.
061: */
062: QueryExecutor(String dateFormat, String timeFormat,
063: String timestampFormat) {
064: this .dateFormat = dateFormat;
065: this .timeFormat = timeFormat;
066: this .timestampFormat = timestampFormat;
067: defaultDateFormat = new SimpleDateFormat(dateFormat);
068: defaultTimeFormat = new SimpleDateFormat(timeFormat);
069: defaultTimestampFormat = new SimpleDateFormat(timestampFormat);
070: }
071:
072: /**
073: * Check if a column is a numeric column.
074: * @param metadata the metadata describing the resultset
075: * @param column the column to check
076: * @return true if the column is numeric, false otherwise.
077: */
078: private boolean isNumeric(ResultSetMetaData metadata, int column)
079: throws SQLException {
080: switch (metadata.getColumnType(column)) {
081: case Types.BIGINT:
082: case Types.BOOLEAN:
083: case Types.DECIMAL:
084: case Types.DOUBLE:
085: case Types.FLOAT:
086: case Types.INTEGER:
087: case Types.NUMERIC:
088: case Types.SMALLINT:
089: case Types.TINYINT:
090: return true;
091: }
092: return false;
093: }
094:
095: /**
096: * Returns the width at wich a column should be displayed.
097: * Usually the ResultSetMetaData will be responsible for this width, but a few exceptions
098: * are made (this would typicall be the case for dates).
099: * A minimum of 4 is used, so that NULL values won't break the layout.
100: * @param metadata the metadata describing the resultset
101: * @param column the column to check
102: * @return the width in characters that should be used to display the column.
103: */
104: private int getColumnWidth(ResultSetMetaData metadata, int column)
105: throws SQLException {
106: switch (metadata.getColumnType(column)) {
107: case Types.DATE:
108: return dateFormat.length();
109: case Types.TIMESTAMP:
110: return timestampFormat.length();
111: case Types.TIME:
112: return timeFormat.length();
113: }
114: // Let's assume for now that most columns CAN actually contain NULL values, and therefore we want every column to have a minimum width of 4
115: return Math.max(4, metadata.getColumnDisplaySize(column));
116: }
117:
118: /**
119: * Returns the value to display.
120: * This deals with alignment for numeric columns, formatting for dates and special
121: * treatment for NULL values.
122: * @param ResultSet the query result
123: * @param column the column who's value to take.
124: * @return the formatted value to display
125: */
126: private CharSequence getDisplayValue(ResultSet rset, int column)
127: throws SQLException {
128: ResultSetMetaData metadata = rset.getMetaData();
129:
130: switch (metadata.getColumnType(column)) {
131: case Types.DATE: {
132: if (dateFormat.equals(timestampFormat)) {// for databases that see date as a timestamp
133: Timestamp date = rset.getTimestamp(column);
134: if (date == null) {
135: return "NULL";
136: }
137: return defaultTimestampFormat.format(date);
138: }
139: Date date = rset.getDate(column);
140: if (date == null) {
141: return "NULL";
142: }
143: return defaultDateFormat.format(date);
144: }
145: case Types.TIMESTAMP: {
146: try {
147: Timestamp date = rset.getTimestamp(column);
148: if (date == null) {
149: return "NULL";
150: }
151: return defaultTimestampFormat.format(rset
152: .getTimestamp(column));
153: } catch (SQLException e) {
154: return "NULL";
155: }
156: }
157: case Types.TIME: {
158: Time date = rset.getTime(column);
159: if (date == null) {
160: return "NULL";
161: }
162: return defaultTimeFormat.format(date);
163: }
164: }
165: Object colValue = rset.getObject(column);
166: if (colValue == null) {
167: return "NULL";
168: } else {
169: return colValue.toString();
170: }
171: }
172:
173: public CharSequence cancel() {
174: try {
175: DBConnector.getInstance().getStatement().cancel();
176: cancelled = true;
177: return "Cancel accepted";
178: } catch (SQLException ex) {
179: return "Cancel Failed: " + ex.toString();
180: }
181: }
182:
183: /**
184: * Executes a SQL query.
185: * @param command the SQL query to execute.
186: * @return the formatted result.
187: * @throws SQLException if the database could not execute the SQL query for some reason.
188: */
189: protected CharSequence executeQuery(CharSequence command)
190: throws SQLException {
191: cancelled = false;
192: ResultSet results = DBConnector.getInstance().getStatement()
193: .executeQuery(command.toString());
194:
195: //StringBuffer separator = new StringBuffer();
196: //StringBuffer displayValue = new StringBuffer();
197:
198: ResultSetMetaData metadata = results.getMetaData();
199: long start = System.currentTimeMillis();
200:
201: // TODO specify labels
202: List<String> labels = new ArrayList<String>();
203: for (int col = 1; col <= metadata.getColumnCount(); col++) {
204: StringBuffer labelBuffer = new StringBuffer();
205: labels.add(metadata.getColumnLabel(col));
206: }
207:
208: ResultBuilder displayValue = new ResultBuilder();
209: displayValue.setHeader(labels);
210: int rowCount = 0;
211: while (results.next() && !cancelled) {
212: for (int col = 1; col <= metadata.getColumnCount(); col++) {
213: displayValue
214: .set(
215: col - 1,
216: rowCount,
217: getDisplayValue(results, col),
218: isNumeric(metadata, col) ? ResultBuilder.Alignment.RIGHT
219: : ResultBuilder.Alignment.LEFT);
220: //try {Thread.sleep(10);} catch(Exception e2) {}
221: }
222: rowCount++;
223: }
224: StringBuilder footer = new StringBuilder();
225: footer.append(rowCount);
226: footer.append(" row");
227: if (rowCount != 1) {
228: footer.append("s");
229: }
230: footer.append(" selected.\n");
231: if (cancelled) {
232: footer.append("Aborted....\n");
233: }
234: footer.append("Query took: "
235: + (System.currentTimeMillis() - start) + " millis\n\n");
236: displayValue.setFooter(footer);
237: return displayValue.toString();
238: }
239: }
|