深入浅析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;     // 获得文件/*获得文件也可以这样写Iterator<String> iterator = multipartRequest.getFileNames();MultipartFile multipartFile = multipartRequest.getFile(iterator.next());*/     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字段，并写入成输出流。

以上就是本文的全部叙述，希望对大家有所帮助。