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