从网上看到这篇文章,转过来做为学习用
设有表:
create table blobimg (id int primary key, contents blob);
一、BLOB入库的专用访问:
1) 最常见于Oracle的JDBC示例中
一般是先通过select ... for update锁定blob列,然后写入blob值,然后提交。要用到特定的Oracle BLOB类。
- Class.forName("oracle.jdbc.driver.OracleDriver");
- Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");
- //处理事务
- con.setAutoCommit(false);
- Statement st = con.createStatement();
- //插入一个空对象
- st.executeUpdate("insert into BLOBIMG values(1,empty_blob())");
- //用for update方式锁定数据行
- ResultSet rs = st.executeQuery(
- "select contents from BLOBIMG where id=1 for update");
- if (rs.next()) {
- //使用oracle.sql.BLOB类,没办法了,变成专用的了
- oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1).;
- //到数据库的输出流
- OutputStream outStream = blob.getBinaryOutputStream();
- //这里用一个文件模拟输入流
- File file = new File("d:\\proxy.txt");
- InputStream fin = new FileInputStream(file);
- //将输入流写到输出流
- byte[] b = new byte[blob.getBufferSize()];
- int len = 0;
- while ( (len = fin.read(b)) != -1) {
- outStream.write(b, 0, len);
- }
- //依次关闭
- fin.close();
- outStream.flush();
- outStream.close();
- }
- con.commit();
- con.close();
2) 再厉害一点的,是通过调用DBMS_LOB包中的一些函数来处理,效率好像也不错.
不过,要使用到存储过程,用到专用类OracleCallableStatement。
- import java.sql.*;
- import java.io.*;
- import oracle.jdbc.driver.*;
- import oracle.sql.*;
- class TestBlobWriteByDBMS_LOB {
- public static void main (String args []) throws SQLException ,
- FileNotFoundException, IOException
- {
- DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
- Connection conn =
- DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora92","scott","tiger");
- conn.setAutoCommit(false);
- Statement stmt = conn.createStatement();
- stmt.execute( "delete from demo" );
- System.out.println( "deleted from demo" );
- stmt.execute( "insert into demo (id,theBlob) values (s_enr.nextval,empty_blob())" );
- conn.commit();
- System.out.println( "committed" );
- ResultSet rset = stmt.executeQuery ("SELECT theBlob FROM demo where id = s_enr.currval FOR UPDATE");
- System.out.println( "Executed Query" );
- if(rset.next())
- {
- System.out.println( "Fetched row " );
- BLOB l_mapBLOB = ((OracleResultSet)rset).getBLOB(1);
- File binaryFile = new File("e:\\free\\jo.jpg");
- FileInputStream instream=new FileInputStream(binaryFile);
- int chunk = 32000;
- System.out.println( "Chunk = "+ chunk );
- byte[] l_buffer = new byte[chunk];
- int l_nread = 0;
- OracleCallableStatement cstmt =
- (OracleCallableStatement)conn.prepareCall( "begin dbms_lob.writeappend( :1, :2, :3 ); end;" );
- cstmt.registerOutParameter( 1, OracleTypes.BLOB );
- while ((l_nread= instream.read(l_buffer)) != -1)
- {
- cstmt.setBLOB( 1, l_mapBLOB );
- cstmt.setInt( 2, l_nread );
- cstmt.setBytes( 3, l_buffer );
- cstmt.executeUpdate();
- l_mapBLOB = cstmt.getBLOB(1);
- }
- instream.close();
- conn.commit();
- rset.close();
- stmt.close();
- conn.close();
- }
- }
- }
二、BLOB值读取的通用处理:
这个jdbc标准接口可以直接调用,因此比较简单,如下所示:
- Connection con = ConnectionFactory.getConnection();
- con.setAutoCommit(false);
- Statement st = con.createStatement();
- ResultSet rs = st.executeQuery("select contents from BLOBIMG where id=1");
- if (rs.next()) {
- java.sql.Blob blob = rs.getBlob(1);
- InputStream ins = blob.getBinaryStream();
- //输出到文件
- File file = new File("d:\\output.txt");
- OutputStream fout = new FileOutputStream(file);
- //下面将BLOB数据写入文件
- byte[] b = new byte[1024];
- int len = 0;
- while ( (len = ins.read(b)) != -1) {
- fout.write(b, 0, len);
- }
- //依次关闭
- fout.close();
- ins.close();
- }
- con.commit();
- con.close();
三、BLOB值写入的通用处理:
这时要借助于PreparedStatement的动态绑定功能,借用其setObject()方法插入字节流到BLOB字段。
- public void insertFile(File f) throws Exception{
- FileInputStream fis=new FileInputStream(f,Connection conn);
- byte[] buffer=new byte[1024];
- data=null;
- int sept=0;int len=0;
- while((sept=fis.read(buffer))!=-1){
- if(data==null){
- len=sept;
- data=buffer;
- }else{
- byte[] temp;
- int tempLength;
- tempLength=len+sept;
- temp=new byte[tempLength];
- System.arraycopy(data,0,temp,0,len);
- System.arraycopy(buffer,0,temp,len,sept);
- data=temp;
- len=tempLength;
- }
- if(len!=data.length()){
- byte temp=new byte[len];
- System.arraycopy(data,0,temp,0,len);
- data=temp;
- }
- }
- String sql="insert into fileData (filename,blobData) value(?,?)";
- PreparedStatement ps=conn.prepareStatement(sql);
- ps.setString(1,f.getName());
- ps.setObject(2,data);
- ps.executeUpdate();
- }
四. CLOB读取的通用处理
- public static String getClobString(ResultSet rs, int col) {
- try {
- Clob c=resultSet.getClob(2);
- Reader reader=c.getCharacterStream():
- if (reader == null) {
- return null;
- }
- StringBuffer sb = new StringBuffer();
- char[] charbuf = new char[4096];
- for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) {
- sb.append(charbuf, 0, i);
- }
- return sb.toString();
- } catch (Exception e) {
- return "";
- }
- }