/*
* Copyright 2007 Roy van der Kuil (roy@vanderkuil.nl) and Stefan Rotman (stefan@rotman.net)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package nl.improved.sqlclient;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.List;
import java.util.ArrayList;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import nl.improved.sqlclient.util.ResultBuilder;
/**
* Class to execute SQL Queries.
* Typical examples would be:
* <code>
* QueryExecutor qe = new QueryExecutor();
* qe.executeQuery("select * from test where id=12;");
* </code>
* which would then print the resulting row with id 12.
* If the table <tt>test</tt> does not exist, or doesn not have an <tt>id</tt> column,
* a {@link java.sql.SQLException} is being thrown.
*
* @see StatementExecutor
*/
public class QueryExecutor {
/**
* Used to format dates with unspecified patterns.
*/
private DateFormat defaultDateFormat, defaultTimeFormat, defaultTimestampFormat;
private String timeFormat;
private String timestampFormat;
private String dateFormat;
private boolean cancelled = false;
/**
* Constructor.
*/
QueryExecutor(String dateFormat, String timeFormat, String timestampFormat) {
this.dateFormat = dateFormat;
this.timeFormat = timeFormat;
this.timestampFormat = timestampFormat;
defaultDateFormat = new SimpleDateFormat(dateFormat);
defaultTimeFormat = new SimpleDateFormat(timeFormat);
defaultTimestampFormat = new SimpleDateFormat(timestampFormat);
}
/**
* Check if a column is a numeric column.
* @param metadata the metadata describing the resultset
* @param column the column to check
* @return true if the column is numeric, false otherwise.
*/
private boolean isNumeric(ResultSetMetaData metadata, int column) throws SQLException {
switch (metadata.getColumnType(column)) {
case Types.BIGINT:
case Types.BOOLEAN:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.INTEGER:
case Types.NUMERIC:
case Types.SMALLINT:
case Types.TINYINT:
return true;
}
return false;
}
/**
* Returns the width at wich a column should be displayed.
* Usually the ResultSetMetaData will be responsible for this width, but a few exceptions
* are made (this would typicall be the case for dates).
* A minimum of 4 is used, so that NULL values won't break the layout.
* @param metadata the metadata describing the resultset
* @param column the column to check
* @return the width in characters that should be used to display the column.
*/
private int getColumnWidth(ResultSetMetaData metadata, int column) throws SQLException {
switch (metadata.getColumnType(column)) {
case Types.DATE:
return dateFormat.length();
case Types.TIMESTAMP:
return timestampFormat.length();
case Types.TIME:
return timeFormat.length();
}
// 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
return Math.max(4, metadata.getColumnDisplaySize(column));
}
/**
* Returns the value to display.
* This deals with alignment for numeric columns, formatting for dates and special
* treatment for NULL values.
* @param ResultSet the query result
* @param column the column who's value to take.
* @return the formatted value to display
*/
private CharSequence getDisplayValue(ResultSet rset, int column) throws SQLException {
ResultSetMetaData metadata = rset.getMetaData();
switch (metadata.getColumnType(column)) {
case Types.DATE: {
if (dateFormat.equals(timestampFormat)) {// for databases that see date as a timestamp
Timestamp date = rset.getTimestamp(column);
if (date == null) {
return "NULL";
}
return defaultTimestampFormat.format(date);
}
Date date = rset.getDate(column);
if (date == null) {
return "NULL";
}
return defaultDateFormat.format(date);
}
case Types.TIMESTAMP: {
try {
Timestamp date = rset.getTimestamp(column);
if (date == null) {
return "NULL";
}
return defaultTimestampFormat.format(rset.getTimestamp(column));
} catch(SQLException e) {
return "NULL";
}
}
case Types.TIME: {
Time date = rset.getTime(column);
if (date == null) {
return "NULL";
}
return defaultTimeFormat.format(date);
}
}
Object colValue = rset.getObject(column);
if (colValue == null) {
return "NULL";
} else {
return colValue.toString();
}
}
public CharSequence cancel() {
try {
DBConnector.getInstance().getStatement().cancel();
cancelled = true;
return "Cancel accepted";
} catch (SQLException ex) {
return "Cancel Failed: "+ ex.toString();
}
}
/**
* Executes a SQL query.
* @param command the SQL query to execute.
* @return the formatted result.
* @throws SQLException if the database could not execute the SQL query for some reason.
*/
protected CharSequence executeQuery(CharSequence command) throws SQLException {
cancelled = false;
ResultSet results = DBConnector.getInstance().getStatement().executeQuery(command.toString());
//StringBuffer separator = new StringBuffer();
//StringBuffer displayValue = new StringBuffer();
ResultSetMetaData metadata = results.getMetaData();
long start = System.currentTimeMillis();
// TODO specify labels
List<String> labels = new ArrayList<String>();
for ( int col = 1; col <= metadata.getColumnCount(); col++) {
StringBuffer labelBuffer = new StringBuffer();
labels.add(metadata.getColumnLabel(col));
}
ResultBuilder displayValue = new ResultBuilder();
displayValue.setHeader(labels);
int rowCount = 0;
while (results.next() && !cancelled) {
for (int col = 1; col <= metadata.getColumnCount(); col++ ) {
displayValue.set(col-1, rowCount, getDisplayValue(results, col), isNumeric(metadata, col) ? ResultBuilder.Alignment.RIGHT : ResultBuilder.Alignment.LEFT);
//try {Thread.sleep(10);} catch(Exception e2) {}
}
rowCount++;
}
StringBuilder footer = new StringBuilder();
footer.append(rowCount);
footer.append(" row");
if (rowCount != 1) {
footer.append("s");
}
footer.append(" selected.\n");
if (cancelled) {
footer.append("Aborted....\n");
}
footer.append("Query took: "+ (System.currentTimeMillis() - start) +" millis\n\n");
displayValue.setFooter(footer);
return displayValue.toString();
}
}
|