001: /*
002: * ============================================================================
003: * GNU Lesser General Public License
004: * ============================================================================
005: *
006: * JasperReports - Free Java report-generating library.
007: * Copyright (C) 2001-2006 JasperSoft Corporation http://www.jaspersoft.com
008: *
009: * This library is free software; you can redistribute it and/or
010: * modify it under the terms of the GNU Lesser General Public
011: * License as published by the Free Software Foundation; either
012: * 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,
015: * but WITHOUT ANY WARRANTY; without even the implied warranty of
016: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
017: * Lesser General Public License for more details.
018: *
019: * You should have received a copy of the GNU Lesser General Public
020: * License along with this library; if not, write to the Free Software
021: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
022: *
023: * JasperSoft Corporation
024: * 303 Second Street, Suite 450 North
025: * San Francisco, CA 94107
026: * http://www.jaspersoft.com
027: */
028: package net.sf.jasperreports.engine.query;
029:
030: import java.lang.reflect.Array;
031: import java.math.BigDecimal;
032: import java.sql.Connection;
033: import java.sql.PreparedStatement;
034: import java.sql.ResultSet;
035: import java.sql.SQLException;
036: import java.sql.Types;
037: import java.util.Collection;
038: import java.util.Iterator;
039: import java.util.List;
040: import java.util.Map;
041:
042: import net.sf.jasperreports.engine.JRDataSource;
043: import net.sf.jasperreports.engine.JRDataset;
044: import net.sf.jasperreports.engine.JRException;
045: import net.sf.jasperreports.engine.JRParameter;
046: import net.sf.jasperreports.engine.JRResultSetDataSource;
047: import net.sf.jasperreports.engine.JRRuntimeException;
048: import net.sf.jasperreports.engine.JRValueParameter;
049: import net.sf.jasperreports.engine.util.JRProperties;
050:
051: import org.apache.commons.logging.Log;
052: import org.apache.commons.logging.LogFactory;
053:
054: /**
055: * JDBC query executer for SQL queries.
056: * <p/>
057: * This query executer implementation offers built-in support for SQL queries.
058: *
059: * @author Teodor Danciu (teodord@users.sourceforge.net)
060: * @version $Id: JRJdbcQueryExecuter.java 1719 2007-05-07 08:54:24Z lucianc $
061: */
062: public class JRJdbcQueryExecuter extends JRAbstractQueryExecuter {
063: private static final Log log = LogFactory
064: .getLog(JRJdbcQueryExecuter.class);
065:
066: protected static final String CLAUSE_ID_IN = "IN";
067: protected static final String CLAUSE_ID_NOTIN = "NOTIN";
068:
069: private Connection connection;
070:
071: /**
072: * The statement used to fire the query.
073: */
074: private PreparedStatement statement;
075:
076: private ResultSet resultSet;
077:
078: public JRJdbcQueryExecuter(JRDataset dataset, Map parameters) {
079: super (dataset, parameters);
080:
081: connection = (Connection) getParameterValue(JRParameter.REPORT_CONNECTION);
082:
083: if (connection == null) {
084: if (log.isWarnEnabled())
085: log
086: .warn("The supplied java.sql.Connection object is null.");
087: }
088:
089: registerFunctions();
090:
091: parseQuery();
092: }
093:
094: /**
095: * Registers built-in {@link JRClauseFunction clause functions}.
096: * @see #registerFunctions()
097: * @see #appendClauseChunk(StringBuffer, String[])
098: */
099: protected void registerFunctions() {
100: registerClauseFunction(CLAUSE_ID_IN, JRSqlInClause.instance());
101: registerClauseFunction(CLAUSE_ID_NOTIN, JRSqlNotInClause
102: .instance());
103: }
104:
105: protected String getParameterReplacement(String parameterName) {
106: return "?";
107: }
108:
109: /* (non-Javadoc)
110: * @see net.sf.jasperreports.engine.util.JRQueryExecuter#createDatasource()
111: */
112: public JRDataSource createDatasource() throws JRException {
113: JRDataSource dataSource = null;
114:
115: createStatement();
116:
117: if (statement != null) {
118: try {
119: Integer reportMaxCount = (Integer) getParameterValue(JRParameter.REPORT_MAX_COUNT);
120: if (reportMaxCount != null) {
121: statement.setMaxRows(reportMaxCount.intValue());
122: }
123:
124: resultSet = statement.executeQuery();
125:
126: dataSource = new JRResultSetDataSource(resultSet);
127: } catch (SQLException e) {
128: throw new JRException(
129: "Error executing SQL statement for : "
130: + dataset.getName(), e);
131: }
132: }
133:
134: return dataSource;
135: }
136:
137: private void createStatement() throws JRException {
138: String queryString = getQueryString();
139:
140: if (log.isDebugEnabled()) {
141: log.debug("SQL query string: " + queryString);
142: }
143:
144: if (connection != null && queryString != null
145: && queryString.trim().length() > 0) {
146: try {
147: statement = connection.prepareStatement(queryString);
148:
149: int fetchSize = JRProperties
150: .getIntegerProperty(
151: dataset.getPropertiesMap(),
152: JRJdbcQueryExecuterFactory.PROPERTY_JDBC_FETCH_SIZE,
153: 0);
154: if (fetchSize != 0) {
155: statement.setFetchSize(fetchSize);
156: }
157:
158: List parameterNames = getCollectedParameters();
159: if (!parameterNames.isEmpty()) {
160: for (int i = 0, paramIdx = 1; i < parameterNames
161: .size(); i++) {
162: QueryParameter queryParameter = (QueryParameter) parameterNames
163: .get(i);
164: if (queryParameter.isMulti()) {
165: paramIdx += setStatementMultiParameters(
166: paramIdx, queryParameter.getName());
167: } else {
168: setStatementParameter(paramIdx,
169: queryParameter.getName());
170: ++paramIdx;
171: }
172: }
173: }
174: } catch (SQLException e) {
175: throw new JRException(
176: "Error preparing statement for executing the report query : "
177: + "\n\n" + queryString + "\n\n", e);
178: }
179: }
180: }
181:
182: protected void setStatementParameter(int parameterIndex,
183: String parameterName) throws SQLException {
184: JRValueParameter parameter = getValueParameter(parameterName);
185: Class clazz = parameter.getValueClass();
186: Object parameterValue = parameter.getValue();
187:
188: if (log.isDebugEnabled()) {
189: log.debug("Parameter #" + parameterIndex + " ("
190: + parameterName + " of type " + clazz.getName()
191: + "): " + parameterValue);
192: }
193:
194: setStatementParameter(parameterIndex, clazz, parameterValue);
195: }
196:
197: protected int setStatementMultiParameters(int parameterIndex,
198: String parameterName) throws SQLException {
199: Object paramValue = getParameterValue(parameterName);
200:
201: int count;
202: if (paramValue.getClass().isArray()) {
203: int arrayCount = Array.getLength(paramValue);
204: for (count = 0; count < arrayCount; ++count) {
205: Object value = Array.get(paramValue, count);
206: setStatementMultiParameter(parameterIndex + count,
207: parameterName, count, value);
208: }
209: } else if (paramValue instanceof Collection) {
210: Collection values = (Collection) paramValue;
211: count = 0;
212: for (Iterator it = values.iterator(); it.hasNext(); ++count) {
213: Object value = it.next();
214: setStatementMultiParameter(parameterIndex + count,
215: parameterName, count, value);
216: }
217: } else {
218: throw new JRRuntimeException(
219: "Multi parameter value is not array nor collection.");
220: }
221:
222: return count;
223: }
224:
225: protected void setStatementMultiParameter(int parameterIndex,
226: String parameterName, int valueIndex, Object value)
227: throws SQLException {
228: if (value == null) {
229: throw new JRRuntimeException(
230: "Multi parameters cannot contain null values.");
231: }
232:
233: Class type = value.getClass();
234:
235: if (log.isDebugEnabled()) {
236: log.debug("Parameter #" + parameterIndex + " ("
237: + parameterName + "[" + valueIndex + "] of type "
238: + type.getName() + "): " + value);
239: }
240:
241: setStatementParameter(parameterIndex, type, value);
242: }
243:
244: protected void setStatementParameter(int parameterIndex,
245: Class parameterType, Object parameterValue)
246: throws SQLException {
247: if (java.lang.Boolean.class.isAssignableFrom(parameterType)) {
248: if (parameterValue == null) {
249: statement.setNull(parameterIndex, Types.BIT);
250: } else {
251: statement.setBoolean(parameterIndex,
252: ((Boolean) parameterValue).booleanValue());
253: }
254: } else if (java.lang.Byte.class.isAssignableFrom(parameterType)) {
255: if (parameterValue == null) {
256: statement.setNull(parameterIndex, Types.TINYINT);
257: } else {
258: statement.setByte(parameterIndex,
259: ((Byte) parameterValue).byteValue());
260: }
261: } else if (java.lang.Double.class
262: .isAssignableFrom(parameterType)) {
263: if (parameterValue == null) {
264: statement.setNull(parameterIndex, Types.DOUBLE);
265: } else {
266: statement.setDouble(parameterIndex,
267: ((Double) parameterValue).doubleValue());
268: }
269: } else if (java.lang.Float.class
270: .isAssignableFrom(parameterType)) {
271: if (parameterValue == null) {
272: statement.setNull(parameterIndex, Types.FLOAT);
273: } else {
274: statement.setFloat(parameterIndex,
275: ((Float) parameterValue).floatValue());
276: }
277: } else if (java.lang.Integer.class
278: .isAssignableFrom(parameterType)) {
279: if (parameterValue == null) {
280: statement.setNull(parameterIndex, Types.INTEGER);
281: } else {
282: statement.setInt(parameterIndex,
283: ((Integer) parameterValue).intValue());
284: }
285: } else if (java.lang.Long.class.isAssignableFrom(parameterType)) {
286: if (parameterValue == null) {
287: statement.setNull(parameterIndex, Types.BIGINT);
288: } else {
289: statement.setLong(parameterIndex,
290: ((Long) parameterValue).longValue());
291: }
292: } else if (java.lang.Short.class
293: .isAssignableFrom(parameterType)) {
294: if (parameterValue == null) {
295: statement.setNull(parameterIndex, Types.SMALLINT);
296: } else {
297: statement.setShort(parameterIndex,
298: ((Short) parameterValue).shortValue());
299: }
300: } else if (java.math.BigDecimal.class
301: .isAssignableFrom(parameterType)) {
302: if (parameterValue == null) {
303: statement.setNull(parameterIndex, Types.DECIMAL);
304: } else {
305: statement.setBigDecimal(parameterIndex,
306: (BigDecimal) parameterValue);
307: }
308: } else if (java.lang.String.class
309: .isAssignableFrom(parameterType)) {
310: if (parameterValue == null) {
311: statement.setNull(parameterIndex, Types.VARCHAR);
312: } else {
313: statement.setString(parameterIndex, parameterValue
314: .toString());
315: }
316: } else if (java.sql.Timestamp.class
317: .isAssignableFrom(parameterType)) {
318: if (parameterValue == null) {
319: statement.setNull(parameterIndex, Types.TIMESTAMP);
320: } else {
321: statement.setTimestamp(parameterIndex,
322: (java.sql.Timestamp) parameterValue);
323: }
324: } else if (java.sql.Time.class.isAssignableFrom(parameterType)) {
325: if (parameterValue == null) {
326: statement.setNull(parameterIndex, Types.TIME);
327: } else {
328: statement.setTime(parameterIndex,
329: (java.sql.Time) parameterValue);
330: }
331: } else if (java.util.Date.class.isAssignableFrom(parameterType)) {
332: if (parameterValue == null) {
333: statement.setNull(parameterIndex, Types.DATE);
334: } else {
335: statement.setDate(parameterIndex, new java.sql.Date(
336: ((java.util.Date) parameterValue).getTime()));
337: }
338: } else {
339: if (parameterValue == null) {
340: statement.setNull(parameterIndex, Types.JAVA_OBJECT);
341: } else {
342: statement.setObject(parameterIndex, parameterValue);
343: }
344: }
345: }
346:
347: /* (non-Javadoc)
348: * @see net.sf.jasperreports.engine.util.JRQueryExecuter#close()
349: */
350: public synchronized void close() {
351: if (resultSet != null) {
352: try {
353: resultSet.close();
354: } catch (SQLException e) {
355: log.error("Error while closing result set.", e);
356: } finally {
357: resultSet = null;
358: }
359: }
360:
361: if (statement != null) {
362: try {
363: statement.close();
364: } catch (SQLException e) {
365: log.error("Error while closing statement.", e);
366: } finally {
367: statement = null;
368: }
369: }
370: }
371:
372: /* (non-Javadoc)
373: * @see net.sf.jasperreports.engine.util.JRQueryExecuter#cancelQuery()
374: */
375: public synchronized boolean cancelQuery() throws JRException {
376: if (statement != null) {
377: try {
378: statement.cancel();
379: return true;
380: } catch (Throwable t) {
381: throw new JRException("Error cancelling SQL statement",
382: t);
383: }
384: }
385:
386: return false;
387: }
388:
389: }
|