Java操作DB2 XML数据实践

import java.sql.*; 

/** 
* 简单的数据连接工具 
* File: DBUtils.java 
* User: leizhimin 
* Date: 2008-3-18 15:19:12 
*/ 
public  class DBUtils { 
     public  static  final String url =  "jdbc:db2://192.168.3.143:50000/lavasoft"; 
    public static final String username = "lavasoft"; 
    public static final String password = "lavasoftdb2"; 
    public static final String driverClassName = "com.ibm.db2.jcc.DB2Driver"; 

    /** 
     * 获取数据库连接Connection 
     * 
     * @return 数据库连接Connection 
     */ 
    public static Connection makeConnection() { 
        Connection conn = null; 
        try { 
            Class.forName(driverClassName); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
        try { 
            conn = DriverManager.getConnection(url, username, password); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return conn; 
    } 

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

    /** 
     * 测试连接方法 
     */ 
    public static void testConnection() { 
        Connection conn = makeConnection(); 
        try { 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT * FROM DM_HYML"); 
            while (rs.next()) { 
                String s1 = rs.getString(1); 
                String s2 = rs.getString(2); 
                System.out.println(s1 + s2); 
            } 
            rs.close(); 
            stmt.close(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
}

import com.topsoft.icib.common.utils.DBUtils; 

import java.sql.*; 
import java.io.ByteArrayInputStream; 
import java.io.InputStream; 
import java.io.IOException; 

/** 
* DB2 XML数据操作测试 
* File: TestXMLDAO.java 
* User: leizhimin 
* Date: 2008-3-18 16:33:51 
*/ 
public  class TestDB2XML { 

     /** 
     * 预删除表Customer 
     * 
     * @throws SQLException 
     */ 
     public  static  void testDropXMLTable()  throws SQLException { 
        String drop_sql =  "DROP TABLE Customer"; 
        Connection conn = DBUtils.makeConnection(); 
        Statement stmt = conn.createStatement(); 
        stmt.executeUpdate(drop_sql); 
        stmt.close(); 
        conn.close(); 
    } 

     /** 
     * 创建表 
     * 
     * @throws SQLException 
     */ 
     public  static  void testCreateXMLTable()  throws SQLException { 
        String ct_sql =  "CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML)"; 
        Connection conn = DBUtils.makeConnection(); 
        Statement stmt = conn.createStatement(); 
        stmt.executeUpdate(ct_sql); 
        stmt.close(); 
        conn.close(); 
    } 

     /** 
     * 插入数据 
     * 
     * @throws SQLException 
     * @throws IOException 
     */ 
     public  static  void testInsertXMLTable()  throws SQLException, IOException { 
        String xml =  "<customerinfo xmlns=\"http://posample.org\" Cid=\"1000\">\n" + 
                "<name>Robert Shoemaker</name>\n" + 
                "<addr country=\"Canada\">\n" + 
                "<street>1596 Baseline</street>\n" + 
                "<city>zhengzhou</city>\n" + 
                "<prov-state>Ontario</prov-state>\n" + 
                "<pcode-zip>N8X 7F8</pcode-zip>\n" + 
                "</addr>\n" + 
                "<phone type=\"work\">905-555-2937</phone>\n" + 
                "</customerinfo>"; 
        String ins_sql = "INSERT INTO CUSTOMER (CID, INFO) VALUES (1000, ?)"; 
        Connection conn = DBUtils.makeConnection(); 
        conn.setAutoCommit(false); 
        PreparedStatement pstmt = conn.prepareStatement(ins_sql); 
        byte[] b = xml.getBytes(); 
        InputStream ins = new ByteArrayInputStream(b); 
        pstmt.setBinaryStream(1, ins, b.length); 
        pstmt.executeUpdate(); 
        conn.commit(); 
        ins.close(); 
        pstmt.close(); 
        conn.close(); 
    } 

    /** 
     * XQuery查询数据 
     * 
     * @throws SQLException 
     */ 
    public static void testQueryXMLTable() throws SQLException { 
        String query_sql = "SELECT XMLQUERY (\n" + 
                "'declare default element namespace \"http://posample.org\";\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 \"http://posample.org\";\n" + 
                "$i/customerinfo/addr[city=\"zhengzhou\"]' passing c.INFO as \"i\")"; 
        Connection conn = DBUtils.makeConnection(); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(query_sql); 
        StringBuffer xmls = new StringBuffer(); 
        while (rs.next()) { 
            xmls.append(rs.getString(1)).append("\n"); 
        } 
        System.out.println(xmls.toString()); 
        stmt.close(); 
        conn.close(); 
    } 

    /** 
     * XQuery更新数据 
     * 
     * @throws SQLException 
     * @throws IOException 
     */ 
    public static void testUpdateXMLTable() throws SQLException, IOException { 
        String xml = "<customerinfo xmlns=\"http://posample.org\" Cid=\"1002\">\n" + 
                "<name>Jim Noodle</name>\n" + 
                "<addr country=\"Canada\">\n" + 
                "<street>1150 Maple Drive</street>\n" + 
                "<city>Newtown</city>\n" + 
                "<prov-state>Ontario</prov-state>\n" + 
                "<pcode-zip>Z9Z 2P2</pcode-zip>\n" + 
                "</addr>\n" + 
                "<phone type=\"work\">905-555-7258</phone>\n" + 
                "</customerinfo>"; 
        String up_sql = "UPDATE customer SET info =?" + 
                "WHERE XMLEXISTS (\n" + 
                "'declare default element namespace \"http://posample.org\";\n" + 
                "$doc/customerinfo[@Cid = 1000]'\n" + 
                "passing INFO as \"doc\")"; 

        Connection conn = DBUtils.makeConnection(); 
        conn.setAutoCommit(false); 
        PreparedStatement pstmt = conn.prepareStatement(up_sql); 
        byte[] b = xml.getBytes(); 
        InputStream ins = new ByteArrayInputStream(b); 
        pstmt.setBinaryStream(1, ins, b.length); 
        pstmt.executeUpdate(); 
        conn.commit(); 
        ins.close(); 
        pstmt.close(); 
        conn.close(); 
    } 

    /** 
     * 查询xml列数据，用于验证 
     * 
     * @throws SQLException 
     */ 
    public static void testQueryXMLColumn() throws SQLException { 
        String query_sql = "SELECT INFO FROM Customer"; 
        Connection conn = DBUtils.makeConnection(); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(query_sql); 
        StringBuffer xmls = new StringBuffer(); 
        while (rs.next()) { 
            xmls.append(rs.getString(1)).append("\n"); 
        } 
        System.out.println(xmls.toString()); 
        stmt.close(); 
        conn.close(); 
    } 

    /** 
     * 测试入口，方法组调用 
     * 
     * @param rags 
     * @throws Exception 
     */ 
    public static void main(String rags[]) throws Exception { 
        testDropXMLTable(); 
        testCreateXMLTable(); 
        testInsertXMLTable(); 
        testQueryXMLTable(); 
        testUpdateXMLTable(); 
        testQueryXMLColumn(); 
    } 
}

< out  xmlns ="http://posample.org" > < name >Robert Shoemaker </ name > </ out > 

< customerinfo  xmlns ="http://posample.org"  Cid ="1002" > < name >Jim Noodle </ name > < addr  country ="Canada" > < street >1150 Maple Drive </ street > < city >Newtown </ city > < prov-state >Ontario </ prov-state > < pcode-zip >Z9Z 2P2 </ pcode-zip > </ addr > < phone  type ="work" >905-555-7258 </ phone > </ customerinfo > 


Process finished with exit code 0