深入浅析mybatis oracle BLOB类型字段保存与读取
发布时间:2016-11-27 20:45:48 所属栏目:大数据 来源:站长网
导读:一、BLOB字段 BLOB是指二进制大对象也就是英文Binary Large Object的所写,而CLOB是指大字符对象也就是英文Character Large Object的所写。其中BLOB是用来存储大量二进制数据的;CLOB用来存储大量文本数据。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代码如下: lt;#63;xml version="." encoding="UTF-" #63;gt; lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper .//EN" "http://mybatis.org/dtd/mybatis--mapper.dtd"gt; lt;mapper namespace="com.test.dao.BlobFieldDao"gt; lt;sql id="blobFieldColumns"gt; 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 lt;/sqlgt; lt;sql id="blobFieldJoins"gt; lt;/sqlgt; lt;select id="get" resultType="blobField"gt; SELECT lt;include refid="blobFieldColumns" /gt; FROM BLOB_FIELD a lt;include refid="blobFieldJoins" /gt; WHERE a.ID = #{id} lt;/selectgt; lt;select id="findList" resultType="blobField"gt; SELECT lt;include refid="blobFieldColumns" /gt; FROM BLOB_FIELD a lt;include refid="blobFieldJoins" /gt; lt;/selectgt; lt;insert id="insert"gt; INSERT INTO BLOB_FIELD( ID , TAB_NAME , TAB_PKID_VALUE , BLOB_COL_NAME , BLOB_COL_VALUE ) VALUES ( #{id}, #{tabName}, #{tabPkidValue}, #{blobColName}, #{blobColValue,jdbcType=BLOB} ) lt;/insertgt; lt;update id="update"gt; UPDATE BLOB_FIELD SET TAB_NAME = #{tabName}, TAB_PKID_VALUE = #{tabPkidValue}, BLOB_COL_NAME = #{blobColName}, BLOB_COL_VALUE = #{blobColValue} WHERE ID = #{id} lt;/updategt; lt;delete id="delete"gt; DELETE FROM BLOB_FIELD WHERE ID = #{id} lt;/deletegt; lt;/mappergt; 3、controller代码如下: a、保存BLOB字段代码 /** * 附件上传 * * @param testId * 主表Id * @param request * @return * @throws UnsupportedEncodingException */ @RequiresPermissions("exc:exceptioninfo:feedback") @RequestMapping(value = "attachment", method = RequestMethod.POST) @ResponseBody public Maplt;String, Objectgt; uploadAttachment(@RequestParam(value = "testId", required = true) String testId, HttpServletRequest request) throws UnsupportedEncodingException { Maplt;String, Objectgt; result = new HashMaplt;String, Objectgt;(); 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") #63; 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字段,并写入成输出流。 以上就是本文的全部叙述,希望对大家有所帮助。 (编辑:常州站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |