    001    /* ===========================================================
    002     * JFreeChart : a free chart library for the Java(tm) platform
    003     * ===========================================================
    004     *
    005     * (C) Copyright 2000-2007, 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-2007, 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        /** For serialization. */
    096        static final long serialVersionUID = -3080395327918844965L;
    097
    098        /** The database connection. */
    099        private transient Connection connection;
    100
    101        /**
    102         * A flag the controls whether or not the table is transposed.  The default
    103         * is 'true' because this provides the behaviour described in the
    104         * documentation.
    105         */
    106        private boolean transpose = true;
    107
    108
    109        /**
    110         * Creates a new dataset with a database connection.
    111         *
    112         * @param  url  the URL of the database connection.
    113         * @param  driverName  the database driver class name.
    114         * @param  user  the database user.
    115         * @param  passwd  the database user's password.
    116         *
    117         * @throws ClassNotFoundException if the driver cannot be found.
    118         * @throws SQLException if there is an error obtaining a connection to the
    119         *                      database.
    120         */
    121        public JDBCCategoryDataset(String url,
    122                                   String driverName,
    123                                   String user,
    124                                   String passwd)
    125            throws ClassNotFoundException, SQLException {
    126
    127            Class.forName(driverName);
    128            this.connection = DriverManager.getConnection(url, user, passwd);
    129        }
    130
    131        /**
    132         * Create a new dataset with the given database connection.
    133         *
    134         * @param connection  the database connection.
    135         */
    136        public JDBCCategoryDataset(Connection connection) {
    137            if (connection == null) {
    138                throw new NullPointerException("A connection must be supplied.");
    139            }
    140            this.connection = connection;
    141        }
    142
    143        /**
    144         * Creates a new dataset with the given database connection, and executes
    145         * the supplied query to populate the dataset.
    146         *
    147         * @param connection  the connection.
    148         * @param query  the query.
    149         *
    150         * @throws SQLException if there is a problem executing the query.
    151         */
    152        public JDBCCategoryDataset(Connection connection, String query)
    153            throws SQLException {
    154            this(connection);
    155            executeQuery(query);
    156        }
    157
    158        /**
    159         * Returns a flag that controls whether or not the table values are
    160         * transposed when added to the dataset.
    161         *
    162         * @return A boolean.
    163         */
    164        public boolean getTranspose() {
    165            return this.transpose;
    166        }
    167
    168        /**
    169         * Sets a flag that controls whether or not the table values are transposed
    170         * when added to the dataset.
    171         *
    172         * @param transpose  the flag.
    173         */
    174        public void setTranspose(boolean transpose) {
    175            this.transpose = transpose;
    176        }
    177
    178        /**
    179         * Populates the dataset by executing the supplied query against the
    180         * existing database connection.  If no connection exists then no action
    181         * is taken.
    182         * <p>
    183         * The results from the query are extracted and cached locally, thus
    184         * applying an upper limit on how many rows can be retrieved successfully.
    185         *
    186         * @param query  the query.
    187         *
    188         * @throws SQLException if there is a problem executing the query.
    189         */
    190        public void executeQuery(String query) throws SQLException {
    191            executeQuery(this.connection, query);
    192        }
    193
    194        /**
    195         * Populates the dataset by executing the supplied query against the
    196         * existing database connection.  If no connection exists then no action
    197         * is taken.
    198         * <p>
    199         * The results from the query are extracted and cached locally, thus
    200         * applying an upper limit on how many rows can be retrieved successfully.
    201         *
    202         * @param con  the connection.
    203         * @param query  the query.
    204         *
    205         * @throws SQLException if there is a problem executing the query.
    206         */
    207        public void executeQuery(Connection con, String query) throws SQLException {
    208
    209            Statement statement = null;
    210            ResultSet resultSet = null;
    211            try {
    212                statement = con.createStatement();
    213                resultSet = statement.executeQuery(query);
    214                ResultSetMetaData metaData = resultSet.getMetaData();
    215
    216                int columnCount = metaData.getColumnCount();
    217
    218                if (columnCount < 2) {
    219                    throw new SQLException(
    220                        "JDBCCategoryDataset.executeQuery() : insufficient columns "
    221                        + "returned from the database.");
    222                }
    223
    224                // Remove any previous old data
    225                int i = getRowCount();
    226                while (--i >= 0) {
    227                    removeRow(i);
    228                }
    229
    230                while (resultSet.next()) {
    231                    // first column contains the row key...
    232                    Comparable rowKey = resultSet.getString(1);
    233                    for (int column = 2; column <= columnCount; column++) {
    234
    235                        Comparable columnKey = metaData.getColumnName(column);
    236                        int columnType = metaData.getColumnType(column);
    237
    238                        switch (columnType) {
    239                            case Types.TINYINT:
    240                            case Types.SMALLINT:
    241                            case Types.INTEGER:
    242                            case Types.BIGINT:
    243                            case Types.FLOAT:
    244                            case Types.DOUBLE:
    245                            case Types.DECIMAL:
    246                            case Types.NUMERIC:
    247                            case Types.REAL: {
    248                                Number value = (Number) resultSet.getObject(column);
    249                                if (this.transpose) {
    250                                    setValue(value, columnKey, rowKey);
    251                                }
    252                                else {
    253                                    setValue(value, rowKey, columnKey);
    254                                }
    255                                break;
    256                            }
    257                            case Types.DATE:
    258                            case Types.TIME:
    259                            case Types.TIMESTAMP: {
    260                                Date date = (Date) resultSet.getObject(column);
    261                                Number value = new Long(date.getTime());
    262                                if (this.transpose) {
    263                                    setValue(value, columnKey, rowKey);
    264                                }
    265                                else {
    266                                    setValue(value, rowKey, columnKey);
    267                                }
    268                                break;
    269                            }
    270                            case Types.CHAR:
    271                            case Types.VARCHAR:
    272                            case Types.LONGVARCHAR: {
    273                                String string
    274                                    = (String) resultSet.getObject(column);
    275                                try {
    276                                    Number value = Double.valueOf(string);
    277                                    if (this.transpose) {
    278                                        setValue(value, columnKey, rowKey);
    279                                    }
    280                                    else {
    281                                        setValue(value, rowKey, columnKey);
    282                                    }
    283                                }
    284                                catch (NumberFormatException e) {
    285                                    // suppress (value defaults to null)
    286                                }
    287                                break;
    288                            }
    289                            default:
    290                                // not a value, can't use it (defaults to null)
    291                                break;
    292                        }
    293                    }
    294                }
    295
    296                fireDatasetChanged();
    297            }
    298            finally {
    299                if (resultSet != null) {
    300                    try {
    301                        resultSet.close();
    302                    }
    303                    catch (Exception e) {
    304                        // report this?
    305                    }
    306                }
    307                if (statement != null) {
    308                    try {
    309                        statement.close();
    310                    }
    311                    catch (Exception e) {
    312                        // report this?
    313                    }
    314                }
    315            }
    316        }
    317
    318    }