KickJava   Java API By Example, From Geeks To Geeks.

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


1 /* ======================================
2  * JFreeChart : a free Java chart library
3  * ======================================
4  *
5  * Project Info: http://www.jfree.org/jfreechart/index.html
6  * Project Lead: David Gilbert (david.gilbert@object-refinery.com);
7  *
8  * (C) Copyright 2000-2003, by Object Refinery Limited and Contributors.
9  *
10  * This library is free software; you can redistribute it and/or modify it under the terms
11  * of the GNU Lesser General Public License as published by the Free Software Foundation;
12  * either version 2.1 of the License, or (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
15  * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
16  * See the GNU Lesser General Public License for more details.
17  *
18  * You should have received a copy of the GNU Lesser General Public License along with this
19  * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
20  * Boston, MA 02111-1307, USA.
21  *
22  * ------------------
23  * JDBCXYDataset.java
24  * ------------------
25  * (C) Copyright 2002, 2003, by Bryan Scott and Contributors.
26  *
27  * Original Author: Bryan Scott;
28  * Contributor(s): David Gilbert (for Object Refinery Limited);
29  *
30  *
31  * Changes
32  * -------
33  * 14-Mar-2002 : Version 1 contributed by Bryan Scott (DG);
34  * 19-Apr-2002 : Updated executeQuery, to close cursors and to improve support for types.
35  * 26-Apr-2002 : Renamed JdbcXYDataset to better fit in with the existing data source conventions.
36  * 26-Apr-2002 : Changed to extend AbstractDataset.
37  * 13-Aug-2002 : Updated Javadoc comments and imports (DG);
38  * 18-Sep-2002 : Updated to support BIGINT (BS);
39  * 21-Jan-2003 : Renamed JdbcXYDataset --> JDBCXYDataset (DG);
40  * 01-Jul-2003 : Added support to query whether a timeseries (BS);
41  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) method (BS);
42  * 24-Sep-2003 : Added a check to ensure at least two valid columns are returned by the
43  * query in executeQuery as suggest in online forum by anonymous (BS);
44  */
45
46 package org.jfree.data;
47
48 import java.sql.Connection JavaDoc;
49 import java.sql.DriverManager JavaDoc;
50 import java.sql.ResultSet JavaDoc;
51 import java.sql.ResultSetMetaData JavaDoc;
52 import java.sql.SQLException JavaDoc;
53 import java.sql.Statement JavaDoc;
54 import java.sql.Types JavaDoc;
55 import java.util.Vector JavaDoc;
56
57 /**
58  * This class provides an {@link XYDataset} implementation over a database JDBC result set.
59  * The dataset is populated via a call to executeQuery with the string sql query.
60  * The sql query must return at least two columns. The first column will be
61  * the x-axis and remaining columns y-axis values.
62  * executeQuery can be called a number of times.
63  *
64  * The database connection is read-only and no write back facility exists.
65  */
66 public class JDBCXYDataset extends AbstractDataset implements XYDataset, RangeInfo {
67
68     /** The database connection. */
69     Connection JavaDoc connection;
70
71     /** The statement. */
72     Statement JavaDoc statement;
73
74     /** The result set. */
75     ResultSet JavaDoc resultSet;
76
77     /** Information about the result set. */
78     ResultSetMetaData JavaDoc metaData;
79
80     /** Column names. */
81     String JavaDoc[] columnNames = {};
82
83     /** Rows. */
84     Vector JavaDoc rows = new Vector JavaDoc(0);
85
86     /** The maximum y value of the returned result set */
87     protected double maxValue = 0.0;
88
89     /** The minimum y value of the returned result set */
90     protected double minValue = 0.0;
91
92     /** Is this dataset a timeseries ? */
93     public boolean isTimeSeries = false;
94
95     /**
96      * Creates a new JDBCXYDataset (initially empty) with no database connection.
97      *
98      */
99     public JDBCXYDataset() {
100     }
101
102     /**
103      * Creates a new JDBCXYDataset (initially empty) and establishes a new database connection.
104      *
105      * @param url URL of the database connection.
106      * @param driverName The database driver class name.
107      * @param user The database user.
108      * @param passwd The database users password.
109      */
110     public JDBCXYDataset(String JavaDoc url,
111                          String JavaDoc driverName,
112                          String JavaDoc user,
113                          String JavaDoc passwd) {
114
115         try {
116             Class.forName(driverName);
117             connection = DriverManager.getConnection(url, user, passwd);
118             statement = connection.createStatement();
119         }
120         catch (ClassNotFoundException JavaDoc ex) {
121             System.err.println("Cannot find the database driver classes.");
122             System.err.println(ex);
123         }
124         catch (SQLException JavaDoc ex) {
125             System.err.println("Cannot connect to this database.");
126             System.err.println(ex);
127         }
128     }
129
130     /**
131      * Creates a new JDBCXYDataset (initially empty) using the specified database connection.
132      *
133      * @param con The database connection.
134      */
135     public JDBCXYDataset(Connection JavaDoc con) {
136         try {
137             connection = con;
138         }
139         catch (Exception JavaDoc e) {
140             e.printStackTrace();
141         }
142     }
143
144     /**
145      * Creates a new JDBCXYDataset using the specified database connection, and populates it
146      * using data obtained with the supplied query.
147      *
148      * @param con The connection.
149      * @param query The SQL query.
150      */
151     public JDBCXYDataset(Connection JavaDoc con, String JavaDoc query) {
152         this(con);
153         executeQuery(query);
154     }
155
156     /**
157      * ExecuteQuery will attempt execute the query passed to it against the
158      * existing database connection. If no connection exists then no action
159      * is taken.
160      *
161      * The results from the query are extracted and cached locally, thus
162      * applying an upper limit on how many rows can be retrieved successfully.
163      *
164      * @param query The query to be executed
165      */
166     public void executeQuery(String JavaDoc query) {
167       executeQuery(connection, query);
168     }
169
170     /**
171      * ExecuteQuery will attempt execute the query passed to it against the
172      * provided database connection. If connection is null then no action is taken
173      *
174      * The results from the query are extracted and cached locally, thus
175      * applying an upper limit on how many rows can be retrieved successfully.
176      *
177      * @param query The query to be executed
178      * @param con The connection the query is to be executed against
179      */
180     public void executeQuery(Connection JavaDoc con, String JavaDoc query) {
181         Object JavaDoc xObject = null;
182         int column = 0;
183         int currentColumn = 0;
184         int numberOfColumns = 0;
185         int numberOfValidColumns = 0;
186         int columnTypes[] = null;
187
188         if (con == null) {
189           System.err.println("There is no database to execute the query.");
190           return;
191         }
192
193         try {
194             statement = con.createStatement();
195             resultSet = statement.executeQuery(query);
196             metaData = resultSet.getMetaData();
197
198             numberOfColumns = metaData.getColumnCount();
199             columnTypes = new int[numberOfColumns];
200             for (column = 0; column < numberOfColumns; column++) {
201                 try {
202                     int type = metaData.getColumnType(column + 1);
203                     switch (type) {
204
205                         case Types.NUMERIC:
206                         case Types.REAL:
207                         case Types.INTEGER:
208                         case Types.DOUBLE:
209                         case Types.FLOAT:
210                         case Types.DECIMAL:
211                         case Types.BIT:
212                         case Types.DATE:
213                         case Types.TIME:
214                         case Types.TIMESTAMP:
215                         case Types.BIGINT:
216                             ++numberOfValidColumns;
217                             columnTypes[column] = type;
218                             break;
219                         default:
220                             System.err.println("Unable to load column "
221                                 + column + " (" + type + ","
222                                 + metaData.getColumnClassName(column + 1) + ")");
223                             columnTypes[column] = Types.NULL;
224                             break;
225                     }
226                 }
227                 catch (SQLException JavaDoc e) {
228                     e.printStackTrace();
229                     columnTypes[column] = Types.NULL;
230                 }
231             }
232
233
234             if (numberOfValidColumns <= 1) {
235               System.err.println("Not enough valid columns where generated by query.");
236               return;
237             }
238
239             /// First column is X data
240 columnNames = new String JavaDoc[numberOfValidColumns - 1];
241             /// Get the column names and cache them.
242 currentColumn = 0;
243             for (column = 1; column < numberOfColumns; column++) {
244                 if (columnTypes[column] != Types.NULL) {
245                     columnNames[currentColumn] = metaData.getColumnLabel(column + 1);
246                     ++currentColumn;
247                 }
248             }
249
250             // Might need to add, to free memory from any previous result sets
251 if (rows != null) {
252                 for (column = 0; column < rows.size(); column++) {
253                     Vector JavaDoc row = (Vector JavaDoc) rows.get(column);
254                     row.removeAllElements();
255                 }
256                 rows.removeAllElements();
257             }
258
259             // Are we working with a time series.
260 switch (columnTypes[0]) {
261                 case Types.DATE:
262                 case Types.TIME:
263                 case Types.TIMESTAMP:
264                     isTimeSeries = true;
265                     break;
266                 default :
267                     isTimeSeries = false;
268                     break;
269             }
270
271             // Get all rows.
272 rows = new Vector JavaDoc();
273             while (resultSet.next()) {
274                 Vector JavaDoc newRow = new Vector JavaDoc();
275                 for (column = 0; column < numberOfColumns; column++) {
276                     xObject = resultSet.getObject(column + 1);
277                     switch (columnTypes[column]) {
278                         case Types.NUMERIC:
279                         case Types.REAL:
280                         case Types.INTEGER:
281                         case Types.DOUBLE:
282                         case Types.FLOAT:
283                         case Types.DECIMAL:
284                         case Types.BIGINT:
285                             newRow.addElement(xObject);
286                             break;
287
288                         case Types.DATE:
289                         case Types.TIME:
290                         case Types.TIMESTAMP:
291                             newRow.addElement(new Long JavaDoc(((java.util.Date JavaDoc) xObject).getTime()));
292                             break;
293                         case Types.NULL:
294                             break;
295                         default:
296                             System.err.println("Unknown data");
297                             columnTypes[column] = Types.NULL;
298                             break;
299                     }
300                 }
301                 rows.addElement(newRow);
302             }
303
304             /// a kludge to make everything work when no rows returned
305 if (rows.size() == 0) {
306                 Vector JavaDoc newRow = new Vector JavaDoc();
307                 for (column = 0; column < numberOfColumns; column++) {
308                     if (columnTypes[column] != Types.NULL) {
309                         newRow.addElement(new Integer JavaDoc(0));
310                     }
311                 }
312                 rows.addElement(newRow);
313             }
314
315             /// Determine max and min values.
316 if (rows.size() < 1) {
317                 maxValue = 0.0;
318                 minValue = 0.0;
319             }
320             else {
321                 Vector JavaDoc row = (Vector JavaDoc) rows.elementAt(0);
322                 double test;
323                 maxValue = ((Number JavaDoc) row.get(1)).doubleValue();
324                 minValue = maxValue;
325                 for (int rowNum = 0; rowNum < rows.size(); ++rowNum) {
326                     row = (Vector JavaDoc) rows.elementAt(rowNum);
327                     for (column = 1; column < numberOfColumns; column++) {
328                         test = ((Number JavaDoc) row.get(column)).doubleValue();
329                         if (test < minValue) {
330                             minValue = test;
331                         }
332                         if (test > maxValue) {
333                             maxValue = test;
334                         }
335                     }
336                 }
337             }
338
339             fireDatasetChanged(); // Tell the listeners a new table has arrived.
340 }
341         catch (SQLException JavaDoc ex) {
342             System.err.println(ex);
343             ex.printStackTrace();
344         }
345         finally {
346             if (resultSet != null) {
347                 try {
348                     resultSet.close();
349                 }
350                 catch (Exception JavaDoc e) {
351                 }
352             }
353             if (statement != null) {
354                 try {
355                     statement.close();
356                 }
357                 catch (Exception JavaDoc e) {
358                 }
359             }
360         }
361
362     }
363
364     /**
365      * Returns the x-value for the specified series and item. The
366      * implementation is responsible for ensuring that the x-values are
367      * presented in ascending order.
368      *
369      * @param seriesIndex The series (zero-based index).
370      * @param itemIndex The item (zero-based index).
371      *
372      * @return The x-value
373      *
374      * @see XYDataset
375      */
376     public Number JavaDoc getXValue(int seriesIndex, int itemIndex) {
377         Vector JavaDoc row = (Vector JavaDoc) rows.elementAt(itemIndex);
378         return (Number JavaDoc) row.elementAt(0);
379     }
380
381     /**
382      * Returns the y-value for the specified series and item.
383      *
384      * @param seriesIndex The series (zero-based index).
385      * @param itemIndex The item (zero-based index).
386      *
387      * @return The yValue value
388      *
389      * @see XYDataset
390      */
391     public Number JavaDoc getYValue(int seriesIndex, int itemIndex) {
392         Vector JavaDoc row = (Vector JavaDoc) rows.elementAt(itemIndex);
393         return (Number JavaDoc) row.elementAt(seriesIndex + 1);
394     }
395
396     /**
397      * Returns the number of items in the specified series.
398      *
399      * @param seriesIndex The series (zero-based index).
400      *
401      * @return The itemCount value
402      *
403      * @see XYDataset
404      */
405     public int getItemCount(int seriesIndex) {
406         return rows.size();
407     }
408
409     /**
410      * Returns the number of series in the dataset.
411      *
412      * @return The seriesCount value
413      *
414      * @see XYDataset
415      * @see Dataset
416      */
417     public int getSeriesCount() {
418         return columnNames.length;
419     }
420
421     /**
422      * Returns the name of the specified series.
423      *
424      * @param seriesIndex The series (zero-based index).
425      *
426      * @return The seriesName value
427      *
428      * @see XYDataset
429      * @see Dataset
430      */
431     public String JavaDoc getSeriesName(int seriesIndex) {
432
433         if ((seriesIndex < columnNames.length) && (columnNames[seriesIndex] != null)) {
434             return columnNames[seriesIndex];
435         }
436         else {
437             return "";
438         }
439
440     }
441
442     /**
443      * Returns the number of items that should be displayed in the legend.
444      *
445      * @return The legendItemCount value
446      */
447     public int getLegendItemCount() {
448         return getSeriesCount();
449     }
450
451     /**
452      * Returns the legend item labels.
453      *
454      * @return The legend item labels.
455      */
456     public String JavaDoc[] getLegendItemLabels() {
457         return columnNames;
458     }
459
460     /**
461      * Returns the minimum data value in the dataset's range.
462      *
463      * @return The minimum value.
464      *
465      * @see RangeInfo
466      */
467     public Number JavaDoc getMinimumRangeValue() {
468         return new Double JavaDoc(minValue);
469     }
470
471     /**
472      * Returns the maximum data value in the dataset's range.
473      *
474      * @return The maximum value.
475      *
476      * @see RangeInfo
477      */
478     public Number JavaDoc getMaximumRangeValue() {
479         return new Double JavaDoc(maxValue);
480     }
481
482     /**
483      * Close the database connection
484      */
485     public void close() {
486
487         try {
488             resultSet.close();
489         }
490         catch (Exception JavaDoc e) {
491             System.err.println("JdbcXYDataset: swallowing exception.");
492         }
493         try {
494             statement.close();
495         }
496         catch (Exception JavaDoc e) {
497             System.err.println("JdbcXYDataset: swallowing exception.");
498         }
499         try {
500             connection.close();
501         }
502         catch (Exception JavaDoc e) {
503             System.err.println("JdbcXYDataset: swallowing exception.");
504         }
505
506     }
507
508     /**
509      * Returns the range of the values in this dataset's range (y-values).
510      *
511      * @return The range.
512      */
513     public Range getValueRange() {
514         return new Range(minValue, maxValue);
515     }
516
517 }
518
Popular Tags