    001/* ===========================================================
    002 * JFreeChart : a free chart library for the Java(tm) platform
    003 * ===========================================================
    004 *
    005 * (C) Copyright 2000-2011, 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 * [Oracle and Java are registered trademarks of Oracle and/or its affiliates.
    025 * Other names may be trademarks of their respective owners.]
    026 *
    027 * ------------------------
    028 * JDBCCategoryDataset.java
    029 * ------------------------
    030 * (C) Copyright 2002-2008, 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
    064package org.jfree.data.jdbc;
    065
    066import java.sql.Connection;
    067import java.sql.Date;
    068import java.sql.DriverManager;
    069import java.sql.ResultSet;
    070import java.sql.ResultSetMetaData;
    071import java.sql.SQLException;
    072import java.sql.Statement;
    073import java.sql.Types;
    074
    075import org.jfree.data.category.CategoryDataset;
    076import 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 */
    093public 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}