JDBC 操作 oracle blob
jdbc 插入、读取oracle blob字段package com.ssgm.jyu.jdbc;import java.io.*;import java.sql.*;import oracle.sql.*;import oracle.jdbc.*;public class JdbcBlob { public static void main(String[] args){ Connection conn = null; Statement stmt = null; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); } catch(ClassNotFoundException e){ e.printStackTrace(); } try{ conn = DriverManager.getConnection("jdbc:oracle:thin:@Host:1521:SID","username","passwd"); stmt = conn.createStatement(); conn.setAutoCommit(false); String sourceDir = "C:\\temp\\"; String targetDir = "C:\\temp\\retrieved\\"; String fileName = "cbr_order_version.dmp"; System.out.println("Writing BLOB to blob_content..."); writeBLOB(stmt,sourceDir+fileName); System.out.println("Reading BLOB from blob_content..."); readBLOB(stmt,fileName,sourceDir,targetDir); } catch(SQLException e){ e.printStackTrace(); } finally{ try{ stmt.close(); conn.close(); } catch(SQLException e){ e.printStackTrace(); } } } public static void writeBLOB(Statement stmt, String fullName){ ResultSet blobRS = null; try{ String sqlInsert = "INSERT INTO blob_content VALUES ('"+fullName+"',EMPTY_BLOB())"; String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+fullName+"' FOR UPDATE"; //step1: initialize the LOB column to set the LOB locator stmt.executeUpdate(sqlInsert); //step2: retrieve the row containing the LOB locator blobRS = stmt.executeQuery(sqlSelect); blobRS.next(); //step3: create a LOB obj and read the LOB locator BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column"); //step4: get the chunksize of the LOB from the LOB object int chunkSize = myBlob.getChunkSize(); //step5: create a buffer to hold a block of data from the file byte[] byteBuffer = new byte; //step6: create a file obj to open the file File file = new File(fullName); //step7: create an input stream obj to read the file contents FileInputStream in = new FileInputStream(file); //step8: read the file contents and write it to the LOB long position = 1; int bytesRead; while((bytesRead = in.read(byteBuffer)) != -1){ //write the buffer contents to myBlob myBlob.setBytes(position, byteBuffer); position += bytesRead; } //step9: commit stmt.execute("COMMIT"); //step10: close the objects used to read the file in.close(); blobRS.close(); System.out.println("Wrote content from "+fullName+" to BLOB\n"); } catch(SQLException e){ System.out.println("Error Code: "+e.getErrorCode()); System.out.println("Error Message: "+e.getMessage()); e.printStackTrace(); } catch(IOException e){ System.out.println("Error Message: "+e.getMessage()); e.printStackTrace(); } } public static void readBLOB( Statement stmt, String fileName, String sourceDir, String targetDir){ String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+ sourceDir+fileName+"'"; ResultSet blobRS = null; try{ //step1: retrieve the row containing the BLOB locator blobRS = stmt.executeQuery(sqlSelect); blobRS.next(); //step2: create a LOB obj and read the LOB locator BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column"); //step3: get the chunk size of the LOB from the LOB obj int chunkSize = myBlob.getChunkSize(); //setp4: create a buffer to hold a chunk of data from LOB byte[] byteBuffer = new byte; //step5: create a file obj String saveFile = targetDir + "retrievedBLOB"+fileName; File file = new File(saveFile); //step6: create output stream obj to write the LOB contents FileOutputStream out = new FileOutputStream(file); //step7: get the long of LOB contents long blobLength = myBlob.length(); //step8: read a chunk of data from myBlob, //then write the buffer contents to file for (long position=1; position<=blobLength; position += chunkSize){ int bytesRead = myBlob.getBytes(position, chunkSize, byteBuffer); out.write(byteBuffer); } //step9: close the stream obj out.close(); System.out.println("Read BLOB and save file"+saveFile); } catch(SQLException e){ System.out.println("Error Code: "+e.getErrorCode()); System.out.println("Error Message: "+e.getMessage()); e.printStackTrace(); } catch(IOException e){ System.out.println("Error Message: "+e.getMessage()); e.printStackTrace(); } } }
原文参考：http://blog.csdn.net/zhyuh_perl/article/details/7247324
页:
[1]