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 }