KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > jfree > data > jdbc > JDBCCategoryDataset


1 /* ===========================================================
2  * JFreeChart : a free chart library for the Java(tm) platform
3  * ===========================================================
4  *
5  * (C) Copyright 2000-2006, by Object Refinery Limited and Contributors.
6  *
7  * Project Info: http://www.jfree.org/jfreechart/index.html
8  *
9  * This library is free software; you can redistribute it and/or modify it
10  * under the terms of the GNU Lesser General Public License as published by
11  * the Free Software Foundation; either version 2.1 of the License, or
12  * (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but
15  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
16  * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
17  * License for more details.
18  *
19  * You should have received a copy of the GNU Lesser General Public
20  * License along with this library; if not, write to the Free Software
21  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
22  * USA.
23  *
24  * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
25  * in the United States and other countries.]
26  *
27  * ------------------------
28  * JDBCCategoryDataset.java
29  * ------------------------
30  * (C) Copyright 2002-2006, by Bryan Scott and Contributors.
31  *
32  * Original Author: Bryan Scott; Andy;
33  * Contributor(s): David Gilbert (for Object Refinery Limited);
34  * Thomas Morgner;
35  *
36  * Changes
37  * -------
38  * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from
39  * Andy;
40  * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
41  * 03-Sep-2002 : Added fix for bug 591385 (DG);
42  * 18-Sep-2002 : Updated to support BIGINT (BS);
43  * 16-Oct-2002 : Added fix for bug 586667 (DG);
44  * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
45  * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
46  * 30-Jun-2003 : CVS Write test (BS);
47  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string)
48  * method (BS);
49  * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily
50  * transposed if required (DG);
51  * 10-Sep-2003 : Added support for additional JDBC types (DG);
52  * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
53  * following being highlighted on online forum (BS);
54  * 02-Dec-2003 : Throwing exceptions allows to handle errors, removed default
55  * constructor, as without a connection, a query can never be
56  * executed (TM);
57  * 04-Dec-2003 : Added missing Javadocs (DG);
58  * ------------- JFREECHART 1.0.0 ---------------------------------------------
59  * 08-Mar-2006 : Fixed bug 1445748 where an exception is thrown if
60  * executeQuery() is called more than once (DG);
61  *
62  */
63
64 package org.jfree.data.jdbc;
65
66 import java.sql.Connection JavaDoc;
67 import java.sql.Date JavaDoc;
68 import java.sql.DriverManager JavaDoc;
69 import java.sql.ResultSet JavaDoc;
70 import java.sql.ResultSetMetaData JavaDoc;
71 import java.sql.SQLException JavaDoc;
72 import java.sql.Statement JavaDoc;
73 import java.sql.Types JavaDoc;
74
75 import org.jfree.data.category.CategoryDataset;
76 import org.jfree.data.category.DefaultCategoryDataset;
77
78 /**
79  * A {@link CategoryDataset} implementation over a database JDBC result set.
80  * The dataset is populated via a call to {@link #executeQuery(String)} with
81  * the string SQL query. The SQL query must return at least two columns. The
82  * first column will be the category name and remaining columns values (each
83  * column represents a series). Subsequent calls to
84  * {@link #executeQuery(String)} will refresh the dataset.
85  * <p>
86  * The database connection is read-only and no write back facility exists.
87  * <p>
88  * NOTE: Many people have found this class too restrictive in general use.
89  * For the greatest flexibility, please consider writing your own code to read
90  * data from a <code>ResultSet</code> and populate a
91  * {@link DefaultCategoryDataset} directly.
92  */
93 public class JDBCCategoryDataset extends DefaultCategoryDataset {
94
95     /** The database connection. */
96     private transient Connection JavaDoc connection;
97
98     /**
99      * 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 JavaDoc url,
119                                String JavaDoc driverName,
120                                String JavaDoc user,
121                                String JavaDoc passwd)
122         throws ClassNotFoundException JavaDoc, SQLException JavaDoc {
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 JavaDoc connection) {
134         if (connection == null) {
135             throw new NullPointerException JavaDoc("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 JavaDoc connection, String JavaDoc query)
150         throws SQLException JavaDoc {
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 JavaDoc query) throws SQLException JavaDoc {
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 JavaDoc con, String JavaDoc query) throws SQLException JavaDoc {
205
206         Statement JavaDoc statement = null;
207         ResultSet JavaDoc resultSet = null;
208         try {
209             statement = con.createStatement();
210             resultSet = statement.executeQuery(query);
211             ResultSetMetaData JavaDoc metaData = resultSet.getMetaData();
212
213             int columnCount = metaData.getColumnCount();
214
215             if (columnCount < 2) {
216                 throw new SQLException JavaDoc(
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 JavaDoc rowKey = resultSet.getString(1);
230                 for (int column = 2; column <= columnCount; column++) {
231
232                     Comparable JavaDoc 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 JavaDoc value = (Number JavaDoc) 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 JavaDoc date = (Date JavaDoc) resultSet.getObject(column);
258                             Number JavaDoc value = new Long JavaDoc(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 JavaDoc string
271                                 = (String JavaDoc) resultSet.getObject(column);
272                             try {
273                                 Number JavaDoc 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 JavaDoc 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 JavaDoc e) {
301                     // report this?
302 }
303             }
304             if (statement != null) {
305                 try {
306                     statement.close();
307                 }
308                 catch (Exception JavaDoc e) {
309                     // report this?
310 }
311             }
312         }
313     }
314
315 }
316
Popular Tags