中文XML论坛--自己写的db2xml转换程序，与大家交流

贴子主题：自己写的db2xml转换程序，与大家交流

举报打印推荐 IE收藏夹

本主题类别:

写了一个程序，两个功能
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
}


	W 3 C h i n a ( since 2003 ) 旗下站点苏ICP备05006046号《全国人大常委会关于维护互联网安全的决定》《计算机信息网络国际联网安全保护管理办法》	121.094ms