一、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代碼如下：
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
SELECT
FROM BLOB_FIELD a
WHERE a.ID = #{id}
SELECT
FROM BLOB_FIELD a
INSERT INTO BLOB_FIELD(
ID ,
TAB_NAME ,
TAB_PKID_VALUE ,
BLOB_COL_NAME ,
BLOB_COL_VALUE
) VALUES (
#{id},
#{tabName},
#{tabPkidValue},
#{blobColName},
#{blobColValue,jdbcType=BLOB}
)
UPDATE BLOB_FIELD SET
TAB_NAME = #{tabName},
TAB_PKID_VALUE = #{tabPkidValue},
BLOB_COL_NAME = #{blobColName},
BLOB_COL_VALUE = #{blobColValue}
WHERE ID = #{id}
DELETE FROM BLOB_FIELD
WHERE ID = #{id}
3、controller代碼如下：
a、儲存BLOB字段代碼
@RequiresPermissions("exc:exceptioninfo:feedback")
@RequestMapping(value = "attachment", method = RequestMethod.POST)
@ResponseBody
public Map uploadAttachment(@RequestParam(value = "testId", required = true) String testId,
HttpServletRequest request)
throws UnsupportedEncodingException {
Map result = new HashMap();
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字段
@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字段，并寫入成輸出流。
以上就是本文的全部叙述，希望對大家有所幫助。