写了一个程序，两个功能
1，用jdbc从db中读出数据，再用dom写入到xml文档
2，用dom遍历转换所得的xml文档，写入到数据库中
在access下，测试成功。
存在问题；
本来设计目标是对任意数据库转换为xml，但在转换sql server为xml文档时总出错。
不知什么原因，请教高手指点。
xml结构：
<database database_name="" dbms_name="">
<table tabla_name="">
<recorder ...
以下是db2xml的源程序；先谢了！
package dbxml;
import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.DOMImplementation;
import org.w3c.dom.DocumentType;
import org.w3c.dom.Element;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.eclipse.swt.widgets.List;
import java.io.FileOutputStream;
import java.sql.*;
public class Db2XML {
Connection connect; //connect to the database
Statement sql;
String[] att,ele;
int attnum,elenum;
ResultSet rsc;
ResultSetMetaData rsmd;
String attdata,databasename;
DatabaseMetaData dbmd;
public Db2XML(String dbname,Connection connection) {
databasename=dbname;
connect=connection;
}
//*******TABLE Tag********
public boolean TableExist(String tname){
for (int m=0;m<attnum;m++){
int index=att[m].indexOf(".");
if(att[m].substring(0,index).equals(tname))
{
return true;
}
}
for (int n=0;n<elenum;n++){
int index=ele[n].indexOf(".");
if(ele[n].substring(0,index).equals(tname))
{
return true;
}
}
return false;
}
public void WriteTable(String f,List selectatt,List selectele) throws Exception
{
att=selectatt.getItems();
ele=selectele.getItems();
attnum=selectatt.getItemCount();
elenum=selectele.getItemCount();
dbmd = connect.getMetaData();
String SYSTEM_ID = "db.dtd";
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
DOMImplementation domImpl = builder.getDOMImplementation();
////////////////////////////////////////////
DocumentType docType = domImpl.createDocumentType("DATABASE",
null,
SYSTEM_ID);
Document dtddocument = domImpl.createDocument("", "DATABASE", docType);
//root
Document document = domImpl.createDocument("", "DATABASE", null);
Element root = document.getDocumentElement();
//root
Attr rootattr1=document.createAttribute("DATABASE_NAME");
rootattr1.setValue(databasename);
root.setAttributeNode(rootattr1);
//root DBMS
Attr rootattr2=document.createAttribute("DBMS_NAME");
rootattr2.setValue(dbmd.getDatabaseProductName());
root.setAttributeNode(rootattr2);
try {
ResultSet rst;
//DatabaseMetaData dbmd;
//dbmd = connect.getMetaData();
String[] type = { "table" };
String tablename;
rst = dbmd.getTables(null, null, null, type);
while (rst.next()) {
tablename = rst.getString("TABLE_NAME");
if(TableExist(tablename)){
//table tag
Element child1 = document.createElement("TABLE");
//table tag table
Attr attr=document.createAttribute("TABLE_NAME");
attr.setValue(tablename);
child1.setAttributeNode(attr);
WriteColumn(document,child1,tablename);
root.appendChild(child1);
int i=0;
i++;
System.out.print(i);
}
}
rst.close();
} catch (Exception e) {
System.out.println(e.getMessage()+"rst");
}
//the foot of tablename
//attnum=0;
TransformerFactory transFactory = TransformerFactory.newInstance();
Transformer transformer = transFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, "yes");
transformer.setOutputProperty(OutputKeys.METHOD, "xml");
transformer.setOutputProperty(OutputKeys.DOCTYPE_SYSTEM, SYSTEM_ID);
//transformer.setOutputProperty(OutputKeys.DOCTYPE_PUBLIC, PUBLIC_ID);
DOMSource source = new DOMSource(document);
FileOutputStream os = new FileOutputStream(f);
StreamResult result = new StreamResult(os);
transformer.transform(source, result);
}
public void WriteColumn(Document document,Element child1,String tablename)
{
try {
ResultSet rsc;
//ResultSetMetaData rsmd;
sql = connect.createStatement();
rsc = sql.executeQuery("SELECT * FROM " + tablename);
ResultSet rscinfo;
rscinfo=dbmd.getColumns(null,null,tablename,"%");
//****************************************************
//******TABLE TAG DataType******************
//****************************************************
//DataType tag
Element child1_0 = document.createElement("DataType");
//DataType TAG
while(rscinfo.next()){
//DataType
Element child1_0_1 =document.createElement(rscinfo.getString("COLUMN_NAME"));
//DATA_TYPE
Attr attr1=document.createAttribute("DATA_TYPE");
attr1.setValue(rscinfo.getString("DATA_TYPE"));
child1_0_1.setAttributeNode(attr1);
//DATA_TYPE
Attr attr2=document.createAttribute("TYPE_NAME");
attr2.setValue(rscinfo.getString("TYPE_NAME"));
child1_0_1.setAttributeNode(attr2);
//DATA_TYPE
Attr attr3=document.createAttribute("COLUMN_SIZE");
attr3.setValue(rscinfo.getString("COLUMN_SIZE"));
child1_0_1.setAttributeNode(attr3);
//DATA_TYPE
Attr attr4=document.createAttribute("NULLABLE");
attr4.setValue(rscinfo.getString("NULLABLE"));
child1_0_1.setAttributeNode(attr4);
//DATA_TYPE
//Attr attr5=document.createAttribute("REMARKS");
//attr5.setValue(rscinfo.getString(2));
//child1_0_1.setAttributeNode(attr5);
//child1_0_1.appendChild(document.createTextNode(rscinfo.getString(6)+"("+rscinfo.getString("COLUMN_SIZE")+")"));
child1_0.appendChild(child1_0_1);
}
//rscinfo.deleteRow();
rscinfo.close();
child1.appendChild(child1_0);
//*****************************************************
//******TABLE TAGNI RECORDER******************
//*****************************************************
while (rsc.next()) {
//RECORDER tag
Element child1_1 = document.createElement("RECORDER");
//*******Recorder************
for (int m=0;m<attnum;m++){
int index=att[m].indexOf(".");
if(att[m].substring(0,index).equals(tablename))
{
Attr attr=document.createAttribute(att[m].substring(att[m].indexOf(".")+1));
attdata=rsc.getString(att[m].substring(att[m].indexOf(".")+1));
attr.setValue(attdata);
child1_1.setAttributeNode(attr);
}
}
//*******Recorder************
for (int n=0;n<elenum;n++){
int index=ele[n].indexOf(".");
if(ele[n].substring(0,index).equals(tablename))
{
Element child1_1_1 =document.createElement(ele[n].substring(ele[n].indexOf(".")+1));
//Text data=document.createTextNode(rsc.getString(ele[n].substring(ele[n].indexOf(".")+1)));
//child1_1_1.setData(rsc.getString(ele[n].substring(ele[n].indexOf(".")+1)));
child1_1_1.appendChild(document.createTextNode(rsc.getString(ele[n].substring(ele[n].indexOf(".")+1))));
child1_1.appendChild(child1_1_1);
}
}
child1.appendChild(child1_1);
}//the end of while (rsc.next())
//rsc.deleteRow();
rsc.close();
}//the end of try
catch (Exception e) {
System.out.println(e.getMessage()+"ggg");
}
} //the end of public void writecolumn
}
[返回] 中文XML论坛 - 专业的XML技术讨论区 →
XML.ORG.CN讨论区 - 高级XML应用 → 『 XML 与 数据库 』 → 如何将数据库中的数据导出成XML文件？
(订阅本版)
2004/8/26 11:01:00
新浪微博
