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 }