1
/* Copyright 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;
31
* Based on Example 8-13 in Processing XML with Java
34
* @author Elliotte Rusty Harold
37
public class DatabaseBuilder {
39
private Connection connection;
42
// The string passed to the constructor must be a JDBC URL that
43
// contains all necessary information for connecting to the
44
// database such as host, port, username, password, and
45
// database name. For example,
46
// jdbc:mysql://host:port]/dbname?user=username&password=pass
47
// The driver should have been loaded before this method is
49
public DatabaseBuilder(String jdbcURL) throws SQLException {
50
connection = DriverManager.getConnection(jdbcURL);
54
public Document build(String selectQuery)
55
throws SQLException, ParsingException {
57
Statement statement = connection.createStatement();
58
ResultSet data = statement.executeQuery(selectQuery);
59
ResultSetMetaData metadata = data.getMetaData();
60
int numFields = metadata.getColumnCount();
62
Element table = new Element("table");
65
Element record = new Element("record");
66
for (int field = 1; field <= numFields; field++) {
67
Element fieldElement = new Element("field");
68
int type = metadata.getColumnType(field);
69
String typeName = getSchemaType(type);
70
Attribute typeAtt = new Attribute("xsi:type",
71
"http://www.w3.org/2001/XMLSchema-instance", typeName);
72
fieldElement.addAttribute(typeAtt);
73
String name = metadata.getColumnName(field);
74
Attribute nameAtt = new Attribute("name", name);
75
fieldElement.addAttribute(nameAtt);
76
// Convert nulls to empty elements with xsi:nil="true"
77
Object value = data.getObject(field);
78
if (value == null) { // null value in database
79
Attribute nilAtt = new Attribute("xsi:nil",
80
"http://www.w3.org/2001/XMLSchema-instance", "true");
81
fieldElement.addAttribute(nilAtt);
83
else { // non-null value
84
fieldElement.appendChild(convertToXML(data, field, type));
86
record.appendChild(fieldElement);
88
table.appendChild(record);
92
table.addNamespaceDeclaration("xsi",
93
"http://www.w3.org/2001/XMLSchema-instance");
94
table.addNamespaceDeclaration("xsd", "http://www.w3.org/2001/XMLSchema");
95
return new Document(table);
100
// I want the XML document to store values in the standard W3C
101
// XML Schema Language forms. This requires certain conversions
102
// depending on the type of the data
103
private Node convertToXML(ResultSet data, int field, int type)
104
throws SQLException, ParsingException {
108
case Types.VARBINARY:
109
case Types.LONGVARBINARY:
110
return hexEncode(data.getBinaryStream(field));
112
Blob blob = data.getBlob(field);
113
return hexEncode(blob.getBinaryStream());
115
Clob clob = data.getClob(field);
116
Reader r = clob.getCharacterStream();
117
char[] text = new char[1024];
120
StringBuffer result = new StringBuffer();
121
while ((numRead = r.read(text, 0, 1024)) != -1) {
122
result.append(escapeText(text, 0, numRead));
124
return new Text(result.toString());
126
catch (IOException ex) {
127
throw new ParsingException("Read from CLOB failed", ex);
130
Array array = data.getArray(field);
131
return writeArray(array);
132
default: // All other types can be handled as strings
133
Object o = data.getObject(field);
134
if (o == null) return new Text("");
135
String s = o.toString();
136
char[] value = s.toCharArray();
137
return escapeText(value, 0, value.length);
143
private Text hexEncode(InputStream in) {
145
StringBuffer result = new StringBuffer();
148
while ((octet = in.read()) != -1) {
149
StringWriter out = new StringWriter(2);
150
if (octet < 16) out.write('0');
151
out.write(Integer.toHexString(octet));
152
result.append(out.toString());
154
return new Text(result.toString());
156
catch (IOException ex) {
157
throw new XMLException("Error while hex-encoding", ex);
163
// String types may contain C0 control characters that are
164
// not legal in XML. I convert these to the Unicode replacement
166
private Text escapeText(char[] text, int start, int length) {
167
StringBuffer result = new StringBuffer(length);
168
for (int i = start; i < length; i++) {
169
result.append(escapeChar(text[i]));
171
return new Text(result.toString());
174
private char escapeChar(char c) {
175
if (c >= 0x20) return c;
176
else if (c == '\n') return c;
177
else if (c == '\r') return c;
178
else if (c == '\t') return c;
182
private Node writeArray(Array array)
183
throws SQLException, ParsingException {
185
ResultSet data = array.getResultSet();
186
int type = array.getBaseType();
187
String typeName = getSchemaType(type);
189
Element arrayElement = new Element("array");
190
while (data.next()) {
191
Element component = new Element("component");
192
component.addAttribute(new Attribute("xsi:type",
193
"http://www.w3.org/2001/XMLSchema-instance",
195
component.appendChild(convertToXML(data, 2, type));
196
arrayElement.appendChild(component);
202
public static String getSchemaType(int type) {
205
case Types.ARRAY: return "array";
206
case Types.BIGINT: return "xsd:long";
207
case Types.BINARY: return "xsd:hexBinary";
208
case Types.BIT: return "xsd:boolean";
209
case Types.BLOB: return "xsd:hexBinary";
210
case Types.CHAR: return "xsd:string";
211
case Types.CLOB: return "xsd:string";
212
case Types.DATE: return "xsd:date";
213
case Types.DECIMAL: return "xsd:decimal";
214
case Types.DOUBLE: return "xsd:double";
215
case Types.FLOAT: return "xsd:decimal";
216
case Types.INTEGER: return "xsd:int";
217
case Types.JAVA_OBJECT: return "xsd:string";
218
case Types.LONGVARBINARY: return "xsd:hexBinary";
219
case Types.LONGVARCHAR: return "xsd:string";
220
case Types.NUMERIC: return "xsd:decimal";
221
case Types.REAL: return "xsd:float";
222
case Types.REF: return "xsd:IDREF";
223
case Types.SMALLINT: return "xsd:short";
224
case Types.STRUCT: return "struct";
225
case Types.TIME: return "xsd:time";
226
case Types.TIMESTAMP: return "xsd:dateTime";
227
case Types.TINYINT: return "xsd:byte";
228
case Types.VARBINARY: return "xsd:hexBinary";
230
default: return "xsd:string";
236
public static void main(String[] args) {
238
if (args.length < 2) {
240
"Usage: java DatabaseBuilder URL query driverClass");
243
String url = args[0];
244
String query = args[1];
245
String driverClass = "org.gjt.mm.mysql.Driver"; // MySQL
246
if (args.length >= 3) driverClass = args[2];
250
Class.forName(driverClass).newInstance();
251
// Technically, the newInstance() call isn't needed,
252
// but the MM.MySQL documentation suggests this to
253
// "work around some broken JVMs"
255
DatabaseBuilder builder = new DatabaseBuilder(url);
256
Serializer out = new Serializer(System.out);
259
Document doc = builder.build(query);
263
catch (InstantiationException ex) {
264
System.out.println(driverClass + " could not be instantiated");
266
catch (ClassNotFoundException ex) {
267
System.out.println(driverClass + " could not be found");
269
catch (Exception ex) { // SQL, SAX, and IO
270
ex.printStackTrace();