1
/* Copyright 2002, 2003 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.io.InputStream;
26
import java.io.Reader;
27
import java.sql.Array;
30
import java.sql.Connection;
31
import java.sql.DriverManager;
32
import java.sql.ResultSet;
33
import java.sql.ResultSetMetaData;
34
import java.sql.SQLException;
35
import java.sql.Statement;
36
import java.sql.Types;
38
import nu.xom.Attribute;
39
import nu.xom.Document;
40
import nu.xom.Element;
43
import nu.xom.XMLException;
48
* Demonstrates the building of a structured XML document,
49
* from a relational database using JDBC. A different version of
50
* this example was originally developed for Chapter 4 of
52
* href="http://www.cafeconleche.org/books/xmljava/">Processing
53
* XML with Java</a></cite>.
56
* @author Elliotte Rusty Harold
60
public class DatabaseConverter {
62
private Connection connection;
64
// The string passed to the constructor must be a JDBC URL that
65
// contains all necessary information for connecting to the
66
// database such as host, port, username, password, and
67
// database name. For example,
68
// jdbc:mysql://host:port]/dbname?user=username&password=pass
69
// The driver should have been loaded before this method is
71
public DatabaseConverter(String jdbcURL) throws SQLException {
72
connection = DriverManager.getConnection(jdbcURL);
75
public Document extract(String selectQuery)
79
Statement statement = connection.createStatement();
80
ResultSet data = statement.executeQuery(selectQuery);
81
ResultSetMetaData metadata = data.getMetaData();
82
int numFields = metadata.getColumnCount();
84
Element table = new Element("table");
87
Element record = new Element("record");
88
for (int field = 1; field <= numFields; field++) {
90
Element fieldElement = new Element("field");
91
int type = metadata.getColumnType(field);
92
String typeName = getSchemaType(type);
93
fieldElement.addAttribute(new Attribute("xsi:type",
94
"http://www.w3.org/2001/XMLSchema-instance",
95
typeName, Attribute.Type.NMTOKEN));
96
String name = metadata.getColumnName(field);
97
fieldElement.addAttribute(new Attribute("name", name));
99
// Convert nulls to empty elements with xsi:nil="true"
100
Object value = data.getObject(field);
101
if (value == null) { // null value in database
102
fieldElement.addAttribute(new Attribute("xsi:nil",
103
"http://www.w3.org/2001/XMLSchema-instance", "true"));
105
else { // non-null value
106
fieldElement.appendChild(convertToXML(data, field, type));
108
record.appendChild(fieldElement);
110
table.appendChild(record);
113
return new Document(table);
115
catch (SQLException ex) { // convert exception type
116
throw new XMLException("SQL error", ex);
121
// I want the XML document to store values in the standard W3C
122
// XML Schema Language forms. This requires certain conversions
123
// depending on the type of the data
124
private Node convertToXML(ResultSet data, int field, int type)
125
throws SQLException, IOException {
129
case Types.VARBINARY:
130
case Types.LONGVARBINARY:
131
return hexEncode(data.getBinaryStream(field));
133
Blob blob = data.getBlob(field);
134
return hexEncode(blob.getBinaryStream());
135
// String types may contain C0 control characters that are
136
// not legal in XML. If so an exception is thrown.
138
Clob clob = data.getClob(field);
139
Reader r = clob.getCharacterStream();
140
char[] text = new char[1024];
142
StringBuffer result = new StringBuffer();
143
while ((numRead = r.read(text, 0, 1024)) != -1) {
144
result.append(text, 0, numRead);
146
return new Text(result.toString());
148
Array array = data.getArray(field);
149
return writeArray(array);
150
default: // All other types can be handled as strings
151
Object o = data.getObject(field);
152
if (o == null) return new Text("");
153
return new Text(o.toString());
158
private Text hexEncode(InputStream in)
161
StringBuffer result = new StringBuffer();
164
while ((octet = in.read()) != -1) {
165
if (octet < 16) result.append('0');
166
result.append(Integer.toHexString(octet));
168
return new Text(result.toString());
172
private Element writeArray(Array array)
173
throws IOException, SQLException {
175
Element holder = new Element("array");
176
ResultSet data = array.getResultSet();
177
int type = array.getBaseType();
178
String typeName = getSchemaType(type);
180
while (data.next()) {
181
Element component = new Element("component");
182
component.addAttribute(new Attribute("xsi:type",
183
"http://www.w3.org/2001/XMLSchema-instance", typeName));
184
component.appendChild(convertToXML(data, 2, type));
185
holder.appendChild(component);
191
public static String getSchemaType(int type) {
194
case Types.ARRAY: return "array";
195
case Types.BIGINT: return "xsd:long";
196
case Types.BINARY: return "xsd:hexBinary";
197
case Types.BIT: return "xsd:boolean";
198
case Types.BLOB: return "xsd:hexBinary";
199
case Types.CHAR: return "xsd:string";
200
case Types.CLOB: return "xsd:string";
201
case Types.DATE: return "xsd:date";
202
case Types.DECIMAL: return "xsd:decimal";
203
case Types.DOUBLE: return "xsd:double";
204
case Types.FLOAT: return "xsd:decimal";
205
case Types.INTEGER: return "xsd:int";
206
case Types.JAVA_OBJECT: return "xsd:string";
207
case Types.LONGVARBINARY: return "xsd:hexBinary";
208
case Types.LONGVARCHAR: return "xsd:string";
209
case Types.NUMERIC: return "xsd:decimal";
210
case Types.REAL: return "xsd:float";
211
case Types.REF: return "xsd:IDREF";
212
case Types.SMALLINT: return "xsd:short";
213
case Types.STRUCT: return "struct";
214
case Types.TIME: return "xsd:time";
215
case Types.TIMESTAMP: return "xsd:dateTime";
216
case Types.TINYINT: return "xsd:byte";
217
case Types.VARBINARY: return "xsd:hexBinary";
219
default: return "xsd:string";
b'\\ No newline at end of file'