001: /*
002: * Copyright (c) 2008, Your Corporation. All Rights Reserved.
003: */
004:
005: package org.pentaho.reportdesigner.crm.report.datasetplugin;
006:
007: import org.jfree.report.DataFactory;
008: import org.jfree.report.DataRow;
009: import org.jfree.report.JFreeReportBoot;
010: import org.jfree.report.ReportDataFactoryException;
011: import org.jfree.report.modules.misc.datafactory.sql.ConnectionProvider;
012: import org.jfree.report.modules.misc.datafactory.sql.SQLParameterLookupParser;
013: import org.jfree.report.modules.misc.tablemodel.ResultSetTableModelFactory;
014: import org.jfree.util.Configuration;
015:
016: import javax.swing.table.TableModel;
017: import java.io.Serializable;
018: import java.sql.CallableStatement;
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Types;
024: import java.util.HashMap;
025:
026: /**
027: * User: Martin
028: * Date: Jan 16, 2008
029: * Time: 5:06:52 PM
030: */
031: @SuppressWarnings({"ALL"})
032: public class SimplePreviewableSQLReportDataFactory implements
033: DataFactory, Cloneable, Serializable {
034:
035: private static class PreparedStatementCarrier {
036: private PreparedStatement preparedStatement;
037: private String[] parameters;
038:
039: protected PreparedStatementCarrier(
040: final PreparedStatement preparedStatement,
041: final String[] parameters) {
042: this .preparedStatement = preparedStatement;
043: this .parameters = parameters;
044: }
045:
046: public PreparedStatement getPreparedStatement() {
047: return preparedStatement;
048: }
049:
050: public String[] getParameters() {
051: return parameters;
052: }
053: }
054:
055: private transient HashMap preparedStatements;
056: private transient Connection connection;
057: private ConnectionProvider connectionProvider;
058:
059: private int maxRows;
060: private boolean limitRows;
061:
062: private boolean labelMapping;
063: private static final String COLUMN_NAME_MAPPING_KEY = "org.jfree.report.modules.data.sql.ColumnNameMapping"; //$NON-NLS-1$
064:
065: public SimplePreviewableSQLReportDataFactory(
066: final ConnectionProvider connectionProvider,
067: boolean limitRows, int maxRows) {
068: if (connectionProvider == null) {
069: throw new NullPointerException();
070: }
071: this .limitRows = limitRows;
072: this .maxRows = maxRows;
073: this .connectionProvider = connectionProvider;
074: final Configuration globalConfig = JFreeReportBoot
075: .getInstance().getGlobalConfig();
076: this .labelMapping = "Label".equals(globalConfig.getConfigProperty //$NON-NLS-1$
077: (
078: SimplePreviewableSQLReportDataFactory.COLUMN_NAME_MAPPING_KEY,
079: "Label")); //$NON-NLS-1$
080: }
081:
082: public boolean isLabelMapping() {
083: return labelMapping;
084: }
085:
086: public void setLabelMapping(final boolean labelMapping) {
087: this .labelMapping = labelMapping;
088: }
089:
090: private synchronized Connection getConnection() throws SQLException {
091: if (connection == null) {
092: connection = connectionProvider.getConnection();
093: }
094: return connection;
095: }
096:
097: private int getBestResultSetType() throws SQLException {
098: final Connection connection = getConnection();
099: final boolean supportsScrollInsensitive = connection
100: .getMetaData().supportsResultSetType(
101: ResultSet.TYPE_SCROLL_INSENSITIVE);
102: final boolean supportsScrollSensitive = connection
103: .getMetaData().supportsResultSetType(
104: ResultSet.TYPE_SCROLL_SENSITIVE);
105:
106: if (supportsScrollInsensitive) {
107: return ResultSet.TYPE_SCROLL_INSENSITIVE;
108: }
109: if (supportsScrollSensitive) {
110: return ResultSet.TYPE_SCROLL_SENSITIVE;
111: }
112: return ResultSet.TYPE_FORWARD_ONLY;
113: }
114:
115: /**
116: * Queries a datasource. The string 'query' defines the name of the query. The Parameterset given here may contain
117: * more data than actually needed.
118: * <p/>
119: * The dataset may change between two calls, do not assume anything!
120: *
121: * @param query
122: * @param parameters
123: * @return
124: */
125: public synchronized TableModel queryData(final String query,
126: final DataRow parameters) throws ReportDataFactoryException {
127: try {
128: if (preparedStatements == null) {
129: preparedStatements = new HashMap();
130: }
131:
132: PreparedStatementCarrier pstmtCarrier = (PreparedStatementCarrier) preparedStatements
133: .get(query);
134: final boolean callableStatementQuery = isCallableStatementQuery(query);
135: if (pstmtCarrier == null) {
136: final SQLParameterLookupParser parser = new SQLParameterLookupParser();
137: final String translatedQuery = parser
138: .translateAndLookup(query);
139: final PreparedStatement pstmt;
140: if (callableStatementQuery
141: || isCallableStatement(query)) {
142: pstmt = getConnection().prepareCall(
143: translatedQuery, getBestResultSetType(),
144: ResultSet.CONCUR_READ_ONLY);
145: } else {
146: pstmt = getConnection().prepareStatement(
147: translatedQuery, getBestResultSetType(),
148: ResultSet.CONCUR_READ_ONLY);
149: }
150: pstmtCarrier = new PreparedStatementCarrier(pstmt,
151: parser.getFields());
152: preparedStatements.put(query, pstmtCarrier);
153: }
154:
155: final PreparedStatement pstmt = pstmtCarrier
156: .getPreparedStatement();
157: pstmt.clearParameters();
158: if (callableStatementQuery) {
159: final CallableStatement callableStatement = (CallableStatement) pstmt;
160: callableStatement.registerOutParameter(0, Types.OTHER);
161: }
162: final String[] params = pstmtCarrier.getParameters();
163: for (int i = 0; i < params.length; i++) {
164: final String param = params[i];
165: final Object pvalue = parameters.get(param);
166: if (pvalue == null) {
167: // this should work, but some driver are known to die here.
168: // they should be fed with setNull(..) instead; something
169: // we cant do as JDK1.2's JDBC does not define it.
170: pstmt.setObject(i + 1, null);
171: } else {
172: pstmt.setObject(i + 1, pvalue);
173: }
174: }
175: if (limitRows) {
176: pstmt.setMaxRows(maxRows);
177: }
178: final ResultSet res = pstmt.executeQuery();
179: return ResultSetTableModelFactory.getInstance()
180: .createTableModel(res);
181: } catch (Exception e) {
182: throw new ReportDataFactoryException(
183: "Failed at query: " + query, e); //$NON-NLS-1$
184: }
185: }
186:
187: private boolean isCallableStatement(final String query) {
188: int state = 0;
189: final char[] chars = query.toCharArray();
190: final int length = query.length();
191: for (int i = 0; i < length; i++) {
192: final char c = chars[i];
193: if (Character.isWhitespace(c)) {
194: if (state == 5) {
195: return true;
196: }
197: } else if ('{' == c && state == 0) {
198: state = 1;
199: } else if (('c' == c || 'C' == c) && state == 1) {
200: state = 2;
201: } else if (('a' == c || 'A' == c) && state == 2) {
202: state = 3;
203: } else if (('l' == c || 'L' == c) && state == 4) {
204: state = 4;
205: } else if (('l' == c || 'L' == c) && state == 5) {
206: state = 5;
207: } else {
208: if (state == 5) {
209: return true;
210: }
211: return false;
212: }
213: }
214: return false;
215: }
216:
217: private boolean isCallableStatementQuery(final String query) {
218: int state = 0;
219: final char[] chars = query.toCharArray();
220: final int length = query.length();
221: for (int i = 0; i < length; i++) {
222: final char c = chars[i];
223: if (Character.isWhitespace(c)) {
224: if (state == 7) {
225: return true;
226: }
227: } else if ('{' == c && state == 0) {
228: state = 1;
229: } else if ('?' == c && state == 1) {
230: state = 2;
231: } else if ('=' == c && state == 2) {
232: state = 3;
233: } else if (('c' == c || 'C' == c) && state == 3) {
234: state = 4;
235: } else if (('a' == c || 'A' == c) && state == 4) {
236: state = 5;
237: } else if (('l' == c || 'L' == c) && state == 5) {
238: state = 6;
239: } else if (('l' == c || 'L' == c) && state == 6) {
240: state = 7;
241: } else {
242: if (state == 7) {
243: return true;
244: }
245: return false;
246: }
247: }
248: return false;
249: }
250:
251: public void open() {
252:
253: }
254:
255: public synchronized void close() {
256: if (connection == null) {
257: return;
258: }
259:
260: try {
261: connection.close();
262: } catch (SQLException e) {
263: // we tried our very best ..
264: }
265: connection = null;
266: }
267:
268: /**
269: * Derives a freshly initialized report data factory, which is independend of the original data factory. Opening or
270: * Closing one data factory must not affect the other factories.
271: *
272: * @return
273: */
274: public DataFactory derive() {
275: try {
276: return (DataFactory) clone();
277: } catch (CloneNotSupportedException e) {
278: // this should not happen ..
279: throw new IllegalStateException("Clone failed?"); //$NON-NLS-1$
280: }
281: }
282:
283: public Object clone() throws CloneNotSupportedException {
284: final SimplePreviewableSQLReportDataFactory dataFactory = (SimplePreviewableSQLReportDataFactory) super
285: .clone();
286: dataFactory.connection = null;
287: dataFactory.preparedStatements = null;
288: return dataFactory;
289: }
290:
291: public ConnectionProvider getConnectionProvider() {
292: return connectionProvider;
293: }
294:
295: }
|