001    /* ===========================================================
002     * JFreeChart : a free chart library for the Java(tm) platform
003     * ===========================================================
004     *
005     * (C) Copyright 2000-2005, 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-2005, 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     *
059     */
060    
061    package org.jfree.data.jdbc;
062    
063    import java.sql.Connection;
064    import java.sql.Date;
065    import java.sql.DriverManager;
066    import java.sql.ResultSet;
067    import java.sql.ResultSetMetaData;
068    import java.sql.SQLException;
069    import java.sql.Statement;
070    import java.sql.Types;
071    
072    import org.jfree.data.category.CategoryDataset;
073    import org.jfree.data.category.DefaultCategoryDataset;
074    
075    /**
076     * A {@link CategoryDataset} implementation over a database JDBC result set.
077     * The dataset is populated via a call to executeQuery with the string sql
078     * query.
079     * The sql query must return at least two columns.  The first column will be
080     * the category name and remaining columns values.
081     * executeQuery can be called a number of times.
082     * <p>
083     * The database connection is read-only and no write back facility exists.
084     */
085    public class JDBCCategoryDataset extends DefaultCategoryDataset {
086    
087        /** The database connection. */
088        private transient Connection connection;
089    
090        /**
091         * A flag the controls whether or not the table is transposed.  The default 
092         * is 'true' because this provides the behaviour described in the 
093         * documentation.
094         */
095        private boolean transpose = true;
096    
097    
098        /**
099         * Creates a new dataset with a database connection.
100         *
101         * @param  url  the URL of the database connection.
102         * @param  driverName  the database driver class name.
103         * @param  user  the database user.
104         * @param  passwd  the database user's password.
105         * 
106         * @throws ClassNotFoundException if the driver cannot be found.
107         * @throws SQLException if there is an error obtaining a connection to the 
108         *                      database.
109         */
110        public JDBCCategoryDataset(String url,
111                                   String driverName,
112                                   String user,
113                                   String passwd)
114            throws ClassNotFoundException, SQLException {
115    
116            Class.forName(driverName);
117            this.connection = DriverManager.getConnection(url, user, passwd);
118        }
119    
120        /**
121         * Create a new dataset with the given database connection.
122         *
123         * @param connection  the database connection.
124         */
125        public JDBCCategoryDataset(Connection connection) {
126            if (connection == null) {
127                throw new NullPointerException("A connection must be supplied.");
128            }
129            this.connection = connection;
130        }
131    
132        /**
133         * Creates a new dataset with the given database connection, and executes 
134         * the supplied query to populate the dataset.
135         *
136         * @param connection  the connection.
137         * @param query  the query.
138         * 
139         * @throws SQLException if there is a problem executing the query.
140         */
141        public JDBCCategoryDataset(Connection connection, String query) 
142            throws SQLException {
143            this(connection);
144            executeQuery(query);
145        }
146    
147        /**
148         * Returns a flag that controls whether or not the table values are 
149         * transposed when added to the dataset.
150         *
151         * @return A boolean.
152         */
153        public boolean getTranspose() {
154            return this.transpose;
155        }
156    
157        /**
158         * Sets a flag that controls whether or not the table values are transposed
159         * when added to the dataset.
160         *
161         * @param transpose  the flag.
162         */
163        public void setTranspose(boolean transpose) {
164            this.transpose = transpose;
165        }
166    
167        /**
168         * Populates the dataset by executing the supplied query against the 
169         * existing database connection.  If no connection exists then no action 
170         * is taken.
171         * <p>
172         * The results from the query are extracted and cached locally, thus 
173         * applying an upper limit on how many rows can be retrieved successfully.
174         *
175         * @param query  the query.
176         * 
177         * @throws SQLException if there is a problem executing the query.
178         */
179        public void executeQuery(String query) throws SQLException {
180            executeQuery(this.connection, query);
181        }
182    
183        /**
184         * Populates the dataset by executing the supplied query against the 
185         * existing database connection.  If no connection exists then no action 
186         * is taken.
187         * <p>
188         * The results from the query are extracted and cached locally, thus 
189         * applying an upper limit on how many rows can be retrieved successfully.
190         *
191         * @param con  the connection.
192         * @param query  the query.
193         * 
194         * @throws SQLException if there is a problem executing the query.
195         */
196        public void executeQuery(Connection con, String query) throws SQLException {
197    
198            Statement statement = null;
199            ResultSet resultSet = null;
200            try {
201                statement = con.createStatement();
202                resultSet = statement.executeQuery(query);
203                ResultSetMetaData metaData = resultSet.getMetaData();
204    
205                int columnCount = metaData.getColumnCount();
206    
207                if (columnCount < 2) {
208                    throw new SQLException(
209                        "JDBCCategoryDataset.executeQuery() : insufficient columns "
210                        + "returned from the database.");
211                }
212    
213                // Remove any previous old data
214                int i = getRowCount();
215                for (; i > 0; --i) {
216                    removeRow(i);
217                }
218    
219                while (resultSet.next()) {
220                    // first column contains the row key...
221                    Comparable rowKey = resultSet.getString(1);
222                    for (int column = 2; column <= columnCount; column++) {
223    
224                        Comparable columnKey = metaData.getColumnName(column);
225                        int columnType = metaData.getColumnType(column);
226    
227                        switch (columnType) {
228                            case Types.TINYINT:
229                            case Types.SMALLINT:
230                            case Types.INTEGER:
231                            case Types.BIGINT:
232                            case Types.FLOAT:
233                            case Types.DOUBLE:
234                            case Types.DECIMAL:
235                            case Types.NUMERIC:
236                            case Types.REAL: {
237                                Number value = (Number) resultSet.getObject(column);
238                                if (this.transpose) {
239                                    setValue(value, columnKey, rowKey);
240                                }
241                                else {
242                                    setValue(value, rowKey, columnKey);
243                                }
244                                break;
245                            }
246                            case Types.DATE:
247                            case Types.TIME:
248                            case Types.TIMESTAMP: {
249                                Date date = (Date) resultSet.getObject(column);
250                                Number value = new Long(date.getTime());
251                                if (this.transpose) {
252                                    setValue(value, columnKey, rowKey);
253                                }
254                                else {
255                                    setValue(value, rowKey, columnKey);
256                                }
257                                break;
258                            }
259                            case Types.CHAR:
260                            case Types.VARCHAR:
261                            case Types.LONGVARCHAR: {
262                                String string 
263                                    = (String) resultSet.getObject(column);
264                                try {
265                                    Number value = Double.valueOf(string);
266                                    if (this.transpose) {
267                                        setValue(value, columnKey, rowKey);
268                                    }
269                                    else {
270                                        setValue(value, rowKey, columnKey);
271                                    }
272                                }
273                                catch (NumberFormatException e) {
274                                    // suppress (value defaults to null)
275                                }
276                                break;
277                            }
278                            default:
279                                // not a value, can't use it (defaults to null)
280                                break;
281                        }
282                    }
283                }
284    
285                fireDatasetChanged();
286            }
287            finally {
288                if (resultSet != null) {
289                    try {
290                        resultSet.close();
291                    }
292                    catch (Exception e) {
293                        // report this?
294                    }
295                }
296                if (statement != null) {
297                    try {
298                        statement.close();
299                    }
300                    catch (Exception e) {
301                        // report this?
302                    }
303                }
304            }
305        }
306    
307    }