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,v 1.8 2007/04/01 18:49:26 taqua Exp $
027 * ------------
028 * (C) Copyright 2000-2005, by Object Refinery Limited.
029 * (C) Copyright 2005-2007, by Pentaho Corporation.
030 */
031 package org.jfree.report.modules.data.sql;
032
033 import java.sql.Connection;
034 import java.sql.PreparedStatement;
035 import java.sql.ResultSet;
036 import java.sql.ResultSetMetaData;
037 import java.sql.SQLException;
038 import java.util.ArrayList;
039 import java.util.HashMap;
040 import javax.swing.table.DefaultTableModel;
041 import javax.swing.table.TableModel;
042
043 import org.jfree.report.DataSet;
044 import org.jfree.report.JFreeReportBoot;
045 import org.jfree.report.ReportData;
046 import org.jfree.report.ReportDataFactory;
047 import org.jfree.report.ReportDataFactoryException;
048 import org.jfree.report.TableReportData;
049 import org.jfree.report.util.DataSetUtility;
050 import org.jfree.util.Configuration;
051
052 /**
053 * Creation-Date: 19.02.2006, 17:37:33
054 *
055 * @author Thomas Morgner
056 */
057 public class SimpleSQLReportDataFactory implements ReportDataFactory, Cloneable
058 {
059 private static final Object NULL_TOKEN = new Object();
060
061 private static class PreparedStatementCarrier
062 {
063 private PreparedStatement preparedStatement;
064 private String[] parameters;
065
066 public PreparedStatementCarrier(final PreparedStatement preparedStatement,
067 final String[] parameters)
068 {
069 this.preparedStatement = preparedStatement;
070 this.parameters = parameters;
071 }
072
073 public PreparedStatement getPreparedStatement()
074 {
075 return preparedStatement;
076 }
077
078 public String[] getParameters()
079 {
080 return parameters;
081 }
082 }
083
084 private HashMap preparedStatements;
085 private Connection connection;
086 private ConnectionProvider connectionProvider;
087
088 private boolean labelMapping;
089 private static final String COLUMN_NAME_MAPPING_KEY =
090 "org.jfree.report.modules.data.sql.ColumnNameMapping";
091
092 public SimpleSQLReportDataFactory(final Connection connection)
093 {
094 this (new StaticConnectionProvider(connection));
095 }
096
097 public SimpleSQLReportDataFactory(final ConnectionProvider connectionProvider)
098 {
099 if (connectionProvider == null)
100 {
101 throw new NullPointerException();
102 }
103 this.connectionProvider = connectionProvider;
104 this.preparedStatements = new HashMap();
105 final Configuration globalConfig =
106 JFreeReportBoot.getInstance().getGlobalConfig();
107 this.labelMapping = globalConfig.getConfigProperty
108 (SimpleSQLReportDataFactory.COLUMN_NAME_MAPPING_KEY, "Label").equals("Label");
109 }
110
111 public boolean isLabelMapping()
112 {
113 return labelMapping;
114 }
115
116 public void setLabelMapping(final boolean labelMapping)
117 {
118 this.labelMapping = labelMapping;
119 }
120
121 private synchronized Connection getConnection() throws SQLException
122 {
123 if (connection == null)
124 {
125 connection = connectionProvider.getConnection();
126 }
127 return connection;
128 }
129
130 private int getBestResultSetType() throws SQLException
131 {
132 final Connection connection = getConnection();
133 boolean supportsScrollInsensitive =
134 connection.getMetaData().supportsResultSetType
135 (ResultSet.TYPE_SCROLL_INSENSITIVE);
136 boolean supportsScrollSensitive =
137 connection.getMetaData().supportsResultSetType
138 (ResultSet.TYPE_SCROLL_SENSITIVE);
139
140 if (supportsScrollInsensitive)
141 {
142 return ResultSet.TYPE_SCROLL_INSENSITIVE;
143 }
144 if (supportsScrollSensitive)
145 {
146 return ResultSet.TYPE_SCROLL_SENSITIVE;
147 }
148 return ResultSet.TYPE_FORWARD_ONLY;
149 }
150
151 /**
152 * Queries a datasource. The string 'query' defines the name of the query. The
153 * Parameterset given here may contain more data than actually needed.
154 * <p/>
155 * The dataset may change between two calls, do not assume anything!
156 *
157 * @param query
158 * @param parameters
159 * @return
160 */
161 public synchronized ReportData queryData(final String query, final DataSet parameters)
162 throws ReportDataFactoryException
163 {
164 try
165 {
166 PreparedStatementCarrier pstmtCarrier = (PreparedStatementCarrier)
167 preparedStatements.get(query);
168 if (pstmtCarrier == null)
169 {
170 SQLParameterLookupParser parser = new SQLParameterLookupParser();
171 final String translatedQuery = parser.translateAndLookup(query);
172 PreparedStatement pstmt = getConnection().prepareStatement
173 (translatedQuery, getBestResultSetType(), ResultSet.CONCUR_READ_ONLY);
174 pstmtCarrier = new PreparedStatementCarrier(pstmt, parser.getFields());
175 preparedStatements.put(query, pstmtCarrier);
176 }
177
178 final PreparedStatement pstmt = pstmtCarrier.getPreparedStatement();
179 pstmt.clearParameters();
180
181 final String[] params = pstmtCarrier.getParameters();
182 for (int i = 0; i < params.length; i++)
183 {
184 final String param = params[i];
185 final Object pvalue = DataSetUtility.getByName(parameters, param, NULL_TOKEN);
186 if (pvalue == NULL_TOKEN)
187 {
188 // this either means, that the parameter is explicitly set to null
189 // or that there is no such column.
190 throw new ReportDataFactoryException ("Setting parameter '" +
191 param + "' failed: No such column.");
192 }
193 else if (pvalue == null)
194 {
195 // this should work, but some driver are known to die here.
196 // they should be fed with setNull(..) instead; something
197 // we cant do as JDK1.2's JDBC does not define it.
198 pstmt.setObject(i+1, null);
199 }
200 else
201 {
202 pstmt.setObject(i+1, pvalue);
203 }
204 }
205 ResultSet res = pstmt.executeQuery();
206 final int resultSetType = res.getType();
207
208 if (resultSetType == ResultSet.TYPE_FORWARD_ONLY)
209 {
210 TableModel model = generateDefaultTableModel(res, labelMapping);
211 res.close();
212 return new TableReportData(model);
213 }
214 else
215 {
216 return new SQLReportData(res, labelMapping);
217 }
218 }
219 catch(ReportDataFactoryException rdfe)
220 {
221 throw rdfe;
222 }
223 catch (Exception e)
224 {
225 throw new ReportDataFactoryException("Failed at query: " + query, e);
226 }
227 }
228
229 public void open()
230 {
231
232 }
233
234 public synchronized void close()
235 {
236 if (connection == null)
237 {
238 return;
239 }
240
241 try
242 {
243 connection.close();
244 }
245 catch (SQLException e)
246 {
247 // we tried our very best ..
248 }
249 connection = null;
250 }
251
252 /**
253 * Generates a <code>TableModel</code> that gets its contents filled from a
254 * <code>ResultSet</code>. The column names of the <code>ResultSet</code> will form the
255 * column names of the table model.
256 * <p/>
257 * Hint: To customize the names of the columns, use the SQL column aliasing (done with
258 * <code>SELECT nativecolumnname AS "JavaColumnName" FROM ....</code>
259 *
260 * @param rs the result set.
261 * @param labelMapping defines, whether to use column names or column labels to compute
262 * the column index.
263 * @return a closeable table model.
264 *
265 * @throws SQLException if there is a problem with the result set.
266 */
267 private TableModel generateDefaultTableModel
268 (final ResultSet rs, final boolean labelMapping)
269 throws SQLException
270 {
271 final ResultSetMetaData rsmd = rs.getMetaData();
272 final int colcount = rsmd.getColumnCount();
273 final ArrayList header = new ArrayList(colcount);
274 for (int i = 0; i < colcount; i++)
275 {
276 if (labelMapping)
277 {
278 final String name = rsmd.getColumnLabel(i + 1);
279 header.add(name);
280 }
281 else
282 {
283 final String name = rsmd.getColumnName(i + 1);
284 header.add(name);
285 }
286 }
287 final ArrayList rows = new ArrayList();
288 while (rs.next())
289 {
290 final Object[] column = new Object[colcount];
291 for (int i = 0; i < colcount; i++)
292 {
293 column[i] = rs.getObject(i + 1);
294 if (rs.wasNull())
295 {
296 column[i] = null;
297 }
298 }
299 rows.add(column);
300 }
301
302 final Object[] tempRows = rows.toArray();
303 final Object[][] rowMap = new Object[tempRows.length][];
304 for (int i = 0; i < tempRows.length; i++)
305 {
306 rowMap[i] = (Object[]) tempRows[i];
307 }
308 return new DefaultTableModel(rowMap, header.toArray());
309 }
310
311 /**
312 * Derives a freshly initialized report data factory, which is independend of
313 * the original data factory. Opening or Closing one data factory must not
314 * affect the other factories.
315 *
316 * @return
317 */
318 public ReportDataFactory derive()
319 {
320 try
321 {
322 return (ReportDataFactory) clone();
323 }
324 catch (CloneNotSupportedException e)
325 {
326 // this should not happen ..
327 throw new IllegalStateException("Clone failed?");
328 }
329 }
330
331 public Object clone () throws CloneNotSupportedException
332 {
333 SimpleSQLReportDataFactory dataFactory = (SimpleSQLReportDataFactory) super.clone();
334 dataFactory.connection = null;
335 dataFactory.preparedStatements = new HashMap();
336 return dataFactory;
337 }
338 }