KickJava   Java API By Example, From Geeks To Geeks.

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


1 /* Copyright 2002, 2003 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.io.InputStream JavaDoc;
26 import java.io.Reader JavaDoc;
27 import java.sql.Array JavaDoc;
28 import java.sql.Blob JavaDoc;
29 import java.sql.Clob JavaDoc;
30 import java.sql.Connection JavaDoc;
31 import java.sql.DriverManager JavaDoc;
32 import java.sql.ResultSet JavaDoc;
33 import java.sql.ResultSetMetaData JavaDoc;
34 import java.sql.SQLException JavaDoc;
35 import java.sql.Statement JavaDoc;
36 import java.sql.Types JavaDoc;
37
38 import nu.xom.Attribute;
39 import nu.xom.Document;
40 import nu.xom.Element;
41 import nu.xom.Node;
42 import nu.xom.Text;
43 import nu.xom.XMLException;
44
45
46 /**
47  * <p>
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
51  * <cite><a
52  * HREF="http://www.cafeconleche.org/books/xmljava/">Processing
53  * XML with Java</a></cite>.
54  * </p>
55  *
56  * @author Elliotte Rusty Harold
57  * @version 1.0
58  *
59  */
60 public class DatabaseConverter {
61
62     private Connection JavaDoc connection;
63   
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
70 // called
71 public DatabaseConverter(String JavaDoc jdbcURL) throws SQLException JavaDoc {
72        connection = DriverManager.getConnection(jdbcURL);
73     }
74
75     public Document extract(String JavaDoc selectQuery)
76       throws IOException JavaDoc {
77     
78         try {
79             Statement JavaDoc statement = connection.createStatement();
80             ResultSet JavaDoc data = statement.executeQuery(selectQuery);
81             ResultSetMetaData JavaDoc metadata = data.getMetaData();
82             int numFields = metadata.getColumnCount();
83       
84             Element table = new Element("table");
85           
86             while (data.next()) {
87                 Element record = new Element("record");
88                 for (int field = 1; field <= numFields; field++) {
89                 
90                     Element fieldElement = new Element("field");
91                     int type = metadata.getColumnType(field);
92                     String JavaDoc 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 JavaDoc name = metadata.getColumnName(field);
97                     fieldElement.addAttribute(new Attribute("name", name));
98           
99                     // Convert nulls to empty elements with xsi:nil="true"
100 Object JavaDoc 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"));
104                      }
105                      else { // non-null value
106 fieldElement.appendChild(convertToXML(data, field, type));
107                     }
108                     record.appendChild(fieldElement);
109                  }
110                  table.appendChild(record);
111              } // end while
112 statement.close();
113              return new Document(table);
114         }
115         catch (SQLException JavaDoc ex) { // convert exception type
116 throw new XMLException("SQL error", ex);
117         }
118     
119   }
120
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 JavaDoc data, int field, int type)
125       throws SQLException JavaDoc, IOException JavaDoc {
126
127         switch (type) {
128           case Types.BINARY:
129           case Types.VARBINARY:
130           case Types.LONGVARBINARY:
131             return hexEncode(data.getBinaryStream(field));
132           case Types.BLOB:
133             Blob JavaDoc 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.
137 case Types.CLOB:
138             Clob JavaDoc clob = data.getClob(field);
139             Reader JavaDoc r = clob.getCharacterStream();
140             char[] text = new char[1024];
141             int numRead;
142             StringBuffer JavaDoc result = new StringBuffer JavaDoc();
143             while ((numRead = r.read(text, 0, 1024)) != -1) {
144               result.append(text, 0, numRead);
145             }
146             return new Text(result.toString());
147           case Types.ARRAY:
148             Array JavaDoc array = data.getArray(field);
149             return writeArray(array);
150           default: // All other types can be handled as strings
151 Object JavaDoc o = data.getObject(field);
152             if (o == null) return new Text("");
153             return new Text(o.toString());
154         }
155
156   }
157   
158     private Text hexEncode(InputStream JavaDoc in)
159       throws IOException JavaDoc {
160     
161         StringBuffer JavaDoc result = new StringBuffer JavaDoc();
162
163         int octet;
164         while ((octet = in.read()) != -1) {
165             if (octet < 16) result.append('0');
166             result.append(Integer.toHexString(octet));
167         }
168         return new Text(result.toString());
169     
170     }
171   
172     private Element writeArray(Array JavaDoc array)
173       throws IOException JavaDoc, SQLException JavaDoc {
174     
175         Element holder = new Element("array");
176         ResultSet JavaDoc data = array.getResultSet();
177         int type = array.getBaseType();
178         String JavaDoc typeName = getSchemaType(type);
179
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);
186         }
187         return holder;
188     
189   }
190   
191   public static String JavaDoc getSchemaType(int type) {
192    
193     switch (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";
218                                 // most general type
219 default: return "xsd:string";
220     }
221     
222   }
223
224 }
Popular Tags