    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     * JDBCCategoryDataset.java
    029     * ------------------------
    030     * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
    031     *
    032     * Original Author:  Bryan Scott; Andy;
    033     * Contributor(s):   David Gilbert (for Object Refinery Limited);
    034     *                   Thomas Morgner;
    035     *
    036     * Changes
    037     * -------
    038     * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from
    039     *               Andy;
    040     * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
    041     * 03-Sep-2002 : Added fix for bug 591385 (DG);
    042     * 18-Sep-2002 : Updated to support BIGINT (BS);
    043     * 16-Oct-2002 : Added fix for bug 586667 (DG);
    044     * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
    045     * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
    046     * 30-Jun-2003 : CVS Write test (BS);
    047     * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
    048     *               method (BS);
    049     * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily
    050     *               transposed if required (DG);
    051     * 10-Sep-2003 : Added support for additional JDBC types (DG);
    052     * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
    053     *               following being highlighted on online forum (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 (TM);
    057     * 04-Dec-2003 : Added missing Javadocs (DG);
    058     * ------------- JFREECHART 1.0.0 ---------------------------------------------
    059     * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if
    060     *               executeQuery() is called more than once (DG);
    061     *
    062     */
    063
    064    package org.jfree.data.jdbc;
    065
    066    import java.sql.Connection;
    067    import java.sql.Date;
    068    import java.sql.DriverManager;
    069    import java.sql.ResultSet;
    070    import java.sql.ResultSetMetaData;
    071    import java.sql.SQLException;
    072    import java.sql.Statement;
    073    import java.sql.Types;
    074
    075    import org.jfree.data.category.CategoryDataset;
    076    import org.jfree.data.category.DefaultCategoryDataset;
    077
    078    /**
    079     * A {@link CategoryDataset} implementation over a database JDBC result set.
    080     * The dataset is populated via a call to {@link #executeQuery(String)} with
    081     * the string SQL query.  The SQL query must return at least two columns.  The
    082     * first column will be the category name and remaining columns values (each
    083     * column represents a series).  Subsequent calls to
    084     * {@link #executeQuery(String)} will refresh the dataset.
    085     * <p>
    086     * The database connection is read-only and no write back facility exists.
    087     * <p>
    088     * NOTE: Many people have found this class too restrictive in general use.
    089     * For the greatest flexibility, please consider writing your own code to read
    090     * data from a <code>ResultSet</code> and populate a
    091     * {@link DefaultCategoryDataset} directly.
    092     */
    093    public class JDBCCategoryDataset extends DefaultCategoryDataset {
    094
    095        /** The database connection. */
    096        private transient Connection connection;
    097
    098        /**
    099         * A flag the controls whether or not the table is transposed.  The default
    100         * is 'true' because this provides the behaviour described in the
    101         * documentation.
    102         */
    103        private boolean transpose = true;
    104
    105
    106        /**
    107         * Creates a new dataset with a database connection.
    108         *
    109         * @param  url  the URL of the database connection.
    110         * @param  driverName  the database driver class name.
    111         * @param  user  the database user.
    112         * @param  passwd  the database user's password.
    113         *
    114         * @throws ClassNotFoundException if the driver cannot be found.
    115         * @throws SQLException if there is an error obtaining a connection to the
    116         *                      database.
    117         */
    118        public JDBCCategoryDataset(String url,
    119                                   String driverName,
    120                                   String user,
    121                                   String passwd)
    122            throws ClassNotFoundException, SQLException {
    123
    124            Class.forName(driverName);
    125            this.connection = DriverManager.getConnection(url, user, passwd);
    126        }
    127
    128        /**
    129         * Create a new dataset with the given database connection.
    130         *
    131         * @param connection  the database connection.
    132         */
    133        public JDBCCategoryDataset(Connection connection) {
    134            if (connection == null) {
    135                throw new NullPointerException("A connection must be supplied.");
    136            }
    137            this.connection = connection;
    138        }
    139
    140        /**
    141         * Creates a new dataset with the given database connection, and executes
    142         * the supplied query to populate the dataset.
    143         *
    144         * @param connection  the connection.
    145         * @param query  the query.
    146         *
    147         * @throws SQLException if there is a problem executing the query.
    148         */
    149        public JDBCCategoryDataset(Connection connection, String query)
    150            throws SQLException {
    151            this(connection);
    152            executeQuery(query);
    153        }
    154
    155        /**
    156         * Returns a flag that controls whether or not the table values are
    157         * transposed when added to the dataset.
    158         *
    159         * @return A boolean.
    160         */
    161        public boolean getTranspose() {
    162            return this.transpose;
    163        }
    164
    165        /**
    166         * Sets a flag that controls whether or not the table values are transposed
    167         * when added to the dataset.
    168         *
    169         * @param transpose  the flag.
    170         */
    171        public void setTranspose(boolean transpose) {
    172            this.transpose = transpose;
    173        }
    174
    175        /**
    176         * Populates the dataset by executing the supplied query against the
    177         * existing database connection.  If no connection exists then no action
    178         * is taken.
    179         * <p>
    180         * The results from the query are extracted and cached locally, thus
    181         * applying an upper limit on how many rows can be retrieved successfully.
    182         *
    183         * @param query  the query.
    184         *
    185         * @throws SQLException if there is a problem executing the query.
    186         */
    187        public void executeQuery(String query) throws SQLException {
    188            executeQuery(this.connection, query);
    189        }
    190
    191        /**
    192         * Populates the dataset by executing the supplied query against the
    193         * existing database connection.  If no connection exists then no action
    194         * is taken.
    195         * <p>
    196         * The results from the query are extracted and cached locally, thus
    197         * applying an upper limit on how many rows can be retrieved successfully.
    198         *
    199         * @param con  the connection.
    200         * @param query  the query.
    201         *
    202         * @throws SQLException if there is a problem executing the query.
    203         */
    204        public void executeQuery(Connection con, String query) throws SQLException {
    205
    206            Statement statement = null;
    207            ResultSet resultSet = null;
    208            try {
    209                statement = con.createStatement();
    210                resultSet = statement.executeQuery(query);
    211                ResultSetMetaData metaData = resultSet.getMetaData();
    212
    213                int columnCount = metaData.getColumnCount();
    214
    215                if (columnCount < 2) {
    216                    throw new SQLException(
    217                        "JDBCCategoryDataset.executeQuery() : insufficient columns "
    218                        + "returned from the database.");
    219                }
    220
    221                // Remove any previous old data
    222                int i = getRowCount();
    223                while (--i >= 0) {
    224                    removeRow(i);
    225                }
    226
    227                while (resultSet.next()) {
    228                    // first column contains the row key...
    229                    Comparable rowKey = resultSet.getString(1);
    230                    for (int column = 2; column <= columnCount; column++) {
    231
    232                        Comparable columnKey = metaData.getColumnName(column);
    233                        int columnType = metaData.getColumnType(column);
    234
    235                        switch (columnType) {
    236                            case Types.TINYINT:
    237                            case Types.SMALLINT:
    238                            case Types.INTEGER:
    239                            case Types.BIGINT:
    240                            case Types.FLOAT:
    241                            case Types.DOUBLE:
    242                            case Types.DECIMAL:
    243                            case Types.NUMERIC:
    244                            case Types.REAL: {
    245                                Number value = (Number) resultSet.getObject(column);
    246                                if (this.transpose) {
    247                                    setValue(value, columnKey, rowKey);
    248                                }
    249                                else {
    250                                    setValue(value, rowKey, columnKey);
    251                                }
    252                                break;
    253                            }
    254                            case Types.DATE:
    255                            case Types.TIME:
    256                            case Types.TIMESTAMP: {
    257                                Date date = (Date) resultSet.getObject(column);
    258                                Number value = new Long(date.getTime());
    259                                if (this.transpose) {
    260                                    setValue(value, columnKey, rowKey);
    261                                }
    262                                else {
    263                                    setValue(value, rowKey, columnKey);
    264                                }
    265                                break;
    266                            }
    267                            case Types.CHAR:
    268                            case Types.VARCHAR:
    269                            case Types.LONGVARCHAR: {
    270                                String string
    271                                    = (String) resultSet.getObject(column);
    272                                try {
    273                                    Number value = Double.valueOf(string);
    274                                    if (this.transpose) {
    275                                        setValue(value, columnKey, rowKey);
    276                                    }
    277                                    else {
    278                                        setValue(value, rowKey, columnKey);
    279                                    }
    280                                }
    281                                catch (NumberFormatException e) {
    282                                    // suppress (value defaults to null)
    283                                }
    284                                break;
    285                            }
    286                            default:
    287                                // not a value, can't use it (defaults to null)
    288                                break;
    289                        }
    290                    }
    291                }
    292
    293                fireDatasetChanged();
    294            }
    295            finally {
    296                if (resultSet != null) {
    297                    try {
    298                        resultSet.close();
    299                    }
    300                    catch (Exception e) {
    301                        // report this?
    302                    }
    303                }
    304                if (statement != null) {
    305                    try {
    306                        statement.close();
    307                    }
    308                    catch (Exception e) {
    309                        // report this?
    310                    }
    311                }
    312            }
    313        }
    314
    315    }