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    }