Java操作DB2 XML数据实践
自学了90分钟的DB2 XQuery，还不很熟悉，就要在项目中用了，心里很不踏实，还是先跑个CRUD的Demo看看，以免走弯路。
代码很粗糙，主要目的是看看JDBC是否能很好的执行这种新SQL，呵呵。
另外，在此之前，看到Oracle老大已经开始实现一个操作XML数据的规范，目前还没有正式出台，希望Sun能尽快跟进，将标准的API接口定出来，以支持广大的Java社区。项目有期限，我们也没时间等Sun给我们做好任何东西，自己动手实现吧。
下面是我做的一个Demo，希望能给正在研究这一块的朋友一点参考，XQuery SQL代码参考了DB2官方文档。
一、实现一个简单的数据库工具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();
}
}
}
}
二、写一个简单的测试类执行各种XQuery SQL
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
呵呵，终于看到运行结果了。
0
顶一下0
埋一下引用地址：
