    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
    073    package org.jfree.data.jdbc;
    074
    075    import java.sql.Connection;
    076    import java.sql.DriverManager;
    077    import java.sql.ResultSet;
    078    import java.sql.ResultSetMetaData;
    079    import java.sql.SQLException;
    080    import java.sql.Statement;
    081    import java.sql.Types;
    082    import java.util.ArrayList;
    083    import java.util.Date;
    084
    085    import org.jfree.data.Range;
    086    import org.jfree.data.RangeInfo;
    087    import org.jfree.data.general.Dataset;
    088    import org.jfree.data.xy.AbstractXYDataset;
    089    import org.jfree.data.xy.TableXYDataset;
    090    import org.jfree.data.xy.XYDataset;
    091    import 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     */
    102    public 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    }