    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
    059package org.jfree.data.jdbc;
    060
    061import java.sql.Connection;
    062import java.sql.DriverManager;
    063import java.sql.ResultSet;
    064import java.sql.ResultSetMetaData;
    065import java.sql.SQLException;
    066import java.sql.Statement;
    067import java.sql.Timestamp;
    068import java.sql.Types;
    069
    070import org.jfree.data.general.DefaultPieDataset;
    071import 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 */
    080public 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}