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