lobexample3.java

Connection Oracle9i database Read and Wrote Lob ,long or long raw type and save to file

import oracle.sql.*;
import oracle.jdbc.*;
import java.sql.*;
import java.io.*;

/** 使用流写CLOB和BLOB列
 * @author Sean.Liu
 * @version 1.0
 */
public class LobExample3 {
	public static void writeBLOB(Statement stmt,String fileName) throws SQLException,IOException {
		
		//step 1 :将LOB列初始化以便设置LOB定位器
		stmt.executeUpdate("insert into blob_content values"+
		                   "('"+ fileName +"',empty_blob())");
	  
	  //step 2 :将包含LOB定位器的行读入结果集
	  ResultSet rs = stmt.executeQuery("select blob_column from blob_content where file_name='"+fileName+"' for update");
	  rs.next();
	  
	  //step 3 :在JAVA程序中创建LOB对象，并且从结果集读取LOB定位器
	  oracle.sql.BLOB myBlob = ((OracleResultSet)rs).getBLOB("blob_column");
	  
	  //step 4 :从LOB对象获取LOB的缓冲区大小
	  int bufferSize = myBlob.getBufferSize();
	  
	  //step 5 :创建一个字节缓冲区来存储来自文件的数据块
	  byte[] byteBuffer = new byte[bufferSize];
	  
	  //step 6 :创建文件对象 create a file
	  File myFile = new File(fileName);
	  
	  //step 7 :创建文件输入流对象来读取文件内容
	  FileInputStream myFileInputStream = new FileInputStream(myFile);
	  
	  //step 8 :创建一个输出流对象
	  OutputStream myOutputStream = myBlob.getBinaryOutputStream();
	  
	  //step 9:使用以下步骤读取文件的内容并且将它写到LOB；
	  //如果还没有到达文件的末尾：
	  //a) 将数据块从文件读入第五步中创建的缓冲区。
	  //b) 将缓冲区的内容写到输出流对象，这个对象将缓冲区内容发送到LOB。
	  int bytesRead;
	  while((bytesRead = myFileInputStream.read(byteBuffer)) != -1 ) {
	  	myOutputStream.write(byteBuffer);
	  } //end of while
	  
	  //stmt.execute("COMMIT");
	  //step 10 :关闭流对象
	  
	  myFileInputStream.close();
	  myOutputStream.close();
	  
	  System.out.println("Wrote content from file "+fileName+" to BLOB");
	} // end of writeBLOB
	
	public static void writeCLOB(Statement stmt,String fileName) throws SQLException,IOException {
		stmt.executeUpdate("insert into clob_content values"+
		                   "('"+fileName+"',empty_clob())");
	  ResultSet rs = stmt.executeQuery("select clob_column from clob_content where file_name = '"+fileName+"' for update");
	  rs.next();
	  oracle.sql.CLOB myClob = ((OracleResultSet)rs).getCLOB(1);
	  int bufferSize = myClob.getBufferSize();
	  byte[] charBuffer = new byte[bufferSize];
	  FileInputStream myFileInputStream = new FileInputStream (
	                                          new File(fileName));
	  OutputStream myOutputStream = myClob.getAsciiOutputStream();
	  int charsRead;
	  while((charsRead = myFileInputStream.read(charBuffer)) != -1) {
	  	myOutputStream.write(charBuffer);
	  } // end of while
	  //stmt.execute("COMMIT");
	  myFileInputStream.close();
	  myOutputStream.close();
	  System.out.println("Wrote content from file "+fileName +" to CLOB");
	} //end of writeCLOB
	
	public static void main(String [] args) throws SQLException,IOException {
		DriverManager.registerDriver(new OracleDriver());
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:swatt/swgood@10.10.10.251:1521:test");
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		String sourceDirectory = "D:\\JSPBook\\JDBC\\JDBC_book\\sample_files\\";
		writeBLOB(stmt,sourceDirectory+"pxt.jpg");
		writeCLOB(stmt,sourceDirectory+"oracletosqlserver.doc");
	} // end of main
} // end of LobExample3