1
/* Copyright 2002-2004 Elliotte Rusty Harold
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.
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.
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
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/
22
package nu.xom.samples;
24
import java.io.IOException;
25
import java.sql.Connection;
26
import java.sql.DriverManager;
27
import java.sql.ResultSet;
28
import java.sql.SQLException;
29
import java.sql.Statement;
31
import nu.xom.Attribute;
32
import nu.xom.Document;
33
import nu.xom.Element;
34
import nu.xom.Serializer;
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>.
47
* @author Elliotte Rusty Harold
51
public class SQLToXML {
53
public static void main(String[] args ) {
55
// Load the ODBC driver
57
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
59
catch (ClassNotFoundException ex) {
60
System.err.println("Could not load the JDBC-ODBC Bridge");
65
Element budget = new Element("Budget");
66
writeAgencies(budget);
68
Document doc = new Document(budget);
69
Serializer sout = new Serializer(System.out, "UTF-8");
73
catch (IOException ex) {
74
System.err.println(ex);
80
private static void writeAgencies(Element parent) {
82
Connection conn = null;
83
Statement stmnt = null;
85
conn = DriverManager.getConnection(
86
"jdbc:odbc:budauth", "", "");
87
stmnt = conn.createStatement();
88
String query = "SELECT DISTINCT AgencyName, AgencyCode"
89
+ " FROM BudgetAuthorizationTable;";
90
ResultSet agencies = stmnt.executeQuery( query );
92
while( agencies.next() ) {
94
String agencyName = agencies.getString("AgencyName");
95
String 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);
107
catch (SQLException e) {
108
System.err.println(e);
116
catch(SQLException ex) {
117
System.err.println(ex);
123
private static void writeBureaus(Element parent, Connection conn,
124
String agencyCode) throws SQLException {
127
= "SELECT DISTINCT BureauName, BureauCode "
128
+ "FROM BudgetAuthorizationTable WHERE AgencyCode='"
130
Statement stmnt = conn.createStatement();
131
ResultSet bureaus = stmnt.executeQuery(query);
133
while( bureaus.next() ) {
134
String bureauName = bureaus.getString("BureauName");
135
String 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);
149
private static void writeAccounts(Element parent, Connection conn,
150
String agencyCode, String bureauCode)
151
throws SQLException {
153
String query = "SELECT DISTINCT AccountName, AccountCode "
154
+ "FROM BudgetAuthorizationTable WHERE AgencyCode='"
155
+ agencyCode + "' AND BureauCode='" + bureauCode + "';";
156
Statement stmnt = conn.createStatement();
157
ResultSet accounts = stmnt.executeQuery(query);
159
while( accounts.next() ) {
160
String accountName = accounts.getString("AccountName");
161
String 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);
170
account, conn, agencyCode, bureauCode, accountCode
172
parent.appendChild(account);
177
private static void writeSubfunctions(Element parent,
178
Connection conn, String agencyCode, String bureauCode,
179
String accountCode) throws SQLException {
181
String query = "SELECT * FROM BudgetAuthorizationTable"
182
+ " WHERE AgencyCode='" + agencyCode + "' AND BureauCode='"
183
+ bureauCode + "' AND AccountCode='" + accountCode + "';";
184
Statement stmnt = conn.createStatement();
185
ResultSet subfunctions = stmnt.executeQuery(query);
187
while( subfunctions.next() ) {
188
String subfunctionTitle
189
= subfunctions.getString("SubfunctionTitle");
190
String 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"));
202
String.valueOf(subfunctions.getInt("TransitionQuarter") * 1000L));
203
subfunction.appendChild(amount);
204
for (int year = 1976; year <= 2006; year++) {
205
String 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);
212
parent.appendChild(subfunction);
b'\\ No newline at end of file'