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     * JDBCCategoryDataset.java
029     * ------------------------
030     * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
031     *
032     * Original Author:  Bryan Scott; Andy;
033     * Contributor(s):   David Gilbert (for Object Refinery Limited);
034     *                   Thomas Morgner;
035     *
036     * Changes
037     * -------
038     * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from 
039     *               Andy;
040     * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
041     * 03-Sep-2002 : Added fix for bug 591385 (DG);
042     * 18-Sep-2002 : Updated to support BIGINT (BS);
043     * 16-Oct-2002 : Added fix for bug 586667 (DG);
044     * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
045     * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
046     * 30-Jun-2003 : CVS Write test (BS);
047     * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) 
048     *               method (BS);
049     * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily 
050     *               transposed if required (DG);
051     * 10-Sep-2003 : Added support for additional JDBC types (DG);
052     * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
053     *               following being highlighted on online forum (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 (TM);
057     * 04-Dec-2003 : Added missing Javadocs (DG);
058     * ------------- JFREECHART 1.0.0 ---------------------------------------------
059     * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if 
060     *               executeQuery() is called more than once (DG);
061     * 
062     */
063    
064    package org.jfree.data.jdbc;
065    
066    import java.sql.Connection;
067    import java.sql.Date;
068    import java.sql.DriverManager;
069    import java.sql.ResultSet;
070    import java.sql.ResultSetMetaData;
071    import java.sql.SQLException;
072    import java.sql.Statement;
073    import java.sql.Types;
074    
075    import org.jfree.data.category.CategoryDataset;
076    import org.jfree.data.category.DefaultCategoryDataset;
077    
078    /**
079     * A {@link CategoryDataset} implementation over a database JDBC result set.
080     * The dataset is populated via a call to {@link #executeQuery(String)} with 
081     * the string SQL query.  The SQL query must return at least two columns.  The 
082     * first column will be the category name and remaining columns values (each
083     * column represents a series).  Subsequent calls to 
084     * {@link #executeQuery(String)} will refresh the dataset.
085     * <p>
086     * The database connection is read-only and no write back facility exists.
087     * <p>
088     * NOTE: Many people have found this class too restrictive in general use.  
089     * For the greatest flexibility, please consider writing your own code to read 
090     * data from a <code>ResultSet</code> and populate a 
091     * {@link DefaultCategoryDataset} directly.
092     */
093    public class JDBCCategoryDataset extends DefaultCategoryDataset {
094    
095        /** The database connection. */
096        private transient Connection connection;
097    
098        /**
099         * A flag the controls whether or not the table is transposed.  The default 
100         * is 'true' because this provides the behaviour described in the 
101         * documentation.
102         */
103        private boolean transpose = true;
104    
105    
106        /**
107         * Creates a new dataset with a database connection.
108         *
109         * @param  url  the URL of the database connection.
110         * @param  driverName  the database driver class name.
111         * @param  user  the database user.
112         * @param  passwd  the database user's password.
113         * 
114         * @throws ClassNotFoundException if the driver cannot be found.
115         * @throws SQLException if there is an error obtaining a connection to the 
116         *                      database.
117         */
118        public JDBCCategoryDataset(String url,
119                                   String driverName,
120                                   String user,
121                                   String passwd)
122            throws ClassNotFoundException, SQLException {
123    
124            Class.forName(driverName);
125            this.connection = DriverManager.getConnection(url, user, passwd);
126        }
127    
128        /**
129         * Create a new dataset with the given database connection.
130         *
131         * @param connection  the database connection.
132         */
133        public JDBCCategoryDataset(Connection connection) {
134            if (connection == null) {
135                throw new NullPointerException("A connection must be supplied.");
136            }
137            this.connection = connection;
138        }
139    
140        /**
141         * Creates a new dataset with the given database connection, and executes 
142         * the supplied query to populate the dataset.
143         *
144         * @param connection  the connection.
145         * @param query  the query.
146         * 
147         * @throws SQLException if there is a problem executing the query.
148         */
149        public JDBCCategoryDataset(Connection connection, String query) 
150            throws SQLException {
151            this(connection);
152            executeQuery(query);
153        }
154    
155        /**
156         * Returns a flag that controls whether or not the table values are 
157         * transposed when added to the dataset.
158         *
159         * @return A boolean.
160         */
161        public boolean getTranspose() {
162            return this.transpose;
163        }
164    
165        /**
166         * Sets a flag that controls whether or not the table values are transposed
167         * when added to the dataset.
168         *
169         * @param transpose  the flag.
170         */
171        public void setTranspose(boolean transpose) {
172            this.transpose = transpose;
173        }
174    
175        /**
176         * Populates the dataset by executing the supplied query against the 
177         * existing database connection.  If no connection exists then no action 
178         * is taken.
179         * <p>
180         * The results from the query are extracted and cached locally, thus 
181         * applying an upper limit on how many rows can be retrieved successfully.
182         *
183         * @param query  the query.
184         * 
185         * @throws SQLException if there is a problem executing the query.
186         */
187        public void executeQuery(String query) throws SQLException {
188            executeQuery(this.connection, query);
189        }
190    
191        /**
192         * Populates the dataset by executing the supplied query against the 
193         * existing database connection.  If no connection exists then no action 
194         * is taken.
195         * <p>
196         * The results from the query are extracted and cached locally, thus 
197         * applying an upper limit on how many rows can be retrieved successfully.
198         *
199         * @param con  the connection.
200         * @param query  the query.
201         * 
202         * @throws SQLException if there is a problem executing the query.
203         */
204        public void executeQuery(Connection con, String query) throws SQLException {
205    
206            Statement statement = null;
207            ResultSet resultSet = null;
208            try {
209                statement = con.createStatement();
210                resultSet = statement.executeQuery(query);
211                ResultSetMetaData metaData = resultSet.getMetaData();
212    
213                int columnCount = metaData.getColumnCount();
214    
215                if (columnCount < 2) {
216                    throw new SQLException(
217                        "JDBCCategoryDataset.executeQuery() : insufficient columns "
218                        + "returned from the database.");
219                }
220    
221                // Remove any previous old data
222                int i = getRowCount();
223                while (--i >= 0) {
224                    removeRow(i);
225                }
226    
227                while (resultSet.next()) {
228                    // first column contains the row key...
229                    Comparable rowKey = resultSet.getString(1);
230                    for (int column = 2; column <= columnCount; column++) {
231    
232                        Comparable columnKey = metaData.getColumnName(column);
233                        int columnType = metaData.getColumnType(column);
234    
235                        switch (columnType) {
236                            case Types.TINYINT:
237                            case Types.SMALLINT:
238                            case Types.INTEGER:
239                            case Types.BIGINT:
240                            case Types.FLOAT:
241                            case Types.DOUBLE:
242                            case Types.DECIMAL:
243                            case Types.NUMERIC:
244                            case Types.REAL: {
245                                Number value = (Number) resultSet.getObject(column);
246                                if (this.transpose) {
247                                    setValue(value, columnKey, rowKey);
248                                }
249                                else {
250                                    setValue(value, rowKey, columnKey);
251                                }
252                                break;
253                            }
254                            case Types.DATE:
255                            case Types.TIME:
256                            case Types.TIMESTAMP: {
257                                Date date = (Date) resultSet.getObject(column);
258                                Number value = new Long(date.getTime());
259                                if (this.transpose) {
260                                    setValue(value, columnKey, rowKey);
261                                }
262                                else {
263                                    setValue(value, rowKey, columnKey);
264                                }
265                                break;
266                            }
267                            case Types.CHAR:
268                            case Types.VARCHAR:
269                            case Types.LONGVARCHAR: {
270                                String string 
271                                    = (String) resultSet.getObject(column);
272                                try {
273                                    Number value = Double.valueOf(string);
274                                    if (this.transpose) {
275                                        setValue(value, columnKey, rowKey);
276                                    }
277                                    else {
278                                        setValue(value, rowKey, columnKey);
279                                    }
280                                }
281                                catch (NumberFormatException e) {
282                                    // suppress (value defaults to null)
283                                }
284                                break;
285                            }
286                            default:
287                                // not a value, can't use it (defaults to null)
288                                break;
289                        }
290                    }
291                }
292    
293                fireDatasetChanged();
294            }
295            finally {
296                if (resultSet != null) {
297                    try {
298                        resultSet.close();
299                    }
300                    catch (Exception e) {
301                        // report this?
302                    }
303                }
304                if (statement != null) {
305                    try {
306                        statement.close();
307                    }
308                    catch (Exception e) {
309                        // report this?
310                    }
311                }
312            }
313        }
314    
315    }