一、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、实体代码如下：
1 package com.test.entity; 2 3 import java.sql.Clob; 4 5 /** 6 * 大字段 7 */ 8 public class BlobField { 9 10 private String tabName;// 表名11 private String tabPkidValue;// 主键值12 private String blobColName;// 列名13 private byte[] blobColValue;// 列值 clob类型14 15 public String getTabName() {16 return tabName;17 }18 19 public void setTabName(String tabName) {20 this.tabName = tabName;21 }22 23 public String getTabPkidValue() {24 return tabPkidValue;25 }26 27 public void setTabPkidValue(String tabPkidValue) {28 this.tabPkidValue = tabPkidValue;29 }30 31 public String getBlobColName() {32 return blobColName;33 }34 35 public void setBlobColName(String blobColName) {36 this.blobColName = blobColName;37 }38 39 public byte[] getBlobColValue() {40 return blobColValue;41 }42 43 public void setBlobColValue(byte[] blobColValue) {44 this.blobColValue = blobColValue;45 }46 47 }
3、mybatis sql代码如下：
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.test.dao.BlobFieldDao"> 4 5 <sql id="blobFieldColumns"> 6 a.ID AS id, 7 a.TAB_NAME AS tabName, 8 a.TAB_PKID_VALUE AS tabPkidValue, 9 a.BLOB_COL_NAME AS blobColName,10 a.BLOB_COL_VALUE AS blobColValue11 </sql>12 13 <sql id="blobFieldJoins">14 </sql>15 16 <select id="get" resultType="blobField">17 SELECT18 <include refid="blobFieldColumns" />19 FROM BLOB_FIELD a20 <include refid="blobFieldJoins" />21 WHERE a.ID = #{id}22 </select>23 24 <select id="findList" resultType="blobField">25 SELECT26 <include refid="blobFieldColumns" />27 FROM BLOB_FIELD a28 <include refid="blobFieldJoins" />29 </select> 30 31 <insert id="insert">32 INSERT INTO BLOB_FIELD(33 ID ,34 TAB_NAME ,35 TAB_PKID_VALUE ,36 BLOB_COL_NAME ,37 BLOB_COL_VALUE38 ) VALUES (39 #{id},40 #{tabName},41 #{tabPkidValue},42 #{blobColName},43 #{blobColValue,jdbcType=BLOB}44 )45 </insert>46 47 <update id="update">48 UPDATE BLOB_FIELD SET49 TAB_NAME = #{tabName},50 TAB_PKID_VALUE = #{tabPkidValue},51 BLOB_COL_NAME = #{blobColName},52 BLOB_COL_VALUE = #{blobColValue}53 WHERE ID = #{id}54 </update>55 <delete id="delete">56 DELETE FROM BLOB_FIELD 57 WHERE ID = #{id}58 </delete>59 60 </mapper>
3、controller代码如下：
a、保存BLOB字段代码
1 /** 2 * 附件上传 3 * 4 * @param testId 5 * 主表Id 6 * @param request 7 * @return 8 * @throws UnsupportedEncodingException 9 */10 @RequiresPermissions("exc:exceptioninfo:feedback")11 @RequestMapping(value = "attachment", method = RequestMethod.POST)12 @ResponseBody13 public Map<String, Object> uploadAttachment(@RequestParam(value = "testId", required = true) String testId, 14 15 HttpServletRequest request)16 throws UnsupportedEncodingException {17 Map<String, Object> result = new HashMap<String, Object>();18 19 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;20 // 获得文件21 MultipartFile multipartFile = multipartRequest.getFile("Filedata");// 与前端设置的fileDataName属性值一致22 String filename = multipartFile.getOriginalFilename();// 文件名称23 InputStream is = null;24 try {25 //读取文件流26 is = multipartFile.getInputStream();27 byte[] bytes = FileCopyUtils.copyToByteArray(is);28 BlobField blobField = new BlobField();29 blobField.setTabName("testL");30 blobField.setTabPkidValue(testId);31 blobField.setBlobColName("attachment");32 blobField.setBlobColValue(bytes);33 //保存blob字段34 this.testService.save(blobField, testId, filename);35 result.put("flag", true);36 result.put("attachmentId", blobField.getId());37 result.put("attachmentName", filename);38 } catch (IOException e) {39 e.printStackTrace();40 result.put("flag", false);41 } finally {42 IOUtils.closeQuietly(is);43 }44 return result;45 }
b、读取BLOB字段
1 /** 2 * 下载附件 3 * 4 * @param attachmentId 5 * @return 6 */ 7 @RequiresPermissions("exc:exceptioninfo:view") 8 @RequestMapping(value = "download", method = RequestMethod.GET) 9 public void download(@RequestParam(value = "attachmentId", required = true) String attachmentId,10 @RequestParam(value = "attachmentName", required = true) String attachmentName, HttpServletRequest 11 12 request, HttpServletResponse response) {13 ServletOutputStream out = null;14 try {15 response.reset();16 String userAgent = request.getHeader("User-Agent");17 byte[] bytes = userAgent.contains("MSIE") ? attachmentName.getBytes() : attachmentName.getBytes("UTF-18 19 8"); // fileName.getBytes("UTF-8")处理safari的乱码问题20 String fileName = new String(bytes, "ISO-8859-1");21 // 设置输出的格式22 response.setContentType("multipart/form-data");23 response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(attachmentName, 24 25 "UTF-8"));26 BlobField blobField = this.blobFieldService.get(attachmentId);27 //获取blob字段28 byte[] contents = blobField.getBlobColValue();29 out = response.getOutputStream();30 //写到输出流31 out.write(contents);32 out.flush();33 } catch (IOException e) {34 e.printStackTrace();35 }36 }
本例子将文件上传并保存到BLOB类型字段字段，下载的时候读取BLOB字段，并写入成输出流。