Java inserts xmltype data into Oracle using the JDBC or MyBatis framework _java

Let's take a look at what the XmlType type is.
XmlType is an Oracle-specific data type from 9i, a powerful presence that inherits blobs that can be used to store XML and provide a considerable number of operational functions. You can theoretically save 2G size data.
So how do you insert xmltype types of data in Java? The project is the use of MyBatis, always appear inexplicable anomalies, are not clear whether the problem is mybatis or JDBC itself, so the plan is to step by step, first to fix JDBC, and then solve the mybatis.

Jdbc
after a long day of tossing, there are 3 main ways to find JDBC operations:
First, in Java, the XmlType is used as string strings, and the task of creating XmlType is completely handed 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 makes the database more stressful because it is simple and does not require additional dependencies, but in the first case, it was found in the actual use, when the content is longer than 4000, it is thrown: Ora-01461:can bind a LONG value for Insert into a LONG column exception. The reason for using MyBatis at first is still the case with JDBC testing, and there are a number of ways to try no solution. The use of this large segment in the project can not only save 4000 of the length of data, so the use of varchar2 enough, so the method eliminated.

Second, use the CLOB type to operate. XmlType is inherited from the existence of CLOB, so it can be operated by CLOB. The method is to construct the XmlType value via the Oracle XmlType () function after the client has created the CLOB data:

String sql = INSERT INTO xmltable (XML) VALUES (XmlType (?)); 
String XMLData = "<label>this is an XML fragment</label>"; 
Create CLOB CLOB by conn
Tempclob = Clob.createtemporary (connection, False, CLOB. duration_session);
Open CLOB
tempclob.open (CLOB. Mode_readwrite);
Obtain Writer
Writer clobwriter = Tempclob.setcharacterstream (m);
Write Data
clobwriter.write (xmldata);
Refresh
Clobwriter.flush ();
Close writer
clobwriter.close ();
Close Clob
tempclob.close ();
Pst.setobject (1, Tempclob);

This method both the client and the database assume the task of creating the XmlType, so the pressure is more average and there is no problem exceeding the length. However, in the actual use of the process also found that the content of the XML header can not contain the following information:

<?xml version= "1.0" encoding= "UTF-8"?>

Otherwise, an exception is thrown:

PI names starting with XML are reserved

Let's not say less of this in the future processing XML content contains Chinese will not encounter the scrambled egg problem, just look at let people feel bad, and demand also need to save, no way, this method is not workable.

Third, using the Oracle-provided Oracle.xdb.XMLType class, the client creates the XmlType and passes the object directly to the database:

Connection conn = ...;//Get Connection 
preparedstatement PS = ...;//Get preparedsatement 
String sql = INSERT INTO XM Ltable (XML) VALUES (?) "; 
String XMLData = "<label>this is an XML fragment</label>"; 
Create a XmlType object 
xmltype xmltype = xmltype.createxml (conn, xmldata); 
Ps.setobject (1, xmltype); 
Ps.executeupdate ();

This method gives the client the task of creating the XmlType, so the pressure on the client is high and the database pressure is small. In the measured process, you need to add two jar packages, or you will not find the class error:

Xdb.jar
Xmlparserv2.jar

Need to pay attention to this jar package and no version callout, it is easy to mistake, at first I downloaded a xdb.jar, how to do without the hint can not find a class, after viewing the discovery is belonging to an earlier version of Oracle, after downloading a xdb.jar normal.
The above three methods are compared by inserting 200,000 data tests to find:

The first method: the shortest time consuming, the server CPU consumption is the largest;
The second method: the longest time consuming, the server CPU consumption centered;
The third way: The time is centered, the server CPU consumption is minimal.

At this point, the JDBC operation xmltype type data is finally a small fix, needless to say, the third option, but the basic project will not directly use JDBC to operate, like the current project on the use of MyBatis, the above also talked about the use of mybatis always appear abnormal, Look at the next MyBatis also did not xmltype the realization, it seems that some toss, but JDBC has been done, the idea has been clear is not?

MyBatis
using the MyBatis operation XmlType, we also map to string types on the Java side, and when the direct operation does not do any processing, as in the case of JDBC, everything is normal when the content of the transmission is less than 4000, and when the content of the transmission is longer than 4000, Also throws an exception:

Ora-01461:can bind a Long value only as insert into a long column

Visible, the MyBatis operation is actually the same as JDBC, but it is in the outside of JDBC and encapsulated a layer, so that we can use the configuration file, such as mapping the way to more convenient access to the database, we have to do, is to implement the insertion of XmlType type data on the basis of the original MyBatis convenience, in which case, implementing a custom Typehandler processor of XmlType type is the best choice.

Here, we still use the scenario mentioned above, nature's two jar packages: Xdb.jar,xmlparserv2.jar is also to join.

Add a Xmltypetypehandler, implement the Typehandler interface, because the insertion of data is mainly used in the Setparameter method, so this only lists the method, the other method code slightly:

/**
 * 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 mybatis to set the parameters when inserting data into the database, and as for the parameters of this method I believe you see the code, and we follow the implementation of the previous JDBC, insert the following code here:

public void Setparameter (preparedstatement ps, int i, String parameter, Jdbctype jdbctype) throws SQLException {XMLT
 ype XmlType = Xmltype.createxml (Ps.getconnection (), parameter);
 Ps.setobject (I,xmltype);
}

and register the converter in Mapper-config.xml, because MyBatis defines an enumeration org.apache.ibatis.type.JdbcType, there is no xmltype type we need, where we define undefined:

<configuration>
 <typeHandlers>
  <typehandler javatype= "string" jdbctype= "UNDEFINED" Handler = "Com.tyyd.dw.context.XmltypeTypeHandler"/>
 </typeHandlers>
</configuration>

In the configuration file parameter, use our defined converter so that MyBatis can find:

#{xmlfile,jdbctype=undefined},

Of course, you can also be a bit more normative, complete writing its type and the use of the converter:

#{xmlfile,javatype=string,jdbctype=undefined,typehandler=com.tyyd.dw.context.xmltypetypehandler},
By completing the above steps, it is supposed to be all done, let's run it.

The result throws an exception:java.lang.ClassCastException: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection

Unable to convert to Oracle's Connection object OracleConnection, check to see that our data source is using Apache dbcp, it should be incompatible. Online check, a man said is to give a perfect solution copy, is in the Setparameter method to load an Oracle driver class alone to create a connection, as follows:

Class.forName ("Oracle.jdbc.OracleDriver");
Connection Connection = drivermanager.getconnection (URL, username, password);

This can really 100% solve the problem of the connection object can not be converted, but the implementation of the way, hehe, or do not comment. There are also online transmission, said it can be converted into Poolableconnection objects, and then use the Getdelegate method can get the original agent link, this seemingly feasible, we try to:

Poolableconnection connection = (poolableconnection) ps.getconnection ();
XmlType XmlType = Xmltype.createxml (Connection.getdelegate (), parameter);
Ps.setobject (I,xmltype);

The result throws another exception:

org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to org.apache.commons.dbcp.PoolableConnectionand cannot be converted.

No way, it seems that the online transmission of the article is not reliable, there is no shortcut or look at 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 turn it into a delegatingconnection try:

Delegatingconnection connection = (delegatingconnection) ps.getconnection ();
XmlType XmlType = Xmltype.createxml (Connection.getdelegate (), parameter);
Ps.setobject (I,xmltype);

Result throws an exception: unable to construct descriptor: Invalid arguments; Nested exception is java.sql.SQLException: Unable to construct descriptor: Invalid arguments, through breakpoint debugging, found that the connection object is actually null, how can it be null? Online people are all good, to me here will not be, really egg pain, this will not have no solution, it is really like the above that the person said to load a driver class alone? No way, study it again.

Finally found that through the GetMetaData method can get its original agent connection, ah, hurriedly write test, finally normal, not easy Ah, the final code is as follows:

@Override public
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 the MyBatis operation XmlType type is finally done, the process is twists ah. The data has to insert of course must have the query, next will implement XmlType type of query operation.