• 深入淺析mybatis oracle BLOB類型字段保存與讀取

     更新時間:2015年10月16日 15:23:39   作者:煙火_  
    本文給大家淺析mybatis oracle blob類型字段的保存與讀取,blob字段是指二進制大對象,用來存儲大量文本數據。感興趣的朋友一起學習吧

    一、BLOB字段

      BLOB是指二進制大對象也就是英文Binary Large Object的所寫,而CLOB是指大字符對象也就是英文Character Large Object的所寫。其中BLOB是用來存儲大量二進制數據的;CLOB用來存儲大量文本數據。BLOB通常用來保存圖片、文件等二進制類型的數據。

    二、使用mybatis操作blob

      1、表結構如下:

    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、實體代碼如下:

     package com.test.entity;
     import java.sql.Clob; 
     /**
      * 大字段 
      */
     public class BlobField { 
       private String tabName;// 表名
       private String tabPkidValue;// 主鍵值
       private String blobColName;// 列名
       private byte[] blobColValue;// 列值 clob類型
       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、mybatis sql代碼如下:

     <?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、controller代碼如下:

      a、保存BLOB字段代碼

    /**
        * 附件上傳
        * 
        * @param testId
        *      主表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;
         // 獲得文件
         MultipartFile multipartFile = multipartRequest.getFile("Filedata");// 與前端設置的fileDataName屬性值一致
         String filename = multipartFile.getOriginalFilename();// 文件名稱
         InputStream is = null;
         try {
           //讀取文件流
           is = multipartFile.getInputStream();
           byte[] bytes = FileCopyUtils.copyToByteArray(is);
           BlobField blobField = new BlobField();
           blobField.setTabName("testL");
           blobField.setTabPkidValue(testId);
           blobField.setBlobColName("attachment");
           blobField.setBlobColValue(bytes);
           //保存blob字段
           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、讀取BLOB字段

     /**
        * 下載附件
        * 
        * @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) {
         ServletOutputStream out = null;
         try {
           response.reset();
           String userAgent = request.getHeader("User-Agent");
           byte[] bytes = userAgent.contains("MSIE") ? attachmentName.getBytes() : attachmentName.getBytes("UTF-
     "); // fileName.getBytes("UTF-")處理safari的亂碼問題
           String fileName = new String(bytes, "ISO--");
           // 設置輸出的格式
           response.setContentType("multipart/form-data");
           response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(attachmentName, 
     "UTF-"));
           BlobField blobField = this.blobFieldService.get(attachmentId);
           //獲取blob字段
           byte[] contents = blobField.getBlobColValue();
           out = response.getOutputStream();
           //寫到輸出流
           out.write(contents);
           out.flush();
         } catch (IOException e) {
           e.printStackTrace();
         }
       }        

      本例子將文件上傳并保存到BLOB類型字段字段,下載的時候讀取BLOB字段,并寫入成輸出流。

    以上就是本文的全部敘述,希望對大家有所幫助。

    相關文章

    最新評論

    美丽人妻被按摩中出中文字幕