| 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: * JDBCXYDataset.java 29: * ------------------ 30: * (C) Copyright 2002-2006, by Bryan Scott and Contributors. 31: * 32: * Original Author: Bryan Scott; 33: * Contributor(s): David Gilbert (for Object Refinery Limited); 34: * Eric Alexander; 35: * 36: * 37: * Changes 38: * ------- 39: * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG); 40: * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support 41: * for types. 42: * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data 43: * source conventions. 44: * 26-Apr-2002 : Changed to extend AbstractDataset. 45: * 13-Aug-2002 : Updated Javadoc comments and imports (DG); 46: * 18-Sep-2002 : Updated to support BIGINT (BS); 47: * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG); 48: * 01-Jul-2003 : Added support to query whether a timeseries (BS); 49: * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 50: * method (BS); 51: * 24-Sep-2003 : Added a check to ensure at least two valid columns are 52: * returned by the query in executeQuery as suggest in online 53: * forum by anonymous (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. 57: * 16-Mar-2004 : Added check for null values (EA); 58: * 05-May-2004 : Now extends AbstractXYDataset (DG); 59: * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and 60: * fixed bug in code that determines the min and max values (see 61: * bug id 938138) (DG); 62: * 15-Jul-2004 : Switched getX() with getXValue() and getY() with 63: * getYValue() (DG); 64: * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG); 65: * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0 66: * release (DG); 67: * ------------- JFREECHART 1.0.x --------------------------------------------- 68: * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG); 69: * 70: */ 71: 72: package org.jfree.data.jdbc; 73: 74: import java.sql.Connection; 75: import java.sql.DriverManager; 76: import java.sql.ResultSet; 77: import java.sql.ResultSetMetaData; 78: import java.sql.SQLException; 79: import java.sql.Statement; 80: import java.sql.Types; 81: import java.util.ArrayList; 82: import java.util.Date; 83: 84: import org.jfree.data.Range; 85: import org.jfree.data.RangeInfo; 86: import org.jfree.data.general.Dataset; 87: import org.jfree.data.xy.AbstractXYDataset; 88: import org.jfree.data.xy.TableXYDataset; 89: import org.jfree.data.xy.XYDataset; 90: import org.jfree.util.Log; 91: 92: /** 93: * This class provides an {@link XYDataset} implementation over a database 94: * JDBC result set. The dataset is populated via a call to executeQuery with 95: * the string sql query. The sql query must return at least two columns. 96: * The first column will be the x-axis and remaining columns y-axis values. 97: * executeQuery can be called a number of times. 98: * 99: * The database connection is read-only and no write back facility exists. 100: */ 101: public class JDBCXYDataset extends AbstractXYDataset 102: implements XYDataset, 103: TableXYDataset, 104: RangeInfo { 105: 106: /** The database connection. */ 107: private transient Connection connection; 108: 109: /** Column names. */ 110: private String[] columnNames = {}; 111: 112: /** Rows. */ 113: private ArrayList rows; 114: 115: /** The maximum y value of the returned result set */ 116: private double maxValue = 0.0; 117: 118: /** The minimum y value of the returned result set */ 119: private double minValue = 0.0; 120: 121: /** Is this dataset a timeseries ? */ 122: private boolean isTimeSeries = false; 123: 124: /** 125: * Creates a new JDBCXYDataset (initially empty) with no database 126: * connection. 127: */ 128: private JDBCXYDataset() { 129: this.rows = new ArrayList(); 130: } 131: 132: /** 133: * Creates a new dataset (initially empty) and establishes a new database 134: * connection. 135: * 136: * @param url URL of the database connection. 137: * @param driverName the database driver class name. 138: * @param user the database user. 139: * @param password the database user's password. 140: * 141: * @throws ClassNotFoundException if the driver cannot be found. 142: * @throws SQLException if there is a problem connecting to the database. 143: */ 144: public JDBCXYDataset(String url, 145: String driverName, 146: String user, 147: String password) 148: throws SQLException, ClassNotFoundException { 149: 150: this(); 151: Class.forName(driverName); 152: this.connection = DriverManager.getConnection(url, user, password); 153: } 154: 155: /** 156: * Creates a new dataset (initially empty) using the specified database 157: * connection. 158: * 159: * @param con the database connection. 160: * 161: * @throws SQLException if there is a problem connecting to the database. 162: */ 163: public JDBCXYDataset(Connection con) throws SQLException { 164: this(); 165: this.connection = con; 166: } 167: 168: /** 169: * Creates a new dataset using the specified database connection, and 170: * populates it using data obtained with the supplied query. 171: * 172: * @param con the connection. 173: * @param query the SQL query. 174: * 175: * @throws SQLException if there is a problem executing the query. 176: */ 177: public JDBCXYDataset(Connection con, String query) throws SQLException { 178: this(con); 179: executeQuery(query); 180: } 181: 182: /** 183: * Returns <code>true</code> if the dataset represents time series data, 184: * and <code>false</code> otherwise. 185: * 186: * @return A boolean. 187: */ 188: public boolean isTimeSeries() { 189: return this.isTimeSeries; 190: } 191: 192: /** 193: * Sets a flag that indicates whether or not the data represents a time 194: * series. 195: * 196: * @param timeSeries the new value of the flag. 197: */ 198: public void setTimeSeries(boolean timeSeries) { 199: this.isTimeSeries = timeSeries; 200: } 201: 202: /** 203: * ExecuteQuery will attempt execute the query passed to it against the 204: * existing database connection. If no connection exists then no action 205: * is taken. 206: * 207: * The results from the query are extracted and cached locally, thus 208: * applying an upper limit on how many rows can be retrieved successfully. 209: * 210: * @param query the query to be executed. 211: * 212: * @throws SQLException if there is a problem executing the query. 213: */ 214: public void executeQuery(String query) throws SQLException { 215: executeQuery(this.connection, query); 216: } 217: 218: /** 219: * ExecuteQuery will attempt execute the query passed to it against the 220: * provided database connection. If connection is null then no action is 221: * taken. 222: * 223: * The results from the query are extracted and cached locally, thus 224: * applying an upper limit on how many rows can be retrieved successfully. 225: * 226: * @param query the query to be executed. 227: * @param con the connection the query is to be executed against. 228: * 229: * @throws SQLException if there is a problem executing the query. 230: */ 231: public void executeQuery(Connection con, String query) 232: throws SQLException { 233: 234: if (con == null) { 235: throw new SQLException( 236: "There is no database to execute the query." 237: ); 238: } 239: 240: ResultSet resultSet = null; 241: Statement statement = null; 242: try { 243: statement = con.createStatement(); 244: resultSet = statement.executeQuery(query); 245: ResultSetMetaData metaData = resultSet.getMetaData(); 246: 247: int numberOfColumns = metaData.getColumnCount(); 248: int numberOfValidColumns = 0; 249: int [] columnTypes = new int[numberOfColumns]; 250: for (int column = 0; column < numberOfColumns; column++) { 251: try { 252: int type = metaData.getColumnType(column + 1); 253: switch (type) { 254: 255: case Types.NUMERIC: 256: case Types.REAL: 257: case Types.INTEGER: 258: case Types.DOUBLE: 259: case Types.FLOAT: 260: case Types.DECIMAL: 261: case Types.BIT: 262: case Types.DATE: 263: case Types.TIME: 264: case Types.TIMESTAMP: 265: case Types.BIGINT: 266: case Types.SMALLINT: 267: ++numberOfValidColumns; 268: columnTypes[column] = type; 269: break; 270: default: 271: Log.warn( 272: "Unable to load column " 273: + column + " (" + type + "," 274: + metaData.getColumnClassName(column + 1) 275: + ")" 276: ); 277: columnTypes[column] = Types.NULL; 278: break; 279: } 280: } 281: catch (SQLException e) { 282: columnTypes[column] = Types.NULL; 283: throw e; 284: } 285: } 286: 287: 288: if (numberOfValidColumns <= 1) { 289: throw new SQLException( 290: "Not enough valid columns where generated by query." 291: ); 292: } 293: 294: /// First column is X data 295: this.columnNames = new String[numberOfValidColumns - 1]; 296: /// Get the column names and cache them. 297: int currentColumn = 0; 298: for (int column = 1; column < numberOfColumns; column++) { 299: if (columnTypes[column] != Types.NULL) { 300: this.columnNames[currentColumn] 301: = metaData.getColumnLabel(column + 1); 302: ++currentColumn; 303: } 304: } 305: 306: // Might need to add, to free memory from any previous result sets 307: if (this.rows != null) { 308: for (int column = 0; column < this.rows.size(); column++) { 309: ArrayList row = (ArrayList) this.rows.get(column); 310: row.clear(); 311: } 312: this.rows.clear(); 313: } 314: 315: // Are we working with a time series. 316: switch (columnTypes[0]) { 317: case Types.DATE: 318: case Types.TIME: 319: case Types.TIMESTAMP: 320: this.isTimeSeries = true; 321: break; 322: default : 323: this.isTimeSeries = false; 324: break; 325: } 326: 327: // Get all rows. 328: // rows = new ArrayList(); 329: while (resultSet.next()) { 330: ArrayList newRow = new ArrayList(); 331: for (int column = 0; column < numberOfColumns; column++) { 332: Object xObject = resultSet.getObject(column + 1); 333: switch (columnTypes[column]) { 334: case Types.NUMERIC: 335: case Types.REAL: 336: case Types.INTEGER: 337: case Types.DOUBLE: 338: case Types.FLOAT: 339: case Types.DECIMAL: 340: case Types.BIGINT: 341: case Types.SMALLINT: 342: newRow.add(xObject); 343: break; 344: 345: case Types.DATE: 346: case Types.TIME: 347: case Types.TIMESTAMP: 348: newRow.add(new Long(((Date) xObject).getTime())); 349: break; 350: case Types.NULL: 351: break; 352: default: 353: System.err.println("Unknown data"); 354: columnTypes[column] = Types.NULL; 355: break; 356: } 357: } 358: this.rows.add(newRow); 359: } 360: 361: /// a kludge to make everything work when no rows returned 362: if (this.rows.size() == 0) { 363: ArrayList newRow = new ArrayList(); 364: for (int column = 0; column < numberOfColumns; column++) { 365: if (columnTypes[column] != Types.NULL) { 366: newRow.add(new Integer(0)); 367: } 368: } 369: this.rows.add(newRow); 370: } 371: 372: /// Determine max and min values. 373: if (this.rows.size() < 1) { 374: this.maxValue = 0.0; 375: this.minValue = 0.0; 376: } 377: else { 378: ArrayList row = (ArrayList) this.rows.get(0); 379: this.maxValue = Double.NEGATIVE_INFINITY; 380: this.minValue = Double.POSITIVE_INFINITY; 381: for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) { 382: row = (ArrayList) this.rows.get(rowNum); 383: for (int column = 1; column < numberOfColumns; column++) { 384: Object testValue = row.get(column); 385: if (testValue != null) { 386: double test = ((Number) testValue).doubleValue(); 387: 388: if (test < this.minValue) { 389: this.minValue = test; 390: } 391: if (test > this.maxValue) { 392: this.maxValue = test; 393: } 394: } 395: } 396: } 397: } 398: 399: fireDatasetChanged(); // Tell the listeners a new table has arrived. 400: } 401: finally { 402: if (resultSet != null) { 403: try { 404: resultSet.close(); 405: } 406: catch (Exception e) { 407: // TODO: is this a good idea? 408: } 409: } 410: if (statement != null) { 411: try { 412: statement.close(); 413: } 414: catch (Exception e) { 415: // TODO: is this a good idea? 416: } 417: } 418: } 419: 420: } 421: 422: /** 423: * Returns the x-value for the specified series and item. The 424: * implementation is responsible for ensuring that the x-values are 425: * presented in ascending order. 426: * 427: * @param seriesIndex the series (zero-based index). 428: * @param itemIndex the item (zero-based index). 429: * 430: * @return The x-value 431: * 432: * @see XYDataset 433: */ 434: public Number getX(int seriesIndex, int itemIndex) { 435: ArrayList row = (ArrayList) this.rows.get(itemIndex); 436: return (Number) row.get(0); 437: } 438: 439: /** 440: * Returns the y-value for the specified series and item. 441: * 442: * @param seriesIndex the series (zero-based index). 443: * @param itemIndex the item (zero-based index). 444: * 445: * @return The yValue value 446: * 447: * @see XYDataset 448: */ 449: public Number getY(int seriesIndex, int itemIndex) { 450: ArrayList row = (ArrayList) this.rows.get(itemIndex); 451: return (Number) row.get(seriesIndex + 1); 452: } 453: 454: /** 455: * Returns the number of items in the specified series. 456: * 457: * @param seriesIndex the series (zero-based index). 458: * 459: * @return The itemCount value 460: * 461: * @see XYDataset 462: */ 463: public int getItemCount(int seriesIndex) { 464: return this.rows.size(); 465: } 466: 467: /** 468: * Returns the number of items in all series. This method is defined by 469: * the {@link TableXYDataset} interface. 470: * 471: * @return The item count. 472: */ 473: public int getItemCount() { 474: return getItemCount(0); 475: } 476: 477: /** 478: * Returns the number of series in the dataset. 479: * 480: * @return The seriesCount value 481: * 482: * @see XYDataset 483: * @see Dataset 484: */ 485: public int getSeriesCount() { 486: return this.columnNames.length; 487: } 488: 489: /** 490: * Returns the key for the specified series. 491: * 492: * @param seriesIndex the series (zero-based index). 493: * 494: * @return The seriesName value 495: * 496: * @see XYDataset 497: * @see Dataset 498: */ 499: public Comparable getSeriesKey(int seriesIndex) { 500: 501: if ((seriesIndex < this.columnNames.length) 502: && (this.columnNames[seriesIndex] != null)) { 503: return this.columnNames[seriesIndex]; 504: } 505: else { 506: return ""; 507: } 508: 509: } 510: 511: /** 512: * Returns the number of items that should be displayed in the legend. 513: * 514: * @return The legendItemCount value 515: * 516: * @deprecated This method is not used in JFreeChart 1.0.x (it was left in 517: * the API by mistake and is officially deprecated from version 1.0.3 518: * onwards). 519: */ 520: public int getLegendItemCount() { 521: return getSeriesCount(); 522: } 523: 524: /** 525: * Returns the legend item labels. 526: * 527: * @return The legend item labels. 528: * 529: * @deprecated This method is not used in JFreeChart 1.0.x (it was left in 530: * the API by mistake and is officially deprecated from version 1.0.3 531: * onwards). 532: */ 533: public String[] getLegendItemLabels() { 534: return this.columnNames; 535: } 536: 537: /** 538: * Close the database connection 539: */ 540: public void close() { 541: 542: try { 543: this.connection.close(); 544: } 545: catch (Exception e) { 546: System.err.println("JdbcXYDataset: swallowing exception."); 547: } 548: 549: } 550: 551: /** 552: * Returns the minimum y-value in the dataset. 553: * 554: * @param includeInterval a flag that determines whether or not the 555: * y-interval is taken into account. 556: * 557: * @return The minimum value. 558: */ 559: public double getRangeLowerBound(boolean includeInterval) { 560: return this.minValue; 561: } 562: 563: /** 564: * Returns the maximum y-value in the dataset. 565: * 566: * @param includeInterval a flag that determines whether or not the 567: * y-interval is taken into account. 568: * 569: * @return The maximum value. 570: */ 571: public double getRangeUpperBound(boolean includeInterval) { 572: return this.maxValue; 573: } 574: 575: /** 576: * Returns the range of the values in this dataset's range. 577: * 578: * @param includeInterval a flag that determines whether or not the 579: * y-interval is taken into account. 580: * 581: * @return The range. 582: */ 583: public Range getRangeBounds(boolean includeInterval) { 584: return new Range(this.minValue, this.maxValue); 585: } 586: 587: }