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: * Creates a new dataset with a database connection.
107: *
108: * @param url the URL of the database connection.
109: * @param driverName the database driver class name.
110: * @param user the database user.
111: * @param passwd the database user's password.
112: *
113: * @throws ClassNotFoundException if the driver cannot be found.
114: * @throws SQLException if there is an error obtaining a connection to the
115: * database.
116: */
117: public JDBCCategoryDataset(String url, String driverName,
118: String user, String passwd) throws ClassNotFoundException,
119: SQLException {
120:
121: Class.forName(driverName);
122: this .connection = DriverManager
123: .getConnection(url, user, passwd);
124: }
125:
126: /**
127: * Create a new dataset with the given database connection.
128: *
129: * @param connection the database connection.
130: */
131: public JDBCCategoryDataset(Connection connection) {
132: if (connection == null) {
133: throw new NullPointerException(
134: "A connection must be supplied.");
135: }
136: this .connection = connection;
137: }
138:
139: /**
140: * Creates a new dataset with the given database connection, and executes
141: * the supplied query to populate the dataset.
142: *
143: * @param connection the connection.
144: * @param query the query.
145: *
146: * @throws SQLException if there is a problem executing the query.
147: */
148: public JDBCCategoryDataset(Connection connection, String query)
149: throws SQLException {
150: this (connection);
151: executeQuery(query);
152: }
153:
154: /**
155: * Returns a flag that controls whether or not the table values are
156: * transposed when added to the dataset.
157: *
158: * @return A boolean.
159: */
160: public boolean getTranspose() {
161: return this .transpose;
162: }
163:
164: /**
165: * Sets a flag that controls whether or not the table values are transposed
166: * when added to the dataset.
167: *
168: * @param transpose the flag.
169: */
170: public void setTranspose(boolean transpose) {
171: this .transpose = transpose;
172: }
173:
174: /**
175: * Populates the dataset by executing the supplied query against the
176: * existing database connection. If no connection exists then no action
177: * is taken.
178: * <p>
179: * The results from the query are extracted and cached locally, thus
180: * applying an upper limit on how many rows can be retrieved successfully.
181: *
182: * @param query the query.
183: *
184: * @throws SQLException if there is a problem executing the query.
185: */
186: public void executeQuery(String query) throws SQLException {
187: executeQuery(this .connection, query);
188: }
189:
190: /**
191: * Populates the dataset by executing the supplied query against the
192: * existing database connection. If no connection exists then no action
193: * is taken.
194: * <p>
195: * The results from the query are extracted and cached locally, thus
196: * applying an upper limit on how many rows can be retrieved successfully.
197: *
198: * @param con the connection.
199: * @param query the query.
200: *
201: * @throws SQLException if there is a problem executing the query.
202: */
203: public void executeQuery(Connection con, String query)
204: 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
233: .getColumnName(column);
234: int columnType = metaData.getColumnType(column);
235:
236: switch (columnType) {
237: case Types.TINYINT:
238: case Types.SMALLINT:
239: case Types.INTEGER:
240: case Types.BIGINT:
241: case Types.FLOAT:
242: case Types.DOUBLE:
243: case Types.DECIMAL:
244: case Types.NUMERIC:
245: case Types.REAL: {
246: Number value = (Number) resultSet
247: .getObject(column);
248: if (this .transpose) {
249: setValue(value, columnKey, rowKey);
250: } else {
251: setValue(value, rowKey, columnKey);
252: }
253: break;
254: }
255: case Types.DATE:
256: case Types.TIME:
257: case Types.TIMESTAMP: {
258: Date date = (Date) resultSet.getObject(column);
259: Number value = new Long(date.getTime());
260: if (this .transpose) {
261: setValue(value, columnKey, rowKey);
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 = (String) resultSet
271: .getObject(column);
272: try {
273: Number value = Double.valueOf(string);
274: if (this .transpose) {
275: setValue(value, columnKey, rowKey);
276: } else {
277: setValue(value, rowKey, columnKey);
278: }
279: } catch (NumberFormatException e) {
280: // suppress (value defaults to null)
281: }
282: break;
283: }
284: default:
285: // not a value, can't use it (defaults to null)
286: break;
287: }
288: }
289: }
290:
291: fireDatasetChanged();
292: } finally {
293: if (resultSet != null) {
294: try {
295: resultSet.close();
296: } catch (Exception e) {
297: // report this?
298: }
299: }
300: if (statement != null) {
301: try {
302: statement.close();
303: } catch (Exception e) {
304: // report this?
305: }
306: }
307: }
308: }
309:
310: }
|