content: /* ===========================================================
   * JFreeChart : a free chart library for the Java(tm) platform
   * ===========================================================
   *
   * (C) Copyright 2000-2011, by Object Refinery Limited and Contributors.
   *
   * Project Info:  http://www.jfree.org/jfreechart/index.html
   *
   * This library is free software; you can redistribute it and/or modify it
   * under the terms of the GNU Lesser General Public License as published by
   * the Free Software Foundation; either version 2.1 of the License, or
   * (at your option) any later version.
   *
   * This library is distributed in the hope that it will be useful, but
   * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
   * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
   * License for more details.
   *
   * You should have received a copy of the GNU Lesser General Public
   * License along with this library; if not, write to the Free Software
   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301,
   * USA.
   *
   * [Oracle and Java are registered trademarks of Oracle and/or its affiliates.
   * Other names may be trademarks of their respective owners.]
   *
   * ------------------
   * JDBCXYDataset.java
   * ------------------
   * (C) Copyright 2002-2009, by Bryan Scott and Contributors.
   *
   * Original Author:  Bryan Scott;
   * Contributor(s):   David Gilbert (for Object Refinery Limited);
   *                   Eric Alexander;
   *
   *
   * Changes
   * -------
   * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
   * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support
   *               for types.
   * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data
   *               source conventions.
   * 26-Apr-2002 : Changed to extend AbstractDataset.
   * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
   * 18-Sep-2002 : Updated to support BIGINT (BS);
   * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
   * 01-Jul-2003 : Added support to query whether a timeseries (BS);
   * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
   *               method (BS);
   * 24-Sep-2003 : Added a check to ensure at least two valid columns are
   *               returned by the query in executeQuery as suggest in online
   *               forum by anonymous (BS);
   * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
   *               constructor, as without a connection, a query can never be
   *               executed.
   * 16-Mar-2004 : Added check for null values (EA);
   * 05-May-2004 : Now extends AbstractXYDataset (DG);
   * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and
   *               fixed bug in code that determines the min and max values (see
   *               bug id 938138) (DG);
   * 15-Jul-2004 : Switched getX() with getXValue() and getY() with
   *               getYValue() (DG);
   * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG);
   * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0
   *               release (DG);
   * ------------- JFREECHART 1.0.x ---------------------------------------------
   * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG);
   * 19-May-2009 : Fixed FindBugs warnings, patch by Michal Wozniak (DG);
   *
   */
  
  package org.jfree.data.jdbc;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  import org.jfree.data.Range;
  import org.jfree.data.RangeInfo;
  import org.jfree.data.general.Dataset;
  import org.jfree.data.xy.AbstractXYDataset;
  import org.jfree.data.xy.TableXYDataset;
  import org.jfree.data.xy.XYDataset;
  
  /**
   * This class provides an {@link XYDataset} implementation over a database
   * JDBC result set.  The dataset is populated via a call to executeQuery with
   * the string sql query.  The sql query must return at least two columns.
   * The first column will be the x-axis and remaining columns y-axis values.
   * executeQuery can be called a number of times.
   *
   * The database connectionMetadata is read-only and no write back facility exists.
   */
  public class JDBCXYDataset extends AbstractXYDataset
          implements XYDataset, TableXYDataset, RangeInfo {
  
      /** The database connectionMetadata. */
      private transient Connection connection;
  
      /** Column names. */
      private String[] columnNames = {};
  
      /** Rows. */
      private List<List<Number>> rows;
  
      /** The maximum y value of the returned result set */
      private double maxValue = 0.0;
  
      /** The minimum y value of the returned result set */
      private double minValue = 0.0;
  
      /** Is this dataset a timeseries ? */
      private boolean isTimeSeries = false;
  
      /**
       * Creates a new JDBCXYDataset (initially empty) with no database
       * connectionMetadata.
       */
      private JDBCXYDataset() {
          this.rows = new ArrayList<List<Number>>();
      }
  
      /**
       * Creates a new dataset (initially empty) and establishes a new database
       * connectionMetadata.
       *
       * @param  url  URL of the database connectionMetadata.
       * @param  driverName  the database driver class name.
       * @param  user  the database user.
       * @param  password  the database user's password.
       *
       * @throws ClassNotFoundException if the driver cannot be found.
       * @throws SQLException if there is a problem connecting to the database.
       */
      public JDBCXYDataset(String url,
                           String driverName,
                           String user,
                           String password)
          throws SQLException, ClassNotFoundException {
  
          this();
          Class.forName(driverName);
          this.connection = DriverManager.getConnection(url, user, password);
      }
  
      /**
       * Creates a new dataset (initially empty) using the specified database
       * connectionMetadata.
       *
       * @param  con  the database connectionMetadata.
       *
       * @throws SQLException if there is a problem connecting to the database.
       */
      public JDBCXYDataset(Connection con) throws SQLException {
          this();
          this.connection = con;
      }
  
      /**
       * Creates a new dataset using the specified database connectionMetadata, and
       * populates it using data obtained with the supplied query.
       *
       * @param con  the connectionMetadata.
       * @param query  the SQL query.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public JDBCXYDataset(Connection con, String query) throws SQLException {
          this(con);
          executeQuery(query);
      }
  
      /**
       * Returns <code>true</code> if the dataset represents time series data,
       * and <code>false</code> otherwise.
       *
       * @return A boolean.
       */
      public boolean isTimeSeries() {
          return this.isTimeSeries;
      }
  
      /**
       * Sets a flag that indicates whether or not the data represents a time
       * series.
       *
       * @param timeSeries  the new value of the flag.
       */
      public void setTimeSeries(boolean timeSeries) {
          this.isTimeSeries = timeSeries;
      }
  
      /**
       * ExecuteQuery will attempt execute the query passed to it against the
       * existing database connectionMetadata.  If no connectionMetadata exists then no action
       * is taken.
       *
       * The results from the query are extracted and cached locally, thus
       * applying an upper limit on how many rows can be retrieved successfully.
       *
       * @param  query  the query to be executed.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public void executeQuery(String query) throws SQLException {
          executeQuery(this.connection, query);
      }
  
      /**
       * ExecuteQuery will attempt execute the query passed to it against the
       * provided database connectionMetadata.  If connectionMetadata is null then no action is
       * taken.
       *
       * The results from the query are extracted and cached locally, thus
       * applying an upper limit on how many rows can be retrieved successfully.
       *
       * @param  query  the query to be executed.
       * @param  con  the connectionMetadata the query is to be executed against.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public void executeQuery(Connection con, String query)
          throws SQLException {
  
          if (con == null) {
              throw new SQLException(
                  "There is no database to execute the query."
              );
          }
  
          ResultSet resultSet = null;
          PreparedStatement statement = null;
          try {
              statement = con.prepareStatement(query);
              resultSet = statement.executeQuery();
              ResultSetMetaData metaData = resultSet.getMetaData();
  
              int numberOfColumns = metaData.getColumnCount();
              int numberOfValidColumns = 0;
              int [] columnTypes = new int[numberOfColumns];
              for (int column = 0; column < numberOfColumns; column++) {
                  try {
                      int type = metaData.getColumnType(column + 1);
                      switch (type) {
  
                          case Types.NUMERIC:
                          case Types.REAL:
                          case Types.INTEGER:
                          case Types.DOUBLE:
                          case Types.FLOAT:
                          case Types.DECIMAL:
                          case Types.BIT:
                          case Types.DATE:
                          case Types.TIME:
                          case Types.TIMESTAMP:
                          case Types.BIGINT:
                          case Types.SMALLINT:
                              ++numberOfValidColumns;
                              columnTypes[column] = type;
                              break;
                          default:
                              columnTypes[column] = Types.NULL;
                              break;
                      }
                  }
                  catch (SQLException e) {
                      columnTypes[column] = Types.NULL;
                      throw e;
                  }
              }
  
  
              if (numberOfValidColumns <= 1) {
                  throw new SQLException(
                      "Not enough valid columns where generated by query."
                  );
              }
  
              /// First column is X data
              this.columnNames = new String[numberOfValidColumns - 1];
              /// Get the column names and cache them.
              int currentColumn = 0;
              for (int column = 1; column < numberOfColumns; column++) {
                  if (columnTypes[column] != Types.NULL) {
                      this.columnNames[currentColumn]
                          = metaData.getColumnLabel(column + 1);
                      ++currentColumn;
                  }
              }
  
              // Might need to add, to free memory from any previous result sets
              if (this.rows != null) {
                  for (List<Number> row : this.rows) {
                      row.clear();
                  }
                  this.rows.clear();
              }
  
              // Are we working with a time series.
              switch (columnTypes[0]) {
                  case Types.DATE:
                  case Types.TIME:
                  case Types.TIMESTAMP:
                      this.isTimeSeries = true;
                      break;
                  default :
                      this.isTimeSeries = false;
                      break;
              }
  
              // Get all rows.
              // rows = new ArrayList();
              while (resultSet.next()) {
                  List<Number> newRow = new ArrayList<Number>();
                  for (int column = 0; column < numberOfColumns; column++) {
                      Object xObject = resultSet.getObject(column + 1);
                      switch (columnTypes[column]) {
                          case Types.NUMERIC:
                          case Types.REAL:
                          case Types.INTEGER:
                          case Types.DOUBLE:
                          case Types.FLOAT:
                          case Types.DECIMAL:
                          case Types.BIGINT:
                          case Types.SMALLINT:
                              newRow.add((Number)xObject);
                              break;
  
                          case Types.DATE:
                          case Types.TIME:
                          case Types.TIMESTAMP:
                              newRow.add(((Date) xObject).getTime());
                              break;
                          case Types.NULL:
                              break;
                          default:
                              columnTypes[column] = Types.NULL;
                              break;
                      }
                  }
                  this.rows.add(newRow);
              }
  
              /// a kludge to make everything work when no rows returned
              if (this.rows.size() == 0) {
                  List<Number> newRow = new ArrayList<Number>();
                  for (int column = 0; column < numberOfColumns; column++) {
                      if (columnTypes[column] != Types.NULL) {
                          newRow.add(0);
                      }
                  }
                  this.rows.add(newRow);
              }
  
              /// Determine max and min values.
              if (this.rows.size() < 1) {
                  this.maxValue = 0.0;
                  this.minValue = 0.0;
              }
              else {
                  this.maxValue = Double.NEGATIVE_INFINITY;
                  this.minValue = Double.POSITIVE_INFINITY;
                  for (List<Number> row : this.rows) {
                      for (int column = 1; column < numberOfColumns; column++) {
                          Object testValue = row.get(column);
                          if (testValue != null) {
                              double test = ((Number) testValue).doubleValue();
  
                              if (test < this.minValue) {
                                  this.minValue = test;
                              }
                              if (test > this.maxValue) {
                                  this.maxValue = test;
                              }
                          }
                      }
                  }
              }
  
              fireDatasetChanged(); // Tell the listeners a new table has arrived.
          }
          finally {
              if (resultSet != null) {
                  try {
                      resultSet.close();
                  }
                  catch (Exception e) {
                      // TODO: is this a good idea?
                  }
              }
              if (statement != null) {
                  try {
                      statement.close();
                  }
                  catch (Exception e) {
                      // TODO: is this a good idea?
                  }
              }
          }
  
      }
  
      /**
       * Returns the x-value for the specified series and item.  The
       * implementation is responsible for ensuring that the x-values are
       * presented in ascending order.
       *
       * @param  seriesIndex  the series (zero-based index).
       * @param  itemIndex  the item (zero-based index).
       *
       * @return The x-value
       *
       * @see XYDataset
       */
      @Override
      public Number getX(int seriesIndex, int itemIndex) {
          ArrayList row = (ArrayList) this.rows.get(itemIndex);
          return (Number) row.get(0);
      }
  
      /**
       * Returns the y-value for the specified series and item.
       *
       * @param  seriesIndex  the series (zero-based index).
       * @param  itemIndex  the item (zero-based index).
       *
       * @return The yValue value
       *
       * @see XYDataset
       */
      @Override
      public Number getY(int seriesIndex, int itemIndex) {
          ArrayList row = (ArrayList) this.rows.get(itemIndex);
          return (Number) row.get(seriesIndex + 1);
      }
  
      /**
       * Returns the number of items in the specified series.
       *
       * @param  seriesIndex  the series (zero-based index).
       *
       * @return The itemCount value
       *
       * @see XYDataset
       */
      @Override
      public int getItemCount(int seriesIndex) {
          return this.rows.size();
      }
  
      /**
       * Returns the number of items in all series.  This method is defined by
       * the {@link TableXYDataset} interface.
       *
       * @return The item count.
       */
      @Override
      public int getItemCount() {
          return getItemCount(0);
      }
  
      /**
       * Returns the number of series in the dataset.
       *
       * @return The seriesCount value
       *
       * @see XYDataset
       * @see Dataset
       */
      @Override
      public int getSeriesCount() {
          return this.columnNames.length;
      }
  
      /**
       * Returns the key for the specified series.
       *
       * @param seriesIndex  the series (zero-based index).
       *
       * @return The seriesName value
       *
       * @see XYDataset
       * @see Dataset
       */
      @Override
      public Comparable getSeriesKey(int seriesIndex) {
  
          if ((seriesIndex < this.columnNames.length)
                  && (this.columnNames[seriesIndex] != null)) {
              return this.columnNames[seriesIndex];
          }
          else {
              return "";
          }
  
      }
  
      /**
       * Close the database connectionMetadata
       */
      public void close() {
  
          try {
              this.connection.close();
          }
          catch (Exception e) {
              // silently ignore
          }
  
      }
  
      /**
       * Returns the minimum y-value in the dataset.
       *
       * @param includeInterval  a flag that determines whether or not the
       *                         y-interval is taken into account.
       *
       * @return The minimum value.
       */
      @Override
      public double getRangeLowerBound(boolean includeInterval) {
          return this.minValue;
      }
  
      /**
       * Returns the maximum y-value in the dataset.
       *
       * @param includeInterval  a flag that determines whether or not the
       *                         y-interval is taken into account.
       *
       * @return The maximum value.
       */
      @Override
      public double getRangeUpperBound(boolean includeInterval) {
          return this.maxValue;
      }
  
      /**
       * Returns the range of the values in this dataset's range.
       *
       * @param includeInterval  a flag that determines whether or not the
       *                         y-interval is taken into account.
       *
       * @return The range.
       */
      @Override
      public Range getRangeBounds(boolean includeInterval) {
          return new Range(this.minValue, this.maxValue);
      }
  
  }
  