处理Blob类型数据，例如将对象保存到blob字段中

处理Blob类型数据

Blob（Binary Long Object）是二进制长对象的意思，Blob列通常用于存储大文件，例如：图片、声音文件、对象流。

注：无法处理带泛型的集合，如：List<Person>；可以先处理List集合，然后在转化为泛型就OK了，一定要将Person类进行序列化。

Blob数据插入数据库需要使用PreparedStatement,通过该对象的setBinaryStream(intparameterIndex,InputStream x)方法，将指定参数传入二进制输入流;取值时，调用ResultSet的getBlob(int columnIndex)方法，该方法返回一个Blob对象，调用Blob对象的getBinaryStream()方法，获取该Blob数据的输入流，也可使用getByte()方法，直接取出Blob对象封装的二进制数据。

 

一、创建带blob字段表格

Create table img table

(

         Img id int auto incrementprimary key,

         Img name varchar(255),

         --创建一个mediumblob类型的数据列，用于保存图片数据

         Img data mediumblob

)

注：在MySQL数据库里，blob类型最多只能存储64K内容，这可能不够满足实际用途，所以使用mediumblob类型，该类型的数据列可以存储16M内容。

 

二、java部分程序

1、插入带blob字段数据：

String sql = "insert intoPamMMiVIEWCONF (CLIENT_ID,VIEWNAME,VIEWDATA ) values ( ?,?, empty_blob())"; //and clientid=? //no nessery at now

pstmt = con.prepareStatement(sql);

pstmt.setString(1,viewConfig.getClientId());

pstmt.setString(2,viewConfig.getViewName());

pstmt.execute();

 

sql = "select VIEWDATA fromPamMMiVIEWCONF where CLIENT_ID=? and VIEWNAME = ? for update";

pstmt = con.prepareStatement(sql);

pstmt.setString(1,viewConfig.getClientId());

pstmt.setString(2,viewConfig.getViewName());

rs = pstmt.executeQuery();

 

if (rs.next()) {

       //oracle.sql.BLOB blob =(oracle.sql.BLOB) rs.getBlob(1);

       //java.io.ObjectOutputStreamobjOutStm = new ObjectOutputStream(blob.getBinaryOutputStream());

       java.sql.Blob blob =rs.getBlob(1);

        ObjectOutputStream objOutStm = newObjectOutputStream(blob.setBinaryStream(0L));

        objOutStm.writeObject(this.getSaveData(viewConfig));//看下面的方法

        objOutStm.close();

}

 

注：this.getSaveData(viewConfig)方法

publicjava.util.HashMap getSaveData(ViewConfigInfo viewConfig){

                 HashMap htRtn = new HashMap();

                htRtn.put("workQueue", viewConfig.getWorkQueue());

                htRtn.put("workGroup", viewConfig.getWorkGroup());

                htRtn.put("payerGroup", viewConfig.getPayerGroup());

                htRtn.put("payerType", viewConfig.getPayerType());

                htRtn.put("claimStatus", viewConfig.getClaimStatus());

                htRtn.put("payerFullId",viewConfig.getPayerFullId());

                htRtn.put("providers", viewConfig.getProviders());

                htRtn.put("arkPayer", viewConfig.getArkPayer());

                htRtn.put("pamQueConStatus", viewConfig.getPamQueConStatus());

                return htRtn;

            }

 

2、读取带blob字段的数据

HashMap viewData=new HashMap();

try {

con =DBUtil.assistantConnection();

Stringsql = "select * from PamMMiVIEWCONF where CLIENT_ID=? and VIEWNAME = ?"; //and clientid=? //no nessery at now?

pstmt= con.prepareStatement(sql);

pstmt.setString(1,clientId);

pstmt.setString(2,viewName);

rs =pstmt.executeQuery();

if(rs.next()) {

java.sql.Blobblob = rs.getBlob("VIEWDATA");

ObjectInputStreamobjInStm = null;

objInStm= new java.io.ObjectInputStream(blob.getBinaryStream());

viewData= (HashMap) objInStm.readObject();

}

}

 

通过setAlldata方法取值：

public void setAlldata(HashMap alldata) {

Objecto;

if((o = alldata.get("workQueue")) instanceof String[]) {

this.setWorkQueue((String[]) o);

}else {

this.setWorkQueue(new String[0]);

}

 

if((o = alldata.get("workGroup")) instanceof String[]) {

this.setWorkGroup((String[]) o);

}else {

this.setWorkGroup(new String[0]);

}

 

if((o = alldata.get("payerGroup")) instanceof String) {

this.payerGroup = (String) o;

}else {

this.payerGroup = "";

}

if((o = alldata.get("payerType")) instanceof String[]) {

this.payerType = (String[]) o;

}else {

this.payerType = new String[0];

}

 

if((o = alldata.get("payerFullId")) instanceof String) {

this.payerFullId = (String) o;

}else {

this.payerFullId = "";

}

 

if((o = alldata.get("claimStatus")) instanceof String[]) {

this.claimStatus = (String[]) o;

}else {

this.claimStatus = new String[] { "" };

}

 

if((o = alldata.get("providers")) instanceof String[]) {

this.setProviders((String[]) o);

}else {

this.setProviders(new String[0]);

}

 

if((o = alldata.get("arkPayer")) instanceof ARKPayer) {

this.setArkPayer((ARKPayer) o);

}else {

this.setArkPayer(new ARKPayer());

}

 

if ((o =alldata.get("pamQueConStatus")) instanceof PamQueConStatus) {

this.setPamQueConStatus((PamQueConStatus) o);

}else {

this.setPamQueConStatus(new PamQueConStatus());

}

}