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
呵呵终于看到运行结果了
