QueryExecutor.java :  » Database-Client » sqlshell » nl » improved » sqlclient » Java Open Source

Java Open Source » Database Client » sqlshell 
sqlshell » nl » improved » sqlclient » QueryExecutor.java
/*
 * 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();
    }
}
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.