Mybatis stores and reads data from BLOB-type oracle fields, and mybatisblob

Mybatis stores and reads data from BLOB-type oracle fields, and 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:

1 package com. test. entity; 2 3 import java. SQL. clob; 4 5/** 6 * large field 7 */8 public class BlobField {9 10 private String tabName; // table name 11 private String tabPkidValue; // primary key value 12 private String blobColName; // column name 13 private byte [] blobColValue; // column value clob type 14 15 public String getTabName () {16 return tabName; 17} 18 19 public void setTabName (String tabName) {20 this. tabName = tabName; 21} 22 23 public String getTabPkidValue () {24 return tabPkidValue; 25} 26 27 public void setTabPkidValue (String tabPkidValue) {28 this. tabPkidValue = tabPkidValue; 29} 30 31 public String getBlobColName () {32 return blobColName; 33} 34 35 public void setBlobColName (String blobColName) {36 this. blobColName = blobColName; 37} 38 39 public byte [] getBlobColValue () {40 return blobColValue; 41} 42 43 public void setBlobColValue (byte [] blobColValue) {44 this. blobColValue = blobColValue; 45} 46 47}

3. The mybatis SQL code is as follows:

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.test.dao.BlobFieldDao"> 4  5     <sql id="blobFieldColumns"> 6         a.ID AS id, 7         a.TAB_NAME AS tabName, 8         a.TAB_PKID_VALUE AS tabPkidValue, 9         a.BLOB_COL_NAME AS blobColName,10         a.BLOB_COL_VALUE AS blobColValue11     </sql>12 13     <sql id="blobFieldJoins">14     </sql>15 16     <select id="get" resultType="blobField">17         SELECT18         <include refid="blobFieldColumns" />19         FROM BLOB_FIELD a20         <include refid="blobFieldJoins" />21         WHERE a.ID = #{id}22     </select>23 24     <select id="findList" resultType="blobField">25         SELECT26         <include refid="blobFieldColumns" />27         FROM BLOB_FIELD a28         <include refid="blobFieldJoins" />29     </select> 30 31     <insert id="insert">32         INSERT INTO BLOB_FIELD(33         ID ,34         TAB_NAME ,35         TAB_PKID_VALUE ,36         BLOB_COL_NAME ,37         BLOB_COL_VALUE38         ) VALUES (39         #{id},40         #{tabName},41         #{tabPkidValue},42         #{blobColName},43         #{blobColValue,jdbcType=BLOB}44         )45     </insert>46 47     <update id="update">48         UPDATE BLOB_FIELD SET49         TAB_NAME = #{tabName},50         TAB_PKID_VALUE = #{tabPkidValue},51         BLOB_COL_NAME = #{blobColName},52         BLOB_COL_VALUE = #{blobColValue}53         WHERE ID = #{id}54     </update>55     <delete id="delete">56         DELETE FROM BLOB_FIELD 57         WHERE ID = #{id}58     </delete>59     60 </mapper>

3. The controller code is as follows:

A. Save the BLOB field code.

1/** 2 * Attachment upload 3*4 * @ param testId 5 * Primary table Id 6 * @ param request 7 * @ return 8 * @ throws UnsupportedEncodingException 9 */10 @ RequiresPermissions ("exc: predictioninfo: feedback ") 11 @ RequestMapping (value =" attachment ", method = RequestMethod. POST) 12 @ ResponseBody13 public Map <String, Object> uploadAttachment (@ RequestParam (value = "testId", required = true) String testId, 14 15 HttpServletRequest request) 16 throws UnsupportedEncodingException {17 Map <String, Object> result = new HashMap <String, Object> (); 18 19 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; 20 // get file 21 MultipartFile multipartFile = multipartRequest. getFile ("Filedata"); // consistent with the fileDataName attribute value set at the front end 22 String filename = multipartFile. getOriginalFilename (); // file name 23 InputStream is = null; 24 try {25 // read file stream 26 is = multipartFile. getInputStream (); 27 byte [] bytes = FileCopyUtils. copyToByteArray (is); 28 BlobField blobField = new BlobField (); 29 blobField. setTabName ("testL"); 30 blobField. setTabPkidValue (testId); 31 blobField. setBlobColName ("attachment"); 32 blobField. setBlobColValue (bytes); 33 // save blob field 34 this. testService. save (blobField, testId, filename); 35 result. put ("flag", true); 36 result. put ("attachmentId", blobField. getId (); 37 result. put ("attachmentName", filename); 38} catch (IOException e) {39 e. printStackTrace (); 40 result. put ("flag", false); 41} finally {42 IOUtils. closeQuietly (is); 43} 44 return result; 45}

B. Read BLOB fields

1/** 2 * Download Attachment 3*4 * @ param attachmentId 5 * @ return 6 */7 @ RequiresPermissions ("exc: exceptioninfo: view ") 8 @ RequestMapping (value = "download", method = RequestMethod. GET) 9 public void download (@ RequestParam (value = "attachmentId", required = true) String attachmentId, 10 @ RequestParam (value = "attachmentName", required = true) String attachmentName, httpServletRequest 11 12 request, HttpServlet Response response) {13 ServletOutputStream out = null; 14 try {15 response. reset (); 16 String userAgent = request. getHeader ("User-Agent"); 17 byte [] bytes = userAgent. contains ("MSIE ")? AttachmentName. getBytes (): attachmentName. getBytes ("UTF-18 19 8"); // fileName. getBytes ("UTF-8") handles safari garbled problem 20 String fileName = new String (bytes, "ISO-8859-1"); 21 // sets the output format 22 response. setContentType ("multipart/form-data"); 23 response. setHeader ("Content-Disposition", "attachment; fileName =" + URLEncoder. encode (attachmentName, 24 25 "UTF-8"); 26 BlobField blobField = this. blobFieldService. get (attachmentId); 27 // get blob field 28 byte [] contents = blobField. getBlobColValue (); 29 out = response. getOutputStream (); 30 // write to output stream 31 out. write (contents); 32 out. flush (); 33} catch (IOException e) {34 e. printStackTrace (); 35} 36}

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.