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