深入淺析mybatis oracle BLOB類型字段保存與讀取

2024-08-29 13:58:29

字體：大中小

來源：轉載

供稿：網友

本文給大家淺析mybatis oracle blob類型字段的保存與讀取，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代碼如下：

<?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字段代碼

/**

* 附件上傳

*

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

/**

* 下載附件

*

* @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();

}

}