001/* ===========================================================
002 * JFreeChart : a free chart library for the Java(tm) platform
003 * ===========================================================
004 *
005 * (C) Copyright 2000-present, by David Gilbert 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-present, by Bryan Scott and Contributors.
031 *
032 * Original Author:  Bryan Scott;
033 * Contributor(s):   David Gilbert;
034 *                   Eric Alexander;
035 *
036 */
037
038package org.jfree.data.jdbc;
039
040import java.sql.Connection;
041import java.sql.DriverManager;
042import java.sql.ResultSet;
043import java.sql.ResultSetMetaData;
044import java.sql.SQLException;
045import java.sql.Statement;
046import java.sql.Types;
047import java.util.ArrayList;
048import java.util.Date;
049
050import org.jfree.data.Range;
051import org.jfree.data.RangeInfo;
052import org.jfree.data.general.Dataset;
053import org.jfree.data.xy.AbstractXYDataset;
054import org.jfree.data.xy.TableXYDataset;
055import org.jfree.data.xy.XYDataset;
056
057/**
058 * This class provides an {@link XYDataset} implementation over a database
059 * JDBC result set.  The dataset is populated via a call to executeQuery with
060 * the string sql query.  The sql query must return at least two columns.
061 * The first column will be the x-axis and remaining columns y-axis values.
062 * executeQuery can be called a number of times.
063 *
064 * The database connection is read-only and no write back facility exists.
065 */
066public class JDBCXYDataset extends AbstractXYDataset
067        implements XYDataset, TableXYDataset, RangeInfo {
068
069    /** The database connection. */
070    private transient Connection connection;
071
072    /** Column names. */
073    private String[] columnNames = {};
074
075    /** Rows. */
076    private ArrayList rows;
077
078    /** The maximum y value of the returned result set */
079    private double maxValue = 0.0;
080
081    /** The minimum y value of the returned result set */
082    private double minValue = 0.0;
083
084    /** Is this dataset a timeseries ? */
085    private boolean isTimeSeries = false;
086
087    /**
088     * Creates a new JDBCXYDataset (initially empty) with no database
089     * connection.
090     */
091    private JDBCXYDataset() {
092        this.rows = new ArrayList();
093    }
094
095    /**
096     * Creates a new dataset (initially empty) and establishes a new database
097     * connection.
098     *
099     * @param  url  URL of the database connection.
100     * @param  driverName  the database driver class name.
101     * @param  user  the database user.
102     * @param  password  the database user's password.
103     *
104     * @throws ClassNotFoundException if the driver cannot be found.
105     * @throws SQLException if there is a problem connecting to the database.
106     */
107    public JDBCXYDataset(String url,
108                         String driverName,
109                         String user,
110                         String password)
111        throws SQLException, ClassNotFoundException {
112
113        this();
114        Class.forName(driverName);
115        this.connection = DriverManager.getConnection(url, user, password);
116    }
117
118    /**
119     * Creates a new dataset (initially empty) using the specified database
120     * connection.
121     *
122     * @param  con  the database connection.
123     *
124     * @throws SQLException if there is a problem connecting to the database.
125     */
126    public JDBCXYDataset(Connection con) throws SQLException {
127        this();
128        this.connection = con;
129    }
130
131    /**
132     * Creates a new dataset using the specified database connection, and
133     * populates it using data obtained with the supplied query.
134     *
135     * @param con  the connection.
136     * @param query  the SQL query.
137     *
138     * @throws SQLException if there is a problem executing the query.
139     */
140    public JDBCXYDataset(Connection con, String query) throws SQLException {
141        this(con);
142        executeQuery(query);
143    }
144
145    /**
146     * Returns {@code true} if the dataset represents time series data,
147     * and {@code false} otherwise.
148     *
149     * @return A boolean.
150     */
151    public boolean isTimeSeries() {
152        return this.isTimeSeries;
153    }
154
155    /**
156     * Sets a flag that indicates whether or not the data represents a time
157     * series.
158     *
159     * @param timeSeries  the new value of the flag.
160     */
161    public void setTimeSeries(boolean timeSeries) {
162        this.isTimeSeries = timeSeries;
163    }
164
165    /**
166     * ExecuteQuery will attempt execute the query passed to it against the
167     * existing database connection.  If no connection exists then no action
168     * is taken.
169     *
170     * The results from the query are extracted and cached locally, thus
171     * applying an upper limit on how many rows can be retrieved successfully.
172     *
173     * @param  query  the query to be executed.
174     *
175     * @throws SQLException if there is a problem executing the query.
176     */
177    public void executeQuery(String query) throws SQLException {
178        executeQuery(this.connection, query);
179    }
180
181    /**
182     * ExecuteQuery will attempt execute the query passed to it against the
183     * provided database connection.  If connection is null then no action is
184     * taken.
185     *
186     * The results from the query are extracted and cached locally, thus
187     * applying an upper limit on how many rows can be retrieved successfully.
188     *
189     * @param  query  the query to be executed.
190     * @param  con  the connection the query is to be executed against.
191     *
192     * @throws SQLException if there is a problem executing the query.
193     */
194    public void executeQuery(Connection con, String query)
195        throws SQLException {
196
197        if (con == null) {
198            throw new SQLException(
199                "There is no database to execute the query."
200            );
201        }
202
203        ResultSet resultSet = null;
204        Statement statement = null;
205        try {
206            statement = con.createStatement();
207            resultSet = statement.executeQuery(query);
208            ResultSetMetaData metaData = resultSet.getMetaData();
209
210            int numberOfColumns = metaData.getColumnCount();
211            int numberOfValidColumns = 0;
212            int [] columnTypes = new int[numberOfColumns];
213            for (int column = 0; column < numberOfColumns; column++) {
214                try {
215                    int type = metaData.getColumnType(column + 1);
216                    switch (type) {
217
218                        case Types.NUMERIC:
219                        case Types.REAL:
220                        case Types.INTEGER:
221                        case Types.DOUBLE:
222                        case Types.FLOAT:
223                        case Types.DECIMAL:
224                        case Types.BIT:
225                        case Types.DATE:
226                        case Types.TIME:
227                        case Types.TIMESTAMP:
228                        case Types.BIGINT:
229                        case Types.SMALLINT:
230                            ++numberOfValidColumns;
231                            columnTypes[column] = type;
232                            break;
233                        default:
234                            columnTypes[column] = Types.NULL;
235                            break;
236                    }
237                }
238                catch (SQLException e) {
239                    columnTypes[column] = Types.NULL;
240                    throw e;
241                }
242            }
243
244
245            if (numberOfValidColumns <= 1) {
246                throw new SQLException(
247                    "Not enough valid columns where generated by query."
248                );
249            }
250
251            /// First column is X data
252            this.columnNames = new String[numberOfValidColumns - 1];
253            /// Get the column names and cache them.
254            int currentColumn = 0;
255            for (int column = 1; column < numberOfColumns; column++) {
256                if (columnTypes[column] != Types.NULL) {
257                    this.columnNames[currentColumn]
258                        = metaData.getColumnLabel(column + 1);
259                    ++currentColumn;
260                }
261            }
262
263            // Might need to add, to free memory from any previous result sets
264            if (this.rows != null) {
265                for (int column = 0; column < this.rows.size(); column++) {
266                    ArrayList row = (ArrayList) this.rows.get(column);
267                    row.clear();
268                }
269                this.rows.clear();
270            }
271
272            // Are we working with a time series.
273            switch (columnTypes[0]) {
274                case Types.DATE:
275                case Types.TIME:
276                case Types.TIMESTAMP:
277                    this.isTimeSeries = true;
278                    break;
279                default :
280                    this.isTimeSeries = false;
281                    break;
282            }
283
284            // Get all rows.
285            // rows = new ArrayList();
286            while (resultSet.next()) {
287                ArrayList newRow = new ArrayList();
288                for (int column = 0; column < numberOfColumns; column++) {
289                    Object xObject = resultSet.getObject(column + 1);
290                    switch (columnTypes[column]) {
291                        case Types.NUMERIC:
292                        case Types.REAL:
293                        case Types.INTEGER:
294                        case Types.DOUBLE:
295                        case Types.FLOAT:
296                        case Types.DECIMAL:
297                        case Types.BIGINT:
298                        case Types.SMALLINT:
299                            newRow.add(xObject);
300                            break;
301
302                        case Types.DATE:
303                        case Types.TIME:
304                        case Types.TIMESTAMP:
305                            newRow.add(((Date) xObject).getTime());
306                            break;
307                        case Types.NULL:
308                            break;
309                        default:
310                            System.err.println("Unknown data");
311                            columnTypes[column] = Types.NULL;
312                            break;
313                    }
314                }
315                this.rows.add(newRow);
316            }
317
318            /// a kludge to make everything work when no rows returned
319            if (this.rows.isEmpty()) {
320                ArrayList newRow = new ArrayList();
321                for (int column = 0; column < numberOfColumns; column++) {
322                    if (columnTypes[column] != Types.NULL) {
323                        newRow.add(0);
324                    }
325                }
326                this.rows.add(newRow);
327            }
328
329            /// Determine max and min values.
330            if (this.rows.size() < 1) {
331                this.maxValue = 0.0;
332                this.minValue = 0.0;
333            }
334            else {
335                this.maxValue = Double.NEGATIVE_INFINITY;
336                this.minValue = Double.POSITIVE_INFINITY;
337                for (int rowNum = 0; rowNum < this.rows.size(); ++rowNum) {
338                    ArrayList row = (ArrayList) this.rows.get(rowNum);
339                    for (int column = 1; column < numberOfColumns; column++) {
340                        Object testValue = row.get(column);
341                        if (testValue != null) {
342                            double test = ((Number) testValue).doubleValue();
343
344                            if (test < this.minValue) {
345                                this.minValue = test;
346                            }
347                            if (test > this.maxValue) {
348                                this.maxValue = test;
349                            }
350                        }
351                    }
352                }
353            }
354
355            fireDatasetChanged(); // Tell the listeners a new table has arrived.
356        }
357        finally {
358            if (resultSet != null) {
359                try {
360                    resultSet.close();
361                }
362                catch (Exception e) {
363                    // TODO: is this a good idea?
364                }
365            }
366            if (statement != null) {
367                try {
368                    statement.close();
369                }
370                catch (Exception e) {
371                    // TODO: is this a good idea?
372                }
373            }
374        }
375
376    }
377
378    /**
379     * Returns the x-value for the specified series and item.  The
380     * implementation is responsible for ensuring that the x-values are
381     * presented in ascending order.
382     *
383     * @param  seriesIndex  the series (zero-based index).
384     * @param  itemIndex  the item (zero-based index).
385     *
386     * @return The x-value
387     *
388     * @see XYDataset
389     */
390    @Override
391    public Number getX(int seriesIndex, int itemIndex) {
392        ArrayList row = (ArrayList) this.rows.get(itemIndex);
393        return (Number) row.get(0);
394    }
395
396    /**
397     * Returns the y-value for the specified series and item.
398     *
399     * @param  seriesIndex  the series (zero-based index).
400     * @param  itemIndex  the item (zero-based index).
401     *
402     * @return The yValue value
403     *
404     * @see XYDataset
405     */
406    @Override
407    public Number getY(int seriesIndex, int itemIndex) {
408        ArrayList row = (ArrayList) this.rows.get(itemIndex);
409        return (Number) row.get(seriesIndex + 1);
410    }
411
412    /**
413     * Returns the number of items in the specified series.
414     *
415     * @param  seriesIndex  the series (zero-based index).
416     *
417     * @return The itemCount value
418     *
419     * @see XYDataset
420     */
421    @Override
422    public int getItemCount(int seriesIndex) {
423        return this.rows.size();
424    }
425
426    /**
427     * Returns the number of items in all series.  This method is defined by
428     * the {@link TableXYDataset} interface.
429     *
430     * @return The item count.
431     */
432    @Override
433    public int getItemCount() {
434        return getItemCount(0);
435    }
436
437    /**
438     * Returns the number of series in the dataset.
439     *
440     * @return The seriesCount value
441     *
442     * @see XYDataset
443     * @see Dataset
444     */
445    @Override
446    public int getSeriesCount() {
447        return this.columnNames.length;
448    }
449
450    /**
451     * Returns the key for the specified series.
452     *
453     * @param seriesIndex  the series (zero-based index).
454     *
455     * @return The seriesName value
456     *
457     * @see XYDataset
458     * @see Dataset
459     */
460    @Override
461    public Comparable getSeriesKey(int seriesIndex) {
462
463        if ((seriesIndex < this.columnNames.length)
464                && (this.columnNames[seriesIndex] != null)) {
465            return this.columnNames[seriesIndex];
466        }
467        else {
468            return "";
469        }
470
471    }
472
473    /**
474     * Close the database connection
475     */
476    public void close() {
477
478        try {
479            this.connection.close();
480        }
481        catch (Exception e) {
482            System.err.println("JdbcXYDataset: swallowing exception.");
483        }
484
485    }
486
487    /**
488     * Returns the minimum y-value in the dataset.
489     *
490     * @param includeInterval  a flag that determines whether or not the
491     *                         y-interval is taken into account.
492     *
493     * @return The minimum value.
494     */
495    @Override
496    public double getRangeLowerBound(boolean includeInterval) {
497        return this.minValue;
498    }
499
500    /**
501     * Returns the maximum y-value in the dataset.
502     *
503     * @param includeInterval  a flag that determines whether or not the
504     *                         y-interval is taken into account.
505     *
506     * @return The maximum value.
507     */
508    @Override
509    public double getRangeUpperBound(boolean includeInterval) {
510        return this.maxValue;
511    }
512
513    /**
514     * Returns the range of the values in this dataset's range.
515     *
516     * @param includeInterval  a flag that determines whether or not the
517     *                         y-interval is taken into account.
518     *
519     * @return The range.
520     */
521    @Override
522    public Range getRangeBounds(boolean includeInterval) {
523        return new Range(this.minValue, this.maxValue);
524    }
525
526}