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

                                                                        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();
				}
			}
	}
}