-- 作者：s02r307
-- 发布时间：12/28/2004 1:25:00 AM
-- 自己写的db2xml转换程序，与大家交流
写了一个程序，两个功能
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
}
|