001: /**
002: * ========================================
003: * JFreeReport : a free Java report library
004: * ========================================
005: *
006: * Project Info: http://reporting.pentaho.org/
007: *
008: * (C) Copyright 2000-2007, by Object Refinery Limited, Pentaho Corporation and Contributors.
009: *
010: * This library is free software; you can redistribute it and/or modify it under the terms
011: * of the GNU Lesser General Public License as published by the Free Software Foundation;
012: * either version 2.1 of the License, or (at your option) any later version.
013: *
014: * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
015: * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
016: * See the GNU Lesser General Public License for more details.
017: *
018: * You should have received a copy of the GNU Lesser General Public License along with this
019: * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
020: * Boston, MA 02111-1307, USA.
021: *
022: * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
023: * in the United States and other countries.]
024: *
025: * ------------
026: * $Id: SimpleSQLReportDataFactory.java 3525 2007-10-16 11:43:48Z tmorgner $
027: * ------------
028: * (C) Copyright 2000-2005, by Object Refinery Limited.
029: * (C) Copyright 2005-2007, by Pentaho Corporation.
030: */package org.jfree.report.modules.data.sql;
031:
032: import java.sql.Connection;
033: import java.sql.PreparedStatement;
034: import java.sql.ResultSet;
035: import java.sql.ResultSetMetaData;
036: import java.sql.SQLException;
037: import java.util.ArrayList;
038: import java.util.HashMap;
039: import javax.swing.table.DefaultTableModel;
040: import javax.swing.table.TableModel;
041:
042: import org.jfree.report.DataSet;
043: import org.jfree.report.JFreeReportBoot;
044: import org.jfree.report.ReportData;
045: import org.jfree.report.ReportDataFactory;
046: import org.jfree.report.ReportDataFactoryException;
047: import org.jfree.report.TableReportData;
048: import org.jfree.report.util.DataSetUtility;
049: import org.jfree.util.Configuration;
050:
051: /**
052: * Creation-Date: 19.02.2006, 17:37:33
053: *
054: * @author Thomas Morgner
055: */
056: public class SimpleSQLReportDataFactory implements ReportDataFactory,
057: Cloneable {
058: private static final Object NULL_TOKEN = new Object();
059:
060: private static class PreparedStatementCarrier {
061: private PreparedStatement preparedStatement;
062: private String[] parameters;
063:
064: private PreparedStatementCarrier(
065: final PreparedStatement preparedStatement,
066: final String[] parameters) {
067: this .preparedStatement = preparedStatement;
068: this .parameters = parameters;
069: }
070:
071: public PreparedStatement getPreparedStatement() {
072: return preparedStatement;
073: }
074:
075: public String[] getParameters() {
076: return parameters;
077: }
078: }
079:
080: private HashMap preparedStatements;
081: private Connection connection;
082: private ConnectionProvider connectionProvider;
083:
084: private boolean labelMapping;
085: private static final String COLUMN_NAME_MAPPING_KEY = "org.jfree.report.modules.data.sql.ColumnNameMapping";
086:
087: public SimpleSQLReportDataFactory(final Connection connection) {
088: this (new StaticConnectionProvider(connection));
089: }
090:
091: public SimpleSQLReportDataFactory(
092: final ConnectionProvider connectionProvider) {
093: if (connectionProvider == null) {
094: throw new NullPointerException();
095: }
096: this .connectionProvider = connectionProvider;
097: this .preparedStatements = new HashMap();
098: final Configuration globalConfig = JFreeReportBoot
099: .getInstance().getGlobalConfig();
100: this .labelMapping = "Label"
101: .equals(globalConfig
102: .getConfigProperty(
103: SimpleSQLReportDataFactory.COLUMN_NAME_MAPPING_KEY,
104: "Label"));
105: }
106:
107: public boolean isLabelMapping() {
108: return labelMapping;
109: }
110:
111: public void setLabelMapping(final boolean labelMapping) {
112: this .labelMapping = labelMapping;
113: }
114:
115: private synchronized Connection getConnection() throws SQLException {
116: if (connection == null) {
117: connection = connectionProvider.getConnection();
118: }
119: return connection;
120: }
121:
122: private int getBestResultSetType() throws SQLException {
123: final Connection connection = getConnection();
124: final boolean supportsScrollInsensitive = connection
125: .getMetaData().supportsResultSetType(
126: ResultSet.TYPE_SCROLL_INSENSITIVE);
127: final boolean supportsScrollSensitive = connection
128: .getMetaData().supportsResultSetType(
129: ResultSet.TYPE_SCROLL_SENSITIVE);
130:
131: if (supportsScrollInsensitive) {
132: return ResultSet.TYPE_SCROLL_INSENSITIVE;
133: }
134: if (supportsScrollSensitive) {
135: return ResultSet.TYPE_SCROLL_SENSITIVE;
136: }
137: return ResultSet.TYPE_FORWARD_ONLY;
138: }
139:
140: /**
141: * Queries a datasource. The string 'query' defines the name of the query. The
142: * Parameterset given here may contain more data than actually needed.
143: * <p/>
144: * The dataset may change between two calls, do not assume anything!
145: *
146: * @param query
147: * @param parameters
148: * @return
149: */
150: public synchronized ReportData queryData(final String query,
151: final DataSet parameters) throws ReportDataFactoryException {
152: try {
153: PreparedStatementCarrier pstmtCarrier = (PreparedStatementCarrier) preparedStatements
154: .get(query);
155: if (pstmtCarrier == null) {
156: final SQLParameterLookupParser parser = new SQLParameterLookupParser();
157: final String translatedQuery = parser
158: .translateAndLookup(query);
159: final PreparedStatement pstmt = getConnection()
160: .prepareStatement(translatedQuery,
161: getBestResultSetType(),
162: ResultSet.CONCUR_READ_ONLY);
163: pstmtCarrier = new PreparedStatementCarrier(pstmt,
164: parser.getFields());
165: preparedStatements.put(query, pstmtCarrier);
166: }
167:
168: final PreparedStatement pstmt = pstmtCarrier
169: .getPreparedStatement();
170: pstmt.clearParameters();
171:
172: final String[] params = pstmtCarrier.getParameters();
173: for (int i = 0; i < params.length; i++) {
174: final String param = params[i];
175: final Object pvalue = DataSetUtility.getByName(
176: parameters, param, NULL_TOKEN);
177: if (pvalue == NULL_TOKEN) {
178: // this either means, that the parameter is explicitly set to null
179: // or that there is no such column.
180: throw new ReportDataFactoryException(
181: "Setting parameter '" + param
182: + "' failed: No such column.");
183: } else if (pvalue == null) {
184: // this should work, but some driver are known to die here.
185: // they should be fed with setNull(..) instead; something
186: // we cant do as JDK1.2's JDBC does not define it.
187: pstmt.setObject(i + 1, null);
188: } else {
189: pstmt.setObject(i + 1, pvalue);
190: }
191: }
192: final ResultSet res = pstmt.executeQuery();
193: final int resultSetType = res.getType();
194:
195: if (resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
196: final TableModel model = generateDefaultTableModel(res,
197: labelMapping);
198: res.close();
199: return new TableReportData(model);
200: } else {
201: return new SQLReportData(res, labelMapping);
202: }
203: } catch (ReportDataFactoryException rdfe) {
204: throw rdfe;
205: } catch (Exception e) {
206: throw new ReportDataFactoryException("Failed at query: "
207: + query, e);
208: }
209: }
210:
211: public void open() {
212:
213: }
214:
215: public synchronized void close() {
216: if (connection == null) {
217: return;
218: }
219:
220: try {
221: connection.close();
222: } catch (SQLException e) {
223: // we tried our very best ..
224: }
225: connection = null;
226: }
227:
228: /**
229: * Generates a <code>TableModel</code> that gets its contents filled from a
230: * <code>ResultSet</code>. The column names of the <code>ResultSet</code> will form the
231: * column names of the table model.
232: * <p/>
233: * Hint: To customize the names of the columns, use the SQL column aliasing (done with
234: * <code>SELECT nativecolumnname AS "JavaColumnName" FROM ....</code>
235: *
236: * @param rs the result set.
237: * @param labelMapping defines, whether to use column names or column labels to compute
238: * the column index.
239: * @return a closeable table model.
240: *
241: * @throws SQLException if there is a problem with the result set.
242: */
243: private TableModel generateDefaultTableModel(final ResultSet rs,
244: final boolean labelMapping) throws SQLException {
245: final ResultSetMetaData rsmd = rs.getMetaData();
246: final int colcount = rsmd.getColumnCount();
247: final ArrayList header = new ArrayList(colcount);
248: for (int i = 0; i < colcount; i++) {
249: if (labelMapping) {
250: final String name = rsmd.getColumnLabel(i + 1);
251: header.add(name);
252: } else {
253: final String name = rsmd.getColumnName(i + 1);
254: header.add(name);
255: }
256: }
257: final ArrayList rows = new ArrayList();
258: while (rs.next()) {
259: final Object[] column = new Object[colcount];
260: for (int i = 0; i < colcount; i++) {
261: column[i] = rs.getObject(i + 1);
262: if (rs.wasNull()) {
263: column[i] = null;
264: }
265: }
266: rows.add(column);
267: }
268:
269: final Object[] tempRows = rows.toArray();
270: final Object[][] rowMap = new Object[tempRows.length][];
271: for (int i = 0; i < tempRows.length; i++) {
272: rowMap[i] = (Object[]) tempRows[i];
273: }
274: return new DefaultTableModel(rowMap, header.toArray());
275: }
276:
277: /**
278: * Derives a freshly initialized report data factory, which is independend of
279: * the original data factory. Opening or Closing one data factory must not
280: * affect the other factories.
281: *
282: * @return
283: */
284: public ReportDataFactory derive() {
285: try {
286: return (ReportDataFactory) clone();
287: } catch (CloneNotSupportedException e) {
288: // this should not happen ..
289: throw new IllegalStateException("Clone failed?");
290: }
291: }
292:
293: public Object clone() throws CloneNotSupportedException {
294: final SimpleSQLReportDataFactory dataFactory = (SimpleSQLReportDataFactory) super
295: .clone();
296: dataFactory.connection = null;
297: dataFactory.preparedStatements = (HashMap) preparedStatements
298: .clone();
299: dataFactory.preparedStatements.clear();
300: return dataFactory;
301: }
302: }
|