Mybatis操作Oracle中的Clob和Blob字段

说明：CLOB和BLOB的区别，这两个被统称为LOB，即Large Object（大对象类型）最本质的区别：CLOB的C，可以理解为Char，保存的是字符大对象BLOB的B，即Binary，保存的是二进制大对象CLOB应该转换成StringBLOB应该转换成byte[]

1. Oracle中创建测试的表结构：Byte_Array_Test

2. Mybatis配置MapperXml，用Map<String, Object>接受Clob和Blob的数据，如下图：

3. 代码中获取Clob和Blob的值

3-1 接收Blob和Clob数据代码：

Blob blobVal = (Blob)map.get("BLOB");  		// 引号中的BLOB为字段名
Clob clobVal = (Clob)map.get("CLOB");		// 引号中的CLOB为字段名

3-2 Blob实例

@RequestMapping("getArr/{id}")
public String getArr(@PathVariable String id) throws SQLException {Map<String, Object> map = byteArrayTestService.find(id);// 获取Oracle数据库中Blob的值Blob blob = (Blob)map.get("BLOB");// Blob对象自带的方法getBytes，将Blob转为字节数组byte[]byte[] bytes1 = blob.getBytes(1, (int) blob.length());return "success";
}

3-3 Clob实例

@RequestMapping("getArr/{id}")
public String getArr(@PathVariable String id) throws SQLException {Map<String, Object> map = byteArrayTestService.find(id);// 获取Oracle数据库中Clob的值Clob clob = (Clob)map.get("CLOB");// Clob对象自带的方法getSubString，将Clob转为StringString clobStr1 = clob.getSubString(1, (int) clob.length());return "success";
}

3-4 扩展： Blob转byte[]，Clob转string，不考虑对象内置方法的话，亦可用如下自定义方法

/**
* BLOB转byte[]
* @param blob
* @return
*/
private byte[] blobToBytes(Blob blob) {BufferedInputStream is = null;try {is = new BufferedInputStream(blob.getBinaryStream());byte[] bytes = new byte[(int) blob.length()];int len = bytes.length;int offset = 0;int read = 0;while (offset < len && (read = is.read(bytes, offset, len - offset)) >= 0) {offset += read;}return bytes;} catch (Exception e) {return null;} finally {try {is.close();is = null;} catch (IOException e) {return null;}}
}/**
* CLOB转String
* @param clob
* @return
*/
public static String clobToString(Clob clob) {try {Reader inStreamDoc = clob.getCharacterStream();char[] tempDoc = new char[(int) clob.length()];inStreamDoc.read(tempDoc);inStreamDoc.close();return new String(tempDoc);} catch (IOException e) {e.printStackTrace();} catch (SQLException es) {es.printStackTrace();}return null;
}

4. 将Blob生成的字节数组byte[]，写入到文件，如：test.jpg

public class FileUtil {/*** 方法功能：将字节数组写入到新建文件中。* @param String fname* @param byte[] msg* @return boolean* */public static boolean save2File(String fname, byte[] msg){OutputStream fos = null;try{File file = new File(fname);File parent = file.getParentFile();boolean bool;if ((!parent.exists()) && (!parent.mkdirs())) {return false;}fos = new FileOutputStream(file);fos.write(msg);fos.flush();return true;}catch (FileNotFoundException e){return false;}catch (IOException e){File parent;return false;}finally{if (fos != null) {try{fos.close();}catch (IOException e) {}}}}// 写入文件功能测试public static void main(String[] args)  {String msgStr = "我是java爱好者，测试数据";byte[] bytes = msgStr.getBytes();String filename = "D:\\test\\test.txt";//注意修改为自己的文件名boolean flag = FileUtil.save2File(filename, bytes);System.out.println("flag = "+ flag );}
}