Java > Open Source Codes > org > jfree > data > jdbc > JDBCXYDataset


1 /* ===========================================================
2  * JFreeChart : a free chart library for the Java(tm) platform
3  * ===========================================================
4  *
5  * (C) Copyright 2000-2006, 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  * JDBCXYDataset.java
29  * ------------------
30  * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
31  *
32  * Original Author: Bryan Scott;
33  * Contributor(s): David Gilbert (for Object Refinery Limited);
34  * Eric Alexander;
35  *
36  *
37  * Changes
38  * -------
39  * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
40  * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support
41  * for types.
42  * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data
43  * source conventions.
44  * 26-Apr-2002 : Changed to extend AbstractDataset.
45  * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
46  * 18-Sep-2002 : Updated to support BIGINT (BS);
47  * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
48  * 01-Jul-2003 : Added support to query whether a timeseries (BS);
49  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
50  * method (BS);
51  * 24-Sep-2003 : Added a check to ensure at least two valid columns are
52  * returned by the query in executeQuery as suggest in online
53  * forum by anonymous (BS);
54  * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
55  * constructor, as without a connection, a query can never be
56  * executed.
57  * 16-Mar-2004 : Added check for null values (EA);
58  * 05-May-2004 : Now extends AbstractXYDataset (DG);
59  * 21-May-2004 : Implemented TableXYDataset, added support for SMALLINT and
60  * fixed bug in code that determines the min and max values (see
61  * bug id 938138) (DG);
62  * 15-Jul-2004 : Switched getX() with getXValue() and getY() with
63  * getYValue() (DG);
64  * 18-Nov-2004 : Updated for changes in RangeInfo interface (DG);
65  * 11-Jan-2005 : Removed deprecated code in preparation for the 1.0.0
66  * release (DG);
67  * ------------- JFREECHART 1.0.x ---------------------------------------------
68  * 17-Oct-2006 : Deprecated unused methods - see bug 1578293 (DG);
69  *
70  */
71
72 package org.jfree.data.jdbc;
73
74 import java.sql.Connection ;
75 import java.sql.DriverManager ;
76 import java.sql.ResultSet ;
77 import java.sql.ResultSetMetaData ;
78 import java.sql.SQLException ;
79 import java.sql.Statement ;
80 import java.sql.Types ;
81 import java.util.ArrayList ;
82 import java.util.Date ;
83
84 import org.jfree.data.Range;
85 import org.jfree.data.RangeInfo;
86 import org.jfree.data.general.Dataset;
87 import org.jfree.data.xy.AbstractXYDataset;
88 import org.jfree.data.xy.TableXYDataset;
89 import org.jfree.data.xy.XYDataset;
90 import org.jfree.util.Log;
91
92 /**
93  * This class provides an {@link XYDataset} implementation over a database
94  * JDBC result set. The dataset is populated via a call to executeQuery with
95  * the string sql query. The sql query must return at least two columns.
96  * The first column will be the x-axis and remaining columns y-axis values.
97  * executeQuery can be called a number of times.
98  *
99  * 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 }
588

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags