    001/* ===========================================================
    002 * JFreeChart : a free chart library for the Java(tm) platform
    003 * ===========================================================
    004 *
    005 * (C) Copyright 2000-2011, by Object Refinery Limited and Contributors.
    006 *
    007 * Project Info:  http://www.jfree.org/jfreechart/index.html
    008 *
    009 * This library is free software; you can redistribute it and/or modify it
    010 * under the terms of the GNU Lesser General Public License as published by
    011 * the Free Software Foundation; either version 2.1 of the License, or
    012 * (at your option) any later version.
    013 *
    014 * This library is distributed in the hope that it will be useful, but
    015 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
    016 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
    017 * License for more details.
    018 *
    019 * You should have received a copy of the GNU Lesser General Public
    020 * License along with this library; if not, write to the Free Software
    021 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301,
    022 * USA.
    023 *
    024 * [Oracle and Java are registered trademarks of Oracle and/or its affiliates.
    025 * Other names may be trademarks of their respective owners.]
    026 *
    027 * ------------------
    028 * JDBCXYDataset.java
    029 * ------------------
    030 * (C) Copyright 2002-2009, by Bryan Scott and Contributors.
    031 *
    032 * Original Author:  Bryan Scott;
    033 * Contributor(s):   David Gilbert (for Object Refinery Limited);
    034 *                   Eric Alexander;
    035 *
    036 *
    037 * Changes
    038 * -------
    039 * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
    040 * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support
    041 *               for types.
    042 * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data
    043 *               source conventions.
    044 * 26-Apr-2002 : Changed to extend AbstractDataset.
    045 * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
    046 * 18-Sep-2002 : Updated to support BIGINT (BS);
    047 * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
    048 * 01-Jul-2003 : Added support to query whether a timeseries (BS);
    049 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
    050 *               method (BS);
    051 * 24-Sep-2003 : Added a check to ensure at least two valid columns are
    052 *               returned by the query in executeQuery as suggest in online
    053 *               forum by anonymous (BS);
    054 * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
    055 *               constructor, as without a connection, a query can never be
    056 *               executed.
    057 * 16-Mar-2004 : Added check for null values (EA);
    058 * 05-May-2004 : Now extends AbstractXYDataset (DG);
    059 * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and
    060 *               fixed bug in code that determines the min and max values (see
    061 *               bug id 938138) (DG);
    062 * 15-Jul-2004 : Switched getX() with getXValue() and getY() with
    063 *               getYValue() (DG);
    064 * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG);
    065 * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0
    066 *               release (DG);
    067 * ------------- JFREECHART 1.0.x ---------------------------------------------
    068 * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG);
    069 * 19-May-2009 : Fixed FindBugs warnings, patch by Michal Wozniak (DG);
    070 *
    071 */
    072
    073package org.jfree.data.jdbc;
    074
    075import java.sql.Connection;
    076import java.sql.DriverManager;
    077import java.sql.ResultSet;
    078import java.sql.ResultSetMetaData;
    079import java.sql.SQLException;
    080import java.sql.Statement;
    081import java.sql.Types;
    082import java.util.ArrayList;
    083import java.util.Date;
    084
    085import org.jfree.data.Range;
    086import org.jfree.data.RangeInfo;
    087import org.jfree.data.general.Dataset;
    088import org.jfree.data.xy.AbstractXYDataset;
    089import org.jfree.data.xy.TableXYDataset;
    090import org.jfree.data.xy.XYDataset;
    091import org.jfree.util.Log;
    092
    093/**
    094 * This class provides an {@link XYDataset} implementation over a database
    095 * JDBC result set.  The dataset is populated via a call to executeQuery with
    096 * the string sql query.  The sql query must return at least two columns.
    097 * The first column will be the x-axis and remaining columns y-axis values.
    098 * executeQuery can be called a number of times.
    099 *
    100 * The database connection is read-only and no write back facility exists.
    101 */
    102public class JDBCXYDataset extends AbstractXYDataset
    103        implements XYDataset, TableXYDataset, RangeInfo {
    104
    105    /** The database connection. */
    106    private transient Connection connection;
    107
    108    /** Column names. */
    109    private String[] columnNames = {};
    110
    111    /** Rows. */
    112    private ArrayList rows;
    113
    114    /** The maximum y value of the returned result set */
    115    private double maxValue = 0.0;
    116
    117    /** The minimum y value of the returned result set */
    118    private double minValue = 0.0;
    119
    120    /** Is this dataset a timeseries ? */
    121    private boolean isTimeSeries = false;
    122
    123    /**
    124     * Creates a new JDBCXYDataset (initially empty) with no database
    125     * connection.
    126     */
    127    private JDBCXYDataset() {
    128        this.rows = new ArrayList();
    129    }
    130
    131    /**
    132     * Creates a new dataset (initially empty) and establishes a new database
    133     * connection.
    134     *
    135     * @param  url  URL of the database connection.
    136     * @param  driverName  the database driver class name.
    137     * @param  user  the database user.
    138     * @param  password  the database user's password.
    139     *
    140     * @throws ClassNotFoundException if the driver cannot be found.
    141     * @throws SQLException if there is a problem connecting to the database.
    142     */
    143    public JDBCXYDataset(String url,
    144                         String driverName,
    145                         String user,
    146                         String password)
    147        throws SQLException, ClassNotFoundException {
    148
    149        this();
    150        Class.forName(driverName);
    151        this.connection = DriverManager.getConnection(url, user, password);
    152    }
    153
    154    /**
    155     * Creates a new dataset (initially empty) using the specified database
    156     * connection.
    157     *
    158     * @param  con  the database connection.
    159     *
    160     * @throws SQLException if there is a problem connecting to the database.
    161     */
    162    public JDBCXYDataset(Connection con) throws SQLException {
    163        this();
    164        this.connection = con;
    165    }
    166
    167    /**
    168     * Creates a new dataset using the specified database connection, and
    169     * populates it using data obtained with the supplied query.
    170     *
    171     * @param con  the connection.
    172     * @param query  the SQL query.
    173     *
    174     * @throws SQLException if there is a problem executing the query.
    175     */
    176    public JDBCXYDataset(Connection con, String query) throws SQLException {
    177        this(con);
    178        executeQuery(query);
    179    }
    180
    181    /**
    182     * Returns <code>true</code> if the dataset represents time series data,
    183     * and <code>false</code> otherwise.
    184     *
    185     * @return A boolean.
    186     */
    187    public boolean isTimeSeries() {
    188        return this.isTimeSeries;
    189    }
    190
    191    /**
    192     * Sets a flag that indicates whether or not the data represents a time
    193     * series.
    194     *
    195     * @param timeSeries  the new value of the flag.
    196     */
    197    public void setTimeSeries(boolean timeSeries) {
    198        this.isTimeSeries = timeSeries;
    199    }
    200
    201    /**
    202     * ExecuteQuery will attempt execute the query passed to it against the
    203     * existing database connection.  If no connection exists then no action
    204     * is taken.
    205     *
    206     * The results from the query are extracted and cached locally, thus
    207     * applying an upper limit on how many rows can be retrieved successfully.
    208     *
    209     * @param  query  the query to be executed.
    210     *
    211     * @throws SQLException if there is a problem executing the query.
    212     */
    213    public void executeQuery(String query) throws SQLException {
    214        executeQuery(this.connection, query);
    215    }
    216
    217    /**
    218     * ExecuteQuery will attempt execute the query passed to it against the
    219     * provided database connection.  If connection is null then no action is
    220     * taken.
    221     *
    222     * The results from the query are extracted and cached locally, thus
    223     * applying an upper limit on how many rows can be retrieved successfully.
    224     *
    225     * @param  query  the query to be executed.
    226     * @param  con  the connection the query is to be executed against.
    227     *
    228     * @throws SQLException if there is a problem executing the query.
    229     */
    230    public void executeQuery(Connection con, String query)
    231        throws SQLException {
    232
    233        if (con == null) {
    234            throw new SQLException(
    235                "There is no database to execute the query."
    236            );
    237        }
    238
    239        ResultSet resultSet = null;
    240        Statement statement = null;
    241        try {
    242            statement = con.createStatement();
    243            resultSet = statement.executeQuery(query);
    244            ResultSetMetaData metaData = resultSet.getMetaData();
    245
    246            int numberOfColumns = metaData.getColumnCount();
    247            int numberOfValidColumns = 0;
    248            int [] columnTypes = new int[numberOfColumns];
    249            for (int column = 0; column < numberOfColumns; column++) {
    250                try {
    251                    int type = metaData.getColumnType(column + 1);
    252                    switch (type) {
    253
    254                        case Types.NUMERIC:
    255                        case Types.REAL:
    256                        case Types.INTEGER:
    257                        case Types.DOUBLE:
    258                        case Types.FLOAT:
    259                        case Types.DECIMAL:
    260                        case Types.BIT:
    261                        case Types.DATE:
    262                        case Types.TIME:
    263                        case Types.TIMESTAMP:
    264                        case Types.BIGINT:
    265                        case Types.SMALLINT:
    266                            ++numberOfValidColumns;
    267                            columnTypes[column] = type;
    268                            break;
    269                        default:
    270                            Log.warn(
    271                                "Unable to load column "
    272                                + column + " (" + type + ","
    273                                + metaData.getColumnClassName(column + 1)
    274                                + ")"
    275                            );
    276                            columnTypes[column] = Types.NULL;
    277                            break;
    278                    }
    279                }
    280                catch (SQLException e) {
    281                    columnTypes[column] = Types.NULL;
    282                    throw e;
    283                }
    284            }
    285
    286
    287            if (numberOfValidColumns <= 1) {
    288                throw new SQLException(
    289                    "Not enough valid columns where generated by query."
    290                );
    291            }
    292
    293            /// First column is X data
    294            this.columnNames = new String[numberOfValidColumns - 1];
    295            /// Get the column names and cache them.
    296            int currentColumn = 0;
    297            for (int column = 1; column < numberOfColumns; column++) {
    298                if (columnTypes[column] != Types.NULL) {
    299                    this.columnNames[currentColumn]
    300                        = metaData.getColumnLabel(column + 1);
    301                    ++currentColumn;
    302                }
    303            }
    304
    305            // Might need to add, to free memory from any previous result sets
    306            if (this.rows != null) {
    307                for (int column = 0; column < this.rows.size(); column++) {
    308                    ArrayList row = (ArrayList) this.rows.get(column);
    309                    row.clear();
    310                }
    311                this.rows.clear();
    312            }
    313
    314            // Are we working with a time series.
    315            switch (columnTypes[0]) {
    316                case Types.DATE:
    317                case Types.TIME:
    318                case Types.TIMESTAMP:
    319                    this.isTimeSeries = true;
    320                    break;
    321                default :
    322                    this.isTimeSeries = false;
    323                    break;
    324            }
    325
    326            // Get all rows.
    327            // rows = new ArrayList();
    328            while (resultSet.next()) {
    329                ArrayList newRow = new ArrayList();
    330                for (int column = 0; column < numberOfColumns; column++) {
    331                    Object xObject = resultSet.getObject(column + 1);
    332                    switch (columnTypes[column]) {
    333                        case Types.NUMERIC:
    334                        case Types.REAL:
    335                        case Types.INTEGER:
    336                        case Types.DOUBLE:
    337                        case Types.FLOAT:
    338                        case Types.DECIMAL:
    339                        case Types.BIGINT:
    340                        case Types.SMALLINT:
    341                            newRow.add(xObject);
    342                            break;
    343
    344                        case Types.DATE:
    345                        case Types.TIME:
    346                        case Types.TIMESTAMP:
    347                            newRow.add(new Long(((Date) xObject).getTime()));
    348                            break;
    349                        case Types.NULL:
    350                            break;
    351                        default:
    352                            System.err.println("Unknown data");
    353                            columnTypes[column] = Types.NULL;
    354                            break;
    355                    }
    356                }
    357                this.rows.add(newRow);
    358            }
    359
    360            /// a kludge to make everything work when no rows returned
    361            if (this.rows.size() == 0) {
    362                ArrayList newRow = new ArrayList();
    363                for (int column = 0; column < numberOfColumns; column++) {
    364                    if (columnTypes[column] != Types.NULL) {
    365                        newRow.add(new Integer(0));
    366                    }
    367                }
    368                this.rows.add(newRow);
    369            }
    370
    371            /// Determine max and min values.
    372            if (this.rows.size() < 1) {
    373                this.maxValue = 0.0;
    374                this.minValue = 0.0;
    375            }
    376            else {
    377                this.maxValue = Double.NEGATIVE_INFINITY;
    378                this.minValue = Double.POSITIVE_INFINITY;
    379                for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
    380                    ArrayList row = (ArrayList) this.rows.get(rowNum);
    381                    for (int column = 1; column < numberOfColumns; column++) {
    382                        Object testValue = row.get(column);
    383                        if (testValue != null) {
    384                            double test = ((Number) testValue).doubleValue();
    385
    386                            if (test < this.minValue) {
    387                                this.minValue = test;
    388                            }
    389                            if (test > this.maxValue) {
    390                                this.maxValue = test;
    391                            }
    392                        }
    393                    }
    394                }
    395            }
    396
    397            fireDatasetChanged(); // Tell the listeners a new table has arrived.
    398        }
    399        finally {
    400            if (resultSet != null) {
    401                try {
    402                    resultSet.close();
    403                }
    404                catch (Exception e) {
    405                    // TODO: is this a good idea?
    406                }
    407            }
    408            if (statement != null) {
    409                try {
    410                    statement.close();
    411                }
    412                catch (Exception e) {
    413                    // TODO: is this a good idea?
    414                }
    415            }
    416        }
    417
    418    }
    419
    420    /**
    421     * Returns the x-value for the specified series and item.  The
    422     * implementation is responsible for ensuring that the x-values are
    423     * presented in ascending order.
    424     *
    425     * @param  seriesIndex  the series (zero-based index).
    426     * @param  itemIndex  the item (zero-based index).
    427     *
    428     * @return The x-value
    429     *
    430     * @see XYDataset
    431     */
    432    public Number getX(int seriesIndex, int itemIndex) {
    433        ArrayList row = (ArrayList) this.rows.get(itemIndex);
    434        return (Number) row.get(0);
    435    }
    436
    437    /**
    438     * Returns the y-value for the specified series and item.
    439     *
    440     * @param  seriesIndex  the series (zero-based index).
    441     * @param  itemIndex  the item (zero-based index).
    442     *
    443     * @return The yValue value
    444     *
    445     * @see XYDataset
    446     */
    447    public Number getY(int seriesIndex, int itemIndex) {
    448        ArrayList row = (ArrayList) this.rows.get(itemIndex);
    449        return (Number) row.get(seriesIndex + 1);
    450    }
    451
    452    /**
    453     * Returns the number of items in the specified series.
    454     *
    455     * @param  seriesIndex  the series (zero-based index).
    456     *
    457     * @return The itemCount value
    458     *
    459     * @see XYDataset
    460     */
    461    public int getItemCount(int seriesIndex) {
    462        return this.rows.size();
    463    }
    464
    465    /**
    466     * Returns the number of items in all series.  This method is defined by
    467     * the {@link TableXYDataset} interface.
    468     *
    469     * @return The item count.
    470     */
    471    public int getItemCount() {
    472        return getItemCount(0);
    473    }
    474
    475    /**
    476     * Returns the number of series in the dataset.
    477     *
    478     * @return The seriesCount value
    479     *
    480     * @see XYDataset
    481     * @see Dataset
    482     */
    483    public int getSeriesCount() {
    484        return this.columnNames.length;
    485    }
    486
    487    /**
    488     * Returns the key for the specified series.
    489     *
    490     * @param seriesIndex  the series (zero-based index).
    491     *
    492     * @return The seriesName value
    493     *
    494     * @see XYDataset
    495     * @see Dataset
    496     */
    497    public Comparable getSeriesKey(int seriesIndex) {
    498
    499        if ((seriesIndex < this.columnNames.length)
    500                && (this.columnNames[seriesIndex] != null)) {
    501            return this.columnNames[seriesIndex];
    502        }
    503        else {
    504            return "";
    505        }
    506
    507    }
    508
    509    /**
    510     * Returns the number of items that should be displayed in the legend.
    511     *
    512     * @return The legendItemCount value
    513     *
    514     * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
    515     *     the API by mistake and is officially deprecated from version 1.0.3
    516     *     onwards).
    517     */
    518    public int getLegendItemCount() {
    519        return getSeriesCount();
    520    }
    521
    522    /**
    523     * Returns the legend item labels.
    524     *
    525     * @return The legend item labels.
    526     *
    527     * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
    528     *     the API by mistake and is officially deprecated from version 1.0.3
    529     *     onwards).
    530     */
    531    public String[] getLegendItemLabels() {
    532        return this.columnNames;
    533    }
    534
    535    /**
    536     * Close the database connection
    537     */
    538    public void close() {
    539
    540        try {
    541            this.connection.close();
    542        }
    543        catch (Exception e) {
    544            System.err.println("JdbcXYDataset: swallowing exception.");
    545        }
    546
    547    }
    548
    549    /**
    550     * Returns the minimum y-value in the dataset.
    551     *
    552     * @param includeInterval  a flag that determines whether or not the
    553     *                         y-interval is taken into account.
    554     *
    555     * @return The minimum value.
    556     */
    557    public double getRangeLowerBound(boolean includeInterval) {
    558        return this.minValue;
    559    }
    560
    561    /**
    562     * Returns the maximum y-value in the dataset.
    563     *
    564     * @param includeInterval  a flag that determines whether or not the
    565     *                         y-interval is taken into account.
    566     *
    567     * @return The maximum value.
    568     */
    569    public double getRangeUpperBound(boolean includeInterval) {
    570        return this.maxValue;
    571    }
    572
    573    /**
    574     * Returns the range of the values in this dataset's range.
    575     *
    576     * @param includeInterval  a flag that determines whether or not the
    577     *                         y-interval is taken into account.
    578     *
    579     * @return The range.
    580     */
    581    public Range getRangeBounds(boolean includeInterval) {
    582        return new Range(this.minValue, this.maxValue);
    583    }
    584
    585}