Oracle10g BLOB数据插入问题浅议

在Oracle中大数据类型一般常用oracle.sql.BLOB来表示，不同于mysql中的通用的java.sql.Blob接口，因此在插入图片数据到oracle的时候不能直接插入图片，一般采用oracle的empty_blob()函数，先生成空的BLOB对象插入到表中，然后再读取该记录，重新update图片数据到相应字段中；

举例如下：

创建表sql语句
drop sequence seq_changedata_infoid;
create sequence seq_changedata_infoid increment by 1 start with 1;
CREATE TABLE changedata_info (
id number(20) NOT NULL ,
version_id varchar2(20) not null,
sql_id number(20) default NULL,
sql_content varchar2(256) default NULL,
photo blob,
PRIMARY KEY (id)
);

直接sql插入语句为：

insert into changedata_info(id,version_id,sql_id,sql_content,photo)values(1,'1200',1,'update ....',empty_blob())

程序代码如下：

public static void main(String[] args){


BLOB blob = null; //图片类型
OutputStream outputStream = null; //输出流
File file = null; //文件
InputStream inputStream = null; //输入流

ResultSet rs = null;
Statement sm = null;
Statement smt = null;
ResultSet rr = null;

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager
.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"system", "admin");

con.setAutoCommit(false); //事物由程序员操作
String sql = "";



//DesEncrypt tm = new DesEncrypt();
//tm.getKey("aabb");
//String strDes = "";
//String str ="";

for(int i =8;i<11;i++){

int id ;
id = 1000 + i;
String version_id = "20080813060839";

int sql_id = i;

String sql_content = "update ticketinfo set name = mingtian where id = 50006";

String sqlInsert = "insert into changedata_info(id,version_id,sql_id,sql_content,photo)values("
+ id
+","
+ version_id
+ ","
+ sql_id
+ ",'"
+ sql_content
+ "',"
+ "empty_blob()"
+ ")";

//con.setAutoCommit(false); //事物由程序员操作
smt = con.createStatement();

//rs = sm.executeQuery(sqlInsert);//插入数据 ---当前图片为空
smt.execute(sqlInsert);

String sqlUpdate = "select photo from changedata_info where id='" + id + "'";

sm = con.createStatement();
ResultSet r= sm.executeQuery(sqlUpdate + " for update");
if (r.next()) {
blob = (BLOB) r.getBlob(1);
byte [] photo = r.getBytes("photo");
outputStream = blob.getBinaryOutputStream();
file = new File("c:\\11\\photo.jpg");//c:\\11\\photo.jpg
inputStream = new FileInputStream(file);
byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ((len = inputStream.read(b)) != -1) {
System.out.println(len);
outputStream.write(b, 0, len);
}
}



}
con.commit();
con.close();




} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
}

此代码经过测试，与mysql的图片插入做过比较之后有所感悟，所以唠叨一下....;-)