深入浅析mybatis oracle BLOB类型字段保存与读取
程序员文章站
2022-07-01 19:12:09
一、blob字段
blob是指二进制大对象也就是英文binary large object的所写,而clob是指大字符对象也就是英文character large o...
一、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字段,并写入成输出流。
以上就是本文的全部叙述,希望对大家有所帮助。