Source for org.jfree.data.jdbc.JDBCCategoryDataset

   1: /* ===========================================================
   2:  * JFreeChart : a free chart library for the Java(tm) platform
   3:  * ===========================================================
   4:  *
   5:  * (C) Copyright 2000-2007, 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:  * JDBCCategoryDataset.java
  29:  * ------------------------
  30:  * (C) Copyright 2002-2007, by Bryan Scott and Contributors.
  31:  *
  32:  * Original Author:  Bryan Scott; Andy;
  33:  * Contributor(s):   David Gilbert (for Object Refinery Limited);
  34:  *                   Thomas Morgner;
  35:  *
  36:  * Changes
  37:  * -------
  38:  * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from 
  39:  *               Andy;
  40:  * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
  41:  * 03-Sep-2002 : Added fix for bug 591385 (DG);
  42:  * 18-Sep-2002 : Updated to support BIGINT (BS);
  43:  * 16-Oct-2002 : Added fix for bug 586667 (DG);
  44:  * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
  45:  * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
  46:  * 30-Jun-2003 : CVS Write test (BS);
  47:  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 
  48:  *               method (BS);
  49:  * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily 
  50:  *               transposed if required (DG);
  51:  * 10-Sep-2003 : Added support for additional JDBC types (DG);
  52:  * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
  53:  *               following being highlighted on online forum (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 (TM);
  57:  * 04-Dec-2003 : Added missing Javadocs (DG);
  58:  * ------------- JFREECHART 1.0.0 ---------------------------------------------
  59:  * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if 
  60:  *               executeQuery() is called more than once (DG);
  61:  * 
  62:  */
  63: 
  64: package org.jfree.data.jdbc;
  65: 
  66: import java.sql.Connection;
  67: import java.sql.Date;
  68: import java.sql.DriverManager;
  69: import java.sql.ResultSet;
  70: import java.sql.ResultSetMetaData;
  71: import java.sql.SQLException;
  72: import java.sql.Statement;
  73: import java.sql.Types;
  74: 
  75: import org.jfree.data.category.CategoryDataset;
  76: import org.jfree.data.category.DefaultCategoryDataset;
  77: 
  78: /**
  79:  * A {@link CategoryDataset} implementation over a database JDBC result set.
  80:  * The dataset is populated via a call to {@link #executeQuery(String)} with 
  81:  * the string SQL query.  The SQL query must return at least two columns.  The 
  82:  * first column will be the category name and remaining columns values (each
  83:  * column represents a series).  Subsequent calls to 
  84:  * {@link #executeQuery(String)} will refresh the dataset.
  85:  * <p>
  86:  * The database connection is read-only and no write back facility exists.
  87:  * <p>
  88:  * NOTE: Many people have found this class too restrictive in general use.  
  89:  * For the greatest flexibility, please consider writing your own code to read 
  90:  * data from a <code>ResultSet</code> and populate a 
  91:  * {@link DefaultCategoryDataset} directly.
  92:  */
  93: public class JDBCCategoryDataset extends DefaultCategoryDataset {
  94: 
  95:     /** For serialization. */
  96:     static final long serialVersionUID = -3080395327918844965L;
  97:     
  98:     /** The database connection. */
  99:     private transient Connection connection;
 100: 
 101:     /**
 102:      * A flag the controls whether or not the table is transposed.  The default 
 103:      * is 'true' because this provides the behaviour described in the 
 104:      * documentation.
 105:      */
 106:     private boolean transpose = true;
 107: 
 108: 
 109:     /**
 110:      * Creates a new dataset with a database connection.
 111:      *
 112:      * @param  url  the URL of the database connection.
 113:      * @param  driverName  the database driver class name.
 114:      * @param  user  the database user.
 115:      * @param  passwd  the database user's password.
 116:      * 
 117:      * @throws ClassNotFoundException if the driver cannot be found.
 118:      * @throws SQLException if there is an error obtaining a connection to the 
 119:      *                      database.
 120:      */
 121:     public JDBCCategoryDataset(String url,
 122:                                String driverName,
 123:                                String user,
 124:                                String passwd)
 125:         throws ClassNotFoundException, SQLException {
 126: 
 127:         Class.forName(driverName);
 128:         this.connection = DriverManager.getConnection(url, user, passwd);
 129:     }
 130: 
 131:     /**
 132:      * Create a new dataset with the given database connection.
 133:      *
 134:      * @param connection  the database connection.
 135:      */
 136:     public JDBCCategoryDataset(Connection connection) {
 137:         if (connection == null) {
 138:             throw new NullPointerException("A connection must be supplied.");
 139:         }
 140:         this.connection = connection;
 141:     }
 142: 
 143:     /**
 144:      * Creates a new dataset with the given database connection, and executes 
 145:      * the supplied query to populate the dataset.
 146:      *
 147:      * @param connection  the connection.
 148:      * @param query  the query.
 149:      * 
 150:      * @throws SQLException if there is a problem executing the query.
 151:      */
 152:     public JDBCCategoryDataset(Connection connection, String query) 
 153:         throws SQLException {
 154:         this(connection);
 155:         executeQuery(query);
 156:     }
 157: 
 158:     /**
 159:      * Returns a flag that controls whether or not the table values are 
 160:      * transposed when added to the dataset.
 161:      *
 162:      * @return A boolean.
 163:      */
 164:     public boolean getTranspose() {
 165:         return this.transpose;
 166:     }
 167: 
 168:     /**
 169:      * Sets a flag that controls whether or not the table values are transposed
 170:      * when added to the dataset.
 171:      *
 172:      * @param transpose  the flag.
 173:      */
 174:     public void setTranspose(boolean transpose) {
 175:         this.transpose = transpose;
 176:     }
 177: 
 178:     /**
 179:      * Populates the dataset by executing the supplied query against the 
 180:      * existing database connection.  If no connection exists then no action 
 181:      * is taken.
 182:      * <p>
 183:      * The results from the query are extracted and cached locally, thus 
 184:      * applying an upper limit on how many rows can be retrieved successfully.
 185:      *
 186:      * @param query  the query.
 187:      * 
 188:      * @throws SQLException if there is a problem executing the query.
 189:      */
 190:     public void executeQuery(String query) throws SQLException {
 191:         executeQuery(this.connection, query);
 192:     }
 193: 
 194:     /**
 195:      * Populates the dataset by executing the supplied query against the 
 196:      * existing database connection.  If no connection exists then no action 
 197:      * is taken.
 198:      * <p>
 199:      * The results from the query are extracted and cached locally, thus 
 200:      * applying an upper limit on how many rows can be retrieved successfully.
 201:      *
 202:      * @param con  the connection.
 203:      * @param query  the query.
 204:      * 
 205:      * @throws SQLException if there is a problem executing the query.
 206:      */
 207:     public void executeQuery(Connection con, String query) throws SQLException {
 208: 
 209:         Statement statement = null;
 210:         ResultSet resultSet = null;
 211:         try {
 212:             statement = con.createStatement();
 213:             resultSet = statement.executeQuery(query);
 214:             ResultSetMetaData metaData = resultSet.getMetaData();
 215: 
 216:             int columnCount = metaData.getColumnCount();
 217: 
 218:             if (columnCount < 2) {
 219:                 throw new SQLException(
 220:                     "JDBCCategoryDataset.executeQuery() : insufficient columns "
 221:                     + "returned from the database.");
 222:             }
 223: 
 224:             // Remove any previous old data
 225:             int i = getRowCount();
 226:             while (--i >= 0) {
 227:                 removeRow(i);
 228:             }
 229: 
 230:             while (resultSet.next()) {
 231:                 // first column contains the row key...
 232:                 Comparable rowKey = resultSet.getString(1);
 233:                 for (int column = 2; column <= columnCount; column++) {
 234: 
 235:                     Comparable columnKey = metaData.getColumnName(column);
 236:                     int columnType = metaData.getColumnType(column);
 237: 
 238:                     switch (columnType) {
 239:                         case Types.TINYINT:
 240:                         case Types.SMALLINT:
 241:                         case Types.INTEGER:
 242:                         case Types.BIGINT:
 243:                         case Types.FLOAT:
 244:                         case Types.DOUBLE:
 245:                         case Types.DECIMAL:
 246:                         case Types.NUMERIC:
 247:                         case Types.REAL: {
 248:                             Number value = (Number) resultSet.getObject(column);
 249:                             if (this.transpose) {
 250:                                 setValue(value, columnKey, rowKey);
 251:                             }
 252:                             else {
 253:                                 setValue(value, rowKey, columnKey);
 254:                             }
 255:                             break;
 256:                         }
 257:                         case Types.DATE:
 258:                         case Types.TIME:
 259:                         case Types.TIMESTAMP: {
 260:                             Date date = (Date) resultSet.getObject(column);
 261:                             Number value = new Long(date.getTime());
 262:                             if (this.transpose) {
 263:                                 setValue(value, columnKey, rowKey);
 264:                             }
 265:                             else {
 266:                                 setValue(value, rowKey, columnKey);
 267:                             }
 268:                             break;
 269:                         }
 270:                         case Types.CHAR:
 271:                         case Types.VARCHAR:
 272:                         case Types.LONGVARCHAR: {
 273:                             String string 
 274:                                 = (String) resultSet.getObject(column);
 275:                             try {
 276:                                 Number value = Double.valueOf(string);
 277:                                 if (this.transpose) {
 278:                                     setValue(value, columnKey, rowKey);
 279:                                 }
 280:                                 else {
 281:                                     setValue(value, rowKey, columnKey);
 282:                                 }
 283:                             }
 284:                             catch (NumberFormatException e) {
 285:                                 // suppress (value defaults to null)
 286:                             }
 287:                             break;
 288:                         }
 289:                         default:
 290:                             // not a value, can't use it (defaults to null)
 291:                             break;
 292:                     }
 293:                 }
 294:             }
 295: 
 296:             fireDatasetChanged();
 297:         }
 298:         finally {
 299:             if (resultSet != null) {
 300:                 try {
 301:                     resultSet.close();
 302:                 }
 303:                 catch (Exception e) {
 304:                     // report this?
 305:                 }
 306:             }
 307:             if (statement != null) {
 308:                 try {
 309:                     statement.close();
 310:                 }
 311:                 catch (Exception e) {
 312:                     // report this?
 313:                 }
 314:             }
 315:         }
 316:     }
 317: 
 318: }