In-depth analysis of saving and reading mybatis oracle BLOB fields, mybatisblob

In-depth analysis of saving and reading mybatis oracle BLOB fields, mybatisblob

1. BLOB Field

BLOB refers to the writing of a Binary Large Object, that is, the English Binary Large Object, and CLOB refers to the writing of a Large Character Object, that is, the English Character Large Object. BLOB is used to store a large amount of binary data, and CLOB is used to store a large amount of text data. BLOB is usually used to store binary data such as images and files.

2. Use mybatis to operate blob

　　1. The table structure is as follows:

create table BLOB_FIELD(  ID          VARCHAR2(64 BYTE)  not null,  TAB_NAME       VARCHAR2(64 BYTE)  not null,  TAB_PKID_VALUE    VARCHAR2(64 BYTE)  not null,  CLOB_COL_NAME    VARCHAR2(64 BYTE)  not null,  CLOB_COL_VALUE    CLOB,  constraint PK_BLOB_FIELD primary key (ID));

　　2. The entity code is as follows:

Package com. test. entity; import java. SQL. clob;/*** large field */public class BlobField {private String tabName; // table name private String tabPkidValue; // primary key value private String blobColName; // column name private byte [] blobColValue; // column value clob Type public String getTabName () {return tabName;} public void setTabName (String tabName) {this. tabName = tabName;} public String getTabPkidValue () {return tabPkidValue;} public void setTabPkidValue (String tabPkidValue) {this. tabPkidValue = tabPkidValue;} public String getBlobColName () {return blobColName;} public void setBlobColName (String blobColName) {this. blobColName = blobColName;} public byte [] getBlobColValue () {return blobColValue;} public void setBlobColValue (byte [] blobColValue) {this. blobColValue = blobColValue ;}}

　　3. The mybatis SQL code is as follows:

 <?xml version="." encoding="UTF-" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper .//EN" "http://mybatis.org/dtd/mybatis--mapper.dtd"> <mapper namespace="com.test.dao.BlobFieldDao">   <sql id="blobFieldColumns">     a.ID AS id,     a.TAB_NAME AS tabName,     a.TAB_PKID_VALUE AS tabPkidValue,     a.BLOB_COL_NAME AS blobColName,     a.BLOB_COL_VALUE AS blobColValue   </sql>   <sql id="blobFieldJoins">   </sql>   <select id="get" resultType="blobField">     SELECT     <include refid="blobFieldColumns" />     FROM BLOB_FIELD a     <include refid="blobFieldJoins" />     WHERE a.ID = #{id}   </select>   <select id="findList" resultType="blobField">     SELECT     <include refid="blobFieldColumns" />     FROM BLOB_FIELD a     <include refid="blobFieldJoins" />   </select>    <insert id="insert">     INSERT INTO BLOB_FIELD(     ID ,     TAB_NAME ,     TAB_PKID_VALUE ,     BLOB_COL_NAME ,     BLOB_COL_VALUE     ) VALUES (     #{id},     #{tabName},     #{tabPkidValue},     #{blobColName},     #{blobColValue,jdbcType=BLOB}     )   </insert>   <update id="update">     UPDATE BLOB_FIELD SET     TAB_NAME = #{tabName},     TAB_PKID_VALUE = #{tabPkidValue},     BLOB_COL_NAME = #{blobColName},     BLOB_COL_VALUE = #{blobColValue}     WHERE ID = #{id}   </update>   <delete id="delete">     DELETE FROM BLOB_FIELD      WHERE ID = #{id}   </delete> </mapper>

3. The controller code is as follows:

　　A. Save the BLOB field code.

/*** Upload an attachment ** @ param testId * Primary table Id * @ param request * @ return * @ throws UnsupportedEncodingException */@ RequiresPermissions ("exc: exceptioninfo: feedback ") @ RequestMapping (value = "attachment", method = RequestMethod. POST) @ ResponseBody public Map <String, Object> uploadAttachment (@ RequestParam (value = "testId", required = true) String testId, HttpServletRequest request) throws UnsupportedEncodingException {Map <String, object> result = new HashMap <String, Object> (); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; // obtain the file MultipartFile multipartFile = multipartRequest. getFile ("Filedata"); // consistent with the fileDataName attribute value set by the front-end, String filename = multipartFile. getOriginalFilename (); // file name InputStream is = null; try {// read file stream is = multipartFile. getInputStream (); byte [] bytes = FileCopyUtils. copyToByteArray (is); BlobField blobField = new BlobField (); blobField. setTabName ("testL"); blobField. setTabPkidValue (testId); blobField. setBlobColName ("attachment"); blobField. setBlobColValue (bytes); // Save the blob field this. testService. save (blobField, testId, filename); result. put ("flag", true); result. put ("attachmentId", blobField. getId (); result. put ("attachmentName", filename);} catch (IOException e) {e. printStackTrace (); result. put ("flag", false);} finally {IOUtils. closeQuietly (is);} return result ;}

　B. Read BLOB fields

/*** Download Attachment ** @ param attachmentId * @ return */@ RequiresPermissions ("exc: exceptioninfo: view") @ RequestMapping (value = "download", method = RequestMethod. GET) public void download (@ RequestParam (value = "attachmentId", required = true) String attachmentId, @ RequestParam (value = "attachmentName", required = true) String attachmentName, httpServletRequest request, HttpServletResponse response) {Servl EtOutputStream out = null; try {response. reset (); String userAgent = request. getHeader ("User-Agent"); byte [] bytes = userAgent. contains ("MSIE ")? AttachmentName. getBytes (): attachmentName. getBytes ("UTF-"); // fileName. getBytes ("UTF-") handles safari garbled characters. String fileName = new String (bytes, "ISO --"); // sets the output format response. setContentType ("multipart/form-data"); response. setHeader ("Content-Disposition", "attachment; fileName =" + URLEncoder. encode (attachmentName, "UTF-"); BlobField blobField = this. blobFieldService. get (attachmentId); // obtain the blob field byte [] contents = blobField. getBlobColValue (); out = response. getOutputStream (); // write to output stream out. write (contents); out. flush ();} catch (IOException e) {e. printStackTrace ();}}

In this example, the file is uploaded and saved to the BLOB field. When downloading, The BLOB field is read and written to the output stream.

The above is all the descriptions in this article, and I hope to help you.