一、BLOB字段
BLOB是指二进制大对象也就是英文Binary Large Object的所写，而CLOB是指大字符对象也就是英文Character Large Object的所写。其中BLOB是用来存储大量二进制数据的；CLOB用来存储大量文本数据。BLOB通常用来保存图片、文件等二进制类型的数据。
二、使用mybatis操作blob
1、表结构如下：
|
1
2
3
4
5
6
7
8
9
|
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
<?xml version="." encoding="UTF-" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper .//EN" "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.test.dao.BlobFieldDao"> <sql id="blobFieldColumns"> 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 </sql> <sql id="blobFieldJoins"> </sql> <select id="get" resultType="blobField"> SELECT <include refid="blobFieldColumns" /> FROM BLOB_FIELD a <include refid="blobFieldJoins" /> WHERE a.ID = #{id} </select> <select id="findList" resultType="blobField"> SELECT <include refid="blobFieldColumns" /> FROM BLOB_FIELD a <include refid="blobFieldJoins" /> </select> <insert id="insert"> INSERT INTO BLOB_FIELD( ID , TAB_NAME , TAB_PKID_VALUE , BLOB_COL_NAME , BLOB_COL_VALUE ) VALUES ( #{id}, #{tabName}, #{tabPkidValue}, #{blobColName}, #{blobColValue,jdbcType=BLOB} ) </insert> <update id="update"> UPDATE BLOB_FIELD SET TAB_NAME = #{tabName}, TAB_PKID_VALUE = #{tabPkidValue}, BLOB_COL_NAME = #{blobColName}, BLOB_COL_VALUE = #{blobColValue} WHERE ID = #{id} </update> <delete id="delete"> DELETE FROM BLOB_FIELD WHERE ID = #{id} </delete></mapper> |
3、controller代码如下：
a、保存BLOB字段代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
/** * 附件上传 * * @param testId * 主表Id * @param request * @return * @throws UnsupportedEncodingException */ @RequiresPermissions("exc:exceptioninfo:feedback") @RequestMapping(value = "attachment", method = RequestMethod.POST) @ResponseBody public Map<String, Object> uploadAttachment(@RequestParam(value = "testId", required = true) String testId, HttpServletRequest request) throws UnsupportedEncodingException { Map<String, Object> result = new HashMap<String, Object>(); 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字段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
/** * 下载附件 * * @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") ? 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字段，并写入成输出流。
以上就是本文的全部叙述，希望对大家有所帮助。
