001/* =========================================================== 002 * JFreeChart : a free chart library for the Java(tm) platform 003 * =========================================================== 004 * 005 * (C) Copyright 2000-2020, 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 * [Oracle and Java are registered trademarks of Oracle and/or its affiliates. 025 * Other names may be trademarks of their respective owners.] 026 * 027 * ------------------ 028 * JDBCXYDataset.java 029 * ------------------ 030 * (C) Copyright 2002-2009, by Bryan Scott and Contributors. 031 * 032 * Original Author: Bryan Scott; 033 * Contributor(s): David Gilbert (for Object Refinery Limited); 034 * Eric Alexander; 035 * 036 * 037 * Changes 038 * ------- 039 * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG); 040 * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support 041 * for types. 042 * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data 043 * source conventions. 044 * 26-Apr-2002 : Changed to extend AbstractDataset. 045 * 13-Aug-2002 : Updated Javadoc comments and imports (DG); 046 * 18-Sep-2002 : Updated to support BIGINT (BS); 047 * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG); 048 * 01-Jul-2003 : Added support to query whether a timeseries (BS); 049 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 050 * method (BS); 051 * 24-Sep-2003 : Added a check to ensure at least two valid columns are 052 * returned by the query in executeQuery as suggest in online 053 * forum by anonymous (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. 057 * 16-Mar-2004 : Added check for null values (EA); 058 * 05-May-2004 : Now extends AbstractXYDataset (DG); 059 * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and 060 * fixed bug in code that determines the min and max values (see 061 * bug id 938138) (DG); 062 * 15-Jul-2004 : Switched getX() with getXValue() and getY() with 063 * getYValue() (DG); 064 * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG); 065 * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0 066 * release (DG); 067 * ------------- JFREECHART 1.0.x --------------------------------------------- 068 * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG); 069 * 19-May-2009 : Fixed FindBugs warnings, patch by Michal Wozniak (DG); 070 * 071 */ 072 073package org.jfree.data.jdbc; 074 075import java.sql.Connection; 076import java.sql.DriverManager; 077import java.sql.ResultSet; 078import java.sql.ResultSetMetaData; 079import java.sql.SQLException; 080import java.sql.Statement; 081import java.sql.Types; 082import java.util.ArrayList; 083import java.util.Date; 084 085import org.jfree.data.Range; 086import org.jfree.data.RangeInfo; 087import org.jfree.data.general.Dataset; 088import org.jfree.data.xy.AbstractXYDataset; 089import org.jfree.data.xy.TableXYDataset; 090import org.jfree.data.xy.XYDataset; 091 092/** 093 * This class provides an {@link XYDataset} implementation over a database 094 * JDBC result set. The dataset is populated via a call to executeQuery with 095 * the string sql query. The sql query must return at least two columns. 096 * The first column will be the x-axis and remaining columns y-axis values. 097 * executeQuery can be called a number of times. 098 * 099 * The database connection is read-only and no write back facility exists. 100 */ 101public class JDBCXYDataset extends AbstractXYDataset 102 implements XYDataset, TableXYDataset, 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} if the dataset represents time series data, 182 * and {@code false} 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 columnTypes[column] = Types.NULL; 270 break; 271 } 272 } 273 catch (SQLException e) { 274 columnTypes[column] = Types.NULL; 275 throw e; 276 } 277 } 278 279 280 if (numberOfValidColumns <= 1) { 281 throw new SQLException( 282 "Not enough valid columns where generated by query." 283 ); 284 } 285 286 /// First column is X data 287 this.columnNames = new String[numberOfValidColumns - 1]; 288 /// Get the column names and cache them. 289 int currentColumn = 0; 290 for (int column = 1; column < numberOfColumns; column++) { 291 if (columnTypes[column] != Types.NULL) { 292 this.columnNames[currentColumn] 293 = metaData.getColumnLabel(column + 1); 294 ++currentColumn; 295 } 296 } 297 298 // Might need to add, to free memory from any previous result sets 299 if (this.rows != null) { 300 for (int column = 0; column < this.rows.size(); column++) { 301 ArrayList row = (ArrayList) this.rows.get(column); 302 row.clear(); 303 } 304 this.rows.clear(); 305 } 306 307 // Are we working with a time series. 308 switch (columnTypes[0]) { 309 case Types.DATE: 310 case Types.TIME: 311 case Types.TIMESTAMP: 312 this.isTimeSeries = true; 313 break; 314 default : 315 this.isTimeSeries = false; 316 break; 317 } 318 319 // Get all rows. 320 // rows = new ArrayList(); 321 while (resultSet.next()) { 322 ArrayList newRow = new ArrayList(); 323 for (int column = 0; column < numberOfColumns; column++) { 324 Object xObject = resultSet.getObject(column + 1); 325 switch (columnTypes[column]) { 326 case Types.NUMERIC: 327 case Types.REAL: 328 case Types.INTEGER: 329 case Types.DOUBLE: 330 case Types.FLOAT: 331 case Types.DECIMAL: 332 case Types.BIGINT: 333 case Types.SMALLINT: 334 newRow.add(xObject); 335 break; 336 337 case Types.DATE: 338 case Types.TIME: 339 case Types.TIMESTAMP: 340 newRow.add(((Date) xObject).getTime()); 341 break; 342 case Types.NULL: 343 break; 344 default: 345 System.err.println("Unknown data"); 346 columnTypes[column] = Types.NULL; 347 break; 348 } 349 } 350 this.rows.add(newRow); 351 } 352 353 /// a kludge to make everything work when no rows returned 354 if (this.rows.isEmpty()) { 355 ArrayList newRow = new ArrayList(); 356 for (int column = 0; column < numberOfColumns; column++) { 357 if (columnTypes[column] != Types.NULL) { 358 newRow.add(0); 359 } 360 } 361 this.rows.add(newRow); 362 } 363 364 /// Determine max and min values. 365 if (this.rows.size() < 1) { 366 this.maxValue = 0.0; 367 this.minValue = 0.0; 368 } 369 else { 370 this.maxValue = Double.NEGATIVE_INFINITY; 371 this.minValue = Double.POSITIVE_INFINITY; 372 for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) { 373 ArrayList row = (ArrayList) this.rows.get(rowNum); 374 for (int column = 1; column < numberOfColumns; column++) { 375 Object testValue = row.get(column); 376 if (testValue != null) { 377 double test = ((Number) testValue).doubleValue(); 378 379 if (test < this.minValue) { 380 this.minValue = test; 381 } 382 if (test > this.maxValue) { 383 this.maxValue = test; 384 } 385 } 386 } 387 } 388 } 389 390 fireDatasetChanged(); // Tell the listeners a new table has arrived. 391 } 392 finally { 393 if (resultSet != null) { 394 try { 395 resultSet.close(); 396 } 397 catch (Exception e) { 398 // TODO: is this a good idea? 399 } 400 } 401 if (statement != null) { 402 try { 403 statement.close(); 404 } 405 catch (Exception e) { 406 // TODO: is this a good idea? 407 } 408 } 409 } 410 411 } 412 413 /** 414 * Returns the x-value for the specified series and item. The 415 * implementation is responsible for ensuring that the x-values are 416 * presented in ascending order. 417 * 418 * @param seriesIndex the series (zero-based index). 419 * @param itemIndex the item (zero-based index). 420 * 421 * @return The x-value 422 * 423 * @see XYDataset 424 */ 425 @Override 426 public Number getX(int seriesIndex, int itemIndex) { 427 ArrayList row = (ArrayList) this.rows.get(itemIndex); 428 return (Number) row.get(0); 429 } 430 431 /** 432 * Returns the y-value for the specified series and item. 433 * 434 * @param seriesIndex the series (zero-based index). 435 * @param itemIndex the item (zero-based index). 436 * 437 * @return The yValue value 438 * 439 * @see XYDataset 440 */ 441 @Override 442 public Number getY(int seriesIndex, int itemIndex) { 443 ArrayList row = (ArrayList) this.rows.get(itemIndex); 444 return (Number) row.get(seriesIndex + 1); 445 } 446 447 /** 448 * Returns the number of items in the specified series. 449 * 450 * @param seriesIndex the series (zero-based index). 451 * 452 * @return The itemCount value 453 * 454 * @see XYDataset 455 */ 456 @Override 457 public int getItemCount(int seriesIndex) { 458 return this.rows.size(); 459 } 460 461 /** 462 * Returns the number of items in all series. This method is defined by 463 * the {@link TableXYDataset} interface. 464 * 465 * @return The item count. 466 */ 467 @Override 468 public int getItemCount() { 469 return getItemCount(0); 470 } 471 472 /** 473 * Returns the number of series in the dataset. 474 * 475 * @return The seriesCount value 476 * 477 * @see XYDataset 478 * @see Dataset 479 */ 480 @Override 481 public int getSeriesCount() { 482 return this.columnNames.length; 483 } 484 485 /** 486 * Returns the key for the specified series. 487 * 488 * @param seriesIndex the series (zero-based index). 489 * 490 * @return The seriesName value 491 * 492 * @see XYDataset 493 * @see Dataset 494 */ 495 @Override 496 public Comparable getSeriesKey(int seriesIndex) { 497 498 if ((seriesIndex < this.columnNames.length) 499 && (this.columnNames[seriesIndex] != null)) { 500 return this.columnNames[seriesIndex]; 501 } 502 else { 503 return ""; 504 } 505 506 } 507 508 /** 509 * Close the database connection 510 */ 511 public void close() { 512 513 try { 514 this.connection.close(); 515 } 516 catch (Exception e) { 517 System.err.println("JdbcXYDataset: swallowing exception."); 518 } 519 520 } 521 522 /** 523 * Returns the minimum y-value in the dataset. 524 * 525 * @param includeInterval a flag that determines whether or not the 526 * y-interval is taken into account. 527 * 528 * @return The minimum value. 529 */ 530 @Override 531 public double getRangeLowerBound(boolean includeInterval) { 532 return this.minValue; 533 } 534 535 /** 536 * Returns the maximum y-value in the dataset. 537 * 538 * @param includeInterval a flag that determines whether or not the 539 * y-interval is taken into account. 540 * 541 * @return The maximum value. 542 */ 543 @Override 544 public double getRangeUpperBound(boolean includeInterval) { 545 return this.maxValue; 546 } 547 548 /** 549 * Returns the range of the values in this dataset's range. 550 * 551 * @param includeInterval a flag that determines whether or not the 552 * y-interval is taken into account. 553 * 554 * @return The range. 555 */ 556 @Override 557 public Range getRangeBounds(boolean includeInterval) { 558 return new Range(this.minValue, this.maxValue); 559 } 560 561}