clob字段的值插入和查询N种方法【包括java调用存储过程传入clob参数】

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestClob {
public static void main(String[] args) throws SQLException, IOException {
        /**测试所用的表对象
         * CREATE TABLE testclob(
              col_1_1_21_1  VARCHAR2(500)   NOT NULL,
              col_1_1_21_2   VARCHAR2(500), 
              col_1_1_21_46   clob                              
            )
            select * from testclob
         * **/
        String content = "插入内容";
        /**更新clob的内容*/
        String sql = " insert into testclob(col_1_1_21_1,col_1_1_21_2,col_1_1_21_46) values('1','test1',empty_clob())";
        Connection conn = DBPool.getLocalConnection();
        conn.setAutoCommit(false);
        Statement statmt = conn.createStatement();
        statmt.executeUpdate(sql);
        conn.commit();
        
        sql = " select col_1_1_21_46 from testclob where col_1_1_21_1=1 for update";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rset = ps.executeQuery();
        if (rset.next()){
            oracle.sql.CLOB clob=(oracle.sql.CLOB)rset.getClob(1);
            BufferedWriter out=new BufferedWriter(clob.getCharacterOutputStream());
            out.write(content,0,content.length());
            out.close();
        }
        conn.commit();
        rset.close();
        ps.close();
        
        
        /****读取clob的内容*****/
      sql="select col_1_1_21_46 from testclob ";
      ps=conn.prepareStatement(sql);
      rset=ps.executeQuery();
      while (rset.next()){
           oracle.sql.CLOB clob=(oracle.sql.CLOB)rset.getClob(1);
           BufferedReader in=new BufferedReader(clob.getCharacterStream());
           StringWriter out=new StringWriter();
           int c;
           while((c=in.read())!=-1){
               out.write(c);
           }
           content=out.toString();
           System.out.println (content);//输出CLOB内容
      }
      rset.close();
      ps.close();
      conn.close();
 }
}

 

clob和blob最大的区别，我觉得在于blob内容是不可见的，而clob是可见的文本。至于他们读写的方式都是可以用流去处理，没有什么大的差异。

【遇到问题】

百度上一大堆解决方法，但是就是没有一个适用的，最后只能通过修改方法避开这个异常了。

@ clob插入值

public static void main(String[] args) throws SQLException, IOException {
　　　　　　　Connection conn = DBPool.getLocalConnection();
            String sql = "insert into testclob(col_1_1_21_1,col_1_1_21_2,col_1_1_21_46) values ('1','test',?)";// 要执行的SQL语句
            PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句
            // PreparedStatement支持SQL带有问号？，可以动态替换？的内容。
            Reader clobReader = new StringReader(content); // 将 text转成流形式
            stmt.setCharacterStream(1, clobReader, content.length());// 替换sql语句中的？
            stmt.close();
            conn.close();
        }

@  clob取值

public static void main(String[] args) throws SQLException, IOException {
        Connection conn= DBPool.getLocalConnection();
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); 
        String sql="select col_1_1_21_46 from testclob where col_1_1_21_1=1"; 
        ResultSet rs=stmt.executeQuery(sql); 
        while(rs.next()) {
            java.sql.Clob clob = rs.getClob("col_1_1_21_46");
            String rtn = clob.getSubString((long)1,(int)clob.length());
            System.out.println(rtn);
        }
        rs.close(); 
        stmt.close(); 
        conn.close(); 
    }

 @ 创建表对象

CREATE TABLE testclob(
         id  VARCHAR2(500)   NOT NULL,
         name   VARCHAR2(500), 
         vclob   clob                              
)

@ 创建并编译存储过程

create or replace procedure TEST(amobile in clob) is
amobile2 clob;
begin
  amobile2 :=amobile;
  insert into testclob(id,name,vclob) values(1,'test',amobile2);
  commit;
end TEST;

@ java调用存储过程并传入参数

 

import java.io.IOException;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.sql.CLOB;
public class testClobPro {
    /**
     * 该测试方法实现java调用存储过程，参数为clob类型
     * @param args
     * @throws SQLException 
     * @throws IOException 
     */
    public static void main(String[] args) throws SQLException, IOException {
        Connection conn = DBPool.getLocalConnection();
        StringBuffer sBuffer = new StringBuffer();
        for(int i = 1;i<20000;i++){
            sBuffer.append(i).append(",");
        }
        String query = "{call TEST(?)}";
        CallableStatement stmtt = conn.prepareCall(query);
        oracle.sql.CLOB clob = (CLOB)getCLOB(conn,sBuffer.toString());
        stmtt.setObject(1,clob);
        stmtt.execute();
        conn.commit();
        stmtt.close();
        conn.close();
    }
/**该方法实现将string转化为clob类型对象*/
    public static  CLOB getCLOB( Connection conn ,String clobData) throws SQLException, IOException{
        CLOB clob = CLOB.createTemporary(conn , false,CLOB.DURATION_SESSION ); 
        clob.open( CLOB.MODE_READWRITE );
        Writer tempClobWriter = clob.getCharacterOutputStream( ); 
        tempClobWriter.write( clobData ); 
        tempClobWriter.flush( );
        tempClobWriter.close( ); 
        clob.close( ); 
        return clob;
    }
}