创建测试表
- 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();
- }
- }
- }
- 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);
- }
- }