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