KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > nu > xom > samples > SQLToXML


1 /* Copyright 2002-2004 Elliotte Rusty Harold
2    
3    This library is free software; you can redistribute it and/or modify
4    it under the terms of version 2.1 of the GNU Lesser General Public
5    License as published by the Free Software Foundation.
6    
7    This library is distributed in the hope that it will be useful,
8    but WITHOUT ANY WARRANTY; without even the implied warranty of
9    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10    GNU Lesser General Public License for more details.
11    
12    You should have received a copy of the GNU Lesser General Public
13    License along with this library; if not, write to the
14    Free Software Foundation, Inc., 59 Temple Place, Suite 330,
15    Boston, MA 02111-1307 USA
16    
17    You can contact Elliotte Rusty Harold by sending e-mail to
18    elharo@metalab.unc.edu. Please include the word "XOM" in the
19    subject line. The XOM home page is located at http://www.xom.nu/
20 */

21
22 package nu.xom.samples;
23
24 import java.io.IOException JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.DriverManager JavaDoc;
27 import java.sql.ResultSet JavaDoc;
28 import java.sql.SQLException JavaDoc;
29 import java.sql.Statement JavaDoc;
30
31 import nu.xom.Attribute;
32 import nu.xom.Document;
33 import nu.xom.Element;
34 import nu.xom.Serializer;
35
36
37 /**
38  * <p>
39  * Demonstrates building a structured XML document,
40  * from relational data using JDBC. A different version of this
41  * example was originally developed for Example 4.14 of Chapter 4 of
42  * <cite><a target="_top"
43  * HREF="http://www.cafeconleche.org/books/xmljava/">Processing
44  * XML with Java</a></cite>.
45  * </p>
46  *
47  * @author Elliotte Rusty Harold
48  * @version 1.0
49  *
50  */

51 public class SQLToXML {
52         
53   public static void main(String JavaDoc[] args ) {
54         
55     // Load the ODBC driver
56
try {
57       Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
58     }
59     catch (ClassNotFoundException JavaDoc ex) {
60       System.err.println("Could not load the JDBC-ODBC Bridge");
61       return;
62     }
63     
64     try {
65       Element budget = new Element("Budget");
66       writeAgencies(budget);
67       
68       Document doc = new Document(budget);
69       Serializer sout = new Serializer(System.out, "UTF-8");
70       sout.write(doc);
71       sout.flush();
72     }
73     catch (IOException JavaDoc ex) {
74       System.err.println(ex);
75     }
76
77     
78   }
79   
80   private static void writeAgencies(Element parent) {
81
82     Connection JavaDoc conn = null;
83     Statement JavaDoc stmnt = null;
84     try {
85       conn = DriverManager.getConnection(
86        "jdbc:odbc:budauth", "", "");
87       stmnt = conn.createStatement();
88       String JavaDoc query = "SELECT DISTINCT AgencyName, AgencyCode"
89        + " FROM BudgetAuthorizationTable;";
90       ResultSet JavaDoc agencies = stmnt.executeQuery( query );
91
92       while( agencies.next() ) {
93         
94         String JavaDoc agencyName = agencies.getString("AgencyName");
95         String JavaDoc agencyCode = agencies.getString("AgencyCode");
96         Element agency = new Element("Agency");
97         Element name = new Element("Name");
98         Element code = new Element("Code");
99         name.appendChild(agencyName);
100         code.appendChild(agencyCode);
101         agency.appendChild(name);
102         agency.appendChild(code);
103         writeBureaus(agency, conn, agencyCode);
104         parent.appendChild(agency);
105       }
106     }
107     catch (SQLException JavaDoc e) {
108       System.err.println(e);
109       e.printStackTrace();
110     }
111     finally {
112       try {
113         stmnt.close();
114         conn.close();
115       }
116       catch(SQLException JavaDoc ex) {
117         System.err.println(ex);
118       }
119     }
120               
121   }
122   
123   private static void writeBureaus(Element parent, Connection JavaDoc conn,
124    String JavaDoc agencyCode) throws SQLException JavaDoc {
125
126     String JavaDoc query
127      = "SELECT DISTINCT BureauName, BureauCode "
128      + "FROM BudgetAuthorizationTable WHERE AgencyCode='"
129      + agencyCode + "';";
130     Statement JavaDoc stmnt = conn.createStatement();
131     ResultSet JavaDoc bureaus = stmnt.executeQuery(query);
132
133     while( bureaus.next() ) {
134       String JavaDoc bureauName = bureaus.getString("BureauName");
135       String JavaDoc bureauCode = bureaus.getString("BureauCode");
136       Element bureau = new Element("Bureau");
137       Element name = new Element("Name");
138       Element code = new Element("Code");
139       name.appendChild(bureauName);
140       code.appendChild(bureauCode);
141       bureau.appendChild(name);
142       bureau.appendChild(code);
143       writeAccounts(bureau, conn, agencyCode, bureauCode);
144       parent.appendChild(bureau);
145     }
146
147   }
148   
149   private static void writeAccounts(Element parent, Connection JavaDoc conn,
150    String JavaDoc agencyCode, String JavaDoc bureauCode)
151    throws SQLException JavaDoc {
152
153     String JavaDoc query = "SELECT DISTINCT AccountName, AccountCode "
154      + "FROM BudgetAuthorizationTable WHERE AgencyCode='"
155      + agencyCode + "' AND BureauCode='" + bureauCode + "';";
156     Statement JavaDoc stmnt = conn.createStatement();
157     ResultSet JavaDoc accounts = stmnt.executeQuery(query);
158
159     while( accounts.next() ) {
160       String JavaDoc accountName = accounts.getString("AccountName");
161       String JavaDoc accountCode = accounts.getString("AccountCode");
162       Element account = new Element("Account");
163       Element name = new Element("Name");
164       Element code = new Element("Code");
165       name.appendChild(accountName);
166       code.appendChild(accountCode);
167       account.appendChild(name);
168       account.appendChild(code);
169       writeSubfunctions(
170        account, conn, agencyCode, bureauCode, accountCode
171       );
172       parent.appendChild(account);
173     }
174         
175   }
176   
177   private static void writeSubfunctions(Element parent,
178    Connection JavaDoc conn, String JavaDoc agencyCode, String JavaDoc bureauCode,
179    String JavaDoc accountCode) throws SQLException JavaDoc {
180
181     String JavaDoc query = "SELECT * FROM BudgetAuthorizationTable"
182      + " WHERE AgencyCode='" + agencyCode + "' AND BureauCode='"
183      + bureauCode + "' AND AccountCode='" + accountCode + "';";
184     Statement JavaDoc stmnt = conn.createStatement();
185     ResultSet JavaDoc subfunctions = stmnt.executeQuery(query);
186
187     while( subfunctions.next() ) {
188       String JavaDoc subfunctionTitle
189        = subfunctions.getString("SubfunctionTitle");
190       String JavaDoc subfunctionCode
191        = subfunctions.getString("SubfunctionCode");
192       Element subfunction = new Element("Subfunction");
193       Element name = new Element("Name");
194       Element code = new Element("Code");
195       name.appendChild(subfunctionTitle);
196       code.appendChild(subfunctionCode);
197       subfunction.appendChild(name);
198       subfunction.appendChild(code);
199       Element amount = new Element("Amount");
200       amount.addAttribute(new Attribute("year", "TransitionQuarter"));
201       amount.appendChild(
202        String.valueOf(subfunctions.getInt("TransitionQuarter") * 1000L));
203       subfunction.appendChild(amount);
204       for (int year = 1976; year <= 2006; year++) {
205         String JavaDoc fy = "FY" + year;
206         long amt = subfunctions.getInt(fy) * 1000L;
207          amount = new Element("Amount");
208          amount.addAttribute(new Attribute("year", String.valueOf(year)));
209          amount.appendChild(String.valueOf(amt));
210          subfunction.appendChild(amount);
211       }
212       parent.appendChild(subfunction);
213     }
214         
215   }
216  
217  }
Popular Tags