比如,Oracle中有一张含有SYS.XMLTYPE字段,如何向其中插入数据呢?
create table COMPANY (
ID VARCHAR2(255) NOT NULL ,
XMLRECORD SYS.XMLTYPE
)
可以使用如下方法:
String id = "recid";
String xml = "......";
String sql1 = "insert into COMPANY (id, xmlrecord) values ('" + id + "', sys.xmlType.createXML('" + xml + "'))";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql1);
当xml不长时,此方法可行,但当xml很长时,会报
ORA-01704: 字符串文字太长
于是,想到用如下方法:
String id = "recid";
String xml = "......";
String sql2 = "insert into COMPANY (id, xmlrecord) values (?, sys.xmlType.createXML(?))";
PreparedStatement ps = conn.prepareStatement(sql2);
ps.setString(1, id);
ps.setClob(2, new StringReader(xml));
ps.executeUpdate();
运行时会报:
java.lang.ClassNotFoundException: oracle.xdb.XMLType
java.lang.ClassNotFoundException: oracle.xml.binxml.BinXMLMetadataProvider
这是由于缺少xdb.jar、xmlparserv2.jar这两个文件造成的,添加上就行了。
再换如下方法度一下:
String id = "recid";
String xml = "......";
String sql3 = "insert into COMPANY (id, xmlrecord) values (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql3);
ps.setString(1, id);
SQLXML sqlXML = conn.createSQLXML();
sqlXML.setString(xml);
ps.setSQLXML(2, sqlXML);
ps.executeUpdate();
运行时会报:
Class oracle.xdb.XMLType does not implement the requested interface java.sql.SQLXML
这是由于从Oracle Database 11g Release 2 (11.2.0.3)开始,oracle.xdb.XMLType才实现java.sql.SQLXML接口,将xdb.jar更新成11.2.0.3以上版本即可。
参考文档
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/Java-DOM-API-for-XMLType.html
https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb11jav.htm#ADXDB4939
https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/index.html