package com.chinacreator.oraclelob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import com.creator.util.Constants;
public class TestBLOB {
public static void main(String[] args) { //insertBlob(); //readBlob(); } /** * 插入oracle的blob字段类型的数据 * oracle中的BLOB字段是存储2进制的文件,比如文件,图片等。 */ public static void insertBlob() { String sql = "insert into mytest(id, images) values(1, EMPTY_BLOB())" ; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; int count = 0 ; try { Class.forName(Constants.DRIVER); conn = DriverManager.getConnection(Constants.URL, Constants.DB_USER, Constants.DB_PSW); //设置自动提交 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql); //pstmt.setString(1, "张三"); count = pstmt.executeUpdate(); // conn.commit(); // 悲观锁锁定需要更新行 //sql = "SELECT * FROM register ORDER BY ID FOR UPDATE"; sql = "select images from mytest where id=1 for update"; rs = conn.createStatement().executeQuery(sql); //rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql); if(rs.next()){ //boolean toLast = rs.last(); //CLOB content = (CLOB)rs.getClob("content"); /*Writer out = content.getCharacterOutputStream(); InputStream is = new FileInputStream("E:\\temp\\a.txt"); InputStreamReader isr; isr = new InputStreamReader(is, "GB2312"); BufferedReader br = new BufferedReader(isr) ;*/ BLOB images = (BLOB)rs.getBlob("images"); //输出流 OutputStream os = images.getBinaryOutputStream(); //输入流 FileInputStream fis = new FileInputStream("E:\\temp\\a.jpg"); //入库 byte[] temp = new byte[1024] ; while((count=fis.read(temp)) != -1) { os.write(temp, 0, count) ; os.flush(); } os.close(); fis.close(); } conn.commit(); System.out.println("success!"); } catch (SQLException e) { e.printStackTrace(); } catch(FileNotFoundException e){ e.printStackTrace(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); }catch (IOException e) { e.printStackTrace(); }finally { if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if(pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } } } } /** * 从数据库中读取Blob字段数据 */ public static void readBlob() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select images from mytest where id=3"; int count = 0 ; try { Class.forName(Constants.DRIVER); conn = DriverManager.getConnection(Constants.URL, Constants.DB_USER, Constants.DB_PSW); //设置自动提交 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery() ; if(rs != null) { while(rs.next()) { //数据库-->本地文件 BLOB images = (BLOB)rs.getBlob("images"); //得到一个输入流 InputStream is = images.getBinaryStream(); //得到一个输出流 FileOutputStream fos = new FileOutputStream(new File("e:\\temp\\temp.txt")); byte[] temp = new byte[1024] ; while((count = is.read(temp)) != -1) { fos.write(temp, 0, count); fos.flush(); } //fos.flush(); fos.close(); is.close(); } } conn.commit(); System.out.println("success!"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }catch(FileNotFoundException e){ e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if(pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } } } }}