Oracle JDBC存入图片Blob详解编程语言

创建测试表

create table test_img( 
  id    integer primary key, 
  name  varchar2(32), 
  image blob 
); 

存储图片

import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.OutputStream; 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import oracle.sql.BLOB; 
 
public class Test { 
 
    public static void main(String[] args) { 
        // TODO Auto-generated method stub 
        PreparedStatement pst = null; 
        ResultSet rs = null; 
        Connection conn = ConnectionManager.getConnection(); 
        String sql = "insert into test_img(id,name,image) values(?,?,?)"; 
        try { 
            pst = conn.prepareStatement(sql); 
            pst.setInt(1, 1); 
            pst.setString(2, "test"); 
            pst.setBlob(3, BLOB.empty_lob());  //插入空对象empty_blob()   
            int ii = pst.executeUpdate(); 
 
            OutputStream os = null; 
            String q_sql = "select image from test_img where id = ? for update"; // 锁定数据行进行更新   
            pst = conn.prepareStatement(q_sql); 
            pst.setInt(1, 1); 
            rs = pst.executeQuery(); 
            if (rs.next()) { 
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("image"); 
                os = blob.getBinaryOutputStream(); 
                InputStream is = new FileInputStream("D://image.jpg"); 
                int i = 0; 
                while ((i = is.read()) != -1) { 
                    os.write(i); 
                } 
            } 
            os.flush(); 
            os.close(); 
            ConnectionManager.closeAll(rs, pst, conn); // 关闭资源 
 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } catch (FileNotFoundException e) { 
            e.printStackTrace(); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
    } 
}

JDBC辅助类

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
public class ConnectionManager { 
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; 
    public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl"; 
    public static final String USERNAME = "test"; 
    public static final String PASSWORD = "test"; 
    /** 
     * 通过静态代码块 注册数据库驱动 
     */ 
    static{ 
        try { 
            Class.forName(DRIVER); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    /** 
     * 获得Connection 
     *  
     * @return 
     */ 
    public static Connection getConnection() { 
        Connection conn = null; 
        try { 
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD); 
        }catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return conn; 
    } 
 
    /** 
     * 关闭ResultSet 
     * @param rs 
     */ 
    public static void closeResultSet(ResultSet rs) { 
        if (rs != null) { 
            try { 
                rs.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 关闭Statement 
     * @param st 
     */ 
    public static void closeStatement(Statement st) { 
        if (st != null) { 
            try { 
                st.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
 
    /** 
     * 关闭Connection 
     * @param conn 
     */ 
    public static void closeConnection(Connection conn) { 
        if (conn != null) { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
    /** 
     * 关闭全部 
     * @param rs 
     * @param sta 
     * @param conn 
     */ 
    public static void closeAll(ResultSet rs,Statement sta,Connection conn){ 
        closeConnection(conn); 
        closeStatement(sta); 
        closeResultSet(rs); 
    } 
 
}

效果如图：

作者：blog.ytso.com

出处：blog.ytso.com/article/details/41629669