使用hibernate儲存blob大資料，ibatis查詢blob大資料

需求：將對象序列化，儲存到資料庫中，資料庫設計用blob資料類型，使用oracle資料庫，使用hibernate儲存blob大資料，ibatis查詢blob大資料.

 

部分代碼獻上：

一、存：

1、要儲存的類：class SaveQueryBO implements Serializable

 

2、將對象轉換成blob格式：Blob saveQueryBo= Hibernate.createBlob(SearchUtil.objectToByte(saveQueryBO));

3、hibernate配置：

<property name="saveQueryBo" column="SAVE_QUERY_BO" type="java.sql.Blob"
   not-null="false"
  />

4、save這個就不貼了；完事兒

 

二、取

1、ibatis配置

<result property="saveQueryBo" column="save_Query_Bo" jdbcType="BLOB" javaType = "java.lang.Object"/>

說明下：

javaType = "java.lang.Object"必須是這個，不然報你寫個handler吧~

2、相應類：

private Object  saveQueryBo;

 

saveQueryBo雖然是blob類型的，但是必須寫成object的，同上1點；

3、查詢，轉換

if(saveQueryBo != null){
    Blob  saveQueryBoBlob = (Blob)saveQueryBo;
    return (SaveQueryBO)SearchUtil.byteToObject(SearchUtil.blobToByte(saveQueryBoBlob));
   }

 

完整代碼：

 

 

import java.sql.Blob;public class SaveQueryVO {private String tableKey;//主鍵private String queryName;//查詢名稱private String userCode;//使用者編號private String custType;//客戶類型private Object  saveQueryBo;//搜尋相關類private String  sql;//sqlpublic String getSql() {return sql;}public void setSql(String sql) {this.sql = sql;}/** * 返回的是 BLOB對象，需要強轉 */public Object getSaveQueryBo() {return saveQueryBo;}/** * 返回的是 SaveQueryBO對象 * @throws CrmBaseException  */public SaveQueryBO getSaveQueryBO() throws CrmBaseException {try {if(saveQueryBo != null){Blob  saveQueryBoBlob = (Blob)saveQueryBo;return(SaveQueryBO)SearchUtil.byteToObject(SearchUtil.blobToByte(saveQueryBoBlob));}} catch (Exception e) {e.printStackTrace();//TODO throw new CrmBaseException("系統錯誤");}return null;}public void setSaveQueryBo(Object saveQueryBo) {this.saveQueryBo = saveQueryBo;}public String getCustType() {return custType;}public void setCustType(String custType) {this.custType = custType;}public String getTableKey() {return tableKey;}public void setTableKey(String tableKey) {this.tableKey = tableKey;}public String getQueryName() {return queryName;}public void setQueryName(String queryName) {this.queryName = queryName;}public String getUserCode() {return userCode;}public void setUserCode(String userCode) {this.userCode = userCode;}}

import java.io.BufferedInputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.ObjectInputStream;import java.io.ObjectOutputStream;import java.sql.Blob;public class SearchUtil {/**' * 獲得搜尋主表code * @return */public static String getMainTableCode(){return SearchConstant.MAIN_TABLE_CODE_PE_VALUE;}/** *  * @param columnType * @param columnValue * @return * @throws CrmBaseException */public static String getColumValue4Sql(String columnType,String columnValue) throws CrmBaseException{//當類型為數字if(SearchConstant.COLUMNTYPE_NUMBER.equals(columnType) || SearchConstant.COLUMNTYPE_DOUBLE.equals(columnType)){if(StringUtil.isEmpty(columnValue)){return "0";}else{return columnValue;}} else if(SearchConstant.COLUMNTYPE_DATE.equals(columnType)){//當類型為日期if(StringUtil.isEmpty(columnValue)){return "''";}else{return "to_date('"+columnValue+"','yyyy-MM-dd')";}}else{//預設if(StringUtil.isEmpty(columnValue)){return "''";}else{return "'"+columnValue+"'";}}}/** * 將對象轉化為位元組流 * @param obj * @return * @throws Exception */public static byte[] objectToByte(Object object) throws CrmBaseException {try{if (object == null)       return null;ByteArrayOutputStream bos = new ByteArrayOutputStream();ObjectOutputStream oos = new ObjectOutputStream(bos);oos.writeObject(object);byte[] bytes = bos.toByteArray();bos.close();        oos.close();return bytes;}catch (Exception e) {e.printStackTrace();throw new CrmBaseException(e.getMessage());}}/** * 將位元組流轉化為對象 * @param bytes * @return * @throws Exception */public static Object byteToObject(byte[] bytes) throws Exception {if (bytes==null) return null;ByteArrayInputStream bis = new ByteArrayInputStream(bytes);        ObjectInputStream ois = new ObjectInputStream(bis);        Object object = ois.readObject();        bis.close();        ois.close();return object;}public static byte[] blobToByte(Blob blob) {if (blob == null)     return null;byte[] listByte = null;        try {            BufferedInputStream is = new BufferedInputStream(blob.getBinaryStream());            int len = (int)blob.length();            listByte = new byte[len];            int offset = 0;            int read = 0;            while (offset < len && (read=is.read(listByte,offset,len-offset)) >= 0) {                offset += read;            }} catch (Exception e) {e.printStackTrace();}return listByte;}}

 

<resultMap id="saveQueryVO" class="SaveQueryVO"><result property="tableKey" column="table_Key" /><result property="queryName" column="query_Name" /><result property="userCode" column="user_Code" /><result property="custType" column="cust_Type" /><result property="saveQueryBo" column="save_Query_Bo" jdbcType="BLOB" javaType = "java.lang.Object"/><result property="sql" column="sql" /></resultMap>