    001    /* ===========================================================
    002     * JFreeChart : a free chart library for the Java(tm) platform
    003     * ===========================================================
    004     *
    005     * (C) Copyright 2000-2006, 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-2006, 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     *
    070     */
    071
    072    package org.jfree.data.jdbc;
    073
    074    import java.sql.Connection;
    075    import java.sql.DriverManager;
    076    import java.sql.ResultSet;
    077    import java.sql.ResultSetMetaData;
    078    import java.sql.SQLException;
    079    import java.sql.Statement;
    080    import java.sql.Types;
    081    import java.util.ArrayList;
    082    import java.util.Date;
    083
    084    import org.jfree.data.Range;
    085    import org.jfree.data.RangeInfo;
    086    import org.jfree.data.general.Dataset;
    087    import org.jfree.data.xy.AbstractXYDataset;
    088    import org.jfree.data.xy.TableXYDataset;
    089    import org.jfree.data.xy.XYDataset;
    090    import org.jfree.util.Log;
    091
    092    /**
    093     * This class provides an {@link XYDataset} implementation over a database
    094     * JDBC result set.  The dataset is populated via a call to executeQuery with
    095     * the string sql query.  The sql query must return at least two columns.
    096     * The first column will be the x-axis and remaining columns y-axis values.
    097     * executeQuery can be called a number of times.
    098     *
    099     * The database connection is read-only and no write back facility exists.
    100     */
    101    public class JDBCXYDataset extends AbstractXYDataset
    102                               implements XYDataset,
    103                                          TableXYDataset,
    104                                          RangeInfo {
    105
    106        /** The database connection. */
    107        private transient Connection connection;
    108
    109        /** Column names. */
    110        private String[] columnNames = {};
    111
    112        /** Rows. */
    113        private ArrayList rows;
    114
    115        /** The maximum y value of the returned result set */
    116        private double maxValue = 0.0;
    117
    118        /** The minimum y value of the returned result set */
    119        private double minValue = 0.0;
    120
    121        /** Is this dataset a timeseries ? */
    122        private boolean isTimeSeries = false;
    123
    124        /**
    125         * Creates a new JDBCXYDataset (initially empty) with no database
    126         * connection.
    127         */
    128        private JDBCXYDataset() {
    129            this.rows = new ArrayList();
    130        }
    131
    132        /**
    133         * Creates a new dataset (initially empty) and establishes a new database
    134         * connection.
    135         *
    136         * @param  url  URL of the database connection.
    137         * @param  driverName  the database driver class name.
    138         * @param  user  the database user.
    139         * @param  password  the database user's password.
    140         *
    141         * @throws ClassNotFoundException if the driver cannot be found.
    142         * @throws SQLException if there is a problem connecting to the database.
    143         */
    144        public JDBCXYDataset(String url,
    145                             String driverName,
    146                             String user,
    147                             String password)
    148            throws SQLException, ClassNotFoundException {
    149
    150            this();
    151            Class.forName(driverName);
    152            this.connection = DriverManager.getConnection(url, user, password);
    153        }
    154
    155        /**
    156         * Creates a new dataset (initially empty) using the specified database
    157         * connection.
    158         *
    159         * @param  con  the database connection.
    160         *
    161         * @throws SQLException if there is a problem connecting to the database.
    162         */
    163        public JDBCXYDataset(Connection con) throws SQLException {
    164            this();
    165            this.connection = con;
    166        }
    167
    168        /**
    169         * Creates a new dataset using the specified database connection, and
    170         * populates it using data obtained with the supplied query.
    171         *
    172         * @param con  the connection.
    173         * @param query  the SQL query.
    174         *
    175         * @throws SQLException if there is a problem executing the query.
    176         */
    177        public JDBCXYDataset(Connection con, String query) throws SQLException {
    178            this(con);
    179            executeQuery(query);
    180        }
    181
    182        /**
    183         * Returns <code>true</code> if the dataset represents time series data,
    184         * and <code>false</code> otherwise.
    185         *
    186         * @return A boolean.
    187         */
    188        public boolean isTimeSeries() {
    189            return this.isTimeSeries;
    190        }
    191
    192        /**
    193         * Sets a flag that indicates whether or not the data represents a time
    194         * series.
    195         *
    196         * @param timeSeries  the new value of the flag.
    197         */
    198        public void setTimeSeries(boolean timeSeries) {
    199            this.isTimeSeries = timeSeries;
    200        }
    201
    202        /**
    203         * ExecuteQuery will attempt execute the query passed to it against the
    204         * existing database connection.  If no connection exists then no action
    205         * is taken.
    206         *
    207         * The results from the query are extracted and cached locally, thus
    208         * applying an upper limit on how many rows can be retrieved successfully.
    209         *
    210         * @param  query  the query to be executed.
    211         *
    212         * @throws SQLException if there is a problem executing the query.
    213         */
    214        public void executeQuery(String query) throws SQLException {
    215            executeQuery(this.connection, query);
    216        }
    217
    218        /**
    219         * ExecuteQuery will attempt execute the query passed to it against the
    220         * provided database connection.  If connection is null then no action is
    221         * taken.
    222         *
    223         * The results from the query are extracted and cached locally, thus
    224         * applying an upper limit on how many rows can be retrieved successfully.
    225         *
    226         * @param  query  the query to be executed.
    227         * @param  con  the connection the query is to be executed against.
    228         *
    229         * @throws SQLException if there is a problem executing the query.
    230         */
    231        public void executeQuery(Connection con, String query)
    232            throws SQLException {
    233
    234            if (con == null) {
    235                throw new SQLException(
    236                    "There is no database to execute the query."
    237                );
    238            }
    239
    240            ResultSet resultSet = null;
    241            Statement statement = null;
    242            try {
    243                statement = con.createStatement();
    244                resultSet = statement.executeQuery(query);
    245                ResultSetMetaData metaData = resultSet.getMetaData();
    246
    247                int numberOfColumns = metaData.getColumnCount();
    248                int numberOfValidColumns = 0;
    249                int [] columnTypes = new int[numberOfColumns];
    250                for (int column = 0; column < numberOfColumns; column++) {
    251                    try {
    252                        int type = metaData.getColumnType(column + 1);
    253                        switch (type) {
    254
    255                            case Types.NUMERIC:
    256                            case Types.REAL:
    257                            case Types.INTEGER:
    258                            case Types.DOUBLE:
    259                            case Types.FLOAT:
    260                            case Types.DECIMAL:
    261                            case Types.BIT:
    262                            case Types.DATE:
    263                            case Types.TIME:
    264                            case Types.TIMESTAMP:
    265                            case Types.BIGINT:
    266                            case Types.SMALLINT:
    267                                ++numberOfValidColumns;
    268                                columnTypes[column] = type;
    269                                break;
    270                            default:
    271                                Log.warn(
    272                                    "Unable to load column "
    273                                    + column + " (" + type + ","
    274                                    + metaData.getColumnClassName(column + 1)
    275                                    + ")"
    276                                );
    277                                columnTypes[column] = Types.NULL;
    278                                break;
    279                        }
    280                    }
    281                    catch (SQLException e) {
    282                        columnTypes[column] = Types.NULL;
    283                        throw e;
    284                    }
    285                }
    286
    287
    288                if (numberOfValidColumns <= 1) {
    289                    throw new SQLException(
    290                        "Not enough valid columns where generated by query."
    291                    );
    292                }
    293
    294                /// First column is X data
    295                this.columnNames = new String[numberOfValidColumns - 1];
    296                /// Get the column names and cache them.
    297                int currentColumn = 0;
    298                for (int column = 1; column < numberOfColumns; column++) {
    299                    if (columnTypes[column] != Types.NULL) {
    300                        this.columnNames[currentColumn]
    301                            = metaData.getColumnLabel(column + 1);
    302                        ++currentColumn;
    303                    }
    304                }
    305
    306                // Might need to add, to free memory from any previous result sets
    307                if (this.rows != null) {
    308                    for (int column = 0; column < this.rows.size(); column++) {
    309                        ArrayList row = (ArrayList) this.rows.get(column);
    310                        row.clear();
    311                    }
    312                    this.rows.clear();
    313                }
    314
    315                // Are we working with a time series.
    316                switch (columnTypes[0]) {
    317                    case Types.DATE:
    318                    case Types.TIME:
    319                    case Types.TIMESTAMP:
    320                        this.isTimeSeries = true;
    321                        break;
    322                    default :
    323                        this.isTimeSeries = false;
    324                        break;
    325                }
    326
    327                // Get all rows.
    328                // rows = new ArrayList();
    329                while (resultSet.next()) {
    330                    ArrayList newRow = new ArrayList();
    331                    for (int column = 0; column < numberOfColumns; column++) {
    332                        Object xObject = resultSet.getObject(column + 1);
    333                        switch (columnTypes[column]) {
    334                            case Types.NUMERIC:
    335                            case Types.REAL:
    336                            case Types.INTEGER:
    337                            case Types.DOUBLE:
    338                            case Types.FLOAT:
    339                            case Types.DECIMAL:
    340                            case Types.BIGINT:
    341                            case Types.SMALLINT:
    342                                newRow.add(xObject);
    343                                break;
    344
    345                            case Types.DATE:
    346                            case Types.TIME:
    347                            case Types.TIMESTAMP:
    348                                newRow.add(new Long(((Date) xObject).getTime()));
    349                                break;
    350                            case Types.NULL:
    351                                break;
    352                            default:
    353                                System.err.println("Unknown data");
    354                                columnTypes[column] = Types.NULL;
    355                                break;
    356                        }
    357                    }
    358                    this.rows.add(newRow);
    359                }
    360
    361                /// a kludge to make everything work when no rows returned
    362                if (this.rows.size() == 0) {
    363                    ArrayList newRow = new ArrayList();
    364                    for (int column = 0; column < numberOfColumns; column++) {
    365                        if (columnTypes[column] != Types.NULL) {
    366                            newRow.add(new Integer(0));
    367                        }
    368                    }
    369                    this.rows.add(newRow);
    370                }
    371
    372                /// Determine max and min values.
    373                if (this.rows.size() < 1) {
    374                    this.maxValue = 0.0;
    375                    this.minValue = 0.0;
    376                }
    377                else {
    378                    ArrayList row = (ArrayList) this.rows.get(0);
    379                    this.maxValue = Double.NEGATIVE_INFINITY;
    380                    this.minValue = Double.POSITIVE_INFINITY;
    381                    for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
    382                        row = (ArrayList) this.rows.get(rowNum);
    383                        for (int column = 1; column < numberOfColumns; column++) {
    384                            Object testValue = row.get(column);
    385                            if (testValue != null) {
    386                                double test = ((Number) testValue).doubleValue();
    387
    388                                if (test < this.minValue) {
    389                                    this.minValue = test;
    390                                }
    391                                if (test > this.maxValue) {
    392                                    this.maxValue = test;
    393                                }
    394                            }
    395                        }
    396                    }
    397                }
    398
    399                fireDatasetChanged(); // Tell the listeners a new table has arrived.
    400            }
    401            finally {
    402                if (resultSet != null) {
    403                    try {
    404                        resultSet.close();
    405                    }
    406                    catch (Exception e) {
    407                        // TODO: is this a good idea?
    408                    }
    409                }
    410                if (statement != null) {
    411                    try {
    412                        statement.close();
    413                    }
    414                    catch (Exception e) {
    415                        // TODO: is this a good idea?
    416                    }
    417                }
    418            }
    419
    420        }
    421
    422        /**
    423         * Returns the x-value for the specified series and item.  The
    424         * implementation is responsible for ensuring that the x-values are
    425         * presented in ascending order.
    426         *
    427         * @param  seriesIndex  the series (zero-based index).
    428         * @param  itemIndex  the item (zero-based index).
    429         *
    430         * @return The x-value
    431         *
    432         * @see XYDataset
    433         */
    434        public Number getX(int seriesIndex, int itemIndex) {
    435            ArrayList row = (ArrayList) this.rows.get(itemIndex);
    436            return (Number) row.get(0);
    437        }
    438
    439        /**
    440         * Returns the y-value for the specified series and item.
    441         *
    442         * @param  seriesIndex  the series (zero-based index).
    443         * @param  itemIndex  the item (zero-based index).
    444         *
    445         * @return The yValue value
    446         *
    447         * @see XYDataset
    448         */
    449        public Number getY(int seriesIndex, int itemIndex) {
    450            ArrayList row = (ArrayList) this.rows.get(itemIndex);
    451            return (Number) row.get(seriesIndex + 1);
    452        }
    453
    454        /**
    455         * Returns the number of items in the specified series.
    456         *
    457         * @param  seriesIndex  the series (zero-based index).
    458         *
    459         * @return The itemCount value
    460         *
    461         * @see XYDataset
    462         */
    463        public int getItemCount(int seriesIndex) {
    464            return this.rows.size();
    465        }
    466
    467        /**
    468         * Returns the number of items in all series.  This method is defined by
    469         * the {@link TableXYDataset} interface.
    470         *
    471         * @return The item count.
    472         */
    473        public int getItemCount() {
    474            return getItemCount(0);
    475        }
    476
    477        /**
    478         * Returns the number of series in the dataset.
    479         *
    480         * @return The seriesCount value
    481         *
    482         * @see XYDataset
    483         * @see Dataset
    484         */
    485        public int getSeriesCount() {
    486            return this.columnNames.length;
    487        }
    488
    489        /**
    490         * Returns the key for the specified series.
    491         *
    492         * @param seriesIndex  the series (zero-based index).
    493         *
    494         * @return The seriesName value
    495         *
    496         * @see XYDataset
    497         * @see Dataset
    498         */
    499        public Comparable getSeriesKey(int seriesIndex) {
    500
    501            if ((seriesIndex < this.columnNames.length)
    502                    && (this.columnNames[seriesIndex] != null)) {
    503                return this.columnNames[seriesIndex];
    504            }
    505            else {
    506                return "";
    507            }
    508
    509        }
    510
    511        /**
    512         * Returns the number of items that should be displayed in the legend.
    513         *
    514         * @return The legendItemCount value
    515         *
    516         * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
    517         *     the API by mistake and is officially deprecated from version 1.0.3
    518         *     onwards).
    519         */
    520        public int getLegendItemCount() {
    521            return getSeriesCount();
    522        }
    523
    524        /**
    525         * Returns the legend item labels.
    526         *
    527         * @return The legend item labels.
    528         *
    529         * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
    530         *     the API by mistake and is officially deprecated from version 1.0.3
    531         *     onwards).
    532         */
    533        public String[] getLegendItemLabels() {
    534            return this.columnNames;
    535        }
    536
    537        /**
    538         * Close the database connection
    539         */
    540        public void close() {
    541
    542            try {
    543                this.connection.close();
    544            }
    545            catch (Exception e) {
    546                System.err.println("JdbcXYDataset: swallowing exception.");
    547            }
    548
    549        }
    550
    551        /**
    552         * Returns the minimum y-value in the dataset.
    553         *
    554         * @param includeInterval  a flag that determines whether or not the
    555         *                         y-interval is taken into account.
    556         *
    557         * @return The minimum value.
    558         */
    559        public double getRangeLowerBound(boolean includeInterval) {
    560            return this.minValue;
    561        }
    562
    563        /**
    564         * Returns the maximum y-value in the dataset.
    565         *
    566         * @param includeInterval  a flag that determines whether or not the
    567         *                         y-interval is taken into account.
    568         *
    569         * @return The maximum value.
    570         */
    571        public double getRangeUpperBound(boolean includeInterval) {
    572            return this.maxValue;
    573        }
    574
    575        /**
    576         * Returns the range of the values in this dataset's range.
    577         *
    578         * @param includeInterval  a flag that determines whether or not the
    579         *                         y-interval is taken into account.
    580         *
    581         * @return The range.
    582         */
    583        public Range getRangeBounds(boolean includeInterval) {
    584            return new Range(this.minValue, this.maxValue);
    585        }
    586
    587    }