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.]
   *
   * ------------------------
   * JDBCCategoryDataset.java
   * ------------------------
   * (C) Copyright 2002-2008, by Bryan Scott and Contributors.
   *
   * Original Author:  Bryan Scott; Andy;
   * Contributor(s):   David Gilbert (for Object Refinery Limited);
   *                   Thomas Morgner;
   *
   * Changes
   * -------
   * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from
   *               Andy;
   * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
   * 03-Sep-2002 : Added fix for bug 591385 (DG);
   * 18-Sep-2002 : Updated to support BIGINT (BS);
   * 16-Oct-2002 : Added fix for bug 586667 (DG);
   * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
   * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
   * 30-Jun-2003 : CVS Write test (BS);
   * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
   *               method (BS);
   * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily
   *               transposed if required (DG);
   * 10-Sep-2003 : Added support for additional JDBC types (DG);
   * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
   *               following being highlighted on online forum (BS);
   * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
   *               constructor, as without a connection, a query can never be
   *               executed (TM);
   * 04-Dec-2003 : Added missing Javadocs (DG);
   * ------------- JFREECHART 1.0.0 ---------------------------------------------
   * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if
   *               executeQuery() is called more than once (DG);
   *
   */
  
  package org.jfree.data.jdbc;
  
  import java.sql.Connection;
  import java.sql.Date;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Types;
  
  import org.jfree.data.category.CategoryDataset;
  import org.jfree.data.category.DefaultCategoryDataset;
  
  /**
   * A {@link CategoryDataset} implementation over a database JDBC result set.
   * The dataset is populated via a call to {@link #executeQuery(String)} with
   * the string SQL query.  The SQL query must return at least two columns.  The
   * first column will be the category name and remaining columns values (each
   * column represents a series).  Subsequent calls to
   * {@link #executeQuery(String)} will refresh the dataset.
   * <p>
   * The database connectionMetadata is read-only and no write back facility exists.
   * <p>
   * NOTE: Many people have found this class too restrictive in general use.
   * For the greatest flexibility, please consider writing your own code to read
   * data from a <code>ResultSet</code> and populate a
   * {@link DefaultCategoryDataset} directly.
   */
  public class JDBCCategoryDataset extends DefaultCategoryDataset {
  
      /** For serialization. */
      static final long serialVersionUID = -3080395327918844965L;
  
      /** The database connectionMetadata. */
      private transient Connection connection;
  
      /**
       * A flag the controls whether or not the table is transposed.  The default
       * is 'true' because this provides the behaviour described in the
       * documentation.
       */
      private boolean transpose = true;
  
  
      /**
       * Creates a new dataset with a database connectionMetadata.
       *
       * @param  url  the URL of the database connectionMetadata.
       * @param  driverName  the database driver class name.
       * @param  user  the database user.
       * @param  passwd  the database user's password.
       *
       * @throws ClassNotFoundException if the driver cannot be found.
       * @throws SQLException if there is an error obtaining a connectionMetadata to the
       *                      database.
       */
      public JDBCCategoryDataset(String url,
                                 String driverName,
                                 String user,
                                 String passwd)
          throws ClassNotFoundException, SQLException {
  
          Class.forName(driverName);
          this.connection = DriverManager.getConnection(url, user, passwd);
      }
  
      /**
       * Create a new dataset with the given database connectionMetadata.
       *
       * @param connection  the database connectionMetadata.
       */
      public JDBCCategoryDataset(Connection connection) {
          if (connection == null) {
              throw new NullPointerException("A connectionMetadata must be supplied.");
          }
          this.connection = connection;
      }
  
      /**
       * Creates a new dataset with the given database connectionMetadata, and executes
       * the supplied query to populate the dataset.
       *
       * @param connection  the connectionMetadata.
       * @param query  the query.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public JDBCCategoryDataset(Connection connection, String query)
          throws SQLException {
          this(connection);
          executeQuery(query);
      }
  
      /**
       * Returns a flag that controls whether or not the table values are
       * transposed when added to the dataset.
       *
       * @return A boolean.
       */
      public boolean getTranspose() {
          return this.transpose;
      }
  
      /**
       * Sets a flag that controls whether or not the table values are transposed
       * when added to the dataset.
       *
       * @param transpose  the flag.
       */
      public void setTranspose(boolean transpose) {
          this.transpose = transpose;
      }
  
      /**
       * Populates the dataset by executing the supplied query against the
       * existing database connectionMetadata.  If no connectionMetadata exists then no action
       * is taken.
       * <p>
       * 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.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public void executeQuery(String query) throws SQLException {
          executeQuery(this.connection, query);
      }
  
      /**
       * Populates the dataset by executing the supplied query against the
       * existing database connectionMetadata.  If no connectionMetadata exists then no action
       * is taken.
       * <p>
       * The results from the query are extracted and cached locally, thus
       * applying an upper limit on how many rows can be retrieved successfully.
       *
       * @param con  the connectionMetadata.
       * @param query  the query.
       *
       * @throws SQLException if there is a problem executing the query.
       */
      public void executeQuery(Connection con, String query) throws SQLException {
  
          Statement statement = null;
          ResultSet resultSet = null;
          try {
              statement = con.createStatement();
              resultSet = statement.executeQuery(query);
              ResultSetMetaData metaData = resultSet.getMetaData();
  
              int columnCount = metaData.getColumnCount();
  
              if (columnCount < 2) {
                  throw new SQLException(
                      "JDBCCategoryDataset.executeQuery() : insufficient columns "
                      + "returned from the database.");
              }
  
              // Remove any previous old data
              int i = getRowCount();
              while (--i >= 0) {
                  removeRow(i);
              }
  
              while (resultSet.next()) {
                  // first column contains the row key...
                  Comparable rowKey = resultSet.getString(1);
                  for (int column = 2; column <= columnCount; column++) {
  
                      Comparable columnKey = metaData.getColumnName(column);
                      int columnType = metaData.getColumnType(column);
  
                      switch (columnType) {
                          case Types.TINYINT:
                          case Types.SMALLINT:
                          case Types.INTEGER:
                          case Types.BIGINT:
                          case Types.FLOAT:
                          case Types.DOUBLE:
                          case Types.DECIMAL:
                          case Types.NUMERIC:
                          case Types.REAL: {
                              Number value = (Number) resultSet.getObject(column);
                              if (this.transpose) {
                                  setValue(value, columnKey, rowKey);
                              }
                              else {
                                  setValue(value, rowKey, columnKey);
                              }
                              break;
                          }
                          case Types.DATE:
                          case Types.TIME:
                          case Types.TIMESTAMP: {
                              Date date = (Date) resultSet.getObject(column);
                              Number value = date.getTime();
                              if (this.transpose) {
                                  setValue(value, columnKey, rowKey);
                              }
                              else {
                                  setValue(value, rowKey, columnKey);
                              }
                              break;
                          }
                          case Types.CHAR:
                          case Types.VARCHAR:
                          case Types.LONGVARCHAR: {
                              String string
                                  = (String) resultSet.getObject(column);
                              try {
                                  Number value = Double.valueOf(string);
                                  if (this.transpose) {
                                      setValue(value, columnKey, rowKey);
                                  }
                                  else {
                                      setValue(value, rowKey, columnKey);
                                  }
                              }
                              catch (NumberFormatException e) {
                                  // suppress (value defaults to null)
                              }
                              break;
                          }
                          default:
                              // not a value, can't use it (defaults to null)
                              break;
                      }
                  }
              }
  
              fireDatasetChanged();
          }
          finally {
              if (resultSet != null) {
                  try {
                      resultSet.close();
                  }
                  catch (Exception e) {
                      // report this?
                  }
              }
              if (statement != null) {
                  try {
                      statement.close();
                  }
                  catch (Exception e) {
                      // report this?
                  }
              }
          }
      }
  
  }
  