把Oracle或SQLServer表中数据转换成XML的工具

package tabxml.function; import tabxml.entity.*; import org.w3c.dom.*; import javax.xml.parsers.*; import java.sql.*; import javax.xml.transform.dom.*; import javax.xml.transform.stream.*; import javax.xml.transform.*; public class XmlFunction { private Connection conn = null; private PreparedStatement ps = null; private Statement stat = null; private CallableStatement cs = null; private ResultSet rs = null; public XmlFunction() { } // 从config.xml获得Document对象 public Document getConfig(String realPath) { Document doc = null; DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance(); try { DocumentBuilder parser = dbf.newDocumentBuilder(); doc = parser.parse(realPath + "WEB-INF/config.xml"); } catch (Exception ex) { ex.printStackTrace(); } return doc; } // 根据数据库类别从Document对象中取得数据库连接信息，包装成DbBean返回 public DbBean getConnInfo(String catalog, String realPath) { DbBean db = new DbBean(); try { // 调用前面定义的方法获得Document对象 Document doc = getConfig(realPath); // 获取所有<数据库类别>的标签集合 NodeList nlDbs = doc.getElementsByTagName(catalog); // 找到<数据库类别>标签 Element eCatalog = (Element) nlDbs.item(0); // 取<数据库类别>下<classname>的文本 String classname = eCatalog.getElementsByTagName("classname").item( 0).getFirstChild().getNodeValue(); if (classname == null) { classname = ""; } // 取<数据库类别>下<url>的文本 String url = eCatalog.getElementsByTagName("url").item(0) .getFirstChild().getNodeValue(); if (url == null) { url = ""; } // 取<数据库类别>下<host>的文本 String host = null; Node nHost = eCatalog.getElementsByTagName("host").item(0); // 如果<host>为空标签，host="" if (nHost.hasChildNodes()) { host = nHost.getFirstChild().getNodeValue(); } else { host = ""; } // 取<数据库类别>下<port>的文本 String port = eCatalog.getElementsByTagName("port").item(0) .getFirstChild().getNodeValue(); if (port == null) { port = ""; } // 取<数据库类别>下<sid_databasename>的文本 String sid_databasename = null; Node nSid_databasename = eCatalog.getElementsByTagName( "sid_databasename").item(0); // 如果<sid_databasename>为空标签，sid_databasename="" if (nSid_databasename.hasChildNodes()) { sid_databasename = nSid_databasename.getFirstChild() .getNodeValue(); } else { sid_databasename = ""; } // 取<数据库类别>下<user>的文本 String user = null; Node nUser = eCatalog.getElementsByTagName("user").item(0); // 如果<user>为空标签，user="" if (nUser.hasChildNodes()) { user = nUser.getFirstChild().getNodeValue(); } else { user = ""; } // 取<数据库类别>下<password>的文本 String password = null; Node nPassword = eCatalog.getElementsByTagName("password").item(0); // 如果<password>为空标签，password="" if (nPassword.hasChildNodes()) { password = nPassword.getFirstChild().getNodeValue(); } else { password = ""; } // 把以上从config.xml中读取来的数据库连接信息包装到DbBean中去，返回DbBean对象 db.setCatalog(catalog); db.setClassname(classname); db.setUrl(url); db.setHost(host); db.setPort(port); db.setSid_databasename(sid_databasename); db.setUser(user); db.setPassword(password); } catch (Exception ex) { ex.printStackTrace(); } return db; } // 获取Document对象 public Document getDoc(XmlBean xb, DbBean db) { Document doc = null; // 从XmlBean中取出checkbox选中的字段名称数组、表名 String[] colNames = xb.getColNames(); String tname = xb.getTname(); // 把checkbox选中的字段名称拼成字符串，中间用“,”分隔 String sColNames = ""; for (int i = 0; i < colNames.length; i++) { sColNames += (i == 0) ? colNames[i] : (", " + colNames[i]); } // 把字段名称拼成的字符串写进SQL语句 String sql = ""; if (db.getCatalog().equals("Oracle")) { sql = "select " + sColNames + " from " + tname; } else if (db.getCatalog().equals("SQLServer")) { sql = "select " + sColNames + " from [" + tname + "]"; } conn = DbFunction.getConnection(db); try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance(); DocumentBuilder parser = dbf.newDocumentBuilder(); // 生成一棵空DOM树 doc = parser.newDocument(); // 创建根元素<表> tname = tname.replace(" ", "_"); Element root = doc.createElement(tname); // <表>附加到DOM树 doc.appendChild(root); while (rs.next()) { // 创建<row> Element row = doc.createElement("row"); // <row>附加到<表> root.appendChild(row); for (int i = 0; i < colNames.length; i++) { // 创建<字段> Element eCol = doc.createElement(colNames[i]); // <字段>附加到<row> row.appendChild(eCol); // 取字段的值 String sTemp = rs.getString(i + 1); // 如果字段值为null，不创建该<字段>的文本节点 if (sTemp != null) { // 如果字段值不为null，创建<字段>的文本节点 Text tCol = doc.createTextNode(sTemp); // 文本节点附加到<字段> eCol.appendChild(tCol); } } } } catch (Exception ex) { ex.printStackTrace(); } finally { close(); } return doc; } // 把DOM树写出到XML public boolean writeXml(XmlBean xb, DbBean db, String realPath) { boolean b = false; try { Document doc = getDoc(xb, db); DOMSource ds = new DOMSource(doc); // 生成的XML文件都保存在/tabxml/trash文件夹里 String tname = xb.getTname(); StreamResult sr = new StreamResult(realPath+ "trash/" + tname + ".xml"); TransformerFactory tf = TransformerFactory.newInstance(); Transformer t = tf.newTransformer(); // 设置转换器的输出参数 t.setOutputProperty("encoding", "GBK"); t.transform(ds, sr); b = true; } catch (Exception ex) { ex.printStackTrace(); } finally { close(); } return b; } // 按XSL把XML转换成HTML public boolean writeHtml(String sXsl, String sXml, String sHtml) { boolean b = false; // 实例化转换器工厂 TransformerFactory tf = TransformerFactory.newInstance(); // XSL源 Source xslSource = new StreamSource(sXsl); // XML源 Source xmlSource = new StreamSource(sXml); // HTML结果 Result htmlResult = new StreamResult(sHtml); try { // 转换器工厂按XSL做一个转换器 Transformer t = tf.newTransformer(xslSource); // 设置转换器的输出参数 t.setOutputProperty("encoding", "GBK"); // 把XML转换为HTML t.transform(xmlSource, htmlResult); b = true; } catch (Exception ex) { ex.printStackTrace(); } return b; } // 关闭 public void close() { try { if (rs != null) { rs.close(); } } catch (Exception ex) { ex.printStackTrace(); } try { if (stat != null) { stat.close(); } } catch (Exception ex1) { ex1.printStackTrace(); } try { if (ps != null) { ps.close(); } } catch (Exception ex2) { ex2.printStackTrace(); } try { if (cs != null) { cs.close(); } } catch (Exception ex3) { ex3.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception ex4) { ex4.printStackTrace(); } } }