KickJava   Java API By Example, From Geeks To Geeks.

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


1 /* ======================================
2  * JFreeChart : a free Java chart library
3  * ======================================
4  *
5  * Project Info: http://www.jfree.org/jfreechart/index.html
6  * Project Lead: David Gilbert (david.gilbert@object-refinery.com);
7  *
8  * (C) Copyright 2000-2003, by Object Refinery Limited and Contributors.
9  *
10  * This library is free software; you can redistribute it and/or modify it under the terms
11  * of the GNU Lesser General Public License as published by the Free Software Foundation;
12  * either version 2.1 of the License, or (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
15  * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
16  * See the GNU Lesser General Public License for more details.
17  *
18  * You should have received a copy of the GNU Lesser General Public License along with this
19  * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
20  * Boston, MA 02111-1307, USA.
21  *
22  * ------------------------
23  * JDBCCategoryDataset.java
24  * ------------------------
25  * (C) Copyright 2002, 2003, by Bryan Scott and Contributors.
26  *
27  * Original Author: Bryan Scott; Andy;
28  * Contributor(s): David Gilbert (for Object Refinery Limited);
29  *
30  * Changes
31  * -------
32  * 26-Apr-2002 : Creation based on JdbcXYDataSet, using code contributed from Andy;
33  * 13-Aug-2002 : Updated Javadocs, import statements and formatting (DG);
34  * 03-Sep-2002 : Added fix for bug 591385 (DG);
35  * 18-Sep-2002 : Updated to support BIGINT (BS);
36  * 16-Oct-2002 : Added fix for bug 586667 (DG);
37  * 03-Feb-2003 : Added Types.DECIMAL (see bug report 677814) (DG);
38  * 13-Jun-2003 : Added Types.TIME as suggest by Bryan Scott in the forum (DG);
39  * 30-Jun-2003 : CVS Write test (BS);
40  * 30-Jul-2003 : Added empty contructor and executeQuery(connection,string) method (BS);
41  * 29-Aug-2003 : Added a 'transpose' flag, so that data can be easily transposed if required (DG);
42  * 10-Sep-2003 : Added support for additional JDBC types (DG);
43  *
44  */
45
46 package org.jfree.data;
47
48 import java.sql.Connection JavaDoc;
49 import java.sql.Date JavaDoc;
50 import java.sql.DriverManager JavaDoc;
51 import java.sql.ResultSet JavaDoc;
52 import java.sql.ResultSetMetaData JavaDoc;
53 import java.sql.SQLException JavaDoc;
54 import java.sql.Statement JavaDoc;
55 import java.sql.Types JavaDoc;
56
57 /**
58  * A {@link CategoryDataset} implementation over a database JDBC result set.
59  * The dataset is populated via a call to executeQuery with the string sql
60  * query.
61  * The sql query must return at least two columns. The first column will be
62  * the category name and remaining columns values.
63  * executeQuery can be called a number of times.
64  *
65  * The database connection is read-only and no write back facility exists.
66  *
67  * @author Bryan Scott
68  *
69  * Changes
70  * -------
71  * 24-Sep-2003 : Added clearing results from previous queries to executeQuery
72  * following being highlighted on online forum (BS);
73  */
74 public class JDBCCategoryDataset extends DefaultCategoryDataset {
75
76     /** The database connection. */
77     private Connection JavaDoc connection;
78
79     /** The statement. */
80     private Statement JavaDoc statement;
81
82     /** The result set. */
83     private ResultSet JavaDoc resultSet;
84
85     /** The result set meta data. */
86     private ResultSetMetaData JavaDoc metaData;
87
88     /**
89      * A flag the controls whether or not the table is transposed. The default is 'true'
90      * because this provides the behaviour described in the documentation.
91      */
92     private boolean transpose = true;
93
94     /**
95      * Creates a new dataset with no database connection.
96      *
97      */
98     public JDBCCategoryDataset() {
99       super();
100     }
101
102     /**
103      * Creates a new dataset with a database connection.
104      *
105      * @param url the URL of the database connection.
106      * @param driverName the database driver class name.
107      * @param user the database user.
108      * @param passwd the database user's password.
109      */
110     public JDBCCategoryDataset(String JavaDoc url,
111                                String JavaDoc driverName,
112                                String JavaDoc user,
113                                String JavaDoc passwd) {
114
115         super();
116         try {
117             Class.forName(driverName);
118             connection = DriverManager.getConnection(url, user, passwd);
119             this.statement = connection.createStatement();
120         }
121         catch (ClassNotFoundException JavaDoc ex) {
122             System.err.println("JDBCCategoryDataset: cannot find the database driver classes.");
123             System.err.println(ex);
124         }
125         catch (SQLException JavaDoc ex) {
126             System.err.println("JDBCCategoryDataset: cannot connect to the database.");
127             System.err.println(ex);
128         }
129     }
130
131     /**
132      * Create a new dataset with the given database connection.
133      *
134      * @param connection the database connection.
135      */
136     public JDBCCategoryDataset(Connection JavaDoc connection) {
137         super();
138         this.connection = connection;
139     }
140
141     /**
142      * Creates a new dataset with the given database connection, and executes the supplied
143      * query to populate the dataset.
144      *
145      * @param connection the connection.
146      * @param query the query.
147      */
148     public JDBCCategoryDataset(Connection JavaDoc connection, String JavaDoc query) {
149         this(connection);
150         executeQuery(query);
151     }
152
153     /**
154      * Returns a flag that controls whether or not the table values are transposed when added
155      * to the dataset.
156      *
157      * @return A boolean.
158      */
159     public boolean getTranspose() {
160         return this.transpose;
161     }
162
163     /**
164      * Sets a flag that controls whether or not the table values are transposed when added to
165      * the dataset.
166      *
167      * @param transpose the flag.
168      */
169     public void setTranspose(boolean transpose) {
170         this.transpose = transpose;
171     }
172
173     /**
174      * Populates the dataset by executing the supplied query against the existing database
175      * connection. If no connection exists then no action is taken.
176      * <p>
177      * The results from the query are extracted and cached locally, thus applying an upper
178      * limit on how many rows can be retrieved successfully.
179      *
180      * @param query the query.
181      */
182     public void executeQuery(String JavaDoc query) {
183       executeQuery(connection, query);
184     }
185
186     /**
187      * Populates the dataset by executing the supplied query against the existing database
188      * connection. If no connection exists then no action is taken.
189      * <p>
190      * The results from the query are extracted and cached locally, thus applying an upper
191      * limit on how many rows can be retrieved successfully.
192      *
193      * @param con the connection.
194      * @param query the query.
195      */
196     public void executeQuery(Connection JavaDoc con, String JavaDoc query) {
197
198         // if there is no connection, just return
199 if (con == null) {
200             System.err.println("JDBCCategoryDataset.executeQuery(...) : there is no connection.");
201             return;
202         }
203
204         try {
205             statement = con.createStatement();
206             resultSet = statement.executeQuery(query);
207             metaData = resultSet.getMetaData();
208
209             int columnCount = metaData.getColumnCount();
210
211             if (columnCount < 2) {
212                 System.err.println("JDBCCategoryDataset.executeQuery(...) : insufficient columns "
213                                    + "returned from the database.");
214                 return;
215             }
216
217             // Remove any previous old data
218 int i = getRowCount();
219             for (; i > 0; --i) {
220               removeRow(i);
221             }
222
223             while (resultSet.next()) {
224                 // first column contains the row key...
225 Comparable JavaDoc rowKey = resultSet.getString(1);
226                 for (int column = 2; column <= columnCount; column++) {
227
228                     Comparable JavaDoc columnKey = metaData.getColumnName(column);
229                     Number JavaDoc value = null;
230                     int columnType = metaData.getColumnType(column);
231
232                     switch (columnType) {
233                         case Types.TINYINT:
234                         case Types.SMALLINT:
235                         case Types.INTEGER:
236                         case Types.BIGINT:
237                         case Types.FLOAT:
238                         case Types.DOUBLE:
239                         case Types.DECIMAL:
240                         case Types.NUMERIC:
241                         case Types.REAL:
242                             value = (Number JavaDoc) resultSet.getObject(column);
243                             if (transpose) {
244                                 setValue(value, columnKey, rowKey);
245                             }
246                             else {
247                                 setValue(value, rowKey, columnKey);
248                             }
249                             break;
250
251                         case Types.DATE:
252                         case Types.TIME:
253                         case Types.TIMESTAMP:
254                             Date JavaDoc date = (Date JavaDoc) resultSet.getObject(column);
255                             value = new Long JavaDoc(date.getTime());
256                             if (transpose) {
257                                 setValue(value, columnKey, rowKey);
258                             }
259                             else {
260                                 setValue(value, rowKey, columnKey);
261                             }
262                             break;
263
264                         case Types.CHAR:
265                         case Types.VARCHAR:
266                         case Types.LONGVARCHAR:
267                             String JavaDoc string = (String JavaDoc) resultSet.getObject(column);
268                             try {
269                                 value = Double.valueOf(string);
270                                 if (transpose) {
271                                     setValue(value, columnKey, rowKey);
272                                 }
273                                 else {
274                                     setValue(value, rowKey, columnKey);
275                                 }
276                             }
277                             catch (NumberFormatException JavaDoc e) {
278                                 // suppress (value defaults to null)
279 }
280                             break;
281
282                         default:
283                             // not a value, can't use it (defaults to null)
284 break;
285                     }
286                 }
287             }
288
289             fireDatasetChanged();
290         }
291         catch (SQLException JavaDoc ex) {
292             System.err.println(ex);
293         }
294         finally {
295             if (resultSet != null) {
296                 try {
297                     resultSet.close();
298                 }
299                 catch (Exception JavaDoc e) {
300                     // report this?
301 }
302             }
303             if (statement != null) {
304                 try {
305                     statement.close();
306                 }
307                 catch (Exception JavaDoc e) {
308                     // report this?
309 }
310             }
311         }
312     }
313
314 }
315
Popular Tags