001 /* ===========================================================
002 * JFreeChart : a free chart library for the Java(tm) platform
003 * ===========================================================
004 *
005 * (C) Copyright 2000-2008, 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-2008, 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, TableXYDataset, RangeInfo {
103
104 /** The database connection. */
105 private transient Connection connection;
106
107 /** Column names. */
108 private String[] columnNames = {};
109
110 /** Rows. */
111 private ArrayList rows;
112
113 /** The maximum y value of the returned result set */
114 private double maxValue = 0.0;
115
116 /** The minimum y value of the returned result set */
117 private double minValue = 0.0;
118
119 /** Is this dataset a timeseries ? */
120 private boolean isTimeSeries = false;
121
122 /**
123 * Creates a new JDBCXYDataset (initially empty) with no database
124 * connection.
125 */
126 private JDBCXYDataset() {
127 this.rows = new ArrayList();
128 }
129
130 /**
131 * Creates a new dataset (initially empty) and establishes a new database
132 * connection.
133 *
134 * @param url URL of the database connection.
135 * @param driverName the database driver class name.
136 * @param user the database user.
137 * @param password the database user's password.
138 *
139 * @throws ClassNotFoundException if the driver cannot be found.
140 * @throws SQLException if there is a problem connecting to the database.
141 */
142 public JDBCXYDataset(String url,
143 String driverName,
144 String user,
145 String password)
146 throws SQLException, ClassNotFoundException {
147
148 this();
149 Class.forName(driverName);
150 this.connection = DriverManager.getConnection(url, user, password);
151 }
152
153 /**
154 * Creates a new dataset (initially empty) using the specified database
155 * connection.
156 *
157 * @param con the database connection.
158 *
159 * @throws SQLException if there is a problem connecting to the database.
160 */
161 public JDBCXYDataset(Connection con) throws SQLException {
162 this();
163 this.connection = con;
164 }
165
166 /**
167 * Creates a new dataset using the specified database connection, and
168 * populates it using data obtained with the supplied query.
169 *
170 * @param con the connection.
171 * @param query the SQL query.
172 *
173 * @throws SQLException if there is a problem executing the query.
174 */
175 public JDBCXYDataset(Connection con, String query) throws SQLException {
176 this(con);
177 executeQuery(query);
178 }
179
180 /**
181 * Returns <code>true</code> if the dataset represents time series data,
182 * and <code>false</code> otherwise.
183 *
184 * @return A boolean.
185 */
186 public boolean isTimeSeries() {
187 return this.isTimeSeries;
188 }
189
190 /**
191 * Sets a flag that indicates whether or not the data represents a time
192 * series.
193 *
194 * @param timeSeries the new value of the flag.
195 */
196 public void setTimeSeries(boolean timeSeries) {
197 this.isTimeSeries = timeSeries;
198 }
199
200 /**
201 * ExecuteQuery will attempt execute the query passed to it against the
202 * existing database connection. If no connection exists then no action
203 * is taken.
204 *
205 * The results from the query are extracted and cached locally, thus
206 * applying an upper limit on how many rows can be retrieved successfully.
207 *
208 * @param query the query to be executed.
209 *
210 * @throws SQLException if there is a problem executing the query.
211 */
212 public void executeQuery(String query) throws SQLException {
213 executeQuery(this.connection, query);
214 }
215
216 /**
217 * ExecuteQuery will attempt execute the query passed to it against the
218 * provided database connection. If connection is null then no action is
219 * taken.
220 *
221 * The results from the query are extracted and cached locally, thus
222 * applying an upper limit on how many rows can be retrieved successfully.
223 *
224 * @param query the query to be executed.
225 * @param con the connection the query is to be executed against.
226 *
227 * @throws SQLException if there is a problem executing the query.
228 */
229 public void executeQuery(Connection con, String query)
230 throws SQLException {
231
232 if (con == null) {
233 throw new SQLException(
234 "There is no database to execute the query."
235 );
236 }
237
238 ResultSet resultSet = null;
239 Statement statement = null;
240 try {
241 statement = con.createStatement();
242 resultSet = statement.executeQuery(query);
243 ResultSetMetaData metaData = resultSet.getMetaData();
244
245 int numberOfColumns = metaData.getColumnCount();
246 int numberOfValidColumns = 0;
247 int [] columnTypes = new int[numberOfColumns];
248 for (int column = 0; column < numberOfColumns; column++) {
249 try {
250 int type = metaData.getColumnType(column + 1);
251 switch (type) {
252
253 case Types.NUMERIC:
254 case Types.REAL:
255 case Types.INTEGER:
256 case Types.DOUBLE:
257 case Types.FLOAT:
258 case Types.DECIMAL:
259 case Types.BIT:
260 case Types.DATE:
261 case Types.TIME:
262 case Types.TIMESTAMP:
263 case Types.BIGINT:
264 case Types.SMALLINT:
265 ++numberOfValidColumns;
266 columnTypes[column] = type;
267 break;
268 default:
269 Log.warn(
270 "Unable to load column "
271 + column + " (" + type + ","
272 + metaData.getColumnClassName(column + 1)
273 + ")"
274 );
275 columnTypes[column] = Types.NULL;
276 break;
277 }
278 }
279 catch (SQLException e) {
280 columnTypes[column] = Types.NULL;
281 throw e;
282 }
283 }
284
285
286 if (numberOfValidColumns <= 1) {
287 throw new SQLException(
288 "Not enough valid columns where generated by query."
289 );
290 }
291
292 /// First column is X data
293 this.columnNames = new String[numberOfValidColumns - 1];
294 /// Get the column names and cache them.
295 int currentColumn = 0;
296 for (int column = 1; column < numberOfColumns; column++) {
297 if (columnTypes[column] != Types.NULL) {
298 this.columnNames[currentColumn]
299 = metaData.getColumnLabel(column + 1);
300 ++currentColumn;
301 }
302 }
303
304 // Might need to add, to free memory from any previous result sets
305 if (this.rows != null) {
306 for (int column = 0; column < this.rows.size(); column++) {
307 ArrayList row = (ArrayList) this.rows.get(column);
308 row.clear();
309 }
310 this.rows.clear();
311 }
312
313 // Are we working with a time series.
314 switch (columnTypes[0]) {
315 case Types.DATE:
316 case Types.TIME:
317 case Types.TIMESTAMP:
318 this.isTimeSeries = true;
319 break;
320 default :
321 this.isTimeSeries = false;
322 break;
323 }
324
325 // Get all rows.
326 // rows = new ArrayList();
327 while (resultSet.next()) {
328 ArrayList newRow = new ArrayList();
329 for (int column = 0; column < numberOfColumns; column++) {
330 Object xObject = resultSet.getObject(column + 1);
331 switch (columnTypes[column]) {
332 case Types.NUMERIC:
333 case Types.REAL:
334 case Types.INTEGER:
335 case Types.DOUBLE:
336 case Types.FLOAT:
337 case Types.DECIMAL:
338 case Types.BIGINT:
339 case Types.SMALLINT:
340 newRow.add(xObject);
341 break;
342
343 case Types.DATE:
344 case Types.TIME:
345 case Types.TIMESTAMP:
346 newRow.add(new Long(((Date) xObject).getTime()));
347 break;
348 case Types.NULL:
349 break;
350 default:
351 System.err.println("Unknown data");
352 columnTypes[column] = Types.NULL;
353 break;
354 }
355 }
356 this.rows.add(newRow);
357 }
358
359 /// a kludge to make everything work when no rows returned
360 if (this.rows.size() == 0) {
361 ArrayList newRow = new ArrayList();
362 for (int column = 0; column < numberOfColumns; column++) {
363 if (columnTypes[column] != Types.NULL) {
364 newRow.add(new Integer(0));
365 }
366 }
367 this.rows.add(newRow);
368 }
369
370 /// Determine max and min values.
371 if (this.rows.size() < 1) {
372 this.maxValue = 0.0;
373 this.minValue = 0.0;
374 }
375 else {
376 ArrayList row = (ArrayList) this.rows.get(0);
377 this.maxValue = Double.NEGATIVE_INFINITY;
378 this.minValue = Double.POSITIVE_INFINITY;
379 for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
380 row = (ArrayList) this.rows.get(rowNum);
381 for (int column = 1; column < numberOfColumns; column++) {
382 Object testValue = row.get(column);
383 if (testValue != null) {
384 double test = ((Number) testValue).doubleValue();
385
386 if (test < this.minValue) {
387 this.minValue = test;
388 }
389 if (test > this.maxValue) {
390 this.maxValue = test;
391 }
392 }
393 }
394 }
395 }
396
397 fireDatasetChanged(); // Tell the listeners a new table has arrived.
398 }
399 finally {
400 if (resultSet != null) {
401 try {
402 resultSet.close();
403 }
404 catch (Exception e) {
405 // TODO: is this a good idea?
406 }
407 }
408 if (statement != null) {
409 try {
410 statement.close();
411 }
412 catch (Exception e) {
413 // TODO: is this a good idea?
414 }
415 }
416 }
417
418 }
419
420 /**
421 * Returns the x-value for the specified series and item. The
422 * implementation is responsible for ensuring that the x-values are
423 * presented in ascending order.
424 *
425 * @param seriesIndex the series (zero-based index).
426 * @param itemIndex the item (zero-based index).
427 *
428 * @return The x-value
429 *
430 * @see XYDataset
431 */
432 public Number getX(int seriesIndex, int itemIndex) {
433 ArrayList row = (ArrayList) this.rows.get(itemIndex);
434 return (Number) row.get(0);
435 }
436
437 /**
438 * Returns the y-value for the specified series and item.
439 *
440 * @param seriesIndex the series (zero-based index).
441 * @param itemIndex the item (zero-based index).
442 *
443 * @return The yValue value
444 *
445 * @see XYDataset
446 */
447 public Number getY(int seriesIndex, int itemIndex) {
448 ArrayList row = (ArrayList) this.rows.get(itemIndex);
449 return (Number) row.get(seriesIndex + 1);
450 }
451
452 /**
453 * Returns the number of items in the specified series.
454 *
455 * @param seriesIndex the series (zero-based index).
456 *
457 * @return The itemCount value
458 *
459 * @see XYDataset
460 */
461 public int getItemCount(int seriesIndex) {
462 return this.rows.size();
463 }
464
465 /**
466 * Returns the number of items in all series. This method is defined by
467 * the {@link TableXYDataset} interface.
468 *
469 * @return The item count.
470 */
471 public int getItemCount() {
472 return getItemCount(0);
473 }
474
475 /**
476 * Returns the number of series in the dataset.
477 *
478 * @return The seriesCount value
479 *
480 * @see XYDataset
481 * @see Dataset
482 */
483 public int getSeriesCount() {
484 return this.columnNames.length;
485 }
486
487 /**
488 * Returns the key for the specified series.
489 *
490 * @param seriesIndex the series (zero-based index).
491 *
492 * @return The seriesName value
493 *
494 * @see XYDataset
495 * @see Dataset
496 */
497 public Comparable getSeriesKey(int seriesIndex) {
498
499 if ((seriesIndex < this.columnNames.length)
500 && (this.columnNames[seriesIndex] != null)) {
501 return this.columnNames[seriesIndex];
502 }
503 else {
504 return "";
505 }
506
507 }
508
509 /**
510 * Returns the number of items that should be displayed in the legend.
511 *
512 * @return The legendItemCount value
513 *
514 * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
515 * the API by mistake and is officially deprecated from version 1.0.3
516 * onwards).
517 */
518 public int getLegendItemCount() {
519 return getSeriesCount();
520 }
521
522 /**
523 * Returns the legend item labels.
524 *
525 * @return The legend item labels.
526 *
527 * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
528 * the API by mistake and is officially deprecated from version 1.0.3
529 * onwards).
530 */
531 public String[] getLegendItemLabels() {
532 return this.columnNames;
533 }
534
535 /**
536 * Close the database connection
537 */
538 public void close() {
539
540 try {
541 this.connection.close();
542 }
543 catch (Exception e) {
544 System.err.println("JdbcXYDataset: swallowing exception.");
545 }
546
547 }
548
549 /**
550 * Returns the minimum y-value in the dataset.
551 *
552 * @param includeInterval a flag that determines whether or not the
553 * y-interval is taken into account.
554 *
555 * @return The minimum value.
556 */
557 public double getRangeLowerBound(boolean includeInterval) {
558 return this.minValue;
559 }
560
561 /**
562 * Returns the maximum y-value in the dataset.
563 *
564 * @param includeInterval a flag that determines whether or not the
565 * y-interval is taken into account.
566 *
567 * @return The maximum value.
568 */
569 public double getRangeUpperBound(boolean includeInterval) {
570 return this.maxValue;
571 }
572
573 /**
574 * Returns the range of the values in this dataset's range.
575 *
576 * @param includeInterval a flag that determines whether or not the
577 * y-interval is taken into account.
578 *
579 * @return The range.
580 */
581 public Range getRangeBounds(boolean includeInterval) {
582 return new Range(this.minValue, this.maxValue);
583 }
584
585 }