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="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-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-

8"); // fileName.getBytes("UTF-8")处理safari的乱码问题
            String fileName = new String(bytes, "ISO-8859-1");
            // 设置输出的格式
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(attachmentName, 

"UTF-8"));
            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字段，并写入成输出流。