001: package com.xoetrope.service.data;
002:
003: import com.xoetrope.service.xml.DocumentHelper;
004: import java.io.StringReader;
005: import java.sql.ResultSet;
006: import java.sql.ResultSetMetaData;
007: import java.sql.Statement;
008: import java.util.Hashtable;
009: import net.xoetrope.xui.data.XModelHelper;
010: import net.xoetrope.optional.data.XOptionalDataSource;
011: import net.xoetrope.optional.data.sql.ConnectionObject;
012: import net.xoetrope.optional.data.sql.NamedConnectionManager;
013: import net.xoetrope.optional.service.ServiceContext;
014: import net.xoetrope.optional.service.ServiceProxy;
015: import net.xoetrope.optional.service.ServiceProxyArgs;
016: import net.xoetrope.optional.service.ServiceProxyException;
017: import net.xoetrope.optional.service.XRouteManager;
018: import net.xoetrope.optional.data.sql.SqlHelper;
019: import net.xoetrope.xml.XmlElement;
020: import net.xoetrope.xml.XmlSource;
021: import net.xoetrope.xui.XProject;
022: import net.xoetrope.xui.XProjectManager;
023: import net.xoetrope.xui.data.XBaseModel;
024: import net.xoetrope.xui.data.XModel;
025: import org.w3c.dom.Document;
026: import org.w3c.dom.Element;
027:
028: /**
029: * ServiceProxy class which needs to be subclassed in order to retrieve a
030: * specified or generated query on a database connection.
031: *
032: * <p> Copyright (c) Xoetrope Ltd., 2001-2006, This software is licensed under
033: * the GNU Public License (GPL), please see license.txt for more details. If
034: * you make commercial use of this software you must purchase a commercial
035: * license from Xoetrope.</p>
036: * <p> $Revision: 1.17 $</p>
037: */
038: public class DataRetrievalService extends ServiceProxy {
039: /**
040: * This parameter stores the name of the query which is to be called or generated
041: */
042: public static final String ARG_NAME_QUERYNAME = "dataretrievalservice:queryname";
043:
044: /**
045: * This parameter stores the contents of the resultset
046: */
047: public static final String ARG_NAME_RESULTSET = "dataretrievalservice:resultset";
048:
049: /**
050: * This parameter stores the contents of the query fields
051: */
052: public static final String ARG_NAME_QUERYFIELDS = "dataretrievalservice:queryfieldsparam";
053:
054: /**
055: * This parameter should be concatenated with the zero based index of the
056: * parameter being set
057: */
058: public static final String ARG_NAME_MODEL_RESTORE_PATH = "dataretrievalservice:paramMdlRestorePath";
059:
060: public static final String TYPE_PROVIDED = "provided";
061: public static final String TYPE_GENERATED = "generated";
062: protected XModel rootModel;
063: protected char quote = '"';
064: protected String connectionName;
065:
066: protected Document doc;
067: protected Element topElement;
068:
069: protected XProject currentProject = XProjectManager
070: .getCurrentProject();
071:
072: /**
073: * Constructor gets a reference to the root model.
074: */
075: public DataRetrievalService() {
076: rootModel = currentProject.getModel();
077: }
078:
079: /**
080: * Call this proxy with the specified arguments
081: * @return the result of the call
082: * @param context The ServiceContext contain pass and return parameters
083: * @param method the name of the service being called
084: * @throws net.xoetrope.optional.service.ServiceProxyException Throw an exception if there is a problem with the call
085: */
086: public Object call(String method, ServiceContext context)
087: throws ServiceProxyException {
088: ServiceProxyArgs args = context.getArgs();
089: try {
090: status = STARTED;
091: if (side == XRouteManager.CLIENT_SIDE) {
092: callNextProxy(method, context, null);
093: String mdlPath = (String) args
094: .getPassParam(ARG_NAME_MODEL_RESTORE_PATH);
095: XModel restoreMdl = XProjectManager.getModel();
096: if (mdlPath != null)
097: restoreMdl = (XModel) restoreMdl.get(mdlPath);
098: if (!context.hasErrors())
099: loadResults((String) args
100: .getReturnParam(ARG_NAME_RESULTSET),
101: restoreMdl);
102: } else {
103: String queryName = (String) args
104: .getPassParam(ARG_NAME_QUERYNAME);
105: String modelStr = (String) args
106: .getPassParam(ARG_NAME_QUERYFIELDS);
107: String sql = getSql(args, queryName, modelStr);
108:
109: doc = DocumentHelper.createDocument();
110: Element ele = generateDataset(null, args, sql,
111: "resultset", "data", "data");
112: String retStr = DocumentHelper.outputXML(ele);
113: args.setReturnParam(ARG_NAME_RESULTSET, retStr);
114: callNextProxy(method, context, null);
115: }
116: } catch (Exception e) {
117: e.printStackTrace();
118: }
119: return null;
120: }
121:
122: /**
123: * Generate the SQL statement for retrieving data
124: * @param args the arguments passed to the ServiceProxy
125: * @param queryName the name of the query to be invoked
126: * @param modelStr the query fields dataset passed to the query
127: * @return the formatted SQL statement
128: */
129: protected String getSql(ServiceProxyArgs args, String queryName,
130: String modelStr) {
131: XModel queryMdl = (XModel) rootModel.get(queryName);
132: String type = XModelHelper.getAttrib(queryMdl, "type");
133: String sql = "";
134: if (type.compareTo(TYPE_PROVIDED) == 0) {
135: XModel sqlMdl = (XModel) queryMdl.get("sql");
136: sql = (String) sqlMdl.get();
137: sql += getProvidedWhereClauses(args, modelStr);
138: sql = fixSql(args, sql);
139: } else if (type.compareTo(TYPE_GENERATED) == 0) {
140: StringBuffer sqlBuf = new StringBuffer();
141: sqlBuf.append("SELECT ");
142: XModel fieldListMdl = (XModel) queryMdl.get("fieldlists");
143: String[] tableList = new String[fieldListMdl
144: .getNumChildren()];
145: for (int i = 0; i < fieldListMdl.getNumChildren(); i++) {
146: XBaseModel child = (XBaseModel) fieldListMdl.get(i);
147: String path = (String) child.get();
148: XBaseModel tableMdl = (XBaseModel) rootModel.get(path);
149: String tableName = XModelHelper.getAttrib(tableMdl,
150: "table");
151: tableList[i] = tableName;
152: for (int field = 0; field < tableMdl.getNumChildren(); field++) {
153: XBaseModel fieldMdl = (XBaseModel) tableMdl
154: .get(field);
155: String as = XModelHelper.getAttrib(fieldMdl, "as");
156: String function = XModelHelper.getAttrib(fieldMdl,
157: "function");
158: if (function != null)
159: sqlBuf.append(function);
160: else {
161: sqlBuf.append(tableName);
162: sqlBuf.append(".");
163: sqlBuf.append(fieldMdl.getId());
164: }
165:
166: if (as != null) {
167: sqlBuf.append(" AS ");
168: sqlBuf.append(as);
169: }
170: sqlBuf.append(",");
171: }
172: }
173: sqlBuf.setCharAt(sqlBuf.length() - 1, ' ');
174: sqlBuf.append("FROM ");
175: for (int i = 0; i < fieldListMdl.getNumChildren(); i++) {
176: XBaseModel child = (XBaseModel) fieldListMdl.get(i);
177: String path = (String) child.get();
178: XBaseModel tableMdl = (XBaseModel) rootModel.get(path);
179: String tableName = XModelHelper.getAttrib(tableMdl,
180: "table");
181: String joinType = XModelHelper.getAttrib(child, "type");
182: if (joinType == null) {
183: sqlBuf.append(tableName);
184: sqlBuf.append(",");
185: } else {
186: sqlBuf.setCharAt(sqlBuf.length() - 1, ' ');
187: sqlBuf.append(joinType);
188: String joinClause = XModelHelper.getAttrib(child,
189: "joinclause");
190: sqlBuf.append(" ");
191: sqlBuf.append(joinClause);
192: sqlBuf.append(" ");
193: }
194: }
195: sqlBuf.setCharAt(sqlBuf.length() - 1, ' ');
196: sql = sqlBuf.toString();
197: sql += getGeneratedWhereClauses(args, queryMdl);
198: }
199: return sql;
200: }
201:
202: /**
203: * Creates the where clause for the provided arguments.
204: * @param args the ServiceProxyArgs object which has been sent to this Service
205: * @param modelStr the where clause dataset which has been passed to the Service
206: * @return the formatted Where clause
207: */
208: protected String getProvidedWhereClauses(ServiceProxyArgs args,
209: String modelStr) {
210: XBaseModel tempModel = new XBaseModel();
211: SqlHelper sqlHelp = new SqlHelper();
212: sqlHelp.setEscapeCharacter("''");
213: loadResults(modelStr, tempModel);
214: XBaseModel clauses = (XBaseModel) tempModel.get("WhereClauses");
215: String where = "";
216: for (int i = 0; i < clauses.getNumChildren(); i++) {
217: XBaseModel child = (XBaseModel) clauses.get(i);
218: String id = child.getId();
219: id = fixId(id);
220: String operator = XModelHelper.getAttrib(child, "operator");
221: if (operator == null)
222: operator = "=";
223:
224: String value = (String) child.get();
225: where += " AND " + id + " " + operator + " '"
226: + sqlHelp.escapeQuotes(value) + "'";
227: }
228: return where;
229: }
230:
231: /**
232: * Retrieves the where clause provided by a generated query if there is one
233: * @param args the arguments passed to the Service
234: * @param queryMdl the query model created from the passed query dataset
235: * @return the generated where clause if there is one
236: */
237: protected String getGeneratedWhereClauses(ServiceProxyArgs args,
238: XModel queryMdl) {
239: XModel whereMdl = (XModel) queryMdl.get("whereclause");
240: String where = (String) whereMdl.get();
241: return where == null ? "" : where;
242: }
243:
244: /**
245: * Provided for overloading. Used to 'fix' the names of the database fields so
246: * that they do not have to be exposed on the client. For example 'name'
247: * might be fixed to 'accounts.account_name'.
248: * @param id the id of the field which was passed from the client
249: * @return the fixed id
250: */
251: protected String fixId(String id) {
252: return id;
253: }
254:
255: /**
256: * Provided for overloading, this is called when this ServiceProxy has
257: * finished generating the SQL. Overload to make final changes to the SQL
258: * before being executed
259: * @param args the arguments which have been passed to the ServiceProxy
260: * @param sql the SQL statement when this ServiceProxy has finished generating
261: * @return the fixed SQL statement
262: */
263: protected String fixSql(ServiceProxyArgs args, String sql) {
264: return sql;
265: }
266:
267: /**
268: * Executes the generated SQL statement and returns the results dataset
269: * @param args the arguments passed to the ServiceProxy
270: * @param sql the SQL statement which will be executed
271: * @param datasetId the name of the dataset tag
272: * @param datasetName the name to be given to the dataset
273: * @param dataitemName the name to be given to the dataset sub items
274: */
275: protected Element generateDataset(Element parentEle,
276: ServiceProxyArgs args, String sql, String datasetId,
277: String datasetName, String dataitemName) {
278: ConnectionObject connObj = null;
279: try {
280: connObj = getConnection();
281: Statement stmt = getStatement(connObj);
282: ResultSet rs = stmt.executeQuery(sql);
283:
284: // Construct the data XML
285: Element topEle = doc.createElement(datasetName);
286: topEle.setAttribute("id", datasetId);
287: if (parentEle != null)
288: parentEle.appendChild(topEle);
289: else
290: topElement = topEle;
291:
292: ResultSetMetaData rsMeta = rs.getMetaData();
293: while (rs.next()) {
294: Element recordEle = doc.createElement(dataitemName);
295: topEle.appendChild(recordEle);
296: recordStarted(rs, topEle);
297: for (int field = 1; field <= rsMeta.getColumnCount(); field++) {
298: String id = rsMeta.getColumnName(field);
299: String value = rs.getString(field);
300: recordEle.setAttribute(id, value);
301: }
302: addChildren(recordEle, args, dataitemName);
303: }
304: rs.close();
305: stmt.close();
306: return topEle;
307: } catch (Exception e) {
308: e.printStackTrace();
309: } finally {
310: closeConnection(connObj);
311: }
312: return null;
313: }
314:
315: protected ConnectionObject getConnection() {
316: try {
317: ConnectionObject connObj = NamedConnectionManager
318: .getInstance().getConnection(connectionName);
319: return connObj;
320: } catch (Exception e) {
321: e.printStackTrace();
322: }
323: return null;
324: }
325:
326: protected void closeConnection(ConnectionObject conn) {
327: try {
328: conn.close();
329: } catch (Exception ex) {
330: ex.printStackTrace();
331: }
332: }
333:
334: /**
335: * Create a statment for the query
336: */
337: protected Statement getStatement(ConnectionObject connObj) {
338: try {
339: return connObj.createStatement();
340: } catch (Exception e) {
341: e.printStackTrace();
342: }
343: return null;
344: }
345:
346: /**
347: * Returns the standard end of element text. Overload this function if subitems
348: * need to be added to the returned dataset.
349: * @param args the arguments passed to the ServiceProxy
350: * @param dataitemName the name of the dataitem which need to be used if
351: * overloaded
352: * @return the record ending String
353: */
354: protected void addChildren(Element ele, ServiceProxyArgs args,
355: String dataitemName) {
356: }
357:
358: /**
359: * Passes the ResultSet when a new record has its cursor set
360: * @param rs the ResultSet which is being iterated
361: */
362: protected void recordStarted(ResultSet rs, Element ele) {
363: }
364:
365: /**
366: * Load the returned data into the model.
367: * @param resultSet the returned XML data
368: */
369: public void loadResults(String resultSet, XModel model) {
370: StringReader sr = new StringReader(resultSet);
371: XmlElement src = XmlSource.read(sr);
372: XOptionalDataSource dataSource = new XOptionalDataSource(
373: currentProject);
374: dataSource.loadTable(src, model);
375: }
376:
377: /**
378: * Set the attributes for this service proxy. This class can set attribute
379: * for 'connectionname'.
380: * @param attribs The Hashtable of attributes as found in the XML declaration
381: */
382: public void setAttributes(Hashtable attribs) {
383: connectionName = (String) attribs.get("connectionname");
384: }
385: }
|