001 /* ===========================================================
002 * JFreeChart : a free chart library for the Java(tm) platform
003 * ===========================================================
004 *
005 * (C) Copyright 2000-2006, by Object Refinery Limited and Contributors.
006 *
007 * Project Info: http://www.jfree.org/jfreechart/index.html
008 *
009 * This library is free software; you can redistribute it and/or modify it
010 * under the terms of the GNU Lesser General Public License as published by
011 * the Free Software Foundation; either version 2.1 of the License, or
012 * (at your option) any later version.
013 *
014 * This library is distributed in the hope that it will be useful, but
015 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
016 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
017 * License for more details.
018 *
019 * You should have received a copy of the GNU Lesser General Public
020 * License along with this library; if not, write to the Free Software
021 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
022 * USA.
023 *
024 * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
025 * in the United States and other countries.]
026 *
027 * ------------------------
028 * JDBCCategoryDataset.java
029 * ------------------------
030 * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
031 *
032 * Original Author: Bryan Scott; Andy;
033 * Contributor(s): David Gilbert (for Object Refinery Limited);
034 * Thomas Morgner;
035 *
036 * Changes
037 * -------
038 * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from
039 * Andy;
040 * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
041 * 03-Sep-2002 : Added fix for bug 591385 (DG);
042 * 18-Sep-2002 : Updated to support BIGINT (BS);
043 * 16-Oct-2002 : Added fix for bug 586667 (DG);
044 * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
045 * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
046 * 30-Jun-2003 : CVS Write test (BS);
047 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
048 * method (BS);
049 * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily
050 * transposed if required (DG);
051 * 10-Sep-2003 : Added support for additional JDBC types (DG);
052 * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
053 * following being highlighted on online forum (BS);
054 * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
055 * constructor, as without a connection, a query can never be
056 * executed (TM);
057 * 04-Dec-2003 : Added missing Javadocs (DG);
058 * ------------- JFREECHART 1.0.0 ---------------------------------------------
059 * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if
060 * executeQuery() is called more than once (DG);
061 *
062 */
063
064 package org.jfree.data.jdbc;
065
066 import java.sql.Connection;
067 import java.sql.Date;
068 import java.sql.DriverManager;
069 import java.sql.ResultSet;
070 import java.sql.ResultSetMetaData;
071 import java.sql.SQLException;
072 import java.sql.Statement;
073 import java.sql.Types;
074
075 import org.jfree.data.category.CategoryDataset;
076 import org.jfree.data.category.DefaultCategoryDataset;
077
078 /**
079 * A {@link CategoryDataset} implementation over a database JDBC result set.
080 * The dataset is populated via a call to {@link #executeQuery(String)} with
081 * the string SQL query. The SQL query must return at least two columns. The
082 * first column will be the category name and remaining columns values (each
083 * column represents a series). Subsequent calls to
084 * {@link #executeQuery(String)} will refresh the dataset.
085 * <p>
086 * The database connection is read-only and no write back facility exists.
087 * <p>
088 * NOTE: Many people have found this class too restrictive in general use.
089 * For the greatest flexibility, please consider writing your own code to read
090 * data from a <code>ResultSet</code> and populate a
091 * {@link DefaultCategoryDataset} directly.
092 */
093 public class JDBCCategoryDataset extends DefaultCategoryDataset {
094
095 /** The database connection. */
096 private transient Connection connection;
097
098 /**
099 * A flag the controls whether or not the table is transposed. The default
100 * is 'true' because this provides the behaviour described in the
101 * documentation.
102 */
103 private boolean transpose = true;
104
105
106 /**
107 * Creates a new dataset with a database connection.
108 *
109 * @param url the URL of the database connection.
110 * @param driverName the database driver class name.
111 * @param user the database user.
112 * @param passwd the database user's password.
113 *
114 * @throws ClassNotFoundException if the driver cannot be found.
115 * @throws SQLException if there is an error obtaining a connection to the
116 * database.
117 */
118 public JDBCCategoryDataset(String url,
119 String driverName,
120 String user,
121 String passwd)
122 throws ClassNotFoundException, SQLException {
123
124 Class.forName(driverName);
125 this.connection = DriverManager.getConnection(url, user, passwd);
126 }
127
128 /**
129 * Create a new dataset with the given database connection.
130 *
131 * @param connection the database connection.
132 */
133 public JDBCCategoryDataset(Connection connection) {
134 if (connection == null) {
135 throw new NullPointerException("A connection must be supplied.");
136 }
137 this.connection = connection;
138 }
139
140 /**
141 * Creates a new dataset with the given database connection, and executes
142 * the supplied query to populate the dataset.
143 *
144 * @param connection the connection.
145 * @param query the query.
146 *
147 * @throws SQLException if there is a problem executing the query.
148 */
149 public JDBCCategoryDataset(Connection connection, String query)
150 throws SQLException {
151 this(connection);
152 executeQuery(query);
153 }
154
155 /**
156 * Returns a flag that controls whether or not the table values are
157 * transposed when added to the dataset.
158 *
159 * @return A boolean.
160 */
161 public boolean getTranspose() {
162 return this.transpose;
163 }
164
165 /**
166 * Sets a flag that controls whether or not the table values are transposed
167 * when added to the dataset.
168 *
169 * @param transpose the flag.
170 */
171 public void setTranspose(boolean transpose) {
172 this.transpose = transpose;
173 }
174
175 /**
176 * Populates the dataset by executing the supplied query against the
177 * existing database connection. If no connection exists then no action
178 * is taken.
179 * <p>
180 * The results from the query are extracted and cached locally, thus
181 * applying an upper limit on how many rows can be retrieved successfully.
182 *
183 * @param query the query.
184 *
185 * @throws SQLException if there is a problem executing the query.
186 */
187 public void executeQuery(String query) throws SQLException {
188 executeQuery(this.connection, query);
189 }
190
191 /**
192 * Populates the dataset by executing the supplied query against the
193 * existing database connection. If no connection exists then no action
194 * is taken.
195 * <p>
196 * The results from the query are extracted and cached locally, thus
197 * applying an upper limit on how many rows can be retrieved successfully.
198 *
199 * @param con the connection.
200 * @param query the query.
201 *
202 * @throws SQLException if there is a problem executing the query.
203 */
204 public void executeQuery(Connection con, String query) throws SQLException {
205
206 Statement statement = null;
207 ResultSet resultSet = null;
208 try {
209 statement = con.createStatement();
210 resultSet = statement.executeQuery(query);
211 ResultSetMetaData metaData = resultSet.getMetaData();
212
213 int columnCount = metaData.getColumnCount();
214
215 if (columnCount < 2) {
216 throw new SQLException(
217 "JDBCCategoryDataset.executeQuery() : insufficient columns "
218 + "returned from the database.");
219 }
220
221 // Remove any previous old data
222 int i = getRowCount();
223 while (--i >= 0) {
224 removeRow(i);
225 }
226
227 while (resultSet.next()) {
228 // first column contains the row key...
229 Comparable rowKey = resultSet.getString(1);
230 for (int column = 2; column <= columnCount; column++) {
231
232 Comparable columnKey = metaData.getColumnName(column);
233 int columnType = metaData.getColumnType(column);
234
235 switch (columnType) {
236 case Types.TINYINT:
237 case Types.SMALLINT:
238 case Types.INTEGER:
239 case Types.BIGINT:
240 case Types.FLOAT:
241 case Types.DOUBLE:
242 case Types.DECIMAL:
243 case Types.NUMERIC:
244 case Types.REAL: {
245 Number value = (Number) resultSet.getObject(column);
246 if (this.transpose) {
247 setValue(value, columnKey, rowKey);
248 }
249 else {
250 setValue(value, rowKey, columnKey);
251 }
252 break;
253 }
254 case Types.DATE:
255 case Types.TIME:
256 case Types.TIMESTAMP: {
257 Date date = (Date) resultSet.getObject(column);
258 Number value = new Long(date.getTime());
259 if (this.transpose) {
260 setValue(value, columnKey, rowKey);
261 }
262 else {
263 setValue(value, rowKey, columnKey);
264 }
265 break;
266 }
267 case Types.CHAR:
268 case Types.VARCHAR:
269 case Types.LONGVARCHAR: {
270 String string
271 = (String) resultSet.getObject(column);
272 try {
273 Number value = Double.valueOf(string);
274 if (this.transpose) {
275 setValue(value, columnKey, rowKey);
276 }
277 else {
278 setValue(value, rowKey, columnKey);
279 }
280 }
281 catch (NumberFormatException e) {
282 // suppress (value defaults to null)
283 }
284 break;
285 }
286 default:
287 // not a value, can't use it (defaults to null)
288 break;
289 }
290 }
291 }
292
293 fireDatasetChanged();
294 }
295 finally {
296 if (resultSet != null) {
297 try {
298 resultSet.close();
299 }
300 catch (Exception e) {
301 // report this?
302 }
303 }
304 if (statement != null) {
305 try {
306 statement.close();
307 }
308 catch (Exception e) {
309 // report this?
310 }
311 }
312 }
313 }
314
315 }