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;
}
}