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 <?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 blobColValue
11 </sql>
12
13 <sql id="blobFieldJoins">
14 </sql>
15
16 <select id="get" resultType="blobField">
17 SELECT
18 <include refid="blobFieldColumns" />
19 FROM BLOB_FIELD a
20 <include refid="blobFieldJoins" />
21 WHERE a.ID = #{id}
22 </select>
23
24 <select id="findList" resultType="blobField">
25 SELECT
26 <include refid="blobFieldColumns" />
27 FROM BLOB_FIELD a
28 <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_VALUE
38 ) 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 SET
49 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 @ResponseBody
13 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字段,并写入成输出流。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。