| Frames | No Frames |
1: /* =========================================================== 2: * JFreeChart : a free chart library for the Java(tm) platform 3: * =========================================================== 4: * 5: * (C) Copyright 2000-2006, 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-2006, 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: /** The database connection. */ 96: private transient Connection connection; 97: 98: /** 99: * 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: }