深入浅析mybatis oracle BLOB类型字段保存与读取
一、BLOB字段
BLOB是指二进制大对象也就是英文BinaryLargeObject的所写，而CLOB是指大字符对象也就是英文CharacterLargeObject的所写。其中BLOB是用来存储大量二进制数据的；CLOB用来存储大量文本数据。BLOB通常用来保存图片、文件等二进制类型的数据。
二、使用mybatis操作blob
1、表结构如下：
createtableBLOB_FIELD ( IDVARCHAR2(64BYTE)notnull, TAB_NAMEVARCHAR2(64BYTE)notnull, TAB_PKID_VALUEVARCHAR2(64BYTE)notnull, CLOB_COL_NAMEVARCHAR2(64BYTE)notnull, CLOB_COL_VALUECLOB, constraintPK_BLOB_FIELDprimarykey(ID) );
2、实体代码如下：
packagecom.test.entity;
importjava.sql.Clob;
/**
*大字段
*/
publicclassBlobField{
privateStringtabName;//表名
privateStringtabPkidValue;//主键值
privateStringblobColName;//列名
privatebyte[]blobColValue;//列值clob类型
publicStringgetTabName(){
returntabName;
}
publicvoidsetTabName(StringtabName){
this.tabName=tabName;
}
publicStringgetTabPkidValue(){
returntabPkidValue;
}
publicvoidsetTabPkidValue(StringtabPkidValue){
this.tabPkidValue=tabPkidValue;
}
publicStringgetBlobColName(){
returnblobColName;
}
publicvoidsetBlobColName(StringblobColName){
this.blobColName=blobColName;
}
publicbyte[]getBlobColValue(){
returnblobColValue;
}
publicvoidsetBlobColValue(byte[]blobColValue){
this.blobColValue=blobColValue;
}
}
3、mybatissql代码如下：
<?xmlversion="."encoding="UTF-"?>
<!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper.//EN""http://mybatis.org/dtd/mybatis--mapper.dtd">
<mappernamespace="com.test.dao.BlobFieldDao">
<sqlid="blobFieldColumns">
a.IDASid,
a.TAB_NAMEAStabName,
a.TAB_PKID_VALUEAStabPkidValue,
a.BLOB_COL_NAMEASblobColName,
a.BLOB_COL_VALUEASblobColValue
</sql>
<sqlid="blobFieldJoins">
</sql>
<selectid="get"resultType="blobField">
SELECT
<includerefid="blobFieldColumns"/>
FROMBLOB_FIELDa
<includerefid="blobFieldJoins"/>
WHEREa.ID=#{id}
</select>
<selectid="findList"resultType="blobField">
SELECT
<includerefid="blobFieldColumns"/>
FROMBLOB_FIELDa
<includerefid="blobFieldJoins"/>
</select>
<insertid="insert">
INSERTINTOBLOB_FIELD(
ID,
TAB_NAME,
TAB_PKID_VALUE,
BLOB_COL_NAME,
BLOB_COL_VALUE
)VALUES(
#{id},
#{tabName},
#{tabPkidValue},
#{blobColName},
#{blobColValue,jdbcType=BLOB}
)
</insert>
<updateid="update">
UPDATEBLOB_FIELDSET
TAB_NAME=#{tabName},
TAB_PKID_VALUE=#{tabPkidValue},
BLOB_COL_NAME=#{blobColName},
BLOB_COL_VALUE=#{blobColValue}
WHEREID=#{id}
</update>
<deleteid="delete">
DELETEFROMBLOB_FIELD
WHEREID=#{id}
</delete>
</mapper>
3、controller代码如下：
a、保存BLOB字段代码
/**
*附件上传
*
*@paramtestId
*主表Id
*@paramrequest
*@return
*@throwsUnsupportedEncodingException
*/
@RequiresPermissions("exc:exceptioninfo:feedback")
@RequestMapping(value="attachment",method=RequestMethod.POST)
@ResponseBody
publicMap<String,Object>uploadAttachment(@RequestParam(value="testId",required=true)StringtestId,
HttpServletRequestrequest)
throwsUnsupportedEncodingException{
Map<String,Object>result=newHashMap<String,Object>();
MultipartHttpServletRequestmultipartRequest=(MultipartHttpServletRequest)request;
//获得文件
MultipartFilemultipartFile=multipartRequest.getFile("Filedata");//与前端设置的fileDataName属性值一致
Stringfilename=multipartFile.getOriginalFilename();//文件名称
InputStreamis=null;
try{
//读取文件流
is=multipartFile.getInputStream();
byte[]bytes=FileCopyUtils.copyToByteArray(is);
BlobFieldblobField=newBlobField();
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(IOExceptione){
e.printStackTrace();
result.put("flag",false);
}finally{
IOUtils.closeQuietly(is);
}
returnresult;
}
b、读取BLOB字段
/**
*下载附件
*
*@paramattachmentId
*@return
*/
@RequiresPermissions("exc:exceptioninfo:view")
@RequestMapping(value="download",method=RequestMethod.GET)
publicvoiddownload(@RequestParam(value="attachmentId",required=true)StringattachmentId,
@RequestParam(value="attachmentName",required=true)StringattachmentName,HttpServletRequest
request,HttpServletResponseresponse){
ServletOutputStreamout=null;
try{
response.reset();
StringuserAgent=request.getHeader("User-Agent");
byte[]bytes=userAgent.contains("MSIE")?attachmentName.getBytes():attachmentName.getBytes("UTF-
");//fileName.getBytes("UTF-")处理safari的乱码问题
StringfileName=newString(bytes,"ISO--");
//设置输出的格式
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition","attachment;fileName="+URLEncoder.encode(attachmentName,
"UTF-"));
BlobFieldblobField=this.blobFieldService.get(attachmentId);
//获取blob字段
byte[]contents=blobField.getBlobColValue();
out=response.getOutputStream();
//写到输出流
out.write(contents);
out.flush();
}catch(IOExceptione){
e.printStackTrace();
}
}
本例子将文件上传并保存到BLOB类型字段字段，下载的时候读取BLOB字段，并写入成输出流。
以上就是本文的全部叙述，希望对大家有所帮助。