jdbcxydataset.java

JFreeChartweb图表

/* ======================================
 * JFreeChart : a free Java chart library
 * ======================================
 *
 * Project Info:  http://www.jfree.org/jfreechart/index.html
 * Project Lead:  David Gilbert ([e-mail]);
 *
 * (C) Copyright 2000-2003, by Object Refinery Limited and Contributors.
 *
 * This library is free software; you can redistribute it and/or modify it under the terms
 * of the GNU Lesser General Public License as published by the Free Software Foundation;
 * either version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License along with this
 * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307, USA.
 *
 * ------------------
 * JDBCXYDataset.java
 * ------------------
 * (C) Copyright 2002, 2003, by Bryan Scott and Contributors.
 *
 * Original Author:  Bryan Scott;
 * Contributor(s):   David Gilbert (for Object Refinery Limited);
 *
 *
 * Changes
 * -------
 * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
 * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support for types.
 * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data source conventions.
 * 26-Apr-2002 : Changed to extend AbstractDataset.
 * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
 * 18-Sep-2002 : Updated to support BIGINT (BS);
 * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
 * 01-Jul-2003 : Added support to query whether a timeseries (BS);
 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) method (BS);
 */

package org.jfree.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Vector;

/**
 *  This class provides an {@link XYDataset} implementation over a database JDBC result set.
 *  The dataset is populated via a call to executeQuery with the string sql query.
 *  The sql query must return at least two columns.  The first column will be
 *  the x-axis and remaining columns y-axis values.
 *  executeQuery can be called a number of times.
 *
 *  The database connection is read-only and no write back facility exists.
 */
public class JDBCXYDataset extends AbstractDataset implements XYDataset, RangeInfo {

    /** The database connection. */
    Connection connection;

    /** The statement. */
    Statement statement;

    /** The result set. */
    ResultSet resultSet;

    /** Information about the result set. */
    ResultSetMetaData metaData;

    /** Column names. */
    String[] columnNames = {};

    /** Rows. */
    Vector rows = new Vector(0);

    /**  The maximum y value of the returned result set */
    protected double maxValue = 0.0;

    /**  The minimum y value of the returned result set */
    protected double minValue = 0.0;

    /** Is this dataset a timeseries ? */
    public boolean isTimeSeries = false;

    /**
     * Creates a new JDBCXYDataset (initially empty) with no database connection.
     *
     */
    public JDBCXYDataset() {
    }

    /**
     * Creates a new JDBCXYDataset (initially empty) and establishes a new database connection.
     *
     * @param  url         URL of the database connection.
     * @param  driverName  The database driver class name.
     * @param  user        The database user.
     * @param  passwd      The database users password.
     */
    public JDBCXYDataset(String url,
                         String driverName,
                         String user,
                         String passwd) {

        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, user, passwd);
            statement = connection.createStatement();
        }
        catch (ClassNotFoundException ex) {
            System.err.println("Cannot find the database driver classes.");
            System.err.println(ex);
        }
        catch (SQLException ex) {
            System.err.println("Cannot connect to this database.");
            System.err.println(ex);
        }
    }

    /**
     * Creates a new JDBCXYDataset (initially empty) using the specified database connection.
     *
     * @param  con  The database connection.
     */
    public JDBCXYDataset(Connection con) {
        try {
            connection = con;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Creates a new JDBCXYDataset using the specified database connection, and populates it
     * using data obtained with the supplied query.
     *
     * @param con   The connection.
     * @param query The SQL query.
     */
    public JDBCXYDataset(Connection con, String query) {
        this(con);
        executeQuery(query);
    }

    /**
     * ExecuteQuery will attempt execute the query passed to it against the
     * existing database connection.  If no connection exists then no action
     * is taken.
     *
     * The results from the query are extracted and cached locally, thus
     * applying an upper limit on how many rows can be retrieved successfully.
     *
     * @param  query  The query to be executed
     */
    public void executeQuery(String query) {
      executeQuery(connection, query);
    }

    /**
     * ExecuteQuery will attempt execute the query passed to it against the
     * provided database connection.  If connection is null then no action is taken
     *
     * The results from the query are extracted and cached locally, thus
     * applying an upper limit on how many rows can be retrieved successfully.
     *
     * @param  query  The query to be executed
     * @param  con    The connection the query is to be executed against
     */
    public void executeQuery(Connection con, String query) {
        Object xObject = null;
        int column = 0;
        int currentColumn = 0;
        int numberOfColumns = 0;
        int numberOfValidColumns = 0;
        int columnTypes[] = null;

        if (con == null) {
            System.err.println("There is no database to execute the query.");
            return;
        }

        try {
            statement = con.createStatement();
            resultSet = statement.executeQuery(query);
            metaData = resultSet.getMetaData();

            numberOfColumns = metaData.getColumnCount();
            columnTypes = new int[numberOfColumns];
            for (column = 0; column < numberOfColumns; column++) {
                try {
                    int type = metaData.getColumnType(column + 1);
                    switch (type) {

                        case Types.NUMERIC:
                        case Types.REAL:
                        case Types.INTEGER:
                        case Types.DOUBLE:
                        case Types.FLOAT:
                        case Types.DECIMAL:
                        case Types.BIT:
                        case Types.DATE:
                        case Types.TIME:
                        case Types.TIMESTAMP:
                        case Types.BIGINT:
                            ++numberOfValidColumns;
                            columnTypes[column] = type;
                            break;
                        default:
                            System.err.println("Unable to load column "
                                + column + " (" + type + ","
                                + metaData.getColumnClassName(column + 1) + ")");
                            columnTypes[column] = Types.NULL;
                            break;
                    }
                }
                catch (SQLException e) {
                    e.printStackTrace();
                    columnTypes[column] = Types.NULL;
                }
            }

            /// First column is X data
            columnNames = new String[numberOfValidColumns - 1];
            /// Get the column names and cache them.
            currentColumn = 0;
            for (column = 1; column < numberOfColumns; column++) {
                if (columnTypes[column] != Types.NULL) {
                    columnNames[currentColumn] = metaData.getColumnLabel(column + 1);
                    ++currentColumn;
                }
            }

            // Might need to add, to free memory from any previous result sets
            if (rows != null) {
                for (column = 0; column < rows.size(); column++) {
                    Vector row = (Vector) rows.get(column);
                    row.removeAllElements();
                }
                rows.removeAllElements();
            }

            // Are we working with a time series.
            switch (columnTypes[0]) {
                case Types.DATE:
                case Types.TIME:
                case Types.TIMESTAMP: