java.sql.SQLException: 类型长度大于最大值 clob_月儿

// 通过序列器生成帮助ID
Map map = Query.getMap("Select TO_CHAR(SEQ_HID.nextval) HID FROM DUAL ");
hid = String.valueOf(map.get("HID"));
//插入一条数据，注意CLOB字段，需要先插入一个空的clob类型 empty_clob()，然后再单独更新clob字段
sql = "Insert INTO PF_HELP_CONTENT(HID,HCONTENT) VALUES (?,empty_clob()) ";
try
{
//执行插入
rtn = DbUtils.executeUpdate(sql,hid);
//取得数据库连接
Connection conn = DbUtils.getConnection();
//手动提交
conn.setAutoCommit(false);
//定义ResultSet 和 Clob 变量
ResultSet rs = null;
oracle.sql.CLOB clob = null;
//更新SQL
String sqlclob = "Select HCONTENT FROM PF_HELP_CONTENT Where HID=? FOR Update ";
java.sql.PreparedStatement pstmt = conn.prepareStatement(sqlclob);
//hid是varchar2类型的，所以用setString
pstmt.setString(1,hid);
//执行update语句
rs= pstmt.executeQuery();
if(rs.next())
{
//取得刚才的HCONTENT的内容，也就是刚才添加的empty_clob()
clob = (oracle.sql.CLOB)rs.getClob(1);
}
//需要用clob.getCharacterOutputStream()流方式输出
Writer write = clob.getCharacterOutputStream();
//写入具体内容，helpform.getHContent() 存的是帮助的内容
write.write(helpform.getHContent());
write.flush();
write.close();
rs.close();
//提交
conn.commit();
conn.close();
}
catch(Exception ex)
{
//.........
}

// 通过序列器生成帮助ID    
Map map = Query.getMap("Select TO_CHAR(SEQ_HID.nextval) HID FROM DUAL ");    
hid = String.valueOf(map.get("HID"));    
//插入一条数据，注意CLOB字段，需要先插入一个空的clob类型 empty_clob()，然后再单独更新clob字段    
sql = "Insert INTO PF_HELP_CONTENT(HID,HCONTENT) VALUES (?,empty_clob())  ";    
try    
{               
     //执行插入    
     rtn = DbUtils.executeUpdate(sql,hid);         
         
     //取得数据库连接                              
     Connection conn = DbUtils.getConnection();    
     //手动提交    
     conn.setAutoCommit(false);    
     //定义ResultSet 和 Clob 变量     
     ResultSet rs = null;     
     oracle.sql.CLOB clob = null;     
     //更新SQL     
     String sqlclob = "Select HCONTENT FROM PF_HELP_CONTENT Where HID=? FOR Update ";    
     java.sql.PreparedStatement pstmt = conn.prepareStatement(sqlclob);    
     //hid是varchar2类型的，所以用setString    
     pstmt.setString(1,hid);    
     //执行update语句    
     rs= pstmt.executeQuery();     
     if(rs.next())    
     {     
        //取得刚才的HCONTENT的内容，也就是刚才添加的empty_clob()    
        clob = (oracle.sql.CLOB)rs.getClob(1);     
     }    
     //需要用clob.getCharacterOutputStream<wbr>()流方式输出    
     Writer write = clob.getCharacterOutputStream<wbr>();     
     //写入具体内容，helpform.getHContent() 存的是帮助的内容    
     write.write(helpform.getHContent());     
     write.flush();     
     write.close();     
     rs.close();     
     //提交    
     conn.commit();     
     conn.close();    
}    
catch(Exception ex)    
{    
    //.........    
}

2、修改CLOB字段内容

[java] view plain copy print ?

//如果修改前的字段内容长度大于当前修改的长度时，末尾的部分内容仍然会存在
//所以在修改内容前，需要将PF_HELP_CONTENT内容置空
sql = " Update PF_HELP_CONTENT SET HCONTENT=empty_clob() Where HID=? ";
try
{
rtn = DbUtils.executeUpdate(sql,hid);
//以下操作跟添加时一样
Connection conn = DbUtils.getConnection();
conn.setAutoCommit(false);
ResultSet rs = null;
oracle.sql.CLOB clob = null;
String sqlclob = "Select HCONTENT FROM PF_HELP_CONTENT Where HID=? FOR Update ";
java.sql.PreparedStatement pstmt = conn.prepareStatement(sqlclob);
pstmt.setString(1,hid);
rs= pstmt.executeQuery();
if(rs.next())
{
clob = (oracle.sql.CLOB)rs.getClob(1);
}
Writer write = clob.getCharacterOutputStream();
write.write(helpform.getHContent());
write.flush();
write.close();
rs.close();
conn.commit();
conn.close();
}
catch(Exception ex)
{
//...
}

3、取出CLOB字段的文本内容

[java] view plain copy print ?

Connection conn = DbUtils.getConnection();
conn.setAutoCommit(false);
ResultSet rs = null;
oracle.sql.CLOB clob = null;
String sqlclob = "Select HCONTENT FROM PF_HELP_CONTENT Where HID=? ";
java.sql.PreparedStatement pstmt = conn.prepareStatement(sqlclob);
pstmt.setString(1,hid);
rs= pstmt.executeQuery();
if(rs.next())
{
//rs.getClob(1)中参数1指的是HCONTENT字段索引，第一个字段从1开始而不是从0。
//也可以用字段名来取rs.getClob("HCONTENT")
clob = (oracle.sql.CLOB)rs.getClob(1);
}
if(clob==null || clob.length()==0)
{
hcontent = "";
}else
{
//取CLOB字段内容为字符串
hcontent=clob.getSubString((long)1,(int)clob.length());
}
rs.close();
conn.close();
request.setAttribute("HCONTENT",hcontent);
是jdbc驱动有问题。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：sql server函数

后一篇：SQL SERVER 删除日志

新浪BLOG意见反馈留言板　欢迎批评指正