深入浅析mybatis oracle BLOB类型字段保存与读取

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)

);

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;

   }

}

<?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>

/**

     * 附件上传

     *

     * @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;

    }

/**

    * 下载附件

    *

    * @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();

     }

   }