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