Source for org.jfree.data.jdbc.JDBCPieDataset

   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:  * JDBCPieDataset.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, but extending 
  39:  *               DefaultPieDataset (BS);
  40:  * 24-Jun-2002 : Removed unnecessary import and local variable (DG);
  41:  * 13-Aug-2002 : Updated Javadoc comments and imports, removed default 
  42:  *               constructor (DG);
  43:  * 18-Sep-2002 : Updated to support BIGINT (BS);
  44:  * 21-Jan-2003 : Renamed JdbcPieDataset --> JDBCPieDataset (DG);
  45:  * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
  46:  * 05-Jun-2003 : Updated to support TIME, optimised executeQuery method (BS);
  47:  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 
  48:  *               method (BS);
  49:  * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default 
  50:  *               constructor, as without a connection, a query can never be 
  51:  *               executed (TM);
  52:  * 04-Dec-2003 : Added missing Javadocs (DG);
  53:  * ------------- JFREECHART 1.0.x ---------------------------------------------
  54:  * 02-Feb-2007 : Removed author tags all over JFreeChart sources (DG);
  55:  * 
  56:  */
  57: 
  58: package org.jfree.data.jdbc;
  59: 
  60: import java.sql.Connection;
  61: import java.sql.DriverManager;
  62: import java.sql.ResultSet;
  63: import java.sql.ResultSetMetaData;
  64: import java.sql.SQLException;
  65: import java.sql.Statement;
  66: import java.sql.Timestamp;
  67: import java.sql.Types;
  68: 
  69: import org.jfree.data.general.DefaultPieDataset;
  70: import org.jfree.data.general.PieDataset;
  71: 
  72: /**
  73:  * A {@link PieDataset} that reads data from a database via JDBC.
  74:  * <P>
  75:  * A query should be supplied that returns data in two columns, the first 
  76:  * containing VARCHAR data, and the second containing numerical data.  The 
  77:  * data is cached in-memory and can be refreshed at any time.
  78:  */
  79: public class JDBCPieDataset extends DefaultPieDataset {
  80: 
  81:     /** For serialization. */
  82:     static final long serialVersionUID = -8753216855496746108L;
  83: 
  84:     /** The database connection. */
  85:     private transient Connection connection;
  86: 
  87:     /**
  88:      * Creates a new JDBCPieDataset and establishes a new database connection.
  89:      *
  90:      * @param url  the URL of the database connection.
  91:      * @param driverName  the database driver class name.
  92:      * @param user  the database user.
  93:      * @param password  the database users password.
  94:      * 
  95:      * @throws ClassNotFoundException if the driver cannot be found.
  96:      * @throws SQLException if there is a problem obtaining a database 
  97:      *                      connection.
  98:      */
  99:     public JDBCPieDataset(String url,
 100:                           String driverName,
 101:                           String user,
 102:                           String password)
 103:         throws SQLException, ClassNotFoundException {
 104:         
 105:         Class.forName(driverName);
 106:         this.connection = DriverManager.getConnection(url, user, password);
 107:     }
 108: 
 109:     /**
 110:      * Creates a new JDBCPieDataset using a pre-existing database connection.
 111:      * <P>
 112:      * The dataset is initially empty, since no query has been supplied yet.
 113:      *
 114:      * @param con  the database connection.
 115:      */
 116:     public JDBCPieDataset(Connection con) {
 117:         if (con == null) {
 118:             throw new NullPointerException("A connection must be supplied.");
 119:         }
 120:         this.connection = con;
 121:     }
 122: 
 123: 
 124:     /**
 125:      * Creates a new JDBCPieDataset using a pre-existing database connection.
 126:      * <P>
 127:      * The dataset is initialised with the supplied query.
 128:      *
 129:      * @param con  the database connection.
 130:      * @param query  the database connection.
 131:      * 
 132:      * @throws SQLException if there is a problem executing the query.
 133:      */
 134:     public JDBCPieDataset(Connection con, String query) throws SQLException {
 135:         this(con);
 136:         executeQuery(query);
 137:     }
 138: 
 139:     /**
 140:      *  ExecuteQuery will attempt execute the query passed to it against the
 141:      *  existing database connection.  If no connection exists then no action
 142:      *  is taken.
 143:      *  The results from the query are extracted and cached locally, thus
 144:      *  applying an upper limit on how many rows can be retrieved successfully.
 145:      *
 146:      * @param  query  the query to be executed.
 147:      * 
 148:      * @throws SQLException if there is a problem executing the query.
 149:      */
 150:     public void executeQuery(String query) throws SQLException {
 151:       executeQuery(this.connection, query);
 152:     }
 153: 
 154:     /**
 155:      *  ExecuteQuery will attempt execute the query passed to it against the
 156:      *  existing database connection.  If no connection exists then no action
 157:      *  is taken.
 158:      *  The results from the query are extracted and cached locally, thus
 159:      *  applying an upper limit on how many rows can be retrieved successfully.
 160:      *
 161:      * @param  query  the query to be executed
 162:      * @param  con  the connection the query is to be executed against
 163:      * 
 164:      * @throws SQLException if there is a problem executing the query.
 165:      */
 166:     public void executeQuery(Connection con, String query) throws SQLException {
 167: 
 168:         Statement statement = null;
 169:         ResultSet resultSet = null;
 170: 
 171:         try {
 172:             statement = con.createStatement();
 173:             resultSet = statement.executeQuery(query);
 174:             ResultSetMetaData metaData = resultSet.getMetaData();
 175: 
 176:             int columnCount = metaData.getColumnCount();
 177:             if (columnCount != 2) {
 178:                 throw new SQLException(
 179:                     "Invalid sql generated.  PieDataSet requires 2 columns only"
 180:                 );
 181:             }
 182: 
 183:             int columnType = metaData.getColumnType(2);
 184:             double value = Double.NaN;
 185:             while (resultSet.next()) {
 186:                 Comparable key = resultSet.getString(1);
 187:                 switch (columnType) {
 188:                     case Types.NUMERIC:
 189:                     case Types.REAL:
 190:                     case Types.INTEGER:
 191:                     case Types.DOUBLE:
 192:                     case Types.FLOAT:
 193:                     case Types.DECIMAL:
 194:                     case Types.BIGINT:
 195:                         value = resultSet.getDouble(2);
 196:                         setValue(key, value);
 197:                         break;
 198: 
 199:                     case Types.DATE:
 200:                     case Types.TIME:
 201:                     case Types.TIMESTAMP:
 202:                         Timestamp date = resultSet.getTimestamp(2);
 203:                         value = date.getTime();
 204:                         setValue(key, value);
 205:                         break;
 206: 
 207:                     default:
 208:                         System.err.println(
 209:                             "JDBCPieDataset - unknown data type"
 210:                         );
 211:                         break;
 212:                 }
 213:             }
 214: 
 215:             fireDatasetChanged();
 216: 
 217:         }
 218:         finally {
 219:             if (resultSet != null) {
 220:                 try {
 221:                     resultSet.close();
 222:                 }
 223:                 catch (Exception e) {
 224:                     System.err.println("JDBCPieDataset: swallowing exception.");
 225:                 }
 226:             }
 227:             if (statement != null) {
 228:                 try {
 229:                     statement.close();
 230:                 }
 231:                 catch (Exception e) {
 232:                     System.err.println("JDBCPieDataset: swallowing exception.");
 233:                 }
 234:             }
 235:         }
 236:     }
 237: 
 238: 
 239:     /**
 240:      * Close the database connection
 241:      */
 242:     public void close() {
 243:         try {
 244:             this.connection.close();
 245:         }
 246:         catch (Exception e) {
 247:             System.err.println("JdbcXYDataset: swallowing exception.");
 248:         }
 249:     }
 250: }