001    /* ===========================================================
002     * JFreeChart : a free chart library for the Java(tm) platform
003     * ===========================================================
004     *
005     * (C) Copyright 2000-2011, 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     * [Oracle and Java are registered trademarks of Oracle and/or its affiliates. 
025     * Other names may be trademarks of their respective owners.]
026     *
027     * ------------------
028     * JDBCXYDataset.java
029     * ------------------
030     * (C) Copyright 2002-2009, 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     * 19-May-2009 : Fixed FindBugs warnings, patch by Michal Wozniak (DG);
070     *
071     */
072    
073    package org.jfree.data.jdbc;
074    
075    import java.sql.Connection;
076    import java.sql.DriverManager;
077    import java.sql.ResultSet;
078    import java.sql.ResultSetMetaData;
079    import java.sql.SQLException;
080    import java.sql.Statement;
081    import java.sql.Types;
082    import java.util.ArrayList;
083    import java.util.Date;
084    
085    import org.jfree.data.Range;
086    import org.jfree.data.RangeInfo;
087    import org.jfree.data.general.Dataset;
088    import org.jfree.data.xy.AbstractXYDataset;
089    import org.jfree.data.xy.TableXYDataset;
090    import org.jfree.data.xy.XYDataset;
091    import org.jfree.util.Log;
092    
093    /**
094     * This class provides an {@link XYDataset} implementation over a database
095     * JDBC result set.  The dataset is populated via a call to executeQuery with
096     * the string sql query.  The sql query must return at least two columns.
097     * The first column will be the x-axis and remaining columns y-axis values.
098     * executeQuery can be called a number of times.
099     *
100     * The database connection is read-only and no write back facility exists.
101     */
102    public class JDBCXYDataset extends AbstractXYDataset
103            implements XYDataset, TableXYDataset, RangeInfo {
104    
105        /** The database connection. */
106        private transient Connection connection;
107    
108        /** Column names. */
109        private String[] columnNames = {};
110    
111        /** Rows. */
112        private ArrayList rows;
113    
114        /** The maximum y value of the returned result set */
115        private double maxValue = 0.0;
116    
117        /** The minimum y value of the returned result set */
118        private double minValue = 0.0;
119    
120        /** Is this dataset a timeseries ? */
121        private boolean isTimeSeries = false;
122    
123        /**
124         * Creates a new JDBCXYDataset (initially empty) with no database
125         * connection.
126         */
127        private JDBCXYDataset() {
128            this.rows = new ArrayList();
129        }
130    
131        /**
132         * Creates a new dataset (initially empty) and establishes a new database
133         * connection.
134         *
135         * @param  url  URL of the database connection.
136         * @param  driverName  the database driver class name.
137         * @param  user  the database user.
138         * @param  password  the database user's password.
139         *
140         * @throws ClassNotFoundException if the driver cannot be found.
141         * @throws SQLException if there is a problem connecting to the database.
142         */
143        public JDBCXYDataset(String url,
144                             String driverName,
145                             String user,
146                             String password)
147            throws SQLException, ClassNotFoundException {
148    
149            this();
150            Class.forName(driverName);
151            this.connection = DriverManager.getConnection(url, user, password);
152        }
153    
154        /**
155         * Creates a new dataset (initially empty) using the specified database
156         * connection.
157         *
158         * @param  con  the database connection.
159         *
160         * @throws SQLException if there is a problem connecting to the database.
161         */
162        public JDBCXYDataset(Connection con) throws SQLException {
163            this();
164            this.connection = con;
165        }
166    
167        /**
168         * Creates a new dataset using the specified database connection, and
169         * populates it using data obtained with the supplied query.
170         *
171         * @param con  the connection.
172         * @param query  the SQL query.
173         *
174         * @throws SQLException if there is a problem executing the query.
175         */
176        public JDBCXYDataset(Connection con, String query) throws SQLException {
177            this(con);
178            executeQuery(query);
179        }
180    
181        /**
182         * Returns <code>true</code> if the dataset represents time series data,
183         * and <code>false</code> otherwise.
184         *
185         * @return A boolean.
186         */
187        public boolean isTimeSeries() {
188            return this.isTimeSeries;
189        }
190    
191        /**
192         * Sets a flag that indicates whether or not the data represents a time
193         * series.
194         *
195         * @param timeSeries  the new value of the flag.
196         */
197        public void setTimeSeries(boolean timeSeries) {
198            this.isTimeSeries = timeSeries;
199        }
200    
201        /**
202         * ExecuteQuery will attempt execute the query passed to it against the
203         * existing database connection.  If no connection exists then no action
204         * is taken.
205         *
206         * The results from the query are extracted and cached locally, thus
207         * applying an upper limit on how many rows can be retrieved successfully.
208         *
209         * @param  query  the query to be executed.
210         *
211         * @throws SQLException if there is a problem executing the query.
212         */
213        public void executeQuery(String query) throws SQLException {
214            executeQuery(this.connection, query);
215        }
216    
217        /**
218         * ExecuteQuery will attempt execute the query passed to it against the
219         * provided database connection.  If connection is null then no action is
220         * taken.
221         *
222         * The results from the query are extracted and cached locally, thus
223         * applying an upper limit on how many rows can be retrieved successfully.
224         *
225         * @param  query  the query to be executed.
226         * @param  con  the connection the query is to be executed against.
227         *
228         * @throws SQLException if there is a problem executing the query.
229         */
230        public void executeQuery(Connection con, String query)
231            throws SQLException {
232    
233            if (con == null) {
234                throw new SQLException(
235                    "There is no database to execute the query."
236                );
237            }
238    
239            ResultSet resultSet = null;
240            Statement statement = null;
241            try {
242                statement = con.createStatement();
243                resultSet = statement.executeQuery(query);
244                ResultSetMetaData metaData = resultSet.getMetaData();
245    
246                int numberOfColumns = metaData.getColumnCount();
247                int numberOfValidColumns = 0;
248                int [] columnTypes = new int[numberOfColumns];
249                for (int column = 0; column < numberOfColumns; column++) {
250                    try {
251                        int type = metaData.getColumnType(column + 1);
252                        switch (type) {
253    
254                            case Types.NUMERIC:
255                            case Types.REAL:
256                            case Types.INTEGER:
257                            case Types.DOUBLE:
258                            case Types.FLOAT:
259                            case Types.DECIMAL:
260                            case Types.BIT:
261                            case Types.DATE:
262                            case Types.TIME:
263                            case Types.TIMESTAMP:
264                            case Types.BIGINT:
265                            case Types.SMALLINT:
266                                ++numberOfValidColumns;
267                                columnTypes[column] = type;
268                                break;
269                            default:
270                                Log.warn(
271                                    "Unable to load column "
272                                    + column + " (" + type + ","
273                                    + metaData.getColumnClassName(column + 1)
274                                    + ")"
275                                );
276                                columnTypes[column] = Types.NULL;
277                                break;
278                        }
279                    }
280                    catch (SQLException e) {
281                        columnTypes[column] = Types.NULL;
282                        throw e;
283                    }
284                }
285    
286    
287                if (numberOfValidColumns <= 1) {
288                    throw new SQLException(
289                        "Not enough valid columns where generated by query."
290                    );
291                }
292    
293                /// First column is X data
294                this.columnNames = new String[numberOfValidColumns - 1];
295                /// Get the column names and cache them.
296                int currentColumn = 0;
297                for (int column = 1; column < numberOfColumns; column++) {
298                    if (columnTypes[column] != Types.NULL) {
299                        this.columnNames[currentColumn]
300                            = metaData.getColumnLabel(column + 1);
301                        ++currentColumn;
302                    }
303                }
304    
305                // Might need to add, to free memory from any previous result sets
306                if (this.rows != null) {
307                    for (int column = 0; column < this.rows.size(); column++) {
308                        ArrayList row = (ArrayList) this.rows.get(column);
309                        row.clear();
310                    }
311                    this.rows.clear();
312                }
313    
314                // Are we working with a time series.
315                switch (columnTypes[0]) {
316                    case Types.DATE:
317                    case Types.TIME:
318                    case Types.TIMESTAMP:
319                        this.isTimeSeries = true;
320                        break;
321                    default :
322                        this.isTimeSeries = false;
323                        break;
324                }
325    
326                // Get all rows.
327                // rows = new ArrayList();
328                while (resultSet.next()) {
329                    ArrayList newRow = new ArrayList();
330                    for (int column = 0; column < numberOfColumns; column++) {
331                        Object xObject = resultSet.getObject(column + 1);
332                        switch (columnTypes[column]) {
333                            case Types.NUMERIC:
334                            case Types.REAL:
335                            case Types.INTEGER:
336                            case Types.DOUBLE:
337                            case Types.FLOAT:
338                            case Types.DECIMAL:
339                            case Types.BIGINT:
340                            case Types.SMALLINT:
341                                newRow.add(xObject);
342                                break;
343    
344                            case Types.DATE:
345                            case Types.TIME:
346                            case Types.TIMESTAMP:
347                                newRow.add(new Long(((Date) xObject).getTime()));
348                                break;
349                            case Types.NULL:
350                                break;
351                            default:
352                                System.err.println("Unknown data");
353                                columnTypes[column] = Types.NULL;
354                                break;
355                        }
356                    }
357                    this.rows.add(newRow);
358                }
359    
360                /// a kludge to make everything work when no rows returned
361                if (this.rows.size() == 0) {
362                    ArrayList newRow = new ArrayList();
363                    for (int column = 0; column < numberOfColumns; column++) {
364                        if (columnTypes[column] != Types.NULL) {
365                            newRow.add(new Integer(0));
366                        }
367                    }
368                    this.rows.add(newRow);
369                }
370    
371                /// Determine max and min values.
372                if (this.rows.size() < 1) {
373                    this.maxValue = 0.0;
374                    this.minValue = 0.0;
375                }
376                else {
377                    this.maxValue = Double.NEGATIVE_INFINITY;
378                    this.minValue = Double.POSITIVE_INFINITY;
379                    for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
380                        ArrayList 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    }