JDBC操纵Oracle数据库中的BLOB字段

73buolv0ci

　　BLOB字段的写入
　　
　　数据库执行脚本：

create table bxxx(
id   int primary key ,
image  blob
);

　　
　　方法一：（JDBC2.0 规范）

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BlobDemo01 {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:ORCL";
public static final String DBUSER = "scott";
public static final String DBPASSWORD = "tiger";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "insert into bxxx values(?,?)";
File f = new File("d:"+File.separator+"5586.jpg");
InputStream in = null;
try {
in = new FileInputStream(f);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
Class.forName(DBDRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,1);
pstmt.setBinaryStream(2,in,(int)f.length());
if(pstmt.executeUpdate()>0){
System.out.println("OK");
conn.commit();
conn.setAutoCommit(true);
}
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally{
try {
in.close();
pstmt.close();
conn.close();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

　　
　　
　　方法二：（古老）
　　1. 设置不自动提交
　　2.插入一个空的blob
　　3.使用行级锁定blob字段
　　

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
public class BlobDemo02 {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:ORCL";
public static final String DBUSER = "scott";
public static final String DBPASSWORD = "tiger";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql1 = "insert into bxxx values(1,empty_blob())";
String sql2 = "select image from bxxx where id = 1 for update";
File f = new File("d:"+File.separator+"5586.jpg");
try {
Class.forName(DBDRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql1);
pstmt.executeUpdate();
pstmt = conn.prepareStatement(sql2);
rs = pstmt.executeQuery();
while(rs.next()){
oracle.sql.BLOB blob = (BLOB) rs.getBlob(1);
PrintStream out = new PrintStream(blob.getBinaryOutputStream());
BufferedInputStream in = null;
in = new BufferedInputStream(new FileInputStream(f));
byte[] b = new byte[512];
int temp = 0;
while((temp=in.read(b))!=-1){
out.write(b,0,temp);
}
out.flush();
in.close();
out.close();
conn.commit();
conn.setAutoCommit(true);
}
} catch (Exception e) {
e.printStackTrace();
}  finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("OK!");
}
}



                                                                      

                                                                       BLOB字段的读取　　方法一：
　　

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.imageio.ImageIO;
public class BlobDemo03 {
public static void main(String[] args) {
PreparedStatement ps = null;
ResultSet rs  = null;
String sql = "select image from bxxx where id=1";
Connection conn = null;
File f = new File("d:"+File.separator+"yyyy.jpg");
try {
Class.forName("oracle.jdbc.driver.OracleDriver" );
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
Blob blob =rs.getBlob(1);
OutputStream out = new FileOutputStream(f);
BufferedImage image = ImageIO.read(blob.getBinaryStream());
ImageIO.write(image,"jpg",out);
}
System.out.println("ok");
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
ps.close();
rs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

　　
　　
　　方法二：

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BlobDemo04 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select image from bxxx where id = 3";
File f = new File("d:"+File.separator+"xxxx.jpg");
try {
Class.forName("oracle.jdbc.driver.OracleDriver" );
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
InputStream in = rs.getBinaryStream(1);
OutputStream out = new FileOutputStream(f);
int temp = 0;
byte[] b = new byte[512];
while((temp=in.read(b))!=-1){
out.write(b,0,temp);
}
out.flush();
out.close();
in.close();
System.out.println("OK!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}