package tsinghuaip;

import java.util.*;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;
import org.w3c.dom.Document;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.xml.transform.dom.*;
import javax.xml.transform.*;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Text;

public class GenXML {
    private String strXmlFileName; //IP地址XML文件名
    private Connection conn = null;
    private ResultSet rs = null;
    private Statement stmt = null;
    private ResultSetMetaData resultmetadata = null;
    private PreparedStatement statement = null;
    private ResultSet resultset = null;

    private DocumentBuilderFactory docBuilderFactory;
    private DocumentBuilder docBuilder;
    private Document doc;
    private Element root;

    public GenXML() {
        strXmlFileName = new String();

        try {
            docBuilderFactory = DocumentBuilderFactory.newInstance();
            docBuilder = docBuilderFactory.newDocumentBuilder();
            doc = docBuilder.newDocument();
            doc.normalize();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    //設置XML文件名
    public void SetXmlFileName(String strFileName) {
        strXmlFileName = strFileName;
    }

    public void SaveIPFromDB() throws Exception {
        String strColName = new String();
        String strColVal = new String();

        //進行數據庫得連接
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        String url = "jdbc:microsoft:sqlserver://localhost:1433;" +
            "DatabaseName=CampusIP";
        conn = DriverManager.getConnection(url, "sa", "");
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);

        statement = conn.prepareStatement("select * from IPInfo");
        resultset = statement.executeQuery();
        resultmetadata = resultset.getMetaData();
        int intCols = resultmetadata.getColumnCount();

        root = doc.createElement("IPADDRESS");
         while (resultset.next()) {
            Element nodeIPPhase = doc.createElement("IPPHASE");

            for (int i = 1; i <= intCols; i++) {
                strColName = resultmetadata.getColumnName(i);
                strColVal = resultset.getString(i);

                Element nodeElement = doc.createElement(strColName);
                Text data = doc.createTextNode(strColVal);
                nodeElement.appendChild(data);
                nodeIPPhase.appendChild(nodeElement);

                System.out.println(strColVal);
            }
            root.appendChild(nodeIPPhase);
        }

        doc.appendChild(root);

        Source source = new DOMSource(doc);
        File file = new File(strXmlFileName);
        BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(strXmlFileName));
        StreamResult result = new StreamResult(bufferedWriter);
        Transformer xformer = TransformerFactory.newInstance().newTransformer();

        xformer.transform(source, result);
    }
}