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

数据库执行脚本：create table bxxx( id int primary key , image blob);查询lob字段是否写入的sql语句：SELECT DBMS_LOB.GETLENGTH(image) FROM bxxx;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);mit();conn.setAutoCommit(true); catch (Exception e) try conn.rollback(); catch (SQLException e1) e1.printStackTrace();e.printStackTrace(); finallytry 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 blocke.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 byte512;int temp = 0;while(temp=in.read(b)!=-1)out.write(b,0,temp);out.flush();in.close();out.close();mit();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(); finallytry 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 blocke.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