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

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