KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sxql


1 /*--
2
3  Copyright (C) 2000 Brett McLaughlin & Jason Hunter.
4  All rights reserved.
5  
6  Redistribution and use in source and binary forms, with or without
7  modification, are permitted provided that the following conditions
8  are met:
9  
10  1. Redistributions of source code must retain the above copyright
11     notice, this list of conditions, and the following disclaimer.
12  
13  2. Redistributions in binary form must reproduce the above copyright
14     notice, this list of conditions, and the disclaimer that follows
15     these conditions in the documentation and/or other materials
16     provided with the distribution.
17
18  3. The name "JDOM" must not be used to endorse or promote products
19     derived from this software without prior written permission. For
20     written permission, please contact license@jdom.org.
21  
22  4. Products derived from this software may not be called "JDOM", nor
23     may "JDOM" appear in their name, without prior written permission
24     from the JDOM Project Management (pm@jdom.org).
25  
26  In addition, we request (but do not require) that you include in the
27  end-user documentation provided with the redistribution and/or in the
28  software itself an acknowledgement equivalent to the following:
29      "This product includes software developed by the
30       JDOM Project (http://www.jdom.org/)."
31  Alternatively, the acknowledgment may be graphical using the logos
32  available at http://www.jdom.org/images/logos.
33
34  THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
35  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
36  OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
37  DISCLAIMED. IN NO EVENT SHALL THE JDOM AUTHORS OR THE PROJECT
38  CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
39  SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
40  LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
41  USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
42  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
43  OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
44  OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
45  SUCH DAMAGE.
46
47  This software consists of voluntary contributions made by many
48  individuals on behalf of the JDOM Project and was originally
49  created by Brett McLaughlin <brett@jdom.org> and
50  Jason Hunter <jhunter@jdom.org>. For more information on the
51  JDOM Project, please see <http://www.jdom.org/>.
52  
53  */
54
55 import java.sql.*;
56 import java.io.*;
57 import java.util.*;
58
59 import org.jdom.*;
60 import org.jdom.output.*;
61 import org.jdom.contrib.input.ResultSetBuilder;
62
63 /**
64  * A simple sample harness for JDOM ResultSetBuilder
65  *
66  * @author R.Sena (raff@aromatic.org)
67  */
68 public class sxql {
69
70   /**
71    * Return a connection (i.e. from a connection pool)
72    */
73   public static Connection getConnection(String JavaDoc driver, String JavaDoc jdbcURL,
74                                      String JavaDoc user, String JavaDoc pass)
75                                             throws Exception JavaDoc {
76     Class.forName(driver);
77     return DriverManager.getConnection(jdbcURL, user, pass);
78   }
79
80   /**
81    * Execute a SQL query and return the result as XML
82    * (as a String. But this can be changed to return a DOM/SAX/JDOM tree,
83    * to be used, for example, as input to an XSLT processor)
84    */
85   public static String JavaDoc query(Connection con, String JavaDoc query,
86                          String JavaDoc root, String JavaDoc row,
87                          String JavaDoc ns, int maxRows,
88                          Vector attributes, Vector elements)
89                                 throws Exception JavaDoc {
90     // Execute SQL Query
91 Statement stmt = con.createStatement();
92     ResultSet rs = stmt.executeQuery(query);
93
94     // Create a ResultSetBuilder
95 ResultSetBuilder builder = new ResultSetBuilder(rs);
96
97     // Configure some parameters...
98
99     if (root != null) {
100       builder.setRootName(root);
101     }
102
103     if (row != null) {
104       builder.setRowName(row);
105     }
106
107     if (ns != null) {
108       String JavaDoc namespace = null;
109       String JavaDoc url = null;
110       int sep = ns.indexOf("/");
111
112       if (sep > 0) {
113         namespace = ns.substring(0, sep);
114         url = ns.substring(sep+1);
115         builder.setNamespace(Namespace.getNamespace(namespace, url));
116       }
117     }
118
119     if (maxRows > 0) {
120       builder.setMaxRows(maxRows);
121     }
122
123     for (int i=0; i < attributes.size(); i++) {
124       String JavaDoc colName = (String JavaDoc) attributes.get(i);
125       String JavaDoc attrName = null;
126
127       if (colName.indexOf("/") >= 0) {
128         String JavaDoc col = colName;
129         int sep = col.indexOf("/");
130         colName = col.substring(0, sep);
131         attrName = col.substring(sep+1);
132       }
133
134       try { // If it looks like an integer, is the column number
135 int colNum = Integer.parseInt(colName);
136
137         if (attrName == null) {
138           builder.setAsAttribute(colNum); // attrName = column Name
139 }
140         else {
141           builder.setAsAttribute(colNum, attrName);
142         }
143       }
144       catch (NumberFormatException JavaDoc e) {
145         // Otherwise it's the column name
146 if (attrName == null) {
147           builder.setAsAttribute(colName); // attrName = column Name
148 }
149         else {
150           builder.setAsAttribute(colName, attrName);
151         }
152       }
153     }
154
155     // Rename element
156 for (int i=0; i < elements.size(); i++) {
157       String JavaDoc colName = (String JavaDoc) elements.get(i);
158       String JavaDoc elemName = null;
159
160       if (colName.indexOf("/") >= 0) {
161         String JavaDoc col = colName;
162         int sep = col.indexOf("/");
163         colName = col.substring(0, sep);
164         elemName = col.substring(sep+1);
165       }
166
167       try { // If it looks like an integer, is the column number
168 int colNum = Integer.parseInt(colName);
169
170         if (elemName != null) { // It must have an element name
171 builder.setAsElement(colNum, elemName);
172         }
173       }
174       catch (NumberFormatException JavaDoc e) {
175         // Otherwise it's the column name
176 if (elemName != null) { // It must have an element name
177 builder.setAsElement(colName, elemName);
178         }
179       }
180     }
181
182     // Build a JDOM tree
183 Document doc = builder.build();
184
185     // Convert the result to XML (as String)
186 XMLOutputter outputter = new XMLOutputter(Format.getPrettyFormat());
187     ByteArrayOutputStream output = new ByteArrayOutputStream();
188     outputter.output(doc, output);
189     return output.toString();
190   }
191
192   /**
193    * Usage
194    */
195   public static void usage() {
196     System.err.println(
197 "usage: sxql [--root=root-element] [--row=row-element]");
198     System.err.println(
199 " [--namespace=namespace/url] [--maxrows=max-rows]");
200     System.err.println(
201 " [--attribute=column/attr] [--element=column/element]");
202     System.err.println(
203 " driver url user pass query");
204     System.err.println(
205 "where:");
206     System.err.println(
207 " --root: set root element name (root-element)");
208     System.err.println(
209 " --row: set row element name (root-element)");
210     System.err.println(
211 " --namespace: set namespace (namespace, url)");
212     System.err.println(
213 " --maxrows: set maximum number of rows (max-rows)");
214     System.err.println(
215 " --attribute: column as attribute (column name/number, attribute-name)");
216     System.err.println(
217 " --element: rename column (column name/number, element-name)");
218     System.err.println(
219 " driver: driver class name");
220     System.err.println(
221 " url: JDBC url");
222     System.err.println(
223 " user: database user");
224     System.err.println(
225 " pass: database password");
226     System.err.println(
227 " query: SQL query");
228   }
229
230   /**
231    * Main entry point
232    */
233   public static void main(String JavaDoc [] args) throws Exception JavaDoc {
234     String JavaDoc root = null;
235     String JavaDoc row = null;
236     String JavaDoc ns = null;
237     int maxRows = 0;
238     Vector attributes = new Vector();
239     Vector elements = new Vector();
240
241     int i;
242
243     // Read configuration parameters
244
245     for (i=0; i < args.length; i++) {
246       if (args[i].startsWith("--")) {
247         if (args[i].startsWith("--attribute="))
248         attributes.add(args[i].substring(12));
249         else
250         if (args[i].startsWith("--element="))
251         elements.add(args[i].substring(10));
252         else
253         if (args[i].startsWith("--root="))
254         root = args[i].substring(7);
255         else
256         if (args[i].startsWith("--row="))
257         row = args[i].substring(6);
258         else
259         if (args[i].startsWith("--namespace="))
260         ns = args[i].substring(12);
261         else
262         if (args[i].startsWith("--maxrows="))
263         maxRows = Integer.parseInt(args[i].substring(10));
264       }
265       else {
266         break;
267       }
268     }
269
270     if (args.length - i != 5) {
271       usage();
272     }
273     else {
274       System.out.println(
275         query(getConnection(args[i+0], args[i+1], args[i+2], args[i+3]),
276               args[i+4], root, row, ns, maxRows, attributes, elements));
277     }
278   }
279 }
280
Popular Tags