JDBC操作大数据CLOB和BLOB

JDBC操作大数据CLOB和BLOB：

package jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import word.JavaWord;


/**
 * JDBC操作大数据CLOB和BLOB
 * @author wangzg
 *
 */
public class LobTest {
	
	/**
	 * @Title: main
	 * @Description:
	 * @param：
	 * @return void 
	 * @user： wangzg
	 * @Date：2014-7-2
	 * @throws
	 */
	public static void main(String[] args) {
		
		//insertLob("doc\\wzg.doc");
		
		//insertLob2("doc\\wzg.doc");
		
		//queryLob();
	}
	
	/**
	 * 
	 * @Title: getConnection
	 * @Description:
	 * @param：
	 * @return Connection 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static Connection getConnection() throws ClassNotFoundException, SQLException{
		Connection conn = null;
		//加载oracle驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//连接数据库
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
		String user ="wzg";
		String password = "wzg";
		conn = DriverManager.getConnection(url, user, password);
		return conn;
	}

	/**
	 * setBinaryStream
	 * @Title: insertLob
	 * @Description:
	 * @param：
	 * @return void 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static void insertLob(String filePath){

		System.out.println("insert start ................");
		Connection conn = null;
		java.sql.PreparedStatement pstm = null;
		ResultSet rs = null;
		
		File file = new File(filePath);
		
		String fileText = JavaWord.readWord2003(filePath);
		String fileName = file.getName();
		int fileLength = (int) file.length();
		
		System.out.println(fileName);
		
		try {
			InputStream isFileBlob = new FileInputStream(file);
			conn = getConnection();
			pstm = conn.prepareStatement("insert into t_file(id,name,context,bfile)values(TEST_SEQUENCE.NEXTVAL,?,?,?)");
			pstm.setString(1, fileName);
			pstm.setString(2, fileText);
			
			//fileLength 必须为int类型
			pstm.setBinaryStream(3, isFileBlob,fileLength);
			
			pstm.executeUpdate();
			
			pstm.close();
			isFileBlob.close();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		System.out.println("insert end ................");
	}	
	
	/**
	 * for update
	 * @Title: insertLob2
	 * @Description:
	 * @param：
	 * @return void 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static void insertLob2(String filePath){
		 
		
		System.out.println("insert2 start ................");
		Connection conn = null;
		java.sql.PreparedStatement pstm = null;
		ResultSet rs = null;
		
		File file = new File(filePath);
		
		String fileText = JavaWord.readWord2003(filePath);
		String fileName = file.getName();
		int fileLength = (int) file.length();
		
		System.out.println(fileName);
		
		try{
			 
			 conn = getConnection();
			 conn.setAutoCommit(false);
			 
			 pstm = conn.prepareStatement("select TEST_SEQUENCE.NEXTVAL from dual");
			 rs = pstm.executeQuery();
			 int index = 0;
			 if(rs.next()){
				  index  = rs.getInt(1);
			 }
			 
			 System.out.println(index);
			 
			 pstm = conn.prepareStatement("insert into t_file(id,name,context,bfile)values(?,?,?,empty_blob())");
			 pstm.setInt(1, index);
			 pstm.setString(2, fileName);
			 pstm.setString(3, fileText);
			 
			 pstm.executeUpdate();
			 
			 pstm = conn.prepareStatement("select bfile from  t_file  where id= ?  for update");
			 pstm.setInt(1, index);
			 
			 rs = pstm.executeQuery();  
			 if (rs.next()) {  
					 Blob blob=rs.getBlob(1);  
					 //到数据库的输出流  
					 OutputStream outStream = blob.setBinaryStream(0); 
					 byte[] bu=new byte[1024];
					 int num;
					 InputStream fis = new FileInputStream(file);
					 while((num=fis.read(bu))>0){
						 outStream.write(bu,0,num);
					 }
					 outStream.flush();  
					 outStream.close(); 
					 fis.close();
			 }
			 
			 conn.commit();
			 conn.setAutoCommit(true);
			 
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		System.out.println("insert2 end ................");
	}
	
	/**
	 * 
	 * @Title: queryLob
	 * @Description:
	 * @param：
	 * @return void 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static void queryLob(){
		
		System.out.println("query start ................");
		Connection conn = null;
		java.sql.PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstm = conn.prepareStatement("select * from t_file order by id asc");
			rs = pstm.executeQuery();
			
			while(rs.next()){
				
				String id = rs.getString(1);
				System.out.println(id);
				String name = rs.getString(2);
				System.out.println(name);
				
				java.sql.Blob bFile = rs.getBlob(4); 
				InputStream bis = bFile.getBinaryStream() ;
				OutputStream os = new FileOutputStream("doc\\"+getFileName(name)+id+getFileType(name));
				int b = bis.read(); 
				while (b != -1 ) {
					os.write((char)b);
					b = bis.read();
				}
				os.flush();
				os.close();
				bis.close();
				
			}
			rs.close();
			pstm.close();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("query end ................");
	}
    
	/**
	 * 
	 * @Title: getFileName
	 * @Description:
	 * @param：
	 * @return String 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static String getFileName(String fileName){
		return fileName.substring(0, fileName.lastIndexOf('.'));
	}
	
	/**
	 * 
	 * @Title: getFileType
	 * @Description:
	 * @param：
	 * @return String 
	 * @user： wangzg
	 * @Date：2014-7-4
	 * @throws
	 */
	public static String getFileType(String fileName){
		return fileName.substring(fileName.lastIndexOf('.'));
	}
}