001: package com.jat.integration.db;
002:
003: import java.sql.CallableStatement;
004: import java.sql.Connection;
005: import java.sql.PreparedStatement;
006: import java.sql.ResultSet;
007: import java.sql.ResultSetMetaData;
008: import java.sql.SQLException;
009: import java.util.Enumeration;
010: import java.util.Vector;
011:
012: import com.jat.business.BusinessObjectProperties;
013: import com.jat.business.BusinessObjectPropertyList;
014: import com.jat.business.MultiBusinessObjectProperties;
015: import com.jat.business.MultiBusinessObjectProperties.DataRequest;
016: import com.jat.core.log.LogManager;
017: import com.jat.integration.GenericDataSource;
018: import com.jat.integration.GenericOperationDefinition;
019: import com.jat.integration.IntegrationException;
020:
021: /**
022: * <p>Title: JAT</p>
023: * <p>Description: This class implements a generic access to database data source.
024: * It contain methods to execute sql statements such as select, update, insert and stored procedure.</p>
025: * <p><b>Query configuration:</b><br/>
026: * For <u>each query</u> you have to define:
027: * <ul>
028: * <li><b>name</b> the name of the query</li>
029: * <li><b>value</b> the value of the query (e.g. sql)</li>
030: * <li><b>type</b> (<u>optional</u>) the type of the query (see {@link com.jat.integration.db.QueryDefinition} costant field values).
031: * <br/>By default the type of the query is automatically calculated</li>
032: * <li><b>response_plugin</b> (<u>optional</u>) is the class name of an istance of {@link com.jat.integration.db.plugin.CheckReturnPlugin} class
033: * that checks the response of an insert/modify query or of a stored procedure: if an exception is catched, a rollback will be performed</li>
034: * <li>the <i>list of fields</i> (<u>optional</u>) of a query with parameters.
035: * <br/><i>Note that the number of fields must be exactly as the number of parameters (symbol '?') of the value of query</i></li>
036: * </ul>
037: * For <u>each field</u> you have to define:
038: * <ul>
039: * <li><b>name</b> the name of the parameter.
040: * The field must be as parameter in the {@link com.jat.business.BusinessObjectProperties} object when a query is called with <i>getData</i> or <i>execute</i> method (see {@link com.jat.integration.DataSource})</li>
041: * <li><b>type</b> (<u>optional</u>) the type of the parameters. It is a number (see java.sql.Types costant field values)
042: * <br/>Is possible to use different type for Array of String or Array of Integer (see {@link com.jat.integration.db.FieldDefinition} costant field values)</li>
043: * <li><b>plugin</b> (<u>optional</u>) is the class name of an istance of {@link com.jat.integration.db.plugin.ConverterPlugin} class
044: * that converts or obtains the value of the field</li>
045: * <li><b>output</b> (<u>optional</u>, default is <i>false</i>) denote if the field is an output parameter (value: <i>true</i>).
046: * <br/><i>Use this option only for stored procedure or functions</i></li>
047: * </ul>
048: * <i>Example:</i>
049: * <blockquote>
050: * # a simple query<br/>
051: * query1.name = <i>myQueryName</i><br/>
052: * query1.value = <i>select * from dual</i><br/></br>
053: * <br/># a simple query with parameters<br/>
054: * query2.name = <i>myQueryName2</i><br/>
055: * query2.value = <i>select * from myTable where firstField = ? and secondField = ?</i><br/>
056: * query2.field1.name = <i>myFirstParameter</i><br/>
057: * query2.field2.name = <i>mySecondParameter</i><br/>
058: * <br/># calling a stored procedure with an input parameter and an output parameter<br/>
059: * query3.name = <i>callMyStoredProcedure</i><br/>
060: * query3.value = <i>{ call MY_STORED_PROCEDURE(?,?)}</i><br/>
061: * query3.field1.name = <i>myInputParameter</i><br/>
062: * query3.field2.name = <i>myOutputParameter</i><br/>
063: * query3.field2.output = <i>true</i>
064: * </blockquote>
065: * </p>
066: * <p>Copyright: Copyright (c) 2004 -2005 Stefano Fratini (stefano.fratini@gmail.com)</p>
067: * <p>Distributed under the terms of the GNU Lesser General Public License, v2.1 or later</p>
068: * @author stf
069: * @version 1.2
070: * @since 1.2
071: * @see com.jat.integration.DataSource
072: * @see com.jat.integration.db.QueryDefinition
073: */
074:
075: public abstract class GenericDatabaseDataSource extends
076: GenericDataSource {
077:
078: protected abstract Connection getConnection() throws SQLException;
079:
080: public GenericOperationDefinition getEmptyOperationDefinition() {
081: return new QueryDefinition();
082: }
083:
084: public BusinessObjectPropertyList getData(String queryName,
085: BusinessObjectProperties parameters)
086: throws IntegrationException {
087: QueryDefinition qf = (QueryDefinition) this
088: .getOperation(queryName);
089: if (!qf.isType(QueryDefinition.SELECT_TYPE))
090: throw new IntegrationException(
091: "The query '"
092: + qf.getName()
093: + "' is not an select query and cannot be execute as select");
094: return this .select(qf, parameters);
095: }
096:
097: public BusinessObjectProperties execute(String queryName,
098: BusinessObjectProperties parameters)
099: throws IntegrationException {
100: Connection con = null;
101: try {
102: con = this .getConnection();
103: con.setAutoCommit(false);
104: BusinessObjectProperties ret = execute(con, queryName,
105: parameters);
106: this .commit(con);
107: return ret;
108: } catch (Exception ex) {
109: this .rollback(con);
110: throw new IntegrationException(this .getClass().getName()
111: + "::execute: exception: " + ex);
112: } finally {
113: this .close(con);
114: }
115: }
116:
117: public BusinessObjectPropertyList execute(
118: MultiBusinessObjectProperties multi)
119: throws IntegrationException {
120: long time = System.currentTimeMillis();
121: boolean error = false;
122: Connection con = null;
123: try {
124: BusinessObjectPropertyList ret = new BusinessObjectPropertyList();
125: con = this .getConnection();
126: con.setAutoCommit(false);
127: for (Enumeration e = multi.elements(); e.hasMoreElements();) {
128: DataRequest dr = (DataRequest) e.nextElement();
129: ret.addElement(execute(con, dr.getName(), dr
130: .getProperties()));
131: }
132: this .commit(con);
133: return ret;
134: } catch (Exception ex) {
135: error = true;
136: this .rollback(con);
137: throw new IntegrationException(this .getClass().getName()
138: + "::execute: exception: " + ex);
139: } finally {
140: this .close(con);
141: LogManager.sendTime(this .getClass().getName()
142: + "::execute multi query", System
143: .currentTimeMillis()
144: - time, error);
145: }
146: }
147:
148: protected BusinessObjectProperties execute(Connection con,
149: String queryName, BusinessObjectProperties parameters)
150: throws IntegrationException {
151: QueryDefinition qf = (QueryDefinition) this
152: .getOperation(queryName);
153: BusinessObjectProperties data = null;
154: if (qf.isType(QueryDefinition.INSERT_TYPE)
155: || qf.isType(QueryDefinition.UPDATE_TYPE))
156: data = this .executeQuery(con, qf, parameters);
157: else if (qf.isType(QueryDefinition.PROCEDURE_TYPE))
158: data = this .callProcedure(con, qf, parameters);
159: else
160: throw new IntegrationException("The query '" + qf.getName()
161: + "' is not an executable query: type: "
162: + qf.getType());
163: LogManager.sendDebug(this .getClass().getName()
164: + "::execute: response for '" + qf.getName() + "': "
165: + data);
166: data = qf.checkResponse(data);
167: return data;
168: }
169:
170: protected PreparedStatement getPreparedStatement(
171: QueryDefinition qf, BusinessObjectProperties parameters,
172: Connection con) throws IntegrationException, SQLException {
173: LogManager.sendDebug(this .getClass().getName()
174: + "::getPreparedStatement: start");
175: String query = qf.getValue(parameters);
176: PreparedStatement ps = con.prepareStatement(query);
177: int index = 1;
178: Vector fields = (Vector) qf.getFields();
179: if (fields != null && fields.size() > 0) {
180: if (parameters == null)
181: throw new SQLException(
182: "Parameters are required to execute this query");
183: for (Enumeration e = fields.elements(); e.hasMoreElements();) {
184: FieldDefinition field = (FieldDefinition) e
185: .nextElement();
186: if (!field.isArrayType()) {
187: Object par = field.convertValue(parameters
188: .get(field.getName()));
189: try {
190: LogManager.sendDebug(this .getClass().getName()
191: + "::getPreparedStatement: query '"
192: + qf.getName() + "' setting " + index
193: + "th parameter '" + field.getName()
194: + "': " + par);
195: if (field.getType() != -1)
196: ps.setObject(index++, par, field.getType());
197: else
198: ps.setObject(index++, par);
199: } catch (SQLException sqlEx) {
200: throw new IntegrationException(
201: this .getClass().getName()
202: + "::getPreparedStatement: exception for query '"
203: + qf.getName() + "' setting "
204: + (index - 1)
205: + "th parameter '"
206: + field.getName() + "': "
207: + sqlEx);
208: }
209: }
210: }
211: }
212: LogManager.sendDebug(this .getClass().getName()
213: + "::getPreparedStatement: end");
214: return ps;
215: }
216:
217: protected BusinessObjectPropertyList select(QueryDefinition qf,
218: BusinessObjectProperties parameters)
219: throws IntegrationException {
220: Connection con = null;
221: PreparedStatement stmt = null;
222: ResultSet rs = null;
223: boolean error = false;
224: long time = System.currentTimeMillis();
225: try {
226: con = getConnection();
227: stmt = this .getPreparedStatement(qf, parameters, con);
228: rs = stmt.executeQuery();
229: return this .getRows(rs);
230: } catch (Exception ex) {
231: error = true;
232: LogManager.sendDebug(this .getClass().getName()
233: + "::select: exception for '" + qf.getName()
234: + "': " + ex);
235: throw new IntegrationException(ex.toString());
236: } finally {
237: try {
238: stmt.close();
239: } catch (Exception ignored) {
240: }
241: try {
242: rs.close();
243: } catch (Exception ignored) {
244: }
245: this .close(con);
246: LogManager.sendTime(this .getClass().getName()
247: + "::select: " + qf.getValue(), System
248: .currentTimeMillis()
249: - time, error);
250: }
251: }
252:
253: protected BusinessObjectPropertyList getRows(ResultSet rs)
254: throws SQLException {
255: BusinessObjectPropertyList ret = new BusinessObjectPropertyList();
256: ResultSetMetaData rsmd = rs.getMetaData();
257: int numberOfColumns = rsmd.getColumnCount();
258: while (rs.next()) {
259: BusinessObjectProperties data = new BusinessObjectProperties();
260: for (int i = 1; i <= numberOfColumns; i++) {
261: String columnName = rsmd.getColumnName(i);
262: Object rsValue = rs.getObject(i);
263: if (rsValue != null) {
264: data.put(columnName, rsValue);
265: }
266: }
267: ret.addElement(data);
268: }
269: return ret;
270: }
271:
272: protected BusinessObjectProperties executeQuery(Connection con,
273: QueryDefinition qf, BusinessObjectProperties parameters)
274: throws IntegrationException {
275: LogManager.sendDebug(this .getClass().getName()
276: + "::executeQuery: for " + qf.getName());
277: PreparedStatement stmt = null;
278: ResultSet rs = null;
279: long time = System.currentTimeMillis();
280: boolean error = false;
281: try {
282: stmt = this .getPreparedStatement(qf, parameters, con);
283: boolean result = stmt.execute();
284: BusinessObjectProperties ret = new BusinessObjectProperties();
285: ret.put("RESULT", new Boolean(result));
286: ret.put("UPDATE_COUNT", new Integer(stmt.getUpdateCount()));
287: return ret;
288: } catch (SQLException ex) {
289: error = true;
290: LogManager.sendDebug(this .getClass().getName()
291: + "::executeQuery: exception for '" + qf.getName()
292: + "': " + ex);
293: throw new IntegrationException(ex.toString());
294: } finally {
295: try {
296: rs.close();
297: } catch (Exception ignored) {
298: }
299: try {
300: stmt.close();
301: } catch (Exception ignored) {
302: }
303: LogManager.sendTime(this .getClass().getName()
304: + "::executeQuery: " + qf.getValue(), System
305: .currentTimeMillis()
306: - time, error);
307: }
308: }
309:
310: protected CallableStatement getCallableStatement(
311: QueryDefinition qf, BusinessObjectProperties parameters,
312: Connection con) throws IntegrationException, SQLException {
313: CallableStatement ps = con.prepareCall(qf.getValue());
314: int index = 1;
315: Vector fields = (Vector) qf.getFields();
316: if (fields != null && fields.size() > 0) {
317: if (parameters == null)
318: throw new SQLException(
319: "Parameters are required to execute '"
320: + qf.getName() + "'");
321: for (Enumeration e = fields.elements(); e.hasMoreElements();) {
322: FieldDefinition field = (FieldDefinition) e
323: .nextElement();
324: Object par = field.convertValue(parameters.get(field
325: .getName()));
326: try {
327: LogManager.sendDebug(this .getClass().getName()
328: + "::getCallableStatement: procedure '"
329: + qf.getName() + "' setting " + index
330: + "th parameter '" + field.getName()
331: + "': " + par);
332: if (!field.isOutput()) {
333: if (field.getType() != -1)
334: ps.setObject(index++, par, field.getType());
335: else
336: ps.setObject(index++, par);
337: } else {
338: ps.registerOutParameter(index++, field
339: .getType());
340: }
341: } catch (SQLException sqlEx) {
342: throw new IntegrationException(
343: this .getClass().getName()
344: + "::getCallableStatement: exception for procedure '"
345: + qf.getName() + "' setting "
346: + (index - 1) + "th parameter '"
347: + field.getName() + "': " + sqlEx);
348: }
349: }
350: }
351: return ps;
352: }
353:
354: protected BusinessObjectProperties callProcedure(Connection con,
355: QueryDefinition qf, BusinessObjectProperties parameters)
356: throws IntegrationException {
357: LogManager.sendDebug(this .getClass().getName()
358: + "::callProcedure: " + qf.getName());
359: CallableStatement stmt = null;
360: ResultSet rs = null;
361: long time = System.currentTimeMillis();
362: boolean error = false;
363: try {
364: stmt = this .getCallableStatement(qf, parameters, con);
365: stmt.execute();
366: BusinessObjectProperties ret = new BusinessObjectProperties();
367: for (int i = 0; i < qf.getFields().size(); i++) {
368: FieldDefinition field = (FieldDefinition) qf
369: .getFields().elementAt(i);
370: if (field.isOutput()) {
371: ret.put(field.getName(), stmt.getObject(i + 1));
372: }
373: }
374: return ret;
375: } catch (SQLException ex) {
376: LogManager.sendDebug(this .getClass().getName()
377: + "::callProcedure: exception for '" + qf.getName()
378: + "': " + ex);
379: error = true;
380: throw new IntegrationException(ex.toString());
381: } finally {
382: try {
383: rs.close();
384: } catch (Exception ignored) {
385: }
386: try {
387: stmt.close();
388: } catch (Exception ignored) {
389: }
390: LogManager.sendTime(this .getClass().getName()
391: + "::callProcedure: " + qf.getValue(), System
392: .currentTimeMillis()
393: - time, error);
394: }
395: }
396:
397: protected final void close(Connection con) {
398: try {
399: con.close();
400: } catch (Exception ex) {
401: LogManager.sendWarning(this .getClass().getName()
402: + "::close: exception closing connection: " + ex);
403: }
404: }
405:
406: protected final void rollback(Connection con) {
407: try {
408: con.rollback();
409: } catch (Exception ex) {
410: LogManager.sendWarning(this .getClass().getName()
411: + "::rollback: exception rollbacking connection: "
412: + ex);
413: }
414: }
415:
416: protected final void commit(Connection con) throws SQLException {
417: try {
418: con.commit();
419: } catch (SQLException ex) {
420: LogManager
421: .sendWarning(this .getClass().getName()
422: + "::commit: exception commiting connection: "
423: + ex);
424: throw ex;
425: }
426: }
427: }
|