lobexample1.java

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

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

public class LobExample1 {
	public static void writeCLOB(Statement stmt,String fileName) throws SQLException,IOException {
		System.out.println("start wirte CLOB to clob_content table");
		
		//step 1 : initialize the lob column to set the lob locator		
		stmt.executeUpdate("insert into clob_content values"+
		                   "('" + fileName + "',empty_clob())");
		
		//step 2 : retrieve the row containing the lob locator
		ResultSet rs = stmt.executeQuery(
		   "select clob_column "+
		   "from clob_content "+
		   "where file_name = '"+fileName+"' "+
		   "for update");
		rs.next();
		
		//step 3 : 在Java程序中创建LOB对象，并且从结果集读取LOB定位器(create a LOB object and read the LOB locator);
		oracle.sql.CLOB myClob = ((OracleResultSet)rs).getCLOB("clob_column");
		
		//step 4 : 从LOB对象获取LOB的组块大小(get the chunk size of the LOB from the LOB object)
		//ChunkSize的大小是由ORACLE数据库决定的
		int chunkSize = myClob.getChunkSize();//8132 bytes
		System.out.println("ChunkSize = "+chunkSize);
		
		//step 5 : 创建一个缓冲区来存储来自文件的数据块(create a buffer to hold a block of data from the file)
		char[] textBuffer = new char[chunkSize];
		
		//step 6 : 创建一个文件对象(create a file object)
		File myFile = new File(fileName);
		
		//step 7 : 创建输入流对象来读取文件内容(create input stream objects to read the file contents)
		FileInputStream myFileInputStream = new FileInputStream(myFile);
		InputStreamReader myReader = new InputStreamReader(myFileInputStream);
		BufferedReader myBufferedReader = new BufferedReader(myReader);
		//BufferedReader myBufferedReader = new Bufferedreader(
		//                                       new InputStreamReader(
		//                                           new FileInputStream(  
		//                                               new File(fileName))));
		
		//step 8 : 读取文件的内容并且将它写到LOB
		//1) 将数据块从文件读入第五步中创建的缓冲区。
		//2) 将缓冲区的内容复制到LOB对象
		long position = 1;
		int charRead;
		
		while ((charRead = myBufferedReader.read(textBuffer)) != -1) {
			//write the buffer contents to myClob using the putChar() method
			myClob.putChars(position,textBuffer);
			//increment the end position
			position+=charRead;
		} // end of while
		
		//step 9 : 执行提交，使修改持久化 (perform a commit)
		stmt.execute("COMMIT");
		
		//step 10 : 关闭用于读取文件的对象 (close the objects used to read the file)
		myBufferedReader.close();
		myReader.close();
		myFileInputStream.close();
		
		System.out.println("Wrote content from file "+fileName +" to CLOB");
	}// end of writeCLOB()
	
	public static void writeBLOB(Statement stmt,String fileName) throws SQLException,IOException {
	  stmt.executeUpdate("insert into blob_content values"+
	                     "('"+fileName+"',empty_blob())");
	  ResultSet rs = stmt.executeQuery("select blob_column "+
	                                   "from blob_content "+
	                                   "where file_Name = '"+fileName+"' "+
	                                   "for update");
	  rs.next();
	  oracle.sql.BLOB myBlob = ((OracleResultSet)rs).getBLOB("blob_column");
	  int chunkSize = myBlob.getChunkSize();
	  byte[] byteBuffer = new byte[chunkSize];
	  FileInputStream myFileInputStream = new FileInputStream(
	                                          new File(fileName));
	  
	  long position = 1;
	  int byteRead;
	  
	  while((byteRead = myFileInputStream.read(byeteBuffer)) != -1) {	
	  	myBlob.putBytes(position,byteBuffer);
	  	position += byteRead;
		} //end of while
		
		stmt.execute("COMMIT");
		
		myFileInputStream.close();
		
		System.out.println("Wrote content from file "+fileName +" to BLOB");
	} // end of writeBLOB()
	
	public static void addBFILE(Statement stmt,String directory,String fileName) throws SQLException,IOException {
	  //step 1 :创建目录对象
	  //step 2 :使用文件指针填充BFILE列
	  stmt.executeUpdate("insert into bfile_content "+
	                     "values('"+fileName+"',BFILENAME('"+directory+"', '"+fileName+"'))");
		System.out.println("Wrote conten from file "+fileName+" to BFILE");
	}// end of addBFILE()
	
	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\\";
		writeCLOB(stmt,sourceDirectory+"textContent.txt");
		writeBLOB(stmt,sourceDirectory+"binaryContent.doc");
		addBFILE(stmt,"SAMPLE_FILES_DIR","textContent.txt");
		addBFILE(stmt,"SAMPLE_FILES_DIR","binaryContent.doc");
		
		stmt.close();
		conn.close();
		
	} //end of main
}