001 /* =========================================================== 002 * JFreeChart : a free chart library for the Java(tm) platform 003 * =========================================================== 004 * 005 * (C) Copyright 2000-2006, 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 * JDBCCategoryDataset.java 029 * ------------------------ 030 * (C) Copyright 2002-2006, 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, using code contributed from 039 * Andy; 040 * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG); 041 * 03-Sep-2002 : Added fix for bug 591385 (DG); 042 * 18-Sep-2002 : Updated to support BIGINT (BS); 043 * 16-Oct-2002 : Added fix for bug 586667 (DG); 044 * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG); 045 * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG); 046 * 30-Jun-2003 : CVS Write test (BS); 047 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 048 * method (BS); 049 * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily 050 * transposed if required (DG); 051 * 10-Sep-2003 : Added support for additional JDBC types (DG); 052 * 24-Sep-2003 : Added clearing results from previous queries to executeQuery 053 * following being highlighted on online forum (BS); 054 * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default 055 * constructor, as without a connection, a query can never be 056 * executed (TM); 057 * 04-Dec-2003 : Added missing Javadocs (DG); 058 * ------------- JFREECHART 1.0.0 --------------------------------------------- 059 * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if 060 * executeQuery() is called more than once (DG); 061 * 062 */ 063 064 package org.jfree.data.jdbc; 065 066 import java.sql.Connection; 067 import java.sql.Date; 068 import java.sql.DriverManager; 069 import java.sql.ResultSet; 070 import java.sql.ResultSetMetaData; 071 import java.sql.SQLException; 072 import java.sql.Statement; 073 import java.sql.Types; 074 075 import org.jfree.data.category.CategoryDataset; 076 import org.jfree.data.category.DefaultCategoryDataset; 077 078 /** 079 * A {@link CategoryDataset} implementation over a database JDBC result set. 080 * The dataset is populated via a call to {@link #executeQuery(String)} with 081 * the string SQL query. The SQL query must return at least two columns. The 082 * first column will be the category name and remaining columns values (each 083 * column represents a series). Subsequent calls to 084 * {@link #executeQuery(String)} will refresh the dataset. 085 * <p> 086 * The database connection is read-only and no write back facility exists. 087 * <p> 088 * NOTE: Many people have found this class too restrictive in general use. 089 * For the greatest flexibility, please consider writing your own code to read 090 * data from a <code>ResultSet</code> and populate a 091 * {@link DefaultCategoryDataset} directly. 092 */ 093 public class JDBCCategoryDataset extends DefaultCategoryDataset { 094 095 /** The database connection. */ 096 private transient Connection connection; 097 098 /** 099 * A flag the controls whether or not the table is transposed. The default 100 * is 'true' because this provides the behaviour described in the 101 * documentation. 102 */ 103 private boolean transpose = true; 104 105 106 /** 107 * Creates a new dataset with a database connection. 108 * 109 * @param url the URL of the database connection. 110 * @param driverName the database driver class name. 111 * @param user the database user. 112 * @param passwd the database user's password. 113 * 114 * @throws ClassNotFoundException if the driver cannot be found. 115 * @throws SQLException if there is an error obtaining a connection to the 116 * database. 117 */ 118 public JDBCCategoryDataset(String url, 119 String driverName, 120 String user, 121 String passwd) 122 throws ClassNotFoundException, SQLException { 123 124 Class.forName(driverName); 125 this.connection = DriverManager.getConnection(url, user, passwd); 126 } 127 128 /** 129 * Create a new dataset with the given database connection. 130 * 131 * @param connection the database connection. 132 */ 133 public JDBCCategoryDataset(Connection connection) { 134 if (connection == null) { 135 throw new NullPointerException("A connection must be supplied."); 136 } 137 this.connection = connection; 138 } 139 140 /** 141 * Creates a new dataset with the given database connection, and executes 142 * the supplied query to populate the dataset. 143 * 144 * @param connection the connection. 145 * @param query the query. 146 * 147 * @throws SQLException if there is a problem executing the query. 148 */ 149 public JDBCCategoryDataset(Connection connection, String query) 150 throws SQLException { 151 this(connection); 152 executeQuery(query); 153 } 154 155 /** 156 * Returns a flag that controls whether or not the table values are 157 * transposed when added to the dataset. 158 * 159 * @return A boolean. 160 */ 161 public boolean getTranspose() { 162 return this.transpose; 163 } 164 165 /** 166 * Sets a flag that controls whether or not the table values are transposed 167 * when added to the dataset. 168 * 169 * @param transpose the flag. 170 */ 171 public void setTranspose(boolean transpose) { 172 this.transpose = transpose; 173 } 174 175 /** 176 * Populates the dataset by executing the supplied query against the 177 * existing database connection. If no connection exists then no action 178 * is taken. 179 * <p> 180 * The results from the query are extracted and cached locally, thus 181 * applying an upper limit on how many rows can be retrieved successfully. 182 * 183 * @param query the query. 184 * 185 * @throws SQLException if there is a problem executing the query. 186 */ 187 public void executeQuery(String query) throws SQLException { 188 executeQuery(this.connection, query); 189 } 190 191 /** 192 * Populates the dataset by executing the supplied query against the 193 * existing database connection. If no connection exists then no action 194 * is taken. 195 * <p> 196 * The results from the query are extracted and cached locally, thus 197 * applying an upper limit on how many rows can be retrieved successfully. 198 * 199 * @param con the connection. 200 * @param query the query. 201 * 202 * @throws SQLException if there is a problem executing the query. 203 */ 204 public void executeQuery(Connection con, String query) throws SQLException { 205 206 Statement statement = null; 207 ResultSet resultSet = null; 208 try { 209 statement = con.createStatement(); 210 resultSet = statement.executeQuery(query); 211 ResultSetMetaData metaData = resultSet.getMetaData(); 212 213 int columnCount = metaData.getColumnCount(); 214 215 if (columnCount < 2) { 216 throw new SQLException( 217 "JDBCCategoryDataset.executeQuery() : insufficient columns " 218 + "returned from the database."); 219 } 220 221 // Remove any previous old data 222 int i = getRowCount(); 223 while (--i >= 0) { 224 removeRow(i); 225 } 226 227 while (resultSet.next()) { 228 // first column contains the row key... 229 Comparable rowKey = resultSet.getString(1); 230 for (int column = 2; column <= columnCount; column++) { 231 232 Comparable columnKey = metaData.getColumnName(column); 233 int columnType = metaData.getColumnType(column); 234 235 switch (columnType) { 236 case Types.TINYINT: 237 case Types.SMALLINT: 238 case Types.INTEGER: 239 case Types.BIGINT: 240 case Types.FLOAT: 241 case Types.DOUBLE: 242 case Types.DECIMAL: 243 case Types.NUMERIC: 244 case Types.REAL: { 245 Number value = (Number) resultSet.getObject(column); 246 if (this.transpose) { 247 setValue(value, columnKey, rowKey); 248 } 249 else { 250 setValue(value, rowKey, columnKey); 251 } 252 break; 253 } 254 case Types.DATE: 255 case Types.TIME: 256 case Types.TIMESTAMP: { 257 Date date = (Date) resultSet.getObject(column); 258 Number value = new Long(date.getTime()); 259 if (this.transpose) { 260 setValue(value, columnKey, rowKey); 261 } 262 else { 263 setValue(value, rowKey, columnKey); 264 } 265 break; 266 } 267 case Types.CHAR: 268 case Types.VARCHAR: 269 case Types.LONGVARCHAR: { 270 String string 271 = (String) resultSet.getObject(column); 272 try { 273 Number value = Double.valueOf(string); 274 if (this.transpose) { 275 setValue(value, columnKey, rowKey); 276 } 277 else { 278 setValue(value, rowKey, columnKey); 279 } 280 } 281 catch (NumberFormatException e) { 282 // suppress (value defaults to null) 283 } 284 break; 285 } 286 default: 287 // not a value, can't use it (defaults to null) 288 break; 289 } 290 } 291 } 292 293 fireDatasetChanged(); 294 } 295 finally { 296 if (resultSet != null) { 297 try { 298 resultSet.close(); 299 } 300 catch (Exception e) { 301 // report this? 302 } 303 } 304 if (statement != null) { 305 try { 306 statement.close(); 307 } 308 catch (Exception e) { 309 // report this? 310 } 311 } 312 } 313 } 314 315 }