Source for org.jfree.report.modules.data.sql.SimpleSQLReportDataFactory

   1: /**
   2:  * ========================================
   3:  * JFreeReport : a free Java report library
   4:  * ========================================
   5:  *
   6:  * Project Info:  http://reporting.pentaho.org/
   7:  *
   8:  * (C) Copyright 2000-2007, by Object Refinery Limited, Pentaho Corporation and Contributors.
   9:  *
  10:  * This library is free software; you can redistribute it and/or modify it under the terms
  11:  * of the GNU Lesser General Public License as published by the Free Software Foundation;
  12:  * either version 2.1 of the License, or (at your option) any later version.
  13:  *
  14:  * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
  15:  * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  16:  * See the GNU Lesser General Public License for more details.
  17:  *
  18:  * You should have received a copy of the GNU Lesser General Public License along with this
  19:  * library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
  20:  * Boston, MA 02111-1307, USA.
  21:  *
  22:  * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
  23:  * in the United States and other countries.]
  24:  *
  25:  * ------------
  26:  * $Id: SimpleSQLReportDataFactory.java 3525 2007-10-16 11:43:48Z tmorgner $
  27:  * ------------
  28:  * (C) Copyright 2000-2005, by Object Refinery Limited.
  29:  * (C) Copyright 2005-2007, by Pentaho Corporation.
  30:  */
  31: package org.jfree.report.modules.data.sql;
  32: 
  33: import java.sql.Connection;
  34: import java.sql.PreparedStatement;
  35: import java.sql.ResultSet;
  36: import java.sql.ResultSetMetaData;
  37: import java.sql.SQLException;
  38: import java.util.ArrayList;
  39: import java.util.HashMap;
  40: import javax.swing.table.DefaultTableModel;
  41: import javax.swing.table.TableModel;
  42: 
  43: import org.jfree.report.DataSet;
  44: import org.jfree.report.JFreeReportBoot;
  45: import org.jfree.report.ReportData;
  46: import org.jfree.report.ReportDataFactory;
  47: import org.jfree.report.ReportDataFactoryException;
  48: import org.jfree.report.TableReportData;
  49: import org.jfree.report.util.DataSetUtility;
  50: import org.jfree.util.Configuration;
  51: 
  52: /**
  53:  * Creation-Date: 19.02.2006, 17:37:33
  54:  *
  55:  * @author Thomas Morgner
  56:  */
  57: public class SimpleSQLReportDataFactory implements ReportDataFactory, Cloneable
  58: {
  59:   private static final Object NULL_TOKEN = new Object();
  60: 
  61:   private static class PreparedStatementCarrier
  62:   {
  63:     private PreparedStatement preparedStatement;
  64:     private String[] parameters;
  65: 
  66:     private PreparedStatementCarrier(final PreparedStatement preparedStatement,
  67:                                     final String[] parameters)
  68:     {
  69:       this.preparedStatement = preparedStatement;
  70:       this.parameters = parameters;
  71:     }
  72: 
  73:     public PreparedStatement getPreparedStatement()
  74:     {
  75:       return preparedStatement;
  76:     }
  77: 
  78:     public String[] getParameters()
  79:     {
  80:       return parameters;
  81:     }
  82:   }
  83: 
  84:   private HashMap preparedStatements;
  85:   private Connection connection;
  86:   private ConnectionProvider connectionProvider;
  87: 
  88:   private boolean labelMapping;
  89:   private static final String COLUMN_NAME_MAPPING_KEY =
  90:           "org.jfree.report.modules.data.sql.ColumnNameMapping";
  91: 
  92:   public SimpleSQLReportDataFactory(final Connection connection)
  93:   {
  94:     this (new StaticConnectionProvider(connection));
  95:   }
  96: 
  97:   public SimpleSQLReportDataFactory(final ConnectionProvider connectionProvider)
  98:   {
  99:     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: }