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