Java操作DB2 XML數據實踐

　　Java操作DB XML數據實踐

　　自學了分鐘的DB XQuery還不很熟悉就要在項目中用了心裡很不踏實還是先跑個CRUD的Demo看看以免走彎路

　　代碼很粗糙主要目的是看看JDBC是否能很好的執行這種新SQL呵呵

　　另外在此之前看到Oracle老大已經開始實現一個操作XML數據的規范目前還沒有正式出台希望Sun能盡快跟進將標准的API接口定出來以支持廣大的Java社區項目有期限我們也沒時間等Sun給我們做好任何東西自己動手實現吧

　　下面是我做的一個Demo希望能給正在研究這一塊的朋友一點參考XQuery SQL代碼參考了DB官方文檔

　　一實現一個簡單的數據庫工具import javasql*

　　* 簡單的數據連接工具
    * File: DBUtilsjava
    * User: leizhimin
    * Date: ::
    */
    public class DBUtils {
        public static final String url = jdbc:db://:/lavasoft;
        public static final String username = lavasoft;
        public static final String password = lavasoftdb;
        public static final String driverClassName = comibmdbjccDBDriver;

　　/**
         * 獲取數據庫連接Connection
         *
         * @return 數據庫連接Connection
         */
        public static Connection makeConnection() {
            Connection conn = null;
            try {
                ClassforName(driverClassName);
            } catch (ClassNotFoundException e) {
                eprintStackTrace();
            }
            try {
                conn = DriverManagergetConnection(url username password);
            } catch (SQLException e) {
                eprintStackTrace();
            }
            return conn;
        }

　　public static void main(String args[]) {
            testConnection();
        }

　　/**
* 測試連接方法
         */
        public static void testConnection() {
            Connection conn = makeConnection();
            try {
                Statement stmt = conncreateStatement();
                ResultSet rs = stmtexecuteQuery(SELECT * FROM DM_HYML);
                while (rsnext()) {
                    String s = rsgetString();
                    String s = rsgetString();
                    Systemoutprintln(s + s);
                }
                rsclose();
                stmtclose();
            } catch (SQLException e) {
                eprintStackTrace();
            } finally {
                try {
                    connclose();
                } catch (SQLException e) {
                    eprintStackTrace();
                }
            }
        }
    }

　　二寫一個簡單的測試類執行各種XQuery SQL

　　import monutilsDBUtils;

　　import javasql*;
    import javaioByteArrayInputStream;
    import javaioInputStream;
    import javaioIOException;

　　/**
    * DB XML數據操作測試
    * File: TestXMLDAOjava
    * User: leizhimin
    * Date: ::
    */
    public class TestDBXML {

　　/**
         * 預刪除表Customer
         *
         * @throws SQLException
         */
        public static void testDropXMLTable() throws SQLException {
            String drop_sql = DROP TABLE Customer;
            Connection conn = DBUtilsmakeConnection();
            Statement stmt = conncreateStatement();
            stmtexecuteUpdate(drop_sql);
            stmtclose();
            connclose();
        }

　　/**

* 創建表
         *
         * @throws SQLException
         */
        public static void testCreateXMLTable() throws SQLException {
            String ct_sql = CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY Info XML);
            Connection conn = DBUtilsmakeConnection();
            Statement stmt = conncreateStatement();
            stmtexecuteUpdate(ct_sql);
            stmtclose();
            connclose();
        }

　　/**
* 插入數據
         *
         * @throws SQLException
         * @throws IOException
         */
        public static void testInsertXMLTable() throws SQLException IOException {
            String xml = <customerinfo xmlns=\\ Cid=\\>\n +
                    <name>Robert Shoemaker</name>\n +
                    <addr country=\Canada\>\n +
                    <street> Baseline</street>\n +
                    <city>zhengzhou</city>\n +
                    <provstate>Ontario</provstate>\n +
                    <pcodezip>NX F</pcodezip>\n +
                    </addr>\n +
                    <phone type=\work\></phone>\n +
                    </customerinfo>;
            String ins_sql = INSERT INTO CUSTOMER (CID INFO) VALUES ( ?);
            Connection conn = DBUtilsmakeConnection();
            connsetAutoCommit(false);
            PreparedStatement pstmt = connprepareStatement(ins_sql);
            byte[] b = xmlgetBytes();
            InputStream ins = new ByteArrayInputStream(b);
            pstmtsetBinaryStream( ins blength);
            pstmtexecuteUpdate();
            mit();
            insclose();
            pstmtclose();
            connclose();
        }

　　/**
* XQuery查詢數據
         *
         * @throws SQLException
         */
        public static void testQueryXMLTable() throws SQLException {
            String query_sql = SELECT XMLQUERY (\n +
                    declare default element namespace \\;\n +
                    for $d in $doc/customerinfo\n +
                    return <out>{$d/name}</out>\n +
                    passing INFO as \doc\)\n +
                    FROM Customer as c\n +
                    WHERE XMLEXISTS (declare default element namespace \\;\n +
                    $i/customerinfo/addr[city=\zhengzhou\] passing cINFO as \i\);
            Connection conn = DBUtilsmakeConnection();
            Statement stmt = conncreateStatement();
            ResultSet rs = stmtexecuteQuery(query_sql);
            StringBuffer xmls = new StringBuffer();
            while (rsnext()) {
                xmlsappend(rsgetString())append(\n);
            }
            Systemoutprintln(xmlstoString());
            stmtclose();
            connclose();
        }

　　/**
* XQuery更新數據
         *
         * @throws SQLException
         * @throws IOException
         */
        public static void testUpdateXMLTable() throws SQLException IOException {
            String xml = <customerinfo xmlns=\\ Cid=\\>\n +
                    <name>Jim Noodle</name>\n +
                    <addr country=\Canada\>\n +
                    <street> Maple Drive</street>\n +
                    <city>Newtown</city>\n +
                    <provstate>Ontario</provstate>\n +
                    <pcodezip>ZZ P</pcodezip>\n +
                    </addr>\n +
                    <phone type=\work\></phone>\n +
                    </customerinfo>;
            String up_sql = UPDATE customer SET info =? +
                    WHERE XMLEXISTS (\n +
                    declare default element namespace \\;\n +
                    $doc/customerinfo[@Cid = ]\n +
                    passing INFO as \doc\);

　　Connection conn = DBUtilsmakeConnection();
            connsetAutoCommit(false);
            PreparedStatement pstmt = connprepareStatement(up_sql);
            byte[] b = xmlgetBytes();
            InputStream ins = new ByteArrayInputStream(b);
            pstmtsetBinaryStream( ins blength);
            pstmtexecuteUpdate();
            mit();
            insclose();
            pstmtclose();
            connclose();
        }

* 查詢xml列數據用於驗證
         *
         * @throws SQLException
         */
        public static void testQueryXMLColumn() throws SQLException {
            String query_sql = SELECT INFO FROM Customer;
            Connection conn = DBUtilsmakeConnection();
            Statement stmt = conncreateStatement();
            ResultSet rs = stmtexecuteQuery(query_sql);
            StringBuffer xmls = new StringBuffer();
            while (rsnext()) {
                xmlsappend(rsgetString())append(\n);
            }
            Systemoutprintln(xmlstoString());
            stmtclose();
            connclose();
        }

　　/**
* 測試入口方法組調用
         *
         * @param rags
         * @throws Exception
         */
        public static void main(String rags[]) throws Exception {
            testDropXMLTable();
            testCreateXMLTable();
            testInsertXMLTable();
            testQueryXMLTable();
            testUpdateXMLTable();
            testQueryXMLColumn();
        }
    }

　　三運行結果

　　<out xmlns=Robert>><name>Robert Shoemaker</name></out>

　　<customerinfo xmlns= Cid=><name>Jim Noodle</name><addr country=Canada><street> Maple Drive</street><city>Newtown</city><provstate>Ontario</provstate><pcodezip>ZZ P</pcodezip></addr><phone type=work></phone></customerinfo>

　　Process finished with exit code

　　呵呵終於看到運行結果了

From:http://tw.wingwit.com/Article/program/Java/hx/201311/25786.html