Java操作DB2 XML数据实践

来源：中国IT实验室作者：熔岩出处：巧巧读书 2008-05-07 进入讨论组

         * 创建表
         *
         * @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();
        }