详解jdbc实现对CLOB和BLOB数据类型的操作

详解jdbc实现对clob和blob数据类型的操作

1、 读取操作

clob 

//获得数据库连接    
  connection con = connectionfactory.getconnection();    
  con.setautocommit(false);    
  statement st = con.createstatement();    
  //不需要“for update”    
  resultset rs = st.executequery("select clobattr from testclob where id=1");    
  if (rs.next())    
  {    
    java.sql.clob clob = rs.getclob("clobattr");    
    reader instream = clob.getcharacterstream();    
    char[] c = new char[(int) clob.length()];    
    instream.read(c);    
    //data是读出并需要返回的数据，类型是string    
    data = new string(c);    
    instream.close();    
  }    
  instream.close();    
  con.commit();    
  con.close();   

blob

//获得数据库连接    
  connection con = connectionfactory.getconnection();    
  con.setautocommit(false);    
  statement st = con.createstatement();    
  //不需要“for update”    
  resultset rs = st.executequery("select blobattr from testblob where id=1");    
  if (rs.next())    
  {    
    java.sql.blob blob = rs.getblob("blobattr");    
    inputstream instream = blob.getbinarystream();    
    //data是读出并需要返回的数据，类型是byte[]    
    data = new byte[input.available()];    
    instream.read(data);    
    instream.close();    
  }    
  instream.close();    
  con.commit();    
  con.close();  

2、写入操作

clob

//获得数据库连接    
  connection con = connectionfactory.getconnection();    
  con.setautocommit(false);    
  statement st = con.createstatement();    
  //插入一个空对象empty_clob()    
  st.executeupdate("insert into testclob (id, name, clobattr) values (1, "thename", empty_clob())");    
  //锁定数据行进行更新，注意“for update”语句    
  resultset rs = st.executequery("select clobattr from testclob where id=1 for update");    
  if (rs.next())    
  {    
    //得到java.sql.clob对象后强制转换为oracle.sql.clob   
    oracle.sql.clob clob = (oracle.sql.clob) rs.getclob("clobattr");    
    writer outstream = clob.getcharacteroutputstream();    
    //data是传入的字符串，定义：string data    
    char[] c = data.tochararray();    
    outstream.write(c, 0, c.length);    
  }    
  outstream.flush();    
  outstream.close();    
  con.commit();    
  con.close();  
  

blob

//获得数据库连接    
  connection con = connectionfactory.getconnection();    
  con.setautocommit(false);    
  statement st = con.createstatement();    
  //插入一个空对象empty_blob()    
  st.executeupdate("insert into testblob (id, name, blobattr) values (1, "thename", empty_blob())");    
  //锁定数据行进行更新，注意“for update”语句    
  resultset rs = st.executequery("select blobattr from testblob where id=1 for update");    
  if (rs.next())    
  {    
    //得到java.sql.blob对象后强制转换为oracle.sql.blob   
    oracle.sql.blob blob = (oracle.sql.blob) rs.getblob("blobattr");    
    outputstream outstream = blob.getbinaryoutputstream();    
    //data是传入的byte数组，定义：byte[] data   
    outstream.write(data, 0, data.length);    
  }    
  outstream.flush();    
  outstream.close();    
  con.commit();    
  con.close();    

3、读写clob/blob数据到文件

tns：

# tnsnames.ora network configuration file: d:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora  
 # generated by oracle configuration tools.  
 
 oradb =  
   (description =  
     (address_list =  
       (address = (protocol = tcp)(host = 192.168.1.100)(port = 1521))  
     )  
     (connect_data =  
       (sid = orcl)  
     )  
   )  
 
 myorcl =  
   (description =  
     (address_list =  
       (address = (protocol = tcp)(host = 192.168.1.100)(port = 1521))  
     )  
     (connect_data =  
       (service_name = myorcl)  
     )  
   )  

table：

create table test_oralob  
 (  
   id     varchar2(20),  
   tsblob blob not null,  
   tsclob clob not null  
 ) 

测试代码：

package lavasoft.oralob.common;  
 
import oracle.sql.blob;  
 
import java.io.*;  
import java.sql.*;  
 
/**  
 * jdbc读写oracle10g的clob、blob  
 *  
 */  
public class testoralob {  
 
     public static void main(string[] args) {  
         insertblob();  
         queryblob();  
     }  
 
     public static void insertblob() {  
         connection conn = dbtoolkit.getconnection();  
         preparedstatement ps = null;  
         try {  
             string sql = "insert into test_oralob (id, tsblob, tsclob) values (?, ?, ?)";  
             ps = conn.preparestatement(sql);  
             ps.setstring(1, "100");  
             //设置二进制blob参数  
             file file_blob = new file("c:\\a.jpg");  
             inputstream in = new bufferedinputstream(new fileinputstream(file_blob));  
             ps.setbinarystream(2, in, (int) file_blob.length());  
             //设置二进制clob参数  
             file file_clob = new file("c:\\a.txt");  
             inputstreamreader reader = new inputstreamreader(new fileinputstream(file_clob));  
             ps.setcharacterstream(3, reader, (int) file_clob.length());  
             ps.executeupdate();  
             in.close();  
         } catch (ioexception e) {  
             e.printstacktrace();  
         } catch (sqlexception e) {  
             e.printstacktrace();  
         } finally {  
             dbtoolkit.closeconnection(conn);  
         }  
     }  
 
     public static void queryblob() {  
         connection conn = dbtoolkit.getconnection();  
         preparedstatement ps = null;  
         statement stmt = null;  
         resultset rs = null;  
         try {  
             string sql = "select tsblob from test_oralob where id ='100'";  
             stmt = conn.createstatement();  
             rs = stmt.executequery(sql);  
             if (rs.next()) {  
                 //读取oracle的blob字段  
                 inputstream in = rs.getbinarystream(1);  
                 file file = new file("c:\\a1.jpg");  
                 outputstream out = new bufferedoutputstream(new fileoutputstream(file));  
                 byte[] buff1 = new byte[1024];  
                 for (int i = 0; (i = in.read(buff1)) > 0;) {  
                     out.write(buff1, 0, i);  
                 }  
                 out.flush();  
                 out.close();  
                 in.close();  
                 //读取oracle的clob字段  
                 char[] buff2 = new char[1024];  
                 file file_clob = new file("c:\\a1.txt");  
                 outputstreamwriter writer = new outputstreamwriter(new fileoutputstream(file_clob));  
                 reader reader = rs.getcharacterstream(1);  
                 for (int i = 0; (i = reader.read(buff2)) > 0;) {  
                     writer.write(buff2, 0, i);  
                 }  
                 writer.flush();  
                 writer.close();  
                 reader.close();  
             }  
             rs.close();  
             stmt.close();  
         } catch (ioexception e) {  
             e.printstacktrace();  
         } catch (sqlexception e) {  
             e.printstacktrace();  
         } finally {  
             dbtoolkit.closeconnection(conn);  
         }  
     }  
 } 

注：如果是具体的字符串写入clob字段，简化写法：

//设置二进制clob参数   
 string xxx = "abcdefg";  
 ps.setcharacterstream(3, new stringreader(xxx), xxx.getbytes("gbk").length);   
 ps.executeupdate();   
 in.close(); 

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持，如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！