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 */ 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 private 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 = "Label".equals(globalConfig.getConfigProperty 108 (SimpleSQLReportDataFactory.COLUMN_NAME_MAPPING_KEY, "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 final boolean supportsScrollInsensitive = 134 connection.getMetaData().supportsResultSetType 135 (ResultSet.TYPE_SCROLL_INSENSITIVE); 136 final 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 final SQLParameterLookupParser parser = new SQLParameterLookupParser(); 171 final String translatedQuery = parser.translateAndLookup(query); 172 final 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 final ResultSet res = pstmt.executeQuery(); 206 final int resultSetType = res.getType(); 207 208 if (resultSetType == ResultSet.TYPE_FORWARD_ONLY) 209 { 210 final 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 final SimpleSQLReportDataFactory dataFactory = (SimpleSQLReportDataFactory) super.clone(); 334 dataFactory.connection = null; 335 dataFactory.preparedStatements = (HashMap) preparedStatements.clone(); 336 dataFactory.preparedStatements.clear(); 337 return dataFactory; 338 } 339 }