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 }