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