JDBC操纵Oracle数据库中的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(); } }