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