    001    /* ===========================================================
    002     * JFreeChart : a free chart library for the Java(tm) platform
    003     * ===========================================================
    004     *
    005     * (C) Copyright 2000-2005, 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     * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
    025     * in the United States and other countries.]
    026     *
    027     * ------------------
    028     * JDBCXYDataset.java
    029     * ------------------
    030     * (C) Copyright 2002-2005, 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     *
    068     */
    069
    070    package org.jfree.data.jdbc;
    071
    072    import java.sql.Connection;
    073    import java.sql.DriverManager;
    074    import java.sql.ResultSet;
    075    import java.sql.ResultSetMetaData;
    076    import java.sql.SQLException;
    077    import java.sql.Statement;
    078    import java.sql.Types;
    079    import java.util.ArrayList;
    080    import java.util.Date;
    081
    082    import org.jfree.data.Range;
    083    import org.jfree.data.RangeInfo;
    084    import org.jfree.data.general.Dataset;
    085    import org.jfree.data.xy.AbstractXYDataset;
    086    import org.jfree.data.xy.TableXYDataset;
    087    import org.jfree.data.xy.XYDataset;
    088    import org.jfree.util.Log;
    089
    090    /**
    091     * This class provides an {@link XYDataset} implementation over a database
    092     * JDBC result set.  The dataset is populated via a call to executeQuery with
    093     * the string sql query.  The sql query must return at least two columns.
    094     * The first column will be the x-axis and remaining columns y-axis values.
    095     * executeQuery can be called a number of times.
    096     *
    097     * The database connection is read-only and no write back facility exists.
    098     */
    099    public class JDBCXYDataset extends AbstractXYDataset
    100                               implements XYDataset,
    101                                          TableXYDataset,
    102                                          RangeInfo {
    103
    104        /** The database connection. */
    105        private transient Connection connection;
    106
    107        /** Column names. */
    108        private String[] columnNames = {};
    109
    110        /** Rows. */
    111        private ArrayList rows;
    112
    113        /** The maximum y value of the returned result set */
    114        private double maxValue = 0.0;
    115
    116        /** The minimum y value of the returned result set */
    117        private double minValue = 0.0;
    118
    119        /** Is this dataset a timeseries ? */
    120        private boolean isTimeSeries = false;
    121
    122        /**
    123         * Creates a new JDBCXYDataset (initially empty) with no database
    124         * connection.
    125         */
    126        private JDBCXYDataset() {
    127            this.rows = new ArrayList();
    128        }
    129
    130        /**
    131         * Creates a new dataset (initially empty) and establishes a new database
    132         * connection.
    133         *
    134         * @param  url  URL of the database connection.
    135         * @param  driverName  the database driver class name.
    136         * @param  user  the database user.
    137         * @param  password  the database user's password.
    138         *
    139         * @throws ClassNotFoundException if the driver cannot be found.
    140         * @throws SQLException if there is a problem connecting to the database.
    141         */
    142        public JDBCXYDataset(String url,
    143                             String driverName,
    144                             String user,
    145                             String password)
    146            throws SQLException, ClassNotFoundException {
    147
    148            this();
    149            Class.forName(driverName);
    150            this.connection = DriverManager.getConnection(url, user, password);
    151        }
    152
    153        /**
    154         * Creates a new dataset (initially empty) using the specified database
    155         * connection.
    156         *
    157         * @param  con  the database connection.
    158         *
    159         * @throws SQLException if there is a problem connecting to the database.
    160         */
    161        public JDBCXYDataset(Connection con) throws SQLException {
    162            this();
    163            this.connection = con;
    164        }
    165
    166        /**
    167         * Creates a new dataset using the specified database connection, and
    168         * populates it using data obtained with the supplied query.
    169         *
    170         * @param con  the connection.
    171         * @param query  the SQL query.
    172         *
    173         * @throws SQLException if there is a problem executing the query.
    174         */
    175        public JDBCXYDataset(Connection con, String query) throws SQLException {
    176            this(con);
    177            executeQuery(query);
    178        }
    179
    180        /**
    181         * Returns <code>true</code> if the dataset represents time series data,
    182         * and <code>false</code> otherwise.
    183         *
    184         * @return A boolean.
    185         */
    186        public boolean isTimeSeries() {
    187            return this.isTimeSeries;
    188        }
    189
    190        /**
    191         * Sets a flag that indicates whether or not the data represents a time
    192         * series.
    193         *
    194         * @param timeSeries  the new value of the flag.
    195         */
    196        public void setTimeSeries(boolean timeSeries) {
    197            this.isTimeSeries = timeSeries;
    198        }
    199
    200        /**
    201         * ExecuteQuery will attempt execute the query passed to it against the
    202         * existing database connection.  If no connection exists then no action
    203         * is taken.
    204         *
    205         * The results from the query are extracted and cached locally, thus
    206         * applying an upper limit on how many rows can be retrieved successfully.
    207         *
    208         * @param  query  the query to be executed.
    209         *
    210         * @throws SQLException if there is a problem executing the query.
    211         */
    212        public void executeQuery(String query) throws SQLException {
    213            executeQuery(this.connection, query);
    214        }
    215
    216        /**
    217         * ExecuteQuery will attempt execute the query passed to it against the
    218         * provided database connection.  If connection is null then no action is
    219         * taken.
    220         *
    221         * The results from the query are extracted and cached locally, thus
    222         * applying an upper limit on how many rows can be retrieved successfully.
    223         *
    224         * @param  query  the query to be executed.
    225         * @param  con  the connection the query is to be executed against.
    226         *
    227         * @throws SQLException if there is a problem executing the query.
    228         */
    229        public void executeQuery(Connection con, String query)
    230            throws SQLException {
    231
    232            if (con == null) {
    233                throw new SQLException(
    234                    "There is no database to execute the query."
    235                );
    236            }
    237
    238            ResultSet resultSet = null;
    239            Statement statement = null;
    240            try {
    241                statement = con.createStatement();
    242                resultSet = statement.executeQuery(query);
    243                ResultSetMetaData metaData = resultSet.getMetaData();
    244
    245                int numberOfColumns = metaData.getColumnCount();
    246                int numberOfValidColumns = 0;
    247                int [] columnTypes = new int[numberOfColumns];
    248                for (int column = 0; column < numberOfColumns; column++) {
    249                    try {
    250                        int type = metaData.getColumnType(column + 1);
    251                        switch (type) {
    252
    253                            case Types.NUMERIC:
    254                            case Types.REAL:
    255                            case Types.INTEGER:
    256                            case Types.DOUBLE:
    257                            case Types.FLOAT:
    258                            case Types.DECIMAL:
    259                            case Types.BIT:
    260                            case Types.DATE:
    261                            case Types.TIME:
    262                            case Types.TIMESTAMP:
    263                            case Types.BIGINT:
    264                            case Types.SMALLINT:
    265                                ++numberOfValidColumns;
    266                                columnTypes[column] = type;
    267                                break;
    268                            default:
    269                                Log.warn(
    270                                    "Unable to load column "
    271                                    + column + " (" + type + ","
    272                                    + metaData.getColumnClassName(column + 1)
    273                                    + ")"
    274                                );
    275                                columnTypes[column] = Types.NULL;
    276                                break;
    277                        }
    278                    }
    279                    catch (SQLException e) {
    280                        columnTypes[column] = Types.NULL;
    281                        throw e;
    282                    }
    283                }
    284
    285
    286                if (numberOfValidColumns <= 1) {
    287                    throw new SQLException(
    288                        "Not enough valid columns where generated by query."
    289                    );
    290                }
    291
    292                /// First column is X data
    293                this.columnNames = new String[numberOfValidColumns - 1];
    294                /// Get the column names and cache them.
    295                int currentColumn = 0;
    296                for (int column = 1; column < numberOfColumns; column++) {
    297                    if (columnTypes[column] != Types.NULL) {
    298                        this.columnNames[currentColumn]
    299                            = metaData.getColumnLabel(column + 1);
    300                        ++currentColumn;
    301                    }
    302                }
    303
    304                // Might need to add, to free memory from any previous result sets
    305                if (this.rows != null) {
    306                    for (int column = 0; column < this.rows.size(); column++) {
    307                        ArrayList row = (ArrayList) this.rows.get(column);
    308                        row.clear();
    309                    }
    310                    this.rows.clear();
    311                }
    312
    313                // Are we working with a time series.
    314                switch (columnTypes[0]) {
    315                    case Types.DATE:
    316                    case Types.TIME:
    317                    case Types.TIMESTAMP:
    318                        this.isTimeSeries = true;
    319                        break;
    320                    default :
    321                        this.isTimeSeries = false;
    322                        break;
    323                }
    324
    325                // Get all rows.
    326                // rows = new ArrayList();
    327                while (resultSet.next()) {
    328                    ArrayList newRow = new ArrayList();
    329                    for (int column = 0; column < numberOfColumns; column++) {
    330                        Object xObject = resultSet.getObject(column + 1);
    331                        switch (columnTypes[column]) {
    332                            case Types.NUMERIC:
    333                            case Types.REAL:
    334                            case Types.INTEGER:
    335                            case Types.DOUBLE:
    336                            case Types.FLOAT:
    337                            case Types.DECIMAL:
    338                            case Types.BIGINT:
    339                            case Types.SMALLINT:
    340                                newRow.add(xObject);
    341                                break;
    342
    343                            case Types.DATE:
    344                            case Types.TIME:
    345                            case Types.TIMESTAMP:
    346                                newRow.add(new Long(((Date) xObject).getTime()));
    347                                break;
    348                            case Types.NULL:
    349                                break;
    350                            default:
    351                                System.err.println("Unknown data");
    352                                columnTypes[column] = Types.NULL;
    353                                break;
    354                        }
    355                    }
    356                    this.rows.add(newRow);
    357                }
    358
    359                /// a kludge to make everything work when no rows returned
    360                if (this.rows.size() == 0) {
    361                    ArrayList newRow = new ArrayList();
    362                    for (int column = 0; column < numberOfColumns; column++) {
    363                        if (columnTypes[column] != Types.NULL) {
    364                            newRow.add(new Integer(0));
    365                        }
    366                    }
    367                    this.rows.add(newRow);
    368                }
    369
    370                /// Determine max and min values.
    371                if (this.rows.size() < 1) {
    372                    this.maxValue = 0.0;
    373                    this.minValue = 0.0;
    374                }
    375                else {
    376                    ArrayList row = (ArrayList) this.rows.get(0);
    377                    this.maxValue = Double.NEGATIVE_INFINITY;
    378                    this.minValue = Double.POSITIVE_INFINITY;
    379                    for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
    380                        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        public int getLegendItemCount() {
    515            return getSeriesCount();
    516        }
    517
    518        /**
    519         * Returns the legend item labels.
    520         *
    521         * @return The legend item labels.
    522         */
    523        public String[] getLegendItemLabels() {
    524            return this.columnNames;
    525        }
    526
    527        /**
    528         * Close the database connection
    529         */
    530        public void close() {
    531
    532            try {
    533                this.connection.close();
    534            }
    535            catch (Exception e) {
    536                System.err.println("JdbcXYDataset: swallowing exception.");
    537            }
    538
    539        }
    540
    541        /**
    542         * Returns the minimum y-value in the dataset.
    543         *
    544         * @param includeInterval  a flag that determines whether or not the
    545         *                         y-interval is taken into account.
    546         *
    547         * @return The minimum value.
    548         */
    549        public double getRangeLowerBound(boolean includeInterval) {
    550            return this.minValue;
    551        }
    552
    553        /**
    554         * Returns the maximum y-value in the dataset.
    555         *
    556         * @param includeInterval  a flag that determines whether or not the
    557         *                         y-interval is taken into account.
    558         *
    559         * @return The maximum value.
    560         */
    561        public double getRangeUpperBound(boolean includeInterval) {
    562            return this.maxValue;
    563        }
    564
    565        /**
    566         * Returns the range of the values in this dataset's range.
    567         *
    568         * @param includeInterval  a flag that determines whether or not the
    569         *                         y-interval is taken into account.
    570         *
    571         * @return The range.
    572         */
    573        public Range getRangeBounds(boolean includeInterval) {
    574            return new Range(this.minValue, this.maxValue);
    575        }
    576
    577    }