一、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字段，并寫入成輸出流。
以上就是本文的全部敘述，希望對大家有所幫助。
