Java + XML 實現 Access 到 SQL Server 資料遷移

初學 XML 與資料庫程式設計時，面對龐大文檔往往無從下手。作者結合實際工作需求，整理出一個實用案例：使用 Java 從 Access 資料庫提取資料，經 XML 配置資料庫連線，導入 SQL Server 三張關聯表中。

資料結構對應

需將原始編號分別導入三表，並透過 ID 建立關聯。

XML 配置資料庫連線 (Dbs.xml)

<?xml version="1.0" encoding="GBK"?>
<dbcopy>
    <source>
        <class>sun.jdbc.odbc.JdbcOdbcDriver</class>
        <url>jdbc:odbc:asrs</url>
        <user>""</user>
        <password>""</password>
    </source>
    <dest>
        <class>com.microsoft.jdbc.sqlserver.SQLServerDriver</class>
        <url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=vxms</url>
        <user>vxms</user>
        <password>vxms</password>
    </dest>
</dbcopy>

核心類別設計

1. ConnPara.java - 連線參數封裝

public class ConnPara {
    private String dbClass, url, username, password;
    public ConnPara() {}
    public ConnPara(String dbClass, String url, String username, String password) {
        this.dbClass = dbClass; this.url = url; this.username = username; this.password = password;
    }
    // Getter/Setter 方法省略...
}

2. DbXmlParser.java - XML 解析器 (DOM 解析)

public class DbXmlParser {
    private static String xmlfile;
    public DbXmlParser(String filename) { xmlfile = filename; }

    private Element loadDocument() {
        try {
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse(xmlfile);
            return doc.getDocumentElement();
        } catch (Exception e) { e.printStackTrace(); return null; }
    }

    public ConnPara getSource() { /* 解析 source 節點 */ }
    public ConnPara getDest() { /* 解析 dest 節點 */ }

    private String getChildElementValue(Node node, String subTagName) {
        if (node == null) return "";
        NodeList children = node.getChildNodes();
        for (int i = 0; i < children.getLength(); i++) {
            Node child = children.item(i);
            if (child != null && subTagName.equals(child.getNodeName())) {
                Node grandChild = child.getFirstChild();
                if (grandChild != null) return grandChild.getNodeValue();
            }
        }
        return "";
    }
}

3. DbInput.java - 資料遷移核心邏輯

public class DbInput {
    private Connection src, dest, connForMax;
    private ConnPara srcPara, destPara;

    public void dbInit() {
        DbXmlParser parser = new DbXmlParser("dbs.xml");
        srcPara = parser.getSource(); destPara = parser.getDest();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            src = DriverManager.getConnection(srcPara.getUrl(), srcPara.getUsername(), srcPara.getPassword());
            dest = DriverManager.getConnection(destPara.getUrl(), destPara.getUsername(), destPara.getPassword());
            connForMax = DriverManager.getConnection(destPara.getUrl(), destPara.getUsername(), destPara.getPassword());
        } catch (Exception e) { e.printStackTrace(); System.exit(1); }
    }

    public void copyProduct() {
        try {
            Statement st = src.createStatement();
            ResultSet rs = st.executeQuery("select * from production");
            while (rs.next()) {
                String pNumber = rs.getString(1), ljNumber = rs.getString(2), cNumber = rs.getString(3);
                // 產品表
                int pid = getIdFromNumber("project", "number", pNumber);
                if (pid == 0) { pid = getMax("project"); execute(destPara, "INSERT INTO project(id,number) VALUES(" + pid + ",'" + pNumber + "')"); }
                // 零件表
                int ljid = getIdFromNumber("product", "number", ljNumber);
                if (ljid == 0) { ljid = getMax("product"); execute(destPara, "INSERT INTO product(id,pid,number) VALUES(" + ljid + "," + pid + ",'" + ljNumber + "')"); }
                // 工具表
                int cid = getIdFromNumber("componenttype", "number", cNumber);
                if (cid == 0) { cid = getMax("componenttype"); execute(destPara, "INSERT INTO componenttype(id,aid,number) VALUES(" + cid + "," + ljid + ",'" + cNumber + "')"); }
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }

    // 輔助方法：getIdFromNumber、getMax、alreadyIn、execute 等...
    public static void main(String[] args) {
        new DbInput().dbInit().copyProduct();
    }
}

常見問題與解決

1. Access 無法直接 JDBC 存取：須先配置 ODBC 系統資料源名 (DSN)，再透過 JDBC-ODBC Bridge 操作。
2. 找不到 SQLServerDriver 類別：需下載微軟 JDBC 驅動包 (msbase.jar, mssqlserver.jar, msutil.jar) 並加入 classpath。