|
1
21
22 package nu.xom.samples;
23
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 ;
30
31 import nu.xom.Attribute;
32 import nu.xom.Document;
33 import nu.xom.Element;
34 import nu.xom.Serializer;
35
36
37
51 public class SQLToXML {
52
53 public static void main(String [] args ) {
54
55 try {
57 Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
58 }
59 catch (ClassNotFoundException 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 ex) {
74 System.err.println(ex);
75 }
76
77
78 }
79
80 private static void writeAgencies(Element parent) {
81
82 Connection conn = null;
83 Statement stmnt = null;
84 try {
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 );
91
92 while( agencies.next() ) {
93
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);
105 }
106 }
107 catch (SQLException e) {
108 System.err.println(e);
109 e.printStackTrace();
110 }
111 finally {
112 try {
113 stmnt.close();
114 conn.close();
115 }
116 catch(SQLException ex) {
117 System.err.println(ex);
118 }
119 }
120
121 }
122
123 private static void writeBureaus(Element parent, Connection conn,
124 String agencyCode) throws SQLException {
125
126 String query
127 = "SELECT DISTINCT BureauName, BureauCode "
128 + "FROM BudgetAuthorizationTable WHERE AgencyCode='"
129 + agencyCode + "';";
130 Statement stmnt = conn.createStatement();
131 ResultSet bureaus = stmnt.executeQuery(query);
132
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);
145 }
146
147 }
148
149 private static void writeAccounts(Element parent, Connection conn,
150 String agencyCode, String bureauCode)
151 throws SQLException {
152
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);
158
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);
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 conn, String agencyCode, String bureauCode,
179 String accountCode) throws SQLException {
180
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);
186
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"));
201 amount.appendChild(
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);
211 }
212 parent.appendChild(subfunction);
213 }
214
215 }
216
217 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.