mybatis 存取Blob数据到oracle数据库

BLOB和CLOB都是大字段类型，BLOB是按二进制来存储的，而CLOB是可以直接存储文字的。其实两个是可以互换的的，或者可以直接用LOB字段代替这两个。但是为了更好的管理ORACLE数据库，通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。而像文章或者是较长的文字，就用CLOB存储，这样对以后的查询更新存储等操作都提供很大的方便。

本文使用的是 mybatis + oracle，oracle对应的字段类型为BLOB类型。

1.java类 

public class ComplainAttachment {
    private Long id;

    private Long complainOrderId;

    private String url;

    private BigDecimal isDownload;

    private byte[] context; //Blob对应的字段 

    public byte[] getContext() {
		return context;
	}

	public void setContext(byte[] context) {
		this.context = context;
	}

	public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getComplainOrderId() {
        return complainOrderId;
    }

    public void setComplainOrderId(Long complainOrderId) {
        this.complainOrderId = complainOrderId;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url == null ? null : url.trim();
    }

    public BigDecimal getIsDownload() {
        return isDownload;
    }

    public void setIsDownload(BigDecimal isDownload) {
        this.isDownload = isDownload;
    }

}

2.mybatis 对应的xml 将对应的context字段类型声明为BLOB类型  

<mapper namespace="com.cua.cell.ffp.mapper.ComplainAttachmentMapper" >
  <resultMap id="BaseResultMap" type="com.cua.cell.ffp.model.ComplainAttachment" >
    <id column="ID" property="id" jdbcType="DECIMAL" />
    <result column="COMPLAIN_ORDER_ID" property="complainOrderId" jdbcType="DECIMAL" />
    <result column="URL" property="url" jdbcType="VARCHAR" />
    <result column="IS_DOWNLOAD" property="isDownload" jdbcType="DECIMAL" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.cua.cell.ffp.model.ComplainAttachment" extends="BaseResultMap" >
    <result column="CONTEXT" property="context" jdbcType="BLOB" />
  </resultMap>
  <sql id="Base_Column_List" >
    ID, COMPLAIN_ORDER_ID, URL, IS_DOWNLOAD
  </sql>
  <sql id="Blob_Column_List" >
    CONTEXT
  </sql>

  <insert id="insert" parameterType="com.cua.cell.ffp.model.ComplainAttachment" >
    <selectKey resultType="java.lang.Long" keyProperty="id" order="BEFORE" >
      select SEQ_COMPLAIN_ATTACHMENT.nextval from dual
    </selectKey>
    insert into COMPLAIN_ATTACHMENT (ID, COMPLAIN_ORDER_ID, URL, 
      IS_DOWNLOAD, CONTEXT)
    values (#{id,jdbcType=DECIMAL}, #{complainOrderId,jdbcType=DECIMAL}, #{url,jdbcType=VARCHAR}, 
      #{isDownload,jdbcType=DECIMAL}, #{context,jdbcType=BLOB})
  </insert>

</mapper>

3.获取网络URL对应的文件并转换为byte数组,存入数据库

 @org.junit.Test
	public  void testSave() throws Exception{
		
    	byte srtbyte [] = null; 
		  //创建连接
		URL url = new URL("http://www.caacts.org.cn/admin/jiekou_chuanshuxiazai.action?sid=1&attachment=ac1eQQ截图20171207102756.jpg&id=91590");
		HttpURLConnection connection = (HttpURLConnection) url.openConnection();
		connection.setDoOutput(true);
		connection.setDoInput(true);
		connection.setRequestMethod("POST");
		connection.setUseCaches(false);
		connection.setInstanceFollowRedirects(true);
		connection.setRequestProperty("content-type", "text/json");
		connection.connect();
		ByteOutputStream out = new ByteOutputStream();
	        InputStream inputStream = connection.getInputStream();    
	        srtbyte = readInputStream(inputStream);
			//读取响应
	       ComplainAttachment c = new ComplainAttachment();
	       c.setContext(srtbyte);   //赋值  
	       c.setComplainOrderId(666L);
	       c.setUrl("12");  
	      
	       inputStream.close();
	       out.close();
	       connection.disconnect();

               this.complainAttachmentMapper.insert(c);

	    
	}

 public static byte[] readInputStream(InputStream inStream) throws Exception{    
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();    
        byte[] buffer = new byte[1024];    
        int len = 0;    
        while( (len=inStream.read(buffer)) != -1 ){    
            outStream.write(buffer, 0, len);    
        }    
        inStream.close();    
        return outStream.toByteArray();    
    }    

3.取出数据库对应的大文件 并下载到本地磁盘

@org.junit.Test
    public  void test2() throws IOException{
		List<ComplainAttachment> cc = this.complainAttachmentMapper.selectByComplainOrderId(3465L);
		
		if (cc != null) {
			for (ComplainAttachment complainAttachment : cc) {
				byte bb[] = complainAttachment.getContext();
				File ff = new File("E:/"+ test.jpg);
				FileOutputStream fops = new FileOutputStream(ff);
				fops.write(bb);
				fops.close();
			}
		}
		
    }