Oracle blob mybatis xml read/write, blobmybatis

Oracle blob mybatis xml read/write, blobmybatis

Recently, the project used reading and writing of large oracle fields. Here, I want to record it for your convenience. I also hope it will be helpful to other friends.

For project reasons, blob only reads and writes xml packets, and does not involve image storage. Therefore, the following methods may not be comprehensive, if necessary, check other blogs.

1. Read blob

Here, the reading of blob directly creates a function Blob_To_Varchar in the database to facilitate the query of blob elsewhere in the project:

Create or replace Function Blob_To_Varchar (Blob_In Blob) Return Varchar2Is V_Varchar Varchar2 (4000); V_Start Pls_Integer: = 1; V_Buffer Pls_Integer: = 4000; Begin If Dbms_Lob.Getlength (Blob_In) is Null Then Return ''; End If; For I In 1 .. ceil (Dbms_Lob.Getlength (Blob_In)/V_Buffer) Loop -- when the converted string is garbled, you can try to use the annotated function -- V_Varchar: = encode (Utl_Raw.Convert (Dbms_Lob.Substr (Blob_In, V_Buffer, v_Start), 'simplified chinese_china.zhs16gbk', 'American _ the netherlands. utf8'); V_Varchar: = reverse (Dbms_Lob.Substr (Blob_In, V_Buffer, V_Start); V_Start: = V_Start + V_Buffer; End Loop; Return V_Varchar; End Blob_To_Varchar;

Directly use the created Blob_To_Varchar function in SQL.

SELECT  Blob_To_Varchar(req_tpl) as req_tpl FROM inf_xml;

Ii. Write blob

When you store big data in oracle, You need to insert an empty_blob () placeholder to occupy the blob field, and then write the large field as a stream after querying.

Insert an empty_blob () placeholder.

// Insert data
Int insertLogInf = this. logControllerDao. insertLogInfo (params); if (insertLogInf> 0 ){
// Insert the big 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, rsp_xml );}}}

InsertBlob method (because we want to insert two blobs and cannot write them in at the same time, we use a stupid method to loop)

Public void insertBlob (int I, String log_id, String insertXml) throws Exception {BLOB blobXML = null;
// Query 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, req_xml rsp_xml must use Object

@Alias("logInterfaceXML")public class LogInterfaceXML {        private String log_id;    private String op_code ;     private String req_time ;     private String rsp_time ;     private String ep_address ;    private String result_desc ;     private Object req_xml ;     private Object rsp_xml ;        public String getOp_code() {        return op_code;    }    public void setOp_code(String op_code) {        this.op_code = op_code;    }    public String getReq_time() {        return req_time;    }    public void setReq_time(String req_time) {        this.req_time = req_time;    }    public String getRsp_time() {        return rsp_time;    }    public void setRsp_time(String rsp_time) {        this.rsp_time = rsp_time;    }    public String getEp_address() {        return ep_address;    }    public void setEp_address(String ep_address) {        this.ep_address = ep_address;    }    public String getResult_desc() {        return result_desc;    }    public void setResult_desc(String result_desc) {        this.result_desc = result_desc;    }    public Object getReq_xml() {        return req_xml;    }    public void setReq_xml(Object req_xml) {        this.req_xml = req_xml;    }    public Object getRsp_xml() {        return rsp_xml;    }    public void setRsp_xml(Object rsp_xml) {        this.rsp_xml = rsp_xml;    }    public String getLog_id() {        return log_id;    }    public void setLog_id(String log_id) {        this.log_id = log_id;    }    }

Mybatis is used for database operations.

    <resultMap id="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>         <select id="queryBlobLogInfByLogid" resultType="logInterfaceXML" parameterType="string" databaseId="oracle">        select * from inf_xml c where c.log_id = #{log_id} for update    </select>    <insert id="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>

 

It may be due to network problems. The xml data written to blob may cause latency issues from time to time.

If you query data immediately, it may not be necessary to have data.

What is the specific cause? if any of you know what is still under study, please let me know. Thank you.