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 implements
102: 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, String driverName, String user,
143: String password) throws SQLException,
144: ClassNotFoundException {
145:
146: this ();
147: Class.forName(driverName);
148: this .connection = DriverManager.getConnection(url, user,
149: password);
150: }
151:
152: /**
153: * Creates a new dataset (initially empty) using the specified database
154: * connection.
155: *
156: * @param con the database connection.
157: *
158: * @throws SQLException if there is a problem connecting to the database.
159: */
160: public JDBCXYDataset(Connection con) throws SQLException {
161: this ();
162: this .connection = con;
163: }
164:
165: /**
166: * Creates a new dataset using the specified database connection, and
167: * populates it using data obtained with the supplied query.
168: *
169: * @param con the connection.
170: * @param query the SQL query.
171: *
172: * @throws SQLException if there is a problem executing the query.
173: */
174: public JDBCXYDataset(Connection con, String query)
175: 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: ResultSet resultSet = null;
238: Statement statement = null;
239: try {
240: statement = con.createStatement();
241: resultSet = statement.executeQuery(query);
242: ResultSetMetaData metaData = resultSet.getMetaData();
243:
244: int numberOfColumns = metaData.getColumnCount();
245: int numberOfValidColumns = 0;
246: int[] columnTypes = new int[numberOfColumns];
247: for (int column = 0; column < numberOfColumns; column++) {
248: try {
249: int type = metaData.getColumnType(column + 1);
250: switch (type) {
251:
252: case Types.NUMERIC:
253: case Types.REAL:
254: case Types.INTEGER:
255: case Types.DOUBLE:
256: case Types.FLOAT:
257: case Types.DECIMAL:
258: case Types.BIT:
259: case Types.DATE:
260: case Types.TIME:
261: case Types.TIMESTAMP:
262: case Types.BIGINT:
263: case Types.SMALLINT:
264: ++numberOfValidColumns;
265: columnTypes[column] = type;
266: break;
267: default:
268: Log.warn("Unable to load column "
269: + column
270: + " ("
271: + type
272: + ","
273: + metaData
274: .getColumnClassName(column + 1)
275: + ")");
276: columnTypes[column] = Types.NULL;
277: break;
278: }
279: } catch (SQLException e) {
280: columnTypes[column] = Types.NULL;
281: throw e;
282: }
283: }
284:
285: if (numberOfValidColumns <= 1) {
286: throw new SQLException(
287: "Not enough valid columns where generated by query.");
288: }
289:
290: /// First column is X data
291: this .columnNames = new String[numberOfValidColumns - 1];
292: /// Get the column names and cache them.
293: int currentColumn = 0;
294: for (int column = 1; column < numberOfColumns; column++) {
295: if (columnTypes[column] != Types.NULL) {
296: this .columnNames[currentColumn] = metaData
297: .getColumnLabel(column + 1);
298: ++currentColumn;
299: }
300: }
301:
302: // Might need to add, to free memory from any previous result sets
303: if (this .rows != null) {
304: for (int column = 0; column < this .rows.size(); column++) {
305: ArrayList row = (ArrayList) this .rows.get(column);
306: row.clear();
307: }
308: this .rows.clear();
309: }
310:
311: // Are we working with a time series.
312: switch (columnTypes[0]) {
313: case Types.DATE:
314: case Types.TIME:
315: case Types.TIMESTAMP:
316: this .isTimeSeries = true;
317: break;
318: default:
319: this .isTimeSeries = false;
320: break;
321: }
322:
323: // Get all rows.
324: // rows = new ArrayList();
325: while (resultSet.next()) {
326: ArrayList newRow = new ArrayList();
327: for (int column = 0; column < numberOfColumns; column++) {
328: Object xObject = resultSet.getObject(column + 1);
329: switch (columnTypes[column]) {
330: case Types.NUMERIC:
331: case Types.REAL:
332: case Types.INTEGER:
333: case Types.DOUBLE:
334: case Types.FLOAT:
335: case Types.DECIMAL:
336: case Types.BIGINT:
337: case Types.SMALLINT:
338: newRow.add(xObject);
339: break;
340:
341: case Types.DATE:
342: case Types.TIME:
343: case Types.TIMESTAMP:
344: newRow
345: .add(new Long(((Date) xObject)
346: .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: } else {
375: ArrayList row = (ArrayList) this .rows.get(0);
376: this .maxValue = Double.NEGATIVE_INFINITY;
377: this .minValue = Double.POSITIVE_INFINITY;
378: for (int rowNum = 0; rowNum < this .rows.size(); ++rowNum) {
379: row = (ArrayList) this .rows.get(rowNum);
380: for (int column = 1; column < numberOfColumns; column++) {
381: Object testValue = row.get(column);
382: if (testValue != null) {
383: double test = ((Number) testValue)
384: .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: } finally {
399: if (resultSet != null) {
400: try {
401: resultSet.close();
402: } catch (Exception e) {
403: // TODO: is this a good idea?
404: }
405: }
406: if (statement != null) {
407: try {
408: statement.close();
409: } catch (Exception e) {
410: // TODO: is this a good idea?
411: }
412: }
413: }
414:
415: }
416:
417: /**
418: * Returns the x-value for the specified series and item. The
419: * implementation is responsible for ensuring that the x-values are
420: * presented in ascending order.
421: *
422: * @param seriesIndex the series (zero-based index).
423: * @param itemIndex the item (zero-based index).
424: *
425: * @return The x-value
426: *
427: * @see XYDataset
428: */
429: public Number getX(int seriesIndex, int itemIndex) {
430: ArrayList row = (ArrayList) this .rows.get(itemIndex);
431: return (Number) row.get(0);
432: }
433:
434: /**
435: * Returns the y-value for the specified series and item.
436: *
437: * @param seriesIndex the series (zero-based index).
438: * @param itemIndex the item (zero-based index).
439: *
440: * @return The yValue value
441: *
442: * @see XYDataset
443: */
444: public Number getY(int seriesIndex, int itemIndex) {
445: ArrayList row = (ArrayList) this .rows.get(itemIndex);
446: return (Number) row.get(seriesIndex + 1);
447: }
448:
449: /**
450: * Returns the number of items in the specified series.
451: *
452: * @param seriesIndex the series (zero-based index).
453: *
454: * @return The itemCount value
455: *
456: * @see XYDataset
457: */
458: public int getItemCount(int seriesIndex) {
459: return this .rows.size();
460: }
461:
462: /**
463: * Returns the number of items in all series. This method is defined by
464: * the {@link TableXYDataset} interface.
465: *
466: * @return The item count.
467: */
468: public int getItemCount() {
469: return getItemCount(0);
470: }
471:
472: /**
473: * Returns the number of series in the dataset.
474: *
475: * @return The seriesCount value
476: *
477: * @see XYDataset
478: * @see Dataset
479: */
480: public int getSeriesCount() {
481: return this .columnNames.length;
482: }
483:
484: /**
485: * Returns the key for the specified series.
486: *
487: * @param seriesIndex the series (zero-based index).
488: *
489: * @return The seriesName value
490: *
491: * @see XYDataset
492: * @see Dataset
493: */
494: public Comparable getSeriesKey(int seriesIndex) {
495:
496: if ((seriesIndex < this .columnNames.length)
497: && (this .columnNames[seriesIndex] != null)) {
498: return this .columnNames[seriesIndex];
499: } else {
500: return "";
501: }
502:
503: }
504:
505: /**
506: * Returns the number of items that should be displayed in the legend.
507: *
508: * @return The legendItemCount value
509: *
510: * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
511: * the API by mistake and is officially deprecated from version 1.0.3
512: * onwards).
513: */
514: public int getLegendItemCount() {
515: return getSeriesCount();
516: }
517:
518: /**
519: * Returns the legend item labels.
520: *
521: * @return The legend item labels.
522: *
523: * @deprecated This method is not used in JFreeChart 1.0.x (it was left in
524: * the API by mistake and is officially deprecated from version 1.0.3
525: * onwards).
526: */
527: public String[] getLegendItemLabels() {
528: return this .columnNames;
529: }
530:
531: /**
532: * Close the database connection
533: */
534: public void close() {
535:
536: try {
537: this .connection.close();
538: } catch (Exception e) {
539: System.err.println("JdbcXYDataset: swallowing exception.");
540: }
541:
542: }
543:
544: /**
545: * Returns the minimum y-value in the dataset.
546: *
547: * @param includeInterval a flag that determines whether or not the
548: * y-interval is taken into account.
549: *
550: * @return The minimum value.
551: */
552: public double getRangeLowerBound(boolean includeInterval) {
553: return this .minValue;
554: }
555:
556: /**
557: * Returns the maximum y-value in the dataset.
558: *
559: * @param includeInterval a flag that determines whether or not the
560: * y-interval is taken into account.
561: *
562: * @return The maximum value.
563: */
564: public double getRangeUpperBound(boolean includeInterval) {
565: return this .maxValue;
566: }
567:
568: /**
569: * Returns the range of the values in this dataset's range.
570: *
571: * @param includeInterval a flag that determines whether or not the
572: * y-interval is taken into account.
573: *
574: * @return The range.
575: */
576: public Range getRangeBounds(boolean includeInterval) {
577: return new Range(this.minValue, this.maxValue);
578: }
579:
580: }
|