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