通过java往oracle中blob字段写入数据

[raomengwen]

直接用这样 Select GS from BKS_XSZPXX where XH='3071801040' For Update ，再一条一条记录的写入BLOB可行吗？有没有更好的方法？ GS 是BLOB格式

[tangren]

这个很全面
http://blog.csdn.net/wazj0517/archive/2006/10/19/1341074.aspx

[Phoenix_99]

先查空行再用流的方式写入

[duqiangcise]

http://topic.csdn.net/u/20100105/14/b8789e2f-3859-4ed2-86c3-a84c51c2898a.html

[行舟]

我最直接：

package odbcOrJdbc;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @desc:Oracle 大字段
 * @author jiangwh
 * @time\date 下午12:55:03\2010-3-24
 */
public class DealWithLob {

	//存放图片文件以及文本
	/**
	 *1.因为需要使用流操作，因此需要在保存数据之前先向数据库中存放
	 * 	空的blob以及clob记录
	 *2.oracle中empty_bolb()和empty_clob()方法可以实现
	 *3.查询获得blob以及clob的操作流进行写入操作
	 * */
	public static void save(){
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try{
			conn.setAutoCommit(false);//发生异常可以回滚
			//步骤1：插入一条记录，blob就阿clob所对应的列为空值
			//插入空值的目的是获得输出流。
			stmt=conn.createStatement();
			String sql1=
			"insert into lob_tbl values(1,'tom',empty_blob(),empty_clob())";
			stmt.execute(sql1);
			//步骤2：将插入的记录读入到结果集
			String sql2=
				"select * from lob_tbl where id=1";
			rs=stmt.executeQuery(sql2);
			
			oracle.sql.BLOB blob=null;
			oracle.sql.CLOB clob=null;
			//步骤3：通过get方法获得对应的blob，clob对象，为实现获得对应的
			//输出流，需要将其进行oracle.sql.BLOB／CLOB的强制类型转换
			while(rs.next()){
				blob=(oracle.sql.BLOB)rs.getBlob(3);
				clob=(oracle.sql.CLOB)rs.getClob(4);
			}
			//步骤4：进行流操作实现具体blob以及clob内容的存储
			
			//对blob对象存储
			OutputStream osBlob=blob.getBinaryOutputStream();
			
			FileInputStream fisBlob =new FileInputStream
			("/home/briup/JD0802集体照.jpg");
			BufferedInputStream bisBlob=new BufferedInputStream(fisBlob);
			byte[] bufferBlob=new byte[1024];
			int lengthBlob=0;
			while((lengthBlob=bisBlob.read(bufferBlob))!=-1){
				osBlob.write(bufferBlob,0,lengthBlob);
			}
			osBlob.flush();
			osBlob.close();
			bisBlob.close();
			fisBlob.close();
			//对clob的存储
			Writer writerClob=clob.getCharacterOutputStream();
			writerClob.write("I an tom");
			writerClob.flush();
			writerClob.close();
			conn.commit();
		}catch(Exception e){
			try{
				conn.rollback();
			}catch(Exception e1){
				
			}
		}finally{
//			close(conn,stmt,rs);
		}
	}
	//生成图片文件以及文本文件
	public static void load(){
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try{
			String sql="select * from lob_tbl where id=1";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			
			oracle.sql.BLOB blob=null;
			oracle.sql.CLOB clob=null;
			while(rs.next()){
				blob=(oracle.sql.BLOB)rs.getBlob(3);
				clob=(oracle.sql.CLOB)rs.getClob(4);
			}
			InputStream isBlob=blob.getBinaryStream();
			BufferedInputStream bisBlob=new BufferedInputStream(isBlob);
			
			FileOutputStream fosBlob=new FileOutputStream
			("/home/briup/Desktop/blob.gif");
			BufferedOutputStream bosBlob=new BufferedOutputStream(fosBlob);
			
			byte[] bufferBlob=new byte[2048];
			int lengthBlob=0;
			while((lengthBlob=bisBlob.read(bufferBlob))!=-1){
				bosBlob.write(bufferBlob,0,lengthBlob);
			}
			bosBlob.flush();
			bosBlob.close();
			fosBlob.close();
			bisBlob.close();
			isBlob.close();
			
			//对clob的读出操作
			Reader readerClob=clob.getCharacterStream();
			FileOutputStream fosClob=new FileOutputStream
			("/home/briup/Desktop/clob.txt");
			BufferedOutputStream bosClob=new BufferedOutputStream(fosClob);
			OutputStreamWriter oswClob=new OutputStreamWriter(bosClob);
			char ch[]=new char[30];
			int lengthClob=0;
			while((lengthClob=readerClob.read(ch))!=-1){
				oswClob.write(ch,0,lengthClob);
			}
			oswClob.flush();
			oswClob.close();
			bosClob.close();
			fosClob.close();
			readerClob.close();
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
//			close(conn,stmt,rs);
		}
	}
}