001/* ===========================================================
002 * JFreeChart : a free chart library for the Java(tm) platform
003 * ===========================================================
004 *
005 * (C) Copyright 2000-2013, 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}