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