Oracle blob字段文件保存与读取

文件上传

依赖包: ojdbc7-12.1.0.2jar

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;import java.io.*;
import java.sql.*;/***/
public class StoreFile {public static void main(String[] args) {
//        insert();//新增
//        update("d:\\\\TEST\\\\test.png");//上传down("d:\\\\TEST\\\\code1.rar");//下载}public static void insert(){try {Class.forName("oracle.jdbc.driver.OracleDriver");
//            Connection con = DriverManager.getConnection(
//                    "jdbc:oracle:thin:@10.53.20.58:1521:orcl", "longey", "123456");Connection con = DriverManager.getConnection("jdbc:oracle:thin:@10.0.13.21:1521:devdb", "pci", "pci");PreparedStatement ps = con.prepareStatement("insert into Student values(?, ?,?)");File f = new File("d:\\\\TEST\\\\test.png");FileReader fr = new FileReader(f);ps.setString(1, "张三");ps.setInt(2, 10);ps.setBlob(3, con.createBlob());int i = ps.executeUpdate();System.out.println(i + " records affected");con.close();} catch (Exception e) {e.printStackTrace();}}public static void update(String fp) {try {Class.forName("oracle.jdbc.driver.OracleDriver");
//            Connection con = DriverManager.getConnection(
//                    "jdbc:oracle:thin:@10.53.20.58:1521:orcl", "longey", "123456");Connection con = DriverManager.getConnection("jdbc:oracle:thin:@10.0.13.21:1521:devdb", "pci", "pci");PreparedStatement ps = con.prepareStatement("insert into Student values(?, ?,?)");File f = new File(fp);updateBlob(new FileInputStream(f),con,"Student","picture","name","张三");} catch (Exception e) {e.printStackTrace();}}public static void updateBlob(InputStream instream,Connection conn,String table,String blobColumn,String keyColumn,String keyValue)throws SQLException, IOException {Statement stmt = null;OracleResultSet rs = null;BLOB blob = null;boolean oldAutoCommit = conn.getAutoCommit();StringBuffer sqlBuffer = new StringBuffer();try {conn.setAutoCommit(false);sqlBuffer.append("select ");sqlBuffer.append(blobColumn);sqlBuffer.append(" from ");sqlBuffer.append(table);sqlBuffer.append(" where ");sqlBuffer.append(keyColumn);sqlBuffer.append(" ='");sqlBuffer.append(keyValue);
//注意这里的"for update"sqlBuffer.append("' for update ");stmt = conn.createStatement();rs = (OracleResultSet) stmt.executeQuery(sqlBuffer.toString());if (!rs.next()) {rs.close();stmt.close();throw new IllegalArgumentException("no record found for keyValue： ‘" + keyValue + "’");}blob = rs.getBLOB(1);OutputStream outstream = blob.getBinaryOutputStream();int bufferSize = blob.getChunkSize();byte[] buffer = new byte[bufferSize];int bytesRead = -1;while ((bytesRead = instream.read(buffer)) != -1) {outstream.write(buffer, 0, bytesRead);}instream.close();outstream.close();rs.close();stmt.close();} catch (SQLException e) {throw e;} catch (IOException e) {throw e;} finally {conn.setAutoCommit(oldAutoCommit);}}public static void down(String downFilepath) {try {Class.forName("oracle.jdbc.driver.OracleDriver");
//            Connection con = DriverManager.getConnection(
//                    "jdbc:oracle:thin:@10.53.20.58:1521:orcl", "longey", "123456");Connection con = DriverManager.getConnection("jdbc:oracle:thin:@10.0.13.21:1521:devdb", "pci", "pci");PreparedStatement ps = con.prepareStatement("insert into Student values(?, ?,?)");down(con,"Student","picture","name","张三",downFilepath);} catch (Exception e) {e.printStackTrace();}}public static void down(Connection conn,String table,String blobColumn,String keyColumn,String keyValue,String fPath)throws SQLException, IOException {Statement stmt = null;OracleResultSet rs = null;BLOB blob = null;boolean oldAutoCommit = conn.getAutoCommit();StringBuffer sqlBuffer = new StringBuffer();try {conn.setAutoCommit(false);sqlBuffer.append("select ");sqlBuffer.append(blobColumn);sqlBuffer.append(" from ");sqlBuffer.append(table);sqlBuffer.append(" where ");sqlBuffer.append(keyColumn);sqlBuffer.append(" ='");sqlBuffer.append(keyValue);
//注意这里的"for update"sqlBuffer.append("' ");
//            sqlBuffer.append("' for update ");stmt = conn.createStatement();rs = (OracleResultSet) stmt.executeQuery(sqlBuffer.toString());if (!rs.next()) {rs.close();stmt.close();throw new IllegalArgumentException("no record found for keyValue： ‘" + keyValue + "’");}blob = rs.getBLOB(1);InputStream inputStream = blob.getBinaryStream();File f = new File(fPath);f.createNewFile();OutputStream out = new FileOutputStream(f);int bufferSize = blob.getChunkSize();byte[] buffer = new byte[1*1024*1024];int bytesRead = -1;int i= 1;while ((bytesRead = inputStream.read(buffer)) != -1) {System.out.println(i++);out.write(buffer, 0, bytesRead);}inputStream.close();out.close();rs.close();stmt.close();} catch (SQLException e) {throw e;} catch (IOException e) {throw e;} finally {conn.setAutoCommit(oldAutoCommit);}}
}