mybatis mysql clob_深入浅析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、实体代码如下：

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字段代码

/**

* 附件上传

*

* @param testId

* 主表Id

* @param request

* @return

* @throws UnsupportedEncodingException

*/

@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字段

/**

* 下载附件

*

* @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字段，并写入成输出流。

以上就是本文的全部叙述，希望对大家有所帮助。