Let’s first understand what XMLType type is.
XMLType is a unique data type for Oracle since 9i. It is a powerful existence that inherits the Blob. It can be used to store XML and provides a lot of operation functions. In theory, it can save 2G data.
So how do you insert XMLType data through Java? The project uses Mybatis, and there are always inexplicable exceptions. I can't figure out whether it is Mybatis' problem or jdbc itself, so I plan to do it step by step, first solve jdbc, and then solve Mybatis.
JDBC
After a long time of struggle, I found that there are three main methods for jdbc operation:
1. Use XMLType as a string String in Java, and the specific task of creating XMLType is completely handed over to the database:
String sql = "insert into xmltable (XML) values(sys.xmlType.createXML(?))"; String xmldata = "<label>This is an XML fragment</label>"; ps.setString(1, xmldata); ps.executeUpdate();
This method will make the database stress too much, because this method is simple and does not require additional dependencies. This method was used at the beginning, but during actual use, it was found that when the length of the content exceeds about 4000, it will throw: ORA-01461: can bind a LONG value only for insert into a LONG column exception. At first I thought the reason for using mybatis was still the same when using jdbc test, and there was no solution when using many methods. It is impossible to save data with a length of less than 4000 when using this large field in the project. This way, using varchar2 is enough, so this method is eliminated.
2. Use the CLOB type to operate. XMLType inherits the existence of CLOB, so it can be operated through CLOB. The method is to create CLOB data on the client and pass it into the database to construct the XMLType value through Oracle's XMLTYPE() function:
String sql = "insert into xmltable (XML) values(XMLType(?))"; String xmldata = "<label>This is an XML fragment</label>"; //Create CLOBCLOB tempClob = CLOB.createTemporary(connection, false, CLOB.DURATION_SESSION); //Open CLOBtempClob.open(CLOB.MODE_READWRITE); //Get writerWriter clobWriter = tempClob.setCharacterStream(100);//Write data clobWriter.write(xmldata);//Fresh clobWriter.flush();//Close writerclobWriter.close();//Close CLOBtempClob.close(); pst.setObject(1, tempClob);
This method client and database are responsible for creating XMLType at the same time, so the pressure is relatively average and there is no problem of exceeding the length. However, during actual use, it was found that the content header of the xml cannot contain the following information:
<?xml version="1.0" encoding="UTF-8"?>
Otherwise, an exception will be thrown:
PI names starting with XML are reserved
Let’s not talk about whether you will encounter any garbled code problems when processing the content of xml inclusion in Chinese in the future. Just looking at it makes people feel uncomfortable, and the requirements also require saving. There is no way, and this method will not work.
3. Use the oracle.xdb.XMLType class provided by Oracle. After the client creates XMLType, the object is directly passed to the database:
Connection conn = ... ;//Get Connection PreparedStatement ps = ...;//Get PreparedSatement String sql = "insert into xmltable (XML) values(?)"; String xmldata = "<label>This is an XML fragment</label>"; //Create an XMLType object XMLType xmltype = XMLType.createXML(conn, xmldata); ps.setObject(1, xmltype); ps.executeUpdate();
This method completely handes the task of creating XMLType to the client, so the client is under great pressure and the database is under low pressure. During the actual test, two jar packages need to be added, otherwise the class cannot be found error:
xdb.jarxmlparserv2.jar
It is necessary to note that this jar package does not have version annotation, so it is easy to make mistakes. At the beginning, I downloaded an xdb.jar, but no matter how I did it, it was prompted that I could not find a certain class. After checking, I found that it belongs to an earlier version of oracle. After downloading an xdb.jar again, it is normal.
The above three methods were compared by inserting 200,000 pieces of data:
The first method: the shortest time and the server CPU consumption is the largest;
The second method: the longest time is used, and the server CPU consumption is centered;
The third method: time-consuming and centered, the server CPU consumption is minimal.
At this point, jdbc has finally done some small things in the operation of XMLType type data. Needless to say, the third solution is adopted, but the project basically does not directly use jdbc to operate. For example, in the current project, Mybatis is used. The above also mentioned that there are always exceptions when using Mybatis. After checking Mybatis, there is no implementation of XMLType. It seems that there are still some troubles, but jdbc has been done, so the idea is clear, right?
Mybatis
Using Mybatis to operate XMLType, we also map to String type on the Java side. When the direct operation does not do any processing, like jdbc, everything is normal when the transmitted content is less than 4000. When the transmitted content is more than about 4000, an exception is also thrown:
ORA-01461: can bind a LONG value only for insert into a LONG column
It can be seen that Mybatis's operation is actually the same as jdbc, except that it encapsulates a layer outside jdbc, so that we can use configuration files and other mapping methods to more conveniently access the database. What we need to do is to insert XMLType type data based on the original Mybatis convenience. In this case, implementing a custom TypeHandler processor of XMLType type is the best choice.
Here, we still use the solution three mentioned above. Naturally, the two jar packages: xdb.jar and xmlparserv2.jar also need to be added.
Add an XmltypeTypeHandler to implement the TypeHandler interface. Since inserting data mainly uses the setParameter method, only this method is listed here. The other method code is omitted:
/** * oracle SYS.XMLTYPE Type Custom Processor*/public class XmltypeTypeHandler implements TypeHandler<String> { @Override public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { } ...}This setParameter method is used by Mybatis to set parameters when inserting data into the database. As for the parameters of this method, I believe you have already understood the code. We will insert the following code here according to the previous jdbc implementation method:
public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { XMLType xmltype = XMLType.createXML(ps.getConnection(), parameter); ps.setObject(i,xmltype);}And register the converter in mapper-config.xml, because in the enumeration defined by Mybatis org.apache.ibatis.type.JdbcType, there is no XMLType type we need, here we define it as UNDEFINED:
<configuration> <typeHandlers> <typeHandler javaType="string" jdbcType="UNDEFINED" handler="com.tyyd.dw.context.XmltypeTypeHandler"/> </typeHandlers></configuration>
In the configuration file parameters, use our defined converter so that Mybatis can find it:
#{xmlFile,jdbcType=UNDEFINED},Of course, you can also be more standardized and write out its type and the converter you use in a complete manner:
#{xmlFile,javaType=string,jdbcType=UNDEFINED,typeHandler=com.tyyd.dw.context.XmltypeTypeHandler},
Complete the above steps and logically everything is done, let’s run it.
The result is thrown: java.lang.ClassCastException: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
It cannot be converted into Oracle's connection object OracleConnection. After checking, we found that our data source uses apache's dbcp, which should be incompatible with the two. I checked online and a guy said that he gave a perfect solution, which is to load an Oracle driver class in the setParameter method to create a connection, as follows:
Class.forName("oracle.jdbc.OracleDriver");Connection connection = DriverManager.getConnection(url, username, password);This can indeed solve the problem that the connection object cannot be converted 100%, but in terms of implementation, haha, I still won’t comment. There are also people passing around the Internet, saying that they can be converted into a PoolableConnection object, and then use the getDelegate method to obtain the original proxy link. This seems feasible, let's try:
PoolableConnection connection = (PoolableConnection )ps.getConnection();XMLType xmltype = XMLType.createXML(connection.getDelegate(), parameter);ps.setObject(i,xmltype);
As a result, another exception was thrown:
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to org.apache.commons.dbcp.PoolableConnection , cannot be converted.
There is no way, it seems that the articles circulating online are not reliable, so there is no shortcut, so you should check the source code yourself.
By looking at the source code, we found that PoolableConnection inherits the DelegatingConnection class, and the DelegatingConnection class implements the Connection interface. Let's convert it into a DelegatingConnection to try:
DelegatingConnection connection = (DelegatingConnection )ps.getConnection();XMLType xmltype = XMLType.createXML(connection.getDelegate(), parameter);ps.setObject(i,xmltype);
As a result, an exception was thrown: Unable to construct descriptors: Invalid arguments; nested exception is java.sql.SQLException: Unable to construct descriptors: Invalid arguments, through breakpoint debugging, I found that the connection object is actually null. How could it be null? People on the Internet use it well, but it won't work with me. It's really a pain. This is not unsolvable. Do you really have to load a driver class alone like the guy above said? There is no way, let's study it again.
Finally, I found that the original proxy connection can be obtained through the getMetaData method. It is so bright and the test is so clear. It is finally normal and not easy. The final code is as follows:
@Overridepublic void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { DelegatingConnection connection = (DelegatingConnection) ps.getConnection().getMetaData() .getConnection(); XMLType xmltype = XMLType.createXML(connection.getDelegate(), parameter); ps.setObject(i, xmltype);}At this point, using Mybatis to operate XMLType types has finally been done, and the process is full of twists and turns. Of course, there must be queries when data is inserted. Next, we need to implement XMLType type query operations.