你的位置:首页 > 数据库

[数据库]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、实体代码如下:

 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 <??> 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字段,并写入成输出流。