KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > net > sf > saxon > sql > SQLQuery


1 package net.sf.saxon.sql;
2
3 import net.sf.saxon.Controller;
4 import net.sf.saxon.event.Receiver;
5 import net.sf.saxon.event.ReceiverOptions;
6 import net.sf.saxon.expr.Expression;
7 import net.sf.saxon.expr.SimpleExpression;
8 import net.sf.saxon.expr.XPathContext;
9 import net.sf.saxon.instruct.Executable;
10 import net.sf.saxon.om.Item;
11 import net.sf.saxon.om.NamePool;
12 import net.sf.saxon.style.ExtensionInstruction;
13 import net.sf.saxon.style.StandardNames;
14 import net.sf.saxon.trans.DynamicError;
15 import net.sf.saxon.trans.XPathException;
16 import net.sf.saxon.value.ObjectValue;
17 import net.sf.saxon.value.StringValue;
18
19 import java.sql.Connection JavaDoc;
20 import java.sql.PreparedStatement JavaDoc;
21 import java.sql.ResultSet JavaDoc;
22 import java.sql.SQLException JavaDoc;
23
24 /**
25  * An sql:query element in the stylesheet.
26  * <p/>
27  * For example:
28  * <pre>
29  * &lt;sql:query column="{$column}" table="{$table}" where="{$where}"
30  * xsl:extension-element-prefixes="sql"/ &gt;
31  * <p/>
32  * </pre>
33  * (result with HTML-table-output) <BR>
34  * <pre>
35  * &lt;sql:query column="{$column}" table="{$table}" where="{$where}"
36  * row-tag="TR" column-tag="TD"
37  * separatorType="tag"
38  * xsl:extension-element-prefixes="sql"/ &gt;
39  * </pre>
40  *
41  * @author claudio.thomas@unix-ag.org (based on Michael Kay's SQLInsert.java)
42  */

43
44 public class SQLQuery extends ExtensionInstruction {
45
46     Expression connection;
47     /**
48      * selected column(s) to query
49      */

50     Expression column;
51     /**
52      * the table(s) to query in
53      */

54     Expression table;
55     /**
56      * conditions of query (can be omitted)
57      */

58     Expression where;
59
60     String JavaDoc rowTag;
61     /**
62      * name of element to hold the rows
63      */

64     String JavaDoc colTag;
65     /**
66      * name of element to hold the columns
67      */

68
69     boolean disable = false; // true means disable-output-escaping="yes"
70

71     public void prepareAttributes() throws XPathException {
72         // Attributes for SQL-statement
73
String JavaDoc dbCol = attributeList.getValue("", "column");
74         if (dbCol == null) {
75             reportAbsence("column");
76         }
77         column = makeAttributeValueTemplate(dbCol);
78
79         String JavaDoc dbTab = attributeList.getValue("", "table");
80         if (dbTab == null) {
81             reportAbsence("table");
82         }
83         table = makeAttributeValueTemplate(dbTab);
84
85         String JavaDoc dbWhere = attributeList.getValue("", "where");
86         if (dbWhere == null) {
87             where = StringValue.EMPTY_STRING;
88         } else {
89             where = makeAttributeValueTemplate(dbWhere);
90         }
91
92         String JavaDoc connectAtt = attributeList.getValue("", "connection");
93         if (connectAtt == null) {
94             reportAbsence("connection");
95         } else {
96             connection = makeExpression(connectAtt);
97         }
98
99         // Atributes for row & column element names
100

101         rowTag = attributeList.getValue("", "row-tag");
102         if (rowTag == null) {
103             rowTag = "row";
104         }
105         if (rowTag.indexOf(':') >= 0) {
106             compileError("rowTag must not contain a colon");
107         }
108
109         colTag = attributeList.getValue("", "column-tag");
110         if (colTag == null) {
111             colTag = "col";
112         }
113         if (colTag.indexOf(':') >= 0) {
114             compileError("colTag must not contain a colon");
115         }
116         // Attribute output-escaping
117
String JavaDoc disableAtt = attributeList.getValue("", "disable-output-escaping");
118         if (disableAtt != null) {
119             if (disableAtt.equals("yes")) {
120                 disable = true;
121             } else if (disableAtt.equals("no")) {
122                 disable = false;
123             } else {
124                 compileError("disable-output-escaping attribute must be either yes or no");
125             }
126         }
127
128     }
129
130     public void validate() throws XPathException {
131         super.validate();
132         column = typeCheck("column", column);
133         table = typeCheck("table", table);
134         where = typeCheck("where", where);
135         connection = typeCheck("connection", connection);
136     }
137
138     public Expression compile(Executable exec) throws XPathException {
139         QueryInstruction inst = new QueryInstruction(connection,
140                 column, table, where,
141                 rowTag, colTag, disable);
142         return inst;
143     }
144
145     private static class QueryInstruction extends SimpleExpression {
146
147         public static final int CONNECTION = 0;
148         public static final int COLUMN = 1;
149         public static final int TABLE = 2;
150         public static final int WHERE = 3;
151         String JavaDoc rowTag;
152         String JavaDoc colTag;
153         int options;
154
155         public QueryInstruction(Expression connection,
156                                 Expression column,
157                                 Expression table,
158                                 Expression where,
159                                 String JavaDoc rowTag,
160                                 String JavaDoc colTag,
161                                 boolean disable) {
162             Expression[] sub = {connection, column, table, where};
163             setArguments(sub);
164             this.rowTag = rowTag;
165             this.colTag = colTag;
166             this.options = (disable ? ReceiverOptions.DISABLE_ESCAPING : 0);
167         }
168
169         /**
170          * A subclass must provide one of the methods evaluateItem(), iterate(), or process().
171          * This method indicates which of the three is provided.
172          */

173
174         public int getImplementationMethod() {
175             return Expression.PROCESS_METHOD;
176         }
177
178         public String JavaDoc getExpressionType() {
179             return "sql:query";
180         }
181
182         public void process(XPathContext context) throws XPathException {
183             // Prepare the SQL statement (only do this once)
184

185             Controller controller = context.getController();
186             Item conn = arguments[CONNECTION].evaluateItem(context);
187             if (!(conn instanceof ObjectValue && ((ObjectValue)conn).getObject() instanceof Connection JavaDoc)) {
188                 DynamicError de = new DynamicError("Value of connection expression is not a JDBC Connection");
189                 de.setXPathContext(context);
190                 throw de;
191             }
192             Connection JavaDoc connection = (Connection JavaDoc)((ObjectValue)conn).getObject();
193
194             String JavaDoc dbCol = arguments[COLUMN].evaluateAsString(context);
195             String JavaDoc dbTab = arguments[TABLE].evaluateAsString(context);
196             String JavaDoc dbWhere = arguments[WHERE].evaluateAsString(context);
197
198
199             NamePool pool = controller.getNamePool();
200             int rowCode = pool.allocate("", "", rowTag);
201             int colCode = pool.allocate("", "", colTag);
202
203             PreparedStatement JavaDoc ps = null;
204             ResultSet JavaDoc rs = null;
205             DynamicError de = null;
206
207             try {
208                 StringBuffer JavaDoc statement = new StringBuffer JavaDoc();
209                 statement.append("SELECT " + dbCol + " FROM " + dbTab);
210                 if (dbWhere != "") {
211                     statement.append(" WHERE " + dbWhere);
212                 }
213                 //System.err.println("-> SQL: " + statement.toString());
214

215                 // -- Prepare the SQL statement
216
ps = connection.prepareStatement(statement.toString());
217                 controller.setUserData(this, "sql:statement", ps);
218
219                 // -- Execute Statement
220
rs = ps.executeQuery();
221
222                 // -- Print out Result
223
Receiver out = context.getReceiver();
224                 String JavaDoc result = "";
225                 int icol = rs.getMetaData().getColumnCount();
226                 while (rs.next()) { // next row
227
//System.out.print("<- SQL : "+ rowStart);
228
out.startElement(rowCode, StandardNames.XDT_UNTYPED, locationId, 0);
229                     for (int col = 1; col <= icol; col++) { // next column
230
// Read result from RS only once, because
231
// of JDBC-Specifications
232
result = rs.getString(col);
233                         out.startElement(colCode, StandardNames.XDT_UNTYPED, locationId, 0);
234                         if (result != null) {
235                             out.characters(result, locationId, options);
236                         }
237                         out.endElement();
238                     }
239                     //System.out.println(rowEnd);
240
out.endElement();
241                 }
242                 //rs.close();
243

244                 if (!connection.getAutoCommit()) {
245                     connection.commit();
246                 }
247
248             } catch (SQLException JavaDoc ex) {
249                 de = new DynamicError("(SQL) " + ex.getMessage());
250                 de.setXPathContext(context);
251                 throw de;
252             } finally {
253                 boolean wasDEThrown = (de != null);
254                 if (rs != null) {
255                     try {
256                         rs.close();
257                     } catch (SQLException JavaDoc ex) {
258                         de = new DynamicError("(SQL) " + ex.getMessage());
259                         de.setXPathContext(context);
260                     }
261                 }
262                 if (ps != null) {
263                     try {
264                         ps.close();
265                     } catch (SQLException JavaDoc ex) {
266                         de = new DynamicError("(SQL) " + ex.getMessage());
267                         de.setXPathContext(context);
268                     }
269                 }
270                 if (!wasDEThrown && de != null) {
271                     throw de; // test so we don't lose the real exception
272
}
273             }
274         }
275     }
276 }
277
278
279
280 //
281
// The contents of this file are subject to the Mozilla Public License Version 1.0 (the "License");
282
// you may not use this file except in compliance with the License. You may obtain a copy of the
283
// License at http://www.mozilla.org/MPL/
284
//
285
// Software distributed under the License is distributed on an "AS IS" basis,
286
// WITHOUT WARRANTY OF ANY KIND, either express or implied.
287
// See the License for the specific language governing rights and limitations under the License.
288
//
289
//
290
// Contributor(s): claudio.thomas@unix-ag.org (based on SQLInsert.java)
291
//
292
Popular Tags