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
072package org.jfree.data.jdbc;
073
074import java.sql.Connection;
075import java.sql.DriverManager;
076import java.sql.ResultSet;
077import java.sql.ResultSetMetaData;
078import java.sql.SQLException;
079import java.sql.Statement;
080import java.sql.Types;
081import java.util.ArrayList;
082import java.util.Date;
083
084import org.jfree.data.Range;
085import org.jfree.data.RangeInfo;
086import org.jfree.data.general.Dataset;
087import org.jfree.data.xy.AbstractXYDataset;
088import org.jfree.data.xy.TableXYDataset;
089import org.jfree.data.xy.XYDataset;
090import 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 */
101public 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}