Frames | No Frames |
1: /* =========================================================== 2: * JFreeChart : a free chart library for the Java(tm) platform 3: * =========================================================== 4: * 5: * (C) Copyright 2000-2007, by Object Refinery Limited and Contributors. 6: * 7: * Project Info: http://www.jfree.org/jfreechart/index.html 8: * 9: * This library is free software; you can redistribute it and/or modify it 10: * under the terms of the GNU Lesser General Public License as published by 11: * the Free Software Foundation; either version 2.1 of the License, or 12: * (at your option) any later version. 13: * 14: * This library is distributed in the hope that it will be useful, but 15: * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 16: * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public 17: * License for more details. 18: * 19: * You should have received a copy of the GNU Lesser General Public 20: * License along with this library; if not, write to the Free Software 21: * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, 22: * USA. 23: * 24: * [Java is a trademark or registered trademark of Sun Microsystems, Inc. 25: * in the United States and other countries.] 26: * 27: * ------------------------ 28: * JDBCCategoryDataset.java 29: * ------------------------ 30: * (C) Copyright 2002-2007, by Bryan Scott and Contributors. 31: * 32: * Original Author: Bryan Scott; Andy; 33: * Contributor(s): David Gilbert (for Object Refinery Limited); 34: * Thomas Morgner; 35: * 36: * Changes 37: * ------- 38: * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from 39: * Andy; 40: * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG); 41: * 03-Sep-2002 : Added fix for bug 591385 (DG); 42: * 18-Sep-2002 : Updated to support BIGINT (BS); 43: * 16-Oct-2002 : Added fix for bug 586667 (DG); 44: * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG); 45: * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG); 46: * 30-Jun-2003 : CVS Write test (BS); 47: * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 48: * method (BS); 49: * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily 50: * transposed if required (DG); 51: * 10-Sep-2003 : Added support for additional JDBC types (DG); 52: * 24-Sep-2003 : Added clearing results from previous queries to executeQuery 53: * following being highlighted on online forum (BS); 54: * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default 55: * constructor, as without a connection, a query can never be 56: * executed (TM); 57: * 04-Dec-2003 : Added missing Javadocs (DG); 58: * ------------- JFREECHART 1.0.0 --------------------------------------------- 59: * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if 60: * executeQuery() is called more than once (DG); 61: * 62: */ 63: 64: package org.jfree.data.jdbc; 65: 66: import java.sql.Connection; 67: import java.sql.Date; 68: import java.sql.DriverManager; 69: import java.sql.ResultSet; 70: import java.sql.ResultSetMetaData; 71: import java.sql.SQLException; 72: import java.sql.Statement; 73: import java.sql.Types; 74: 75: import org.jfree.data.category.CategoryDataset; 76: import org.jfree.data.category.DefaultCategoryDataset; 77: 78: /** 79: * A {@link CategoryDataset} implementation over a database JDBC result set. 80: * The dataset is populated via a call to {@link #executeQuery(String)} with 81: * the string SQL query. The SQL query must return at least two columns. The 82: * first column will be the category name and remaining columns values (each 83: * column represents a series). Subsequent calls to 84: * {@link #executeQuery(String)} will refresh the dataset. 85: * <p> 86: * The database connection is read-only and no write back facility exists. 87: * <p> 88: * NOTE: Many people have found this class too restrictive in general use. 89: * For the greatest flexibility, please consider writing your own code to read 90: * data from a <code>ResultSet</code> and populate a 91: * {@link DefaultCategoryDataset} directly. 92: */ 93: public class JDBCCategoryDataset extends DefaultCategoryDataset { 94: 95: /** For serialization. */ 96: static final long serialVersionUID = -3080395327918844965L; 97: 98: /** The database connection. */ 99: private transient Connection connection; 100: 101: /** 102: * A flag the controls whether or not the table is transposed. The default 103: * is 'true' because this provides the behaviour described in the 104: * documentation. 105: */ 106: private boolean transpose = true; 107: 108: 109: /** 110: * Creates a new dataset with a database connection. 111: * 112: * @param url the URL of the database connection. 113: * @param driverName the database driver class name. 114: * @param user the database user. 115: * @param passwd the database user's password. 116: * 117: * @throws ClassNotFoundException if the driver cannot be found. 118: * @throws SQLException if there is an error obtaining a connection to the 119: * database. 120: */ 121: public JDBCCategoryDataset(String url, 122: String driverName, 123: String user, 124: String passwd) 125: throws ClassNotFoundException, SQLException { 126: 127: Class.forName(driverName); 128: this.connection = DriverManager.getConnection(url, user, passwd); 129: } 130: 131: /** 132: * Create a new dataset with the given database connection. 133: * 134: * @param connection the database connection. 135: */ 136: public JDBCCategoryDataset(Connection connection) { 137: if (connection == null) { 138: throw new NullPointerException("A connection must be supplied."); 139: } 140: this.connection = connection; 141: } 142: 143: /** 144: * Creates a new dataset with the given database connection, and executes 145: * the supplied query to populate the dataset. 146: * 147: * @param connection the connection. 148: * @param query the query. 149: * 150: * @throws SQLException if there is a problem executing the query. 151: */ 152: public JDBCCategoryDataset(Connection connection, String query) 153: throws SQLException { 154: this(connection); 155: executeQuery(query); 156: } 157: 158: /** 159: * Returns a flag that controls whether or not the table values are 160: * transposed when added to the dataset. 161: * 162: * @return A boolean. 163: */ 164: public boolean getTranspose() { 165: return this.transpose; 166: } 167: 168: /** 169: * Sets a flag that controls whether or not the table values are transposed 170: * when added to the dataset. 171: * 172: * @param transpose the flag. 173: */ 174: public void setTranspose(boolean transpose) { 175: this.transpose = transpose; 176: } 177: 178: /** 179: * Populates the dataset by executing the supplied query against the 180: * existing database connection. If no connection exists then no action 181: * is taken. 182: * <p> 183: * The results from the query are extracted and cached locally, thus 184: * applying an upper limit on how many rows can be retrieved successfully. 185: * 186: * @param query the query. 187: * 188: * @throws SQLException if there is a problem executing the query. 189: */ 190: public void executeQuery(String query) throws SQLException { 191: executeQuery(this.connection, query); 192: } 193: 194: /** 195: * Populates the dataset by executing the supplied query against the 196: * existing database connection. If no connection exists then no action 197: * is taken. 198: * <p> 199: * The results from the query are extracted and cached locally, thus 200: * applying an upper limit on how many rows can be retrieved successfully. 201: * 202: * @param con the connection. 203: * @param query the query. 204: * 205: * @throws SQLException if there is a problem executing the query. 206: */ 207: public void executeQuery(Connection con, String query) throws SQLException { 208: 209: Statement statement = null; 210: ResultSet resultSet = null; 211: try { 212: statement = con.createStatement(); 213: resultSet = statement.executeQuery(query); 214: ResultSetMetaData metaData = resultSet.getMetaData(); 215: 216: int columnCount = metaData.getColumnCount(); 217: 218: if (columnCount < 2) { 219: throw new SQLException( 220: "JDBCCategoryDataset.executeQuery() : insufficient columns " 221: + "returned from the database."); 222: } 223: 224: // Remove any previous old data 225: int i = getRowCount(); 226: while (--i >= 0) { 227: removeRow(i); 228: } 229: 230: while (resultSet.next()) { 231: // first column contains the row key... 232: Comparable rowKey = resultSet.getString(1); 233: for (int column = 2; column <= columnCount; column++) { 234: 235: Comparable columnKey = metaData.getColumnName(column); 236: int columnType = metaData.getColumnType(column); 237: 238: switch (columnType) { 239: case Types.TINYINT: 240: case Types.SMALLINT: 241: case Types.INTEGER: 242: case Types.BIGINT: 243: case Types.FLOAT: 244: case Types.DOUBLE: 245: case Types.DECIMAL: 246: case Types.NUMERIC: 247: case Types.REAL: { 248: Number value = (Number) resultSet.getObject(column); 249: if (this.transpose) { 250: setValue(value, columnKey, rowKey); 251: } 252: else { 253: setValue(value, rowKey, columnKey); 254: } 255: break; 256: } 257: case Types.DATE: 258: case Types.TIME: 259: case Types.TIMESTAMP: { 260: Date date = (Date) resultSet.getObject(column); 261: Number value = new Long(date.getTime()); 262: if (this.transpose) { 263: setValue(value, columnKey, rowKey); 264: } 265: else { 266: setValue(value, rowKey, columnKey); 267: } 268: break; 269: } 270: case Types.CHAR: 271: case Types.VARCHAR: 272: case Types.LONGVARCHAR: { 273: String string 274: = (String) resultSet.getObject(column); 275: try { 276: Number value = Double.valueOf(string); 277: if (this.transpose) { 278: setValue(value, columnKey, rowKey); 279: } 280: else { 281: setValue(value, rowKey, columnKey); 282: } 283: } 284: catch (NumberFormatException e) { 285: // suppress (value defaults to null) 286: } 287: break; 288: } 289: default: 290: // not a value, can't use it (defaults to null) 291: break; 292: } 293: } 294: } 295: 296: fireDatasetChanged(); 297: } 298: finally { 299: if (resultSet != null) { 300: try { 301: resultSet.close(); 302: } 303: catch (Exception e) { 304: // report this? 305: } 306: } 307: if (statement != null) { 308: try { 309: statement.close(); 310: } 311: catch (Exception e) { 312: // report this? 313: } 314: } 315: } 316: } 317: 318: }