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 * JDBCXYDataset.java
029 * ------------------
030 * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
031 *
032 * Original Author: Bryan Scott;
033 * Contributor(s): David Gilbert (for Object Refinery Limited);
034 * Eric Alexander;
035 *
036 *
037 * Changes
038 * -------
039 * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
040 * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support
041 * for types.
042 * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data
043 * source conventions.
044 * 26-Apr-2002 : Changed to extend AbstractDataset.
045 * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
046 * 18-Sep-2002 : Updated to support BIGINT (BS);
047 * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
048 * 01-Jul-2003 : Added support to query whether a timeseries (BS);
049 * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
050 * method (BS);
051 * 24-Sep-2003 : Added a check to ensure at least two valid columns are
052 * returned by the query in executeQuery as suggest in online
053 * forum by anonymous (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.
057 * 16-Mar-2004 : Added check for null values (EA);
058 * 05-May-2004 : Now extends AbstractXYDataset (DG);
059 * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and
060 * fixed bug in code that determines the min and max values (see
061 * bug id 938138) (DG);
062 * 15-Jul-2004 : Switched getX() with getXValue() and getY() with
063 * getYValue() (DG);
064 * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG);
065 * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0
066 * release (DG);
067 * ------------- JFREECHART 1.0.x ---------------------------------------------
068 * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG);
069 *
070 */
071
072 package org.jfree.data.jdbc;
073
074 import java.sql.Connection;
075 import java.sql.DriverManager;
076 import java.sql.ResultSet;
077 import java.sql.ResultSetMetaData;
078 import java.sql.SQLException;
079 import java.sql.Statement;
080 import java.sql.Types;
081 import java.util.ArrayList;
082 import java.util.Date;
083
084 import org.jfree.data.Range;
085 import org.jfree.data.RangeInfo;
086 import org.jfree.data.general.Dataset;
087 import org.jfree.data.xy.AbstractXYDataset;
088 import org.jfree.data.xy.TableXYDataset;
089 import org.jfree.data.xy.XYDataset;
090 import org.jfree.util.Log;
091
092 /**
093 * This class provides an {@link XYDataset} implementation over a database
094 * JDBC result set. The dataset is populated via a call to executeQuery with
095 * the string sql query. The sql query must return at least two columns.
096 * The first column will be the x-axis and remaining columns y-axis values.
097 * executeQuery can be called a number of times.
098 *
099 * The database connection is read-only and no write back facility exists.
100 */
101 public class JDBCXYDataset extends AbstractXYDataset
102 implements XYDataset,
103 TableXYDataset,
104 RangeInfo {
105
106 /** The database connection. */
107 private transient Connection connection;
108
109 /** Column names. */
110 private String[] columnNames = {};
111
112 /** Rows. */
113 private ArrayList rows;
114
115 /** The maximum y value of the returned result set */
116 private double maxValue = 0.0;
117
118 /** The minimum y value of the returned result set */
119 private double minValue = 0.0;
120
121 /** Is this dataset a timeseries ? */
122 private boolean isTimeSeries = false;
123
124 /**
125 * Creates a new JDBCXYDataset (initially empty) with no database
126 * connection.
127 */
128 private JDBCXYDataset() {
129 this.rows = new ArrayList();
130 }
131
132 /**
133 * Creates a new dataset (initially empty) and establishes a new database
134 * connection.
135 *
136 * @param url URL of the database connection.
137 * @param driverName the database driver class name.
138 * @param user the database user.
139 * @param password the database user's password.
140 *
141 * @throws ClassNotFoundException if the driver cannot be found.
142 * @throws SQLException if there is a problem connecting to the database.
143 */
144 public JDBCXYDataset(String url,
145 String driverName,
146 String user,
147 String password)
148 throws SQLException, ClassNotFoundException {
149
150 this();
151 Class.forName(driverName);
152 this.connection = DriverManager.getConnection(url, user, password);
153 }
154
155 /**
156 * Creates a new dataset (initially empty) using the specified database
157 * connection.
158 *
159 * @param con the database connection.
160 *
161 * @throws SQLException if there is a problem connecting to the database.
162 */
163 public JDBCXYDataset(Connection con) throws SQLException {
164 this();
165 this.connection = con;
166 }
167
168 /**
169 * Creates a new dataset using the specified database connection, and
170 * populates it using data obtained with the supplied query.
171 *
172 * @param con the connection.
173 * @param query the SQL query.
174 *
175 * @throws SQLException if there is a problem executing the query.
176 */
177 public JDBCXYDataset(Connection con, String query) throws SQLException {
178 this(con);
179 executeQuery(query);
180 }
181
182 /**
183 * Returns <code>true</code> if the dataset represents time series data,
184 * and <code>false</code> otherwise.
185 *
186 * @return A boolean.
187 */
188 public boolean isTimeSeries() {
189 return this.isTimeSeries;
190 }
191
192 /**
193 * Sets a flag that indicates whether or not the data represents a time
194 * series.
195 *
196 * @param timeSeries the new value of the flag.
197 */
198 public void setTimeSeries(boolean timeSeries) {
199 this.isTimeSeries = timeSeries;
200 }
201
202 /**
203 * ExecuteQuery will attempt execute the query passed to it against the
204 * existing database connection. If no connection exists then no action
205 * is taken.
206 *
207 * The results from the query are extracted and cached locally, thus
208 * applying an upper limit on how many rows can be retrieved successfully.
209 *
210 * @param query the query to be executed.
211 *
212 * @throws SQLException if there is a problem executing the query.
213 */
214 public void executeQuery(String query) throws SQLException {
215 executeQuery(this.connection, query);
216 }
217
218 /**
219 * ExecuteQuery will attempt execute the query passed to it against the
220 * provided database connection. If connection is null then no action is
221 * taken.
222 *
223 * The results from the query are extracted and cached locally, thus
224 * applying an upper limit on how many rows can be retrieved successfully.
225 *
226 * @param query the query to be executed.
227 * @param con the connection the query is to be executed against.
228 *
229 * @throws SQLException if there is a problem executing the query.
230 */
231 public void executeQuery(Connection con, String query)
232 throws SQLException {
233
234 if (con == null) {
235 throw new SQLException(
236 "There is no database to execute the query."
237 );
238 }
239
240 ResultSet resultSet = null;
241 Statement statement = null;
242 try {
243 statement = con.createStatement();
244 resultSet = statement.executeQuery(query);
245 ResultSetMetaData metaData = resultSet.getMetaData();
246
247 int numberOfColumns = metaData.getColumnCount();
248 int numberOfValidColumns = 0;
249 int [] columnTypes = new int[numberOfColumns];
250 for (int column = 0; column < numberOfColumns; column++) {
251 try {
252 int type = metaData.getColumnType(column + 1);
253 switch (type) {
254
255 case Types.NUMERIC:
256 case Types.REAL:
257 case Types.INTEGER:
258 case Types.DOUBLE:
259 case Types.FLOAT:
260 case Types.DECIMAL:
261 case Types.BIT:
262 case Types.DATE:
263 case Types.TIME:
264 case Types.TIMESTAMP:
265 case Types.BIGINT:
266 case Types.SMALLINT:
267 ++numberOfValidColumns;
268 columnTypes[column] = type;
269 break;
270 default:
271 Log.warn(
272 "Unable to load column "
273 + column + " (" + type + ","
274 + metaData.getColumnClassName(column + 1)
275 + ")"
276 );
277 columnTypes[column] = Types.NULL;
278 break;
279 }
280 }
281 catch (SQLException e) {
282 columnTypes[column] = Types.NULL;
283 throw e;
284 }
285 }
286
287
288 if (numberOfValidColumns <= 1) {
289 throw new SQLException(
290 "Not enough valid columns where generated by query."
291 );
292 }
293
294 /// First column is X data
295 this.columnNames = new String[numberOfValidColumns - 1];
296 /// Get the column names and cache them.
297 int currentColumn = 0;
298 for (int column = 1; column < numberOfColumns; column++) {
299 if (columnTypes[column] != Types.NULL) {
300 this.columnNames[currentColumn]
301 = metaData.getColumnLabel(column + 1);
302 ++currentColumn;
303 }
304 }
305
306 // Might need to add, to free memory from any previous result sets
307 if (this.rows != null) {
308 for (int column = 0; column < this.rows.size(); column++) {
309 ArrayList row = (ArrayList) this.rows.get(column);
310 row.clear();
311 }
312 this.rows.clear();
313 }
314
315 // Are we working with a time series.
316 switch (columnTypes[0]) {
317 case Types.DATE:
318 case Types.TIME:
319 case Types.TIMESTAMP:
320 this.isTimeSeries = true;
321 break;
322 default :
323 this.isTimeSeries = false;
324 break;
325 }
326
327 // Get all rows.
328 // rows = new ArrayList();
329 while (resultSet.next()) {
330 ArrayList newRow = new ArrayList();
331 for (int column = 0; column < numberOfColumns; column++) {
332 Object xObject = resultSet.getObject(column + 1);
333 switch (columnTypes[column]) {
334 case Types.NUMERIC:
335 case Types.REAL:
336 case Types.INTEGER:
337 case Types.DOUBLE:
338 case Types.FLOAT:
339 case Types.DECIMAL:
340 case Types.BIGINT:
341 case Types.SMALLINT:
342 newRow.add(xObject);
343 break;
344
345 case Types.DATE:
346 case Types.TIME:
347 case Types.TIMESTAMP:
348 newRow.add(new Long(((Date) xObject).getTime()));
349 break;
350 case Types.NULL:
351 break;
352 default:
353 System.err.println("Unknown data");
354 columnTypes[column] = Types.NULL;
355 break;
356 }
357 }
358 this.rows.add(newRow);
359 }
360
361 /// a kludge to make everything work when no rows returned
362 if (this.rows.size() == 0) {
363 ArrayList newRow = new ArrayList();
364 for (int column = 0; column < numberOfColumns; column++) {
365 if (columnTypes[column] != Types.NULL) {
366 newRow.add(new Integer(0));
367 }
368 }
369 this.rows.add(newRow);
370 }
371
372 /// Determine max and min values.
373 if (this.rows.size() < 1) {
374 this.maxValue = 0.0;
375 this.minValue = 0.0;
376 }
377 else {
378 ArrayList row = (ArrayList) this.rows.get(0);
379 this.maxValue = Double.NEGATIVE_INFINITY;
380 this.minValue = Double.POSITIVE_INFINITY;
381 for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
382 row = (ArrayList) this.rows.get(rowNum);
383 for (int column = 1; column < numberOfColumns; column++) {
384 Object testValue = row.get(column);
385 if (testValue != null) {
386 double test = ((Number) testValue).doubleValue();
387
388 if (test < this.minValue) {
389 this.minValue = test;
390 }
391 if (test > this.maxValue) {
392 this.maxValue = test;
393 }
394 }
395 }
396 }
397 }
398
399 fireDatasetChanged(); // Tell the listeners a new table has arrived.
400 }
401 finally {
402 if (resultSet != null) {
403 try {
404 resultSet.close();
405 }
406 catch (Exception e) {
407 // TODO: is this a good idea?
408 }
409 }
410 if (statement != null) {
411 try {
412 statement.close();
413 }
414 catch (Exception e) {
415 // TODO: is this a good idea?
416 }
417 }
418 }
419
420 }
421
422 /**
423 * Returns the x-value for the specified series and item. The
424 * implementation is responsible for ensuring that the x-values are
425 * presented in ascending order.
426 *
427 * @param seriesIndex the series (zero-based index).
428 * @param itemIndex the item (zero-based index).
429 *
430 * @return The x-value
431 *
432 * @see XYDataset
433 */
434 public Number getX(int seriesIndex, int itemIndex) {
435 ArrayList row = (ArrayList) this.rows.get(itemIndex);
436 return (Number) row.get(0);
437 }
438
439 /**
440 * Returns the y-value for the specified series and item.
441 *
442 * @param seriesIndex the series (zero-based index).
443 * @param itemIndex the item (zero-based index).
444 *
445 * @return The yValue value
446 *
447 * @see XYDataset
448 */
449 public Number getY(int seriesIndex, int itemIndex) {
450 ArrayList row = (ArrayList) this.rows.get(itemIndex);
451 return (Number) row.get(seriesIndex + 1);
452 }
453
454 /**
455 * Returns the number of items in the specified series.
456 *
457 * @param seriesIndex the series (zero-based index).
458 *
459 * @return The itemCount value
460 *
461 * @see XYDataset
462 */
463 public int getItemCount(int seriesIndex) {
464 return this.rows.size();
465 }
466
467 /**
468 * Returns the number of items in all series. This method is defined by
469 * the {@link TableXYDataset} interface.
470 *
471 * @return The item count.
472 */
473 public int getItemCount() {
474 return getItemCount(0);
475 }
476
477 /**
478 * Returns the number of series in the dataset.
479 *
480 * @return The seriesCount value
481 *
482 * @see XYDataset
483 * @see Dataset
484 */
485 public int getSeriesCount() {
486 return this.columnNames.length;
487 }
488
489 /**
490 * Returns the key for the specified series.
491 *
492 * @param seriesIndex the series (zero-based index).
493 *
494 * @return The seriesName value
495 *
496 * @see XYDataset
497 * @see Dataset
498 */
499 public Comparable getSeriesKey(int seriesIndex) {
500
501 if ((seriesIndex < this.columnNames.length)
502 && (this.columnNames[seriesIndex] != null)) {
503 return this.columnNames[seriesIndex];
504 }
505 else {
506 return "";
507 }
508
509 }
510
511 /**
512 * Returns the number of items that should be displayed in the legend.
513 *
514 * @return The legendItemCount value
515 *
516 * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
517 * the API by mistake and is officially deprecated from version 1.0.3
518 * onwards).
519 */
520 public int getLegendItemCount() {
521 return getSeriesCount();
522 }
523
524 /**
525 * Returns the legend item labels.
526 *
527 * @return The legend item labels.
528 *
529 * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
530 * the API by mistake and is officially deprecated from version 1.0.3
531 * onwards).
532 */
533 public String[] getLegendItemLabels() {
534 return this.columnNames;
535 }
536
537 /**
538 * Close the database connection
539 */
540 public void close() {
541
542 try {
543 this.connection.close();
544 }
545 catch (Exception e) {
546 System.err.println("JdbcXYDataset: swallowing exception.");
547 }
548
549 }
550
551 /**
552 * Returns the minimum y-value in the dataset.
553 *
554 * @param includeInterval a flag that determines whether or not the
555 * y-interval is taken into account.
556 *
557 * @return The minimum value.
558 */
559 public double getRangeLowerBound(boolean includeInterval) {
560 return this.minValue;
561 }
562
563 /**
564 * Returns the maximum y-value in the dataset.
565 *
566 * @param includeInterval a flag that determines whether or not the
567 * y-interval is taken into account.
568 *
569 * @return The maximum value.
570 */
571 public double getRangeUpperBound(boolean includeInterval) {
572 return this.maxValue;
573 }
574
575 /**
576 * Returns the range of the values in this dataset's range.
577 *
578 * @param includeInterval a flag that determines whether or not the
579 * y-interval is taken into account.
580 *
581 * @return The range.
582 */
583 public Range getRangeBounds(boolean includeInterval) {
584 return new Range(this.minValue, this.maxValue);
585 }
586
587 }