深入浅析mybatis oracle 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字段，并写入成输出流。