Source for org.jfree.data.jdbc.JDBCXYDataset

   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: }