Oracle BLOB mybatis XML Read-write

The recent project used to read and write to the Oracle large pieces, the small white is recorded here, convenient for their later use, but also hope to other friends have a little help.

Because of the project, the BLOB here is only read and write to the XML message, and does not involve saving the picture, so the following methods may not be comprehensive, if necessary, please check the other great God blog.

One, read blob

Here the BLOB read is directly in the database built a function Blob_to_varchar, so that the convenience of the project in other places to use the query blob:

CREATE OR REPLACE FunctionBlob_to_varchar (blob_ininchBLOB)Return Varchar2 isV_varcharVarchar2(4000); V_start Pls_integer:= 1; V_buffer Pls_integer:= 4000;Begin    IfDbms_lob.getlength (blob_in) is Null  Then        Return "'; End If;  forIinch 1.. Ceil (Dbms_lob.getlength (blob_in)/V_buffer) Loop--when the converted string is garbled, you can try to use the commented out function        --V_varchar: = Utl_raw.cast_to_varchar2 (Utl_raw.convert (Dbms_lob.substr (blob_in, V_buffer, V_Start), ' SIMPLIFIED Chinese_china. ZHS16GBK ', ' american_the Netherlands. UTF8 '));V_varchar:=utl_raw.cast_to_varchar2 (Dbms_lob.substr (blob_in, V_buffer, V_start)); V_start:=V_start+V_buffer; EndLoop; ReturnV_varchar;EndBlob_to_varchar;

The Blob_to_varchar function created directly inside the SQL.

SELECT   as  from Inf_xml;

Second, write blob

When Oracle is saving large data, insert a empty_blob () placeholder, take up the Blob field, and then write it as a stream in the query.

The first is to insert a empty_blob () placeholder

 //insert data 
 int  insertloginf = this  Span style= "color: #000000;"                >.logcontrollerdao.insertloginfo (params);             if  (Insertloginf > 0//Insert Large field data  for  (int  I=0;i<2;i++ if  (i = = 0) {                Insertblob (I,log_id,const.getstrvalue (params,  "Req_xml" ));  else  {Insertblob (I,log_id,rs                P_xml); }}   

Insertblob method (because you want to insert 2 blobs, and can not write at the same time, so the more stupid way to cycle)

     Public voidInsertblob (intI, string log_id, String insertxml)throwsexception{BLOB blobxml=NULL;
Querying data loginterfacexml Retloginf= This. Logcontrollerdao.queryblobloginfbylogid (log_id); if(i = = 0) {Blobxml=(BLOB) retloginf.getreq_xml (); }Else{blobxml=(BLOB) retloginf.getrsp_xml (); } OutputStream Ops=NULL; Try {              byte[] data =NULL; OPS= Blobxml.setbinarystream (0); Data=insertxml.getbytes ();          Ops.write (data); } Catch(Exception e) {e.printstacktrace (); } finally {              Try {                  if(ops!=NULL) {ops.close (); }              } Catch(IOException e) {e.printstacktrace (); }          }    }

Here's req_xml rsp_xml to use Object

@Alias ("Loginterfacexml") Public classLoginterfacexml {PrivateString log_id; PrivateString Op_code;PrivateString Req_time;PrivateString Rsp_time;PrivateString ep_address;PrivateString Result_desc;Private ObjectReq_xml;Private ObjectRsp_xml; PublicString Getop_code () {returnOp_code; }     Public voidSetop_code (String op_code) { This. Op_code =Op_code; }     PublicString Getreq_time () {returnReq_time; }     Public voidsetreq_time (String req_time) { This. Req_time =Req_time; }     PublicString Getrsp_time () {returnRsp_time; }     Public voidsetrsp_time (String rsp_time) { This. Rsp_time =Rsp_time; }     PublicString getep_address () {returnep_address; }     Public voidsetep_address (String ep_address) { This. ep_address =ep_address; }     PublicString Getresult_desc () {returnResult_desc; }     Public voidSetresult_desc (String result_desc) { This. Result_desc =Result_desc; }     PublicObject Getreq_xml () {returnReq_xml; }     Public voidsetreq_xml (Object req_xml) { This. Req_xml =Req_xml; }     PublicObject Getrsp_xml () {returnRsp_xml; }     Public voidsetrsp_xml (Object rsp_xml) { This. Rsp_xml =Rsp_xml; }     PublicString getlog_id () {returnlog_id; }     Public voidsetlog_id (String log_id) { This. log_id =log_id; }    }

The operation of the database is MyBatis

    <ResultmapID= "Loginterfaceresultmap"type= "Loginterfacexml">             <result Property= "log_id"column= "id"/>             <result Property= "Op_code"column= "Op_code"/>          <result Property= "Req_time"column= "Req_time" />          <result Property= "Rsp_time"column= "Rsp_time" />          <result Property= "Ep_address"column= "Ep_address" />          <result Property= "Req_xml"column= "Req_xml"Jdbctype= "BLOB" />          <result Property= "Rsp_xml"column= "Rsp_xml"Jdbctype= "BLOB" />          <result Property= "Result_desc"column= "Result_desc" />      </Resultmap>         <SelectID= "Queryblobloginfbylogid"Resulttype= "Loginterfacexml"ParameterType= "string"databaseId= "Oracle">SELECT * from Inf_xml c where c.log_id = #{log_id} for update</Select>    <InsertID= "Insertloginfo"ParameterType= "Map"databaseId= "Oracle">INSERT INTO Inf_xml (LOG_ID,OP_CODE,REQ_TIME,RSP_TIME,EP_ADDRESS,REQ_XML,RSP_XML,STATE,RESULT_DESC) VALUES (#{log_id},#{op_code},to_date (#{req_time}, ' Yyyy-mm-dd HH24:MI:SS '), To_date (#{rsp_time}, ' Yyyy-mm-dd HH24:MI : SS '), #{ep_address},Empty_blob(),Empty_blob(), ' 1 ', #{result_desc})</Insert>

This may be due to a network problem, where the XML data is written to the blob, and there is a delay problem from time to time.

If the data is queried immediately, it may not necessarily have data.

What is the specific cause, still in the study, if there is a great god if you know, you can tell the younger brother, thank you.

Oracle BLOB mybatis XML Read-write