    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     * JDBCPieDataset.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     * $Id: JDBCPieDataset.java,v 1.3.2.2 2007/02/02 15:03:18 mungady Exp $
    037     *
    038     * Changes
    039     * -------
    040     * 26-Apr-2002 : Creation based on JdbcXYDataSet, but extending
    041     *               DefaultPieDataset (BS);
    042     * 24-Jun-2002 : Removed unnecessary import and local variable (DG);
    043     * 13-Aug-2002 : Updated Javadoc comments and imports, removed default
    044     *               constructor (DG);
    045     * 18-Sep-2002 : Updated to support BIGINT (BS);
    046     * 21-Jan-2003 : Renamed JdbcPieDataset --> JDBCPieDataset (DG);
    047     * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
    048     * 05-Jun-2003 : Updated to support TIME, optimised executeQuery method (BS);
    049     * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
    050     *               method (BS);
    051     * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
    052     *               constructor, as without a connection, a query can never be
    053     *               executed (TM);
    054     * 04-Dec-2003 : Added missing Javadocs (DG);
    055     * ------------- JFREECHART 1.0.x ---------------------------------------------
    056     * 02-Feb-2007 : Removed author tags all over JFreeChart sources (DG);
    057     *
    058     */
    059
    060    package org.jfree.data.jdbc;
    061
    062    import java.sql.Connection;
    063    import java.sql.DriverManager;
    064    import java.sql.SQLException;
    065    import java.sql.Timestamp;
    066    import java.sql.Types;
    067    import java.sql.ResultSet;
    068    import java.sql.ResultSetMetaData;
    069    import java.sql.Statement;
    070
    071    import org.jfree.data.general.DefaultPieDataset;
    072    import org.jfree.data.general.PieDataset;
    073
    074    /**
    075     * A {@link PieDataset} that reads data from a database via JDBC.
    076     * <P>
    077     * A query should be supplied that returns data in two columns, the first
    078     * containing VARCHAR data, and the second containing numerical data.  The
    079     * data is cached in-memory and can be refreshed at any time.
    080     */
    081    public class JDBCPieDataset extends DefaultPieDataset {
    082
    083        /** The database connection. */
    084        private transient Connection connection;
    085
    086        /**
    087         * Creates a new JDBCPieDataset and establishes a new database connection.
    088         *
    089         * @param url  the URL of the database connection.
    090         * @param driverName  the database driver class name.
    091         * @param user  the database user.
    092         * @param password  the database users password.
    093         *
    094         * @throws ClassNotFoundException if the driver cannot be found.
    095         * @throws SQLException if there is a problem obtaining a database
    096         *                      connection.
    097         */
    098        public JDBCPieDataset(String url,
    099                              String driverName,
    100                              String user,
    101                              String password)
    102            throws SQLException, ClassNotFoundException {
    103
    104            Class.forName(driverName);
    105            this.connection = DriverManager.getConnection(url, user, password);
    106        }
    107
    108        /**
    109         * Creates a new JDBCPieDataset using a pre-existing database connection.
    110         * <P>
    111         * The dataset is initially empty, since no query has been supplied yet.
    112         *
    113         * @param con  the database connection.
    114         */
    115        public JDBCPieDataset(Connection con) {
    116            if (con == null) {
    117                throw new NullPointerException("A connection must be supplied.");
    118            }
    119            this.connection = con;
    120        }
    121
    122
    123        /**
    124         * Creates a new JDBCPieDataset using a pre-existing database connection.
    125         * <P>
    126         * The dataset is initialised with the supplied query.
    127         *
    128         * @param con  the database connection.
    129         * @param query  the database connection.
    130         *
    131         * @throws SQLException if there is a problem executing the query.
    132         */
    133        public JDBCPieDataset(Connection con, String query) throws SQLException {
    134            this(con);
    135            executeQuery(query);
    136        }
    137
    138        /**
    139         *  ExecuteQuery will attempt execute the query passed to it against the
    140         *  existing database connection.  If no connection exists then no action
    141         *  is taken.
    142         *  The results from the query are extracted and cached locally, thus
    143         *  applying an upper limit on how many rows can be retrieved successfully.
    144         *
    145         * @param  query  the query to be executed.
    146         *
    147         * @throws SQLException if there is a problem executing the query.
    148         */
    149        public void executeQuery(String query) throws SQLException {
    150          executeQuery(this.connection, query);
    151        }
    152
    153        /**
    154         *  ExecuteQuery will attempt execute the query passed to it against the
    155         *  existing database connection.  If no connection exists then no action
    156         *  is taken.
    157         *  The results from the query are extracted and cached locally, thus
    158         *  applying an upper limit on how many rows can be retrieved successfully.
    159         *
    160         * @param  query  the query to be executed
    161         * @param  con  the connection the query is to be executed against
    162         *
    163         * @throws SQLException if there is a problem executing the query.
    164         */
    165        public void executeQuery(Connection con, String query) throws SQLException {
    166
    167            Statement statement = null;
    168            ResultSet resultSet = null;
    169
    170            try {
    171                statement = con.createStatement();
    172                resultSet = statement.executeQuery(query);
    173                ResultSetMetaData metaData = resultSet.getMetaData();
    174
    175                int columnCount = metaData.getColumnCount();
    176                if (columnCount != 2) {
    177                    throw new SQLException(
    178                        "Invalid sql generated.  PieDataSet requires 2 columns only"
    179                    );
    180                }
    181
    182                int columnType = metaData.getColumnType(2);
    183                double value = Double.NaN;
    184                while (resultSet.next()) {
    185                    Comparable key = resultSet.getString(1);
    186                    switch (columnType) {
    187                        case Types.NUMERIC:
    188                        case Types.REAL:
    189                        case Types.INTEGER:
    190                        case Types.DOUBLE:
    191                        case Types.FLOAT:
    192                        case Types.DECIMAL:
    193                        case Types.BIGINT:
    194                            value = resultSet.getDouble(2);
    195                            setValue(key, value);
    196                            break;
    197
    198                        case Types.DATE:
    199                        case Types.TIME:
    200                        case Types.TIMESTAMP:
    201                            Timestamp date = resultSet.getTimestamp(2);
    202                            value = date.getTime();
    203                            setValue(key, value);
    204                            break;
    205
    206                        default:
    207                            System.err.println(
    208                                "JDBCPieDataset - unknown data type"
    209                            );
    210                            break;
    211                    }
    212                }
    213
    214                fireDatasetChanged();
    215
    216            }
    217            finally {
    218                if (resultSet != null) {
    219                    try {
    220                        resultSet.close();
    221                    }
    222                    catch (Exception e) {
    223                        System.err.println("JDBCPieDataset: swallowing exception.");
    224                    }
    225                }
    226                if (statement != null) {
    227                    try {
    228                        statement.close();
    229                    }
    230                    catch (Exception e) {
    231                        System.err.println("JDBCPieDataset: swallowing exception.");
    232                    }
    233                }
    234            }
    235        }
    236
    237
    238        /**
    239         * Close the database connection
    240         */
    241        public void close() {
    242            try {
    243                this.connection.close();
    244            }
    245            catch (Exception e) {
    246                System.err.println("JdbcXYDataset: swallowing exception.");
    247            }
    248        }
    249    }