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