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 * JDBCCategoryDataset.java 029 * ------------------------ 030 * (C) Copyright 2002-2005, 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 * 059 */ 060 061 package org.jfree.data.jdbc; 062 063 import java.sql.Connection; 064 import java.sql.Date; 065 import java.sql.DriverManager; 066 import java.sql.ResultSet; 067 import java.sql.ResultSetMetaData; 068 import java.sql.SQLException; 069 import java.sql.Statement; 070 import java.sql.Types; 071 072 import org.jfree.data.category.CategoryDataset; 073 import org.jfree.data.category.DefaultCategoryDataset; 074 075 /** 076 * A {@link CategoryDataset} implementation over a database JDBC result set. 077 * The dataset is populated via a call to executeQuery with the string sql 078 * query. 079 * The sql query must return at least two columns. The first column will be 080 * the category name and remaining columns values. 081 * executeQuery can be called a number of times. 082 * <p> 083 * The database connection is read-only and no write back facility exists. 084 */ 085 public class JDBCCategoryDataset extends DefaultCategoryDataset { 086 087 /** The database connection. */ 088 private transient Connection connection; 089 090 /** 091 * A flag the controls whether or not the table is transposed. The default 092 * is 'true' because this provides the behaviour described in the 093 * documentation. 094 */ 095 private boolean transpose = true; 096 097 098 /** 099 * Creates a new dataset with a database connection. 100 * 101 * @param url the URL of the database connection. 102 * @param driverName the database driver class name. 103 * @param user the database user. 104 * @param passwd the database user's password. 105 * 106 * @throws ClassNotFoundException if the driver cannot be found. 107 * @throws SQLException if there is an error obtaining a connection to the 108 * database. 109 */ 110 public JDBCCategoryDataset(String url, 111 String driverName, 112 String user, 113 String passwd) 114 throws ClassNotFoundException, SQLException { 115 116 Class.forName(driverName); 117 this.connection = DriverManager.getConnection(url, user, passwd); 118 } 119 120 /** 121 * Create a new dataset with the given database connection. 122 * 123 * @param connection the database connection. 124 */ 125 public JDBCCategoryDataset(Connection connection) { 126 if (connection == null) { 127 throw new NullPointerException("A connection must be supplied."); 128 } 129 this.connection = connection; 130 } 131 132 /** 133 * Creates a new dataset with the given database connection, and executes 134 * the supplied query to populate the dataset. 135 * 136 * @param connection the connection. 137 * @param query the query. 138 * 139 * @throws SQLException if there is a problem executing the query. 140 */ 141 public JDBCCategoryDataset(Connection connection, String query) 142 throws SQLException { 143 this(connection); 144 executeQuery(query); 145 } 146 147 /** 148 * Returns a flag that controls whether or not the table values are 149 * transposed when added to the dataset. 150 * 151 * @return A boolean. 152 */ 153 public boolean getTranspose() { 154 return this.transpose; 155 } 156 157 /** 158 * Sets a flag that controls whether or not the table values are transposed 159 * when added to the dataset. 160 * 161 * @param transpose the flag. 162 */ 163 public void setTranspose(boolean transpose) { 164 this.transpose = transpose; 165 } 166 167 /** 168 * Populates the dataset by executing the supplied query against the 169 * existing database connection. If no connection exists then no action 170 * is taken. 171 * <p> 172 * The results from the query are extracted and cached locally, thus 173 * applying an upper limit on how many rows can be retrieved successfully. 174 * 175 * @param query the query. 176 * 177 * @throws SQLException if there is a problem executing the query. 178 */ 179 public void executeQuery(String query) throws SQLException { 180 executeQuery(this.connection, query); 181 } 182 183 /** 184 * Populates the dataset by executing the supplied query against the 185 * existing database connection. If no connection exists then no action 186 * is taken. 187 * <p> 188 * The results from the query are extracted and cached locally, thus 189 * applying an upper limit on how many rows can be retrieved successfully. 190 * 191 * @param con the connection. 192 * @param query the query. 193 * 194 * @throws SQLException if there is a problem executing the query. 195 */ 196 public void executeQuery(Connection con, String query) throws SQLException { 197 198 Statement statement = null; 199 ResultSet resultSet = null; 200 try { 201 statement = con.createStatement(); 202 resultSet = statement.executeQuery(query); 203 ResultSetMetaData metaData = resultSet.getMetaData(); 204 205 int columnCount = metaData.getColumnCount(); 206 207 if (columnCount < 2) { 208 throw new SQLException( 209 "JDBCCategoryDataset.executeQuery() : insufficient columns " 210 + "returned from the database."); 211 } 212 213 // Remove any previous old data 214 int i = getRowCount(); 215 for (; i > 0; --i) { 216 removeRow(i); 217 } 218 219 while (resultSet.next()) { 220 // first column contains the row key... 221 Comparable rowKey = resultSet.getString(1); 222 for (int column = 2; column <= columnCount; column++) { 223 224 Comparable columnKey = metaData.getColumnName(column); 225 int columnType = metaData.getColumnType(column); 226 227 switch (columnType) { 228 case Types.TINYINT: 229 case Types.SMALLINT: 230 case Types.INTEGER: 231 case Types.BIGINT: 232 case Types.FLOAT: 233 case Types.DOUBLE: 234 case Types.DECIMAL: 235 case Types.NUMERIC: 236 case Types.REAL: { 237 Number value = (Number) resultSet.getObject(column); 238 if (this.transpose) { 239 setValue(value, columnKey, rowKey); 240 } 241 else { 242 setValue(value, rowKey, columnKey); 243 } 244 break; 245 } 246 case Types.DATE: 247 case Types.TIME: 248 case Types.TIMESTAMP: { 249 Date date = (Date) resultSet.getObject(column); 250 Number value = new Long(date.getTime()); 251 if (this.transpose) { 252 setValue(value, columnKey, rowKey); 253 } 254 else { 255 setValue(value, rowKey, columnKey); 256 } 257 break; 258 } 259 case Types.CHAR: 260 case Types.VARCHAR: 261 case Types.LONGVARCHAR: { 262 String string 263 = (String) resultSet.getObject(column); 264 try { 265 Number value = Double.valueOf(string); 266 if (this.transpose) { 267 setValue(value, columnKey, rowKey); 268 } 269 else { 270 setValue(value, rowKey, columnKey); 271 } 272 } 273 catch (NumberFormatException e) { 274 // suppress (value defaults to null) 275 } 276 break; 277 } 278 default: 279 // not a value, can't use it (defaults to null) 280 break; 281 } 282 } 283 } 284 285 fireDatasetChanged(); 286 } 287 finally { 288 if (resultSet != null) { 289 try { 290 resultSet.close(); 291 } 292 catch (Exception e) { 293 // report this? 294 } 295 } 296 if (statement != null) { 297 try { 298 statement.close(); 299 } 300 catch (Exception e) { 301 // report this? 302 } 303 } 304 } 305 } 306 307 }