001: /*
002: * Copyright (C) 2005-2007 JasperSoft http://www.jaspersoft.com
003: *
004: * This program is free software; you can redistribute it and/or modify
005: * it under the terms of the GNU General Public License as published by
006: * the Free Software Foundation; either version 2 of the License, or
007: * (at your option) any later version.
008: *
009: * This program is distributed WITHOUT ANY WARRANTY; and without the
010: * implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
011: * See the GNU General Public License for more details.
012: *
013: * You should have received a copy of the GNU General Public License
014: * along with this program; if not, see http://www.gnu.org/licenses/gpl.txt
015: * or write to:
016: *
017: * Free Software Foundation, Inc.,
018: * 59 Temple Place - Suite 330,
019: * Boston, MA USA 02111-1307
020: *
021: *
022: * SQLFieldsProvider.java
023: *
024: * Created on December 7, 2006, 9:22 AM
025: *
026: * To change this template, choose Tools | Template Manager
027: * and open the template in the editor.
028: */
029:
030: package it.businesslogic.ireport.data;
031:
032: import bsh.Interpreter;
033: import it.businesslogic.ireport.FieldsProvider;
034: import it.businesslogic.ireport.FieldsProviderEditor;
035: import it.businesslogic.ireport.IReportConnection;
036: import it.businesslogic.ireport.gui.ReportQueryDialog;
037: import it.businesslogic.ireport.util.Misc;
038: import java.awt.Component;
039: import java.math.BigDecimal;
040: import java.sql.Connection;
041: import java.sql.PreparedStatement;
042: import java.sql.ResultSet;
043: import java.sql.ResultSetMetaData;
044: import java.sql.Types;
045: import java.util.ArrayList;
046: import java.util.List;
047: import java.util.Map;
048: import javax.swing.JDialog;
049: import javax.swing.JOptionPane;
050: import net.sf.jasperreports.engine.JRDataset;
051: import net.sf.jasperreports.engine.JRException;
052: import net.sf.jasperreports.engine.JRField;
053: import net.sf.jasperreports.engine.JRParameter;
054: import net.sf.jasperreports.engine.design.JRDesignField;
055:
056: /**
057: *
058: * @author gtoffoli
059: */
060: public class SQLFieldsProvider implements FieldsProvider {
061:
062: public static boolean useVisualDesigner = true;
063:
064: static {
065:
066: java.util.Properties p = new java.util.Properties();
067: try {
068: //java.io.InputStream is = SQLFieldsProvider.class.getClass().getResourceAsStream("/it/businesslogic/ireport/data/fieldsprovider.properties");
069: java.io.InputStream is = SQLFieldsProvider.class
070: .getResourceAsStream("/it/businesslogic/ireport/data/fieldsprovider.properties");
071:
072: p.load(is);
073:
074: if (p.getProperty("sql").equals("0")) {
075: useVisualDesigner = false;
076: }
077: } catch (Exception ex) {
078: ex.printStackTrace();
079: }
080:
081: }
082:
083: /** Creates a new instance of SQLFieldsProvider */
084: public SQLFieldsProvider() {
085:
086: }
087:
088: /**
089: * Returns true if the provider supports the {@link #getFields(IReportConnection,JRDataset,Map) getFields}
090: * operation. By returning true in this method the data source provider indicates
091: * that it is able to introspect the data source and discover the available fields.
092: *
093: * @return true if the getFields() operation is supported.
094: */
095: public boolean supportsGetFieldsOperation() {
096: return true;
097: }
098:
099: public JRField[] getFields(IReportConnection irConn,
100: JRDataset reportDataset, Map parameters)
101: throws JRException, UnsupportedOperationException {
102:
103: if (irConn == null || !irConn.isJDBCConnection()) {
104: throw new JRException(
105: "The active connection is not of type JDBC. Activate a JDBC connection first.");
106: }
107:
108: String query = reportDataset.getQuery().getText();
109: String error_msg = "";
110: Connection con = null;
111: PreparedStatement ps = null;
112:
113: try {
114: // look for parameters in the query and replace them with default values.
115: // parameters look something like
116: // $P{QuoteGroupID}
117: // or
118: // $P!{OrderByClause}
119: java.util.List queryParams = new ArrayList();
120: JRParameter[] jrParams = reportDataset.getParameters();
121: for (int k = 0; k < jrParams.length; ++k) {
122: JRParameter parameter = jrParams[k];
123:
124: String p1 = "$P{" + parameter.getName() + "}";
125: String p2 = "$P!{" + parameter.getName() + "}";
126:
127: Object defValue = parameters.get(parameter.getName());
128:
129: int ip1 = query.indexOf(p1);
130: while (ip1 != -1) {
131: // add a query parameter
132:
133: //if( defValue==null ) {
134: // throw new IllegalArgumentException("Please set a " +
135: // "default value for the parameter '" +
136: // parameter.getName() + "'" );
137: //}
138:
139: String before = query.substring(0, ip1);
140: String after = query.substring(ip1 + p1.length());
141: query = before + " ? " + after;
142: queryParams.add(defValue);
143: ip1 = query.indexOf(p1);
144: }
145:
146: int ip2 = query.indexOf(p2);
147: while (ip2 != -1) {
148: // String replacement, Altering the SQL statement.
149: //if( defValue==null ) {
150: // throw new IllegalArgumentException("Please set a " +
151: // "default value for the parameter '"
152: // + parameter.getName() + "'" );
153: //}
154:
155: String before = query.substring(0, ip2);
156: String after = query.substring(ip2 + p2.length());
157: query = before + "" + defValue + "" + after;
158: ip2 = query.indexOf(p2);
159: }
160: }
161:
162: con = irConn.getConnection();
163:
164: ps = con.prepareStatement(query);
165: // for(int pc=0; pc<queryParams.size(); pc++ ) {
166: // ps.setObject(pc+1, queryParams.get(pc) );
167: // }
168:
169: for (int pc = 0; pc < queryParams.size(); pc++) {
170:
171: Object val = queryParams.get(pc);
172: Class parameterType = String.class;
173: if (val != null)
174: parameterType = val.getClass();
175:
176: if (java.lang.Boolean.class
177: .isAssignableFrom(parameterType)) {
178: if (queryParams.get(pc) == null) {
179: ps.setNull(pc + 1, Types.BIT);
180: } else {
181: ps.setBoolean(pc + 1, ((Boolean) queryParams
182: .get(pc)).booleanValue());
183: }
184: } else if (java.lang.Byte.class
185: .isAssignableFrom(parameterType)) {
186: if (queryParams.get(pc) == null) {
187: ps.setNull(pc + 1, Types.TINYINT);
188: } else {
189: ps.setByte(pc + 1, ((Byte) queryParams.get(pc))
190: .byteValue());
191: }
192: } else if (java.lang.Double.class
193: .isAssignableFrom(parameterType)) {
194: if (queryParams.get(pc) == null) {
195: ps.setNull(pc + 1, Types.DOUBLE);
196: } else {
197: ps.setDouble(pc + 1, ((Double) queryParams
198: .get(pc)).doubleValue());
199: }
200: } else if (java.lang.Float.class
201: .isAssignableFrom(parameterType)) {
202: if (queryParams.get(pc) == null) {
203: ps.setNull(pc + 1, Types.FLOAT);
204: } else {
205: ps.setFloat(pc + 1, ((Float) queryParams
206: .get(pc)).floatValue());
207: }
208: } else if (java.lang.Integer.class
209: .isAssignableFrom(parameterType)) {
210: if (queryParams.get(pc) == null) {
211: ps.setNull(pc + 1, Types.INTEGER);
212: } else {
213: ps.setInt(pc + 1, ((Integer) queryParams
214: .get(pc)).intValue());
215: }
216: } else if (java.lang.Long.class
217: .isAssignableFrom(parameterType)) {
218: if (queryParams.get(pc) == null) {
219: ps.setNull(pc + 1, Types.BIGINT);
220: } else {
221: ps.setLong(pc + 1, ((Long) queryParams.get(pc))
222: .longValue());
223: }
224: } else if (java.lang.Short.class
225: .isAssignableFrom(parameterType)) {
226: if (queryParams.get(pc) == null) {
227: ps.setNull(pc + 1, Types.SMALLINT);
228: } else {
229: ps.setShort(pc + 1, ((Short) queryParams
230: .get(pc)).shortValue());
231: }
232: } else if (java.math.BigDecimal.class
233: .isAssignableFrom(parameterType)) {
234: if (queryParams.get(pc) == null) {
235: ps.setNull(pc + 1, Types.DECIMAL);
236: } else {
237: ps.setBigDecimal(pc + 1,
238: (BigDecimal) queryParams.get(pc));
239: }
240: } else if (java.lang.String.class
241: .isAssignableFrom(parameterType)) {
242: if (queryParams.get(pc) == null) {
243: ps.setNull(pc + 1, Types.VARCHAR);
244: } else {
245: ps.setString(pc + 1, queryParams.get(pc)
246: .toString());
247: }
248: } else if (java.sql.Timestamp.class
249: .isAssignableFrom(parameterType)) {
250: if (queryParams.get(pc) == null) {
251: ps.setNull(pc + 1, Types.TIMESTAMP);
252: } else {
253: ps.setTimestamp(pc + 1,
254: (java.sql.Timestamp) queryParams
255: .get(pc));
256: }
257: } else if (java.sql.Time.class
258: .isAssignableFrom(parameterType)) {
259: if (queryParams.get(pc) == null) {
260: ps.setNull(pc + 1, Types.TIME);
261: } else {
262: ps.setTime(pc + 1, (java.sql.Time) queryParams
263: .get(pc));
264: }
265: } else if (java.util.Date.class
266: .isAssignableFrom(parameterType)) {
267: if (queryParams.get(pc) == null) {
268: ps.setNull(pc + 1, Types.DATE);
269: } else {
270: ps.setDate(pc + 1, new java.sql.Date(
271: ((java.util.Date) queryParams.get(pc))
272: .getTime()));
273: }
274: } else {
275: if (queryParams.get(pc) == null) {
276: ps.setNull(pc + 1, Types.JAVA_OBJECT);
277: } else {
278: ps.setObject(pc + 1, queryParams.get(pc));
279: }
280: }
281: }
282:
283: // Some JDBC drivers don't supports this method...
284: try {
285: ps.setFetchSize(0);
286: } catch (Exception e) {
287: }
288:
289: ResultSet rs = ps.executeQuery();
290:
291: //if (in < num) return;
292:
293: ResultSetMetaData rsmd = rs.getMetaData();
294:
295: //if (in < num) return;
296:
297: List columns = new ArrayList();
298: for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
299: JRDesignField field = new JRDesignField();
300: field.setName(rsmd.getColumnLabel(i));
301: field.setValueClassName(Misc.getJdbcTypeClass(rsmd, i));
302: field.setDescription("");
303: columns.add(field);
304: }
305:
306: JRField[] final_fields = new JRField[columns.size()];
307: for (int i = 0; i < final_fields.length; ++i) {
308: final_fields[i] = (JRField) columns.get(i);
309: }
310:
311: return final_fields;
312:
313: } catch (IllegalArgumentException ie) {
314: throw new JRException(ie.getMessage());
315: } catch (NoClassDefFoundError ex) {
316: ex.printStackTrace();
317: error_msg = "NoClassDefFoundError!!\nCheck your classpath!";
318: throw new JRException(error_msg);
319: } catch (java.sql.SQLException ex) {
320: error_msg = "SQL problems:\n" + ex.getMessage();
321: throw new JRException(error_msg);
322: } catch (Exception ex) {
323: ex.printStackTrace();
324: error_msg = "General problem:\n"
325: + ex.getMessage()
326: + "\n\nCheck username and password; is the DBMS active ?!";
327: throw new JRException(error_msg);
328: } catch (Throwable t) {
329: throw new JRException(t.getMessage());
330: } finally {
331: if (ps != null)
332: try {
333: ps.close();
334: } catch (Exception e) {
335: }
336: if (con != null)
337: try {
338: con.close();
339: } catch (Exception e) {
340: }
341: }
342: }
343:
344: public boolean supportsAutomaticQueryExecution() {
345: return true;
346: }
347:
348: public boolean hasQueryDesigner() {
349: return useVisualDesigner;
350: }
351:
352: public boolean hasEditorComponent() {
353: return false;
354: }
355:
356: public String designQuery(IReportConnection con, String query,
357: ReportQueryDialog reportQueryDialog) throws JRException,
358: UnsupportedOperationException {
359: // Start FREE QUERY BUILDER....
360: QueryBuilderDialog qbd = new QueryBuilderDialog(
361: (reportQueryDialog != null) ? reportQueryDialog
362: : new JDialog(), true);
363:
364: if (con.isJDBCConnection()) {
365: qbd.setConnection(con.getConnection());
366: }
367:
368: try {
369:
370: if (query != null && query.length() > 0) {
371: qbd.setQuery(query);
372: }
373: } catch (Throwable ex) {
374: if (reportQueryDialog != null) {
375: reportQueryDialog.getJLabelStatusSQL().setText(
376: "I'm sorry, I'm unable to parse the query...");
377: ex.printStackTrace();
378: }
379: ex.printStackTrace();
380: return null;
381: }
382: qbd.setVisible(true);
383:
384: if (qbd.getDialogResult() == JOptionPane.OK_OPTION) {
385: return qbd.getQuery();
386: }
387: return null;
388: }
389:
390: public FieldsProviderEditor getEditorComponent(
391: ReportQueryDialog reportQueryDialog) {
392: return null;
393: }
394:
395: }
|