存储blob字段

Blob字段存储在JDBC4.0之前和之后有区别，具体区别看代码。

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

import net.sourceforge.jtds.jdbc.Driver.*;
/**
 * @author:kenny dong
 */
public class StoreBlobDemo {

	public static void main(String[] args) throws IOException, ClassNotFoundException{
		StoreBlobDemo demo = new StoreBlobDemo();
		//demo.storeBlobOldVersion();
		demo.storeBlob4Version();
	}
	
	public void storeBlobOldVersion() throws ClassNotFoundException, IOException{
		//Store blob type before the jdbc 4.0
		String url = "jdbc:jtds:sqlserver://172.20.30.78:1433;databasename=LRIReporterMIS"; 
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet ret = null;
		   try{  
			   Class.forName("net.sourceforge.jtds.jdbc.Driver") ;
			   con = DriverManager.getConnection(url) ; 
			   stmt = con.prepareStatement("select * from KennyTest");
			   ret = stmt.executeQuery();
			   if(ret.next()){
				   Blob blob = ret.getBlob("content");
				   //The blob couldn't be null, if null please insert into something
				   //sqlserver: insert into KennyTest(id,content) values(1,'aaa');
				   //oracle:insert into KennyTest(id,content) values(1,EMPTY_BLOB());
				   BufferedWriter output = new BufferedWriter(
							new OutputStreamWriter(blob.setBinaryStream(1),"UTF-8"),65536);
				   output.write("teststring");
				   output.flush();
				   stmt = con.prepareStatement("update KennyTest set content = ? where id= 1");
				   stmt.setBlob(1, blob);
				   stmt.execute();
			   }
		   }catch(SQLException se){   
			    se.printStackTrace() ;   
			}finally {
	    		if (ret != null) try { ret.close(); } catch(Exception e) {}
	    		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    		if (con != null) try { con.close(); } catch(Exception e) {}
	    	}
	}
	
	public void storeBlob4Version(){
		//Store blob type in the jdbc 4.0
		Connection con = null;
		SQLServerPreparedStatement cstmt = null;
		ResultSet rs = null;
		try {
			// Establish the connection.
			SQLServerDataSource ds = new SQLServerDataSource();
			ds.setIntegratedSecurity(true);
			ds.setServerName("172.20.30.78");
			ds.setPortNumber(1433); 
			ds.setDatabaseName("LRIReporterMIS");
			con = ds.getConnection();
			Blob blob = con.createBlob();
			BufferedWriter output = new BufferedWriter(
					new OutputStreamWriter(blob.setBinaryStream(1),"UTF-8"),65536);
		   output.write("teststring");
		   output.flush();
		   cstmt = (SQLServerPreparedStatement) con.prepareStatement("insert into KennyTest(id,content) values(?,?)");
		   cstmt.setInt(1, 3);
		   cstmt.setBlob(2, blob);
		   cstmt.execute();
		   }catch (Exception e) {
	    		e.printStackTrace();
	    	}finally {
	    		if (rs != null) try { rs.close(); } catch(Exception e) {}
	    		if (cstmt != null) try { cstmt.close(); } catch(Exception e) {}
	    		if (con != null) try { con.close(); } catch(Exception e) {}
	    	}
	}
}

 可以看到4.0之后存储更直接一点更方便。