java操作Oracle的CLOB类型

今天同事问我是否用过oracle中的CLOB类型,说实话-没听过。

百度了解了下，CLOB是oracle中用于存储文字的大字段类型、当需要存入大数据量（大于4000）时，varchar2不够用，可以使用clob。

Oracle中SQL语句中的两个单引号之间的字符数不能大于4000的限制。'" + data + "' data在sql语句之间，当data的值大于4000个字节时就会报错。

ORACLE - 往有CLOB类型的表中插数据

1.其他字段照常插入，CLOB类型字段先插入"empty_clob()"值 ( )。

2.把自动提交设为false，否则，再次查找时就不能正确更新

2.然后使用带有“for update”的查询语句锁定更新行。

3.最后实例化输出流并对CLOB类型字段数据进行插入操作。

tips:

1.CLOB类型的空对象为empty_clob()；

2.如果在插入前没有将自动提交设为false会报  “fetch out of sequence”；

3.如果不加for   update会报：“row containing the LOB value is not locked”；

关键代码：

StringBuffer sqlstr = new StringBuffer();
		sqlstr.append("INSERT INTO notice t (t.notice_id,t.notice_type,t.notice_title," +
				"t.notice_status,t.sender,t.eff_date,t.exp_date,t.creater,t.create_time," +
				"t.notice_content) VALUES (");
		sqlstr.append(+notice.getNoticeId() + ",");
		sqlstr.append("'" + notice.getNoticeType() + "',");
		sqlstr.append("'" + notice.getNoticeTitle() + "',");
		sqlstr.append("'" + notice.getNoticeStatus() + "',");
		sqlstr.append("'" + notice.getSender() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("to_date('" + theForm.getEndDate2() + " 23:59:59','yyyy-MM-dd HH24:mi:ss'),");
		sqlstr.append("'" + notice.getCreater() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("empty_clob()");//插入空值
		sqlstr.append(")");

		String sql = sqlstr.toString();
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url, username, password);		
		con.setAutoCommit(false);//设置不自动提交
		PreparedStatement pstmt = con.prepareStatement(sql); 
		String content = notice.getNoticeContent().replace('\'', '\"');
        
		try {
			pstmt.execute();            
            		con.commit();//插入
            
           		pstmt = con.prepareStatement("select notice_content from notice where notice_id="+notice.getNoticeId()+" for update");//查找刚刚插入的那条记录 for update
            		ResultSet res = pstmt.executeQuery();
            		CLOB clob = null;
            		while(res.next()){
              			clob = (oracle.sql.CLOB)res.getClob(1);
              			clob.putString(1, content);//content为前台提交过来的公告内容，大数据量
            		}
             
            		pstmt = con.prepareStatement("update notice set notice_content = ? where notice_id=?");//修改notice_content字段的内容
            		pstmt.setClob(1, clob);
            		pstmt.setLong(2, notice.getNoticeId());
           		pstmt.executeUpdate();
             
            		con.commit();
			
			if (nps != null && nps.size() > 0) {
				noticePartnerDao.saveOrUpdateAll(nps);
			}

		} catch (Exception e) {
			info.setCode("1");
			info.setFlag(false);
			info.setInfo("发布公告失败!");

			System.out.println(e.getMessage());
			System.out.println(e.getStackTrace());
			FileLog.errorLog(e, "发布公告失败");
			return info;
		} finally {
			if(pstmt!=null) {
				pstmt.close();				
			}
			if(con!=null) {
				con.close();				
			}
		}

ORACLE - 查询带CLOB类型的数据,并把CLOB数据转为String类型

关键代码：

StringBuffer sqlstr = new StringBuffer();
		sqlstr.append("INSERT INTO notice t (t.notice_id,t.notice_type,t.notice_title," +
				"t.notice_status,t.sender,t.eff_date,t.exp_date,t.creater,t.create_time," +
				"t.notice_content) VALUES (");
		sqlstr.append(+notice.getNoticeId() + ",");
		sqlstr.append("'" + notice.getNoticeType() + "',");
		sqlstr.append("'" + notice.getNoticeTitle() + "',");
		sqlstr.append("'" + notice.getNoticeStatus() + "',");
		sqlstr.append("'" + notice.getSender() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("to_date('" + theForm.getEndDate2() + " 23:59:59','yyyy-MM-dd HH24:mi:ss'),");
		sqlstr.append("'" + notice.getCreater() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("empty_clob()");//插入空值
		sqlstr.append(")");

		String sql = sqlstr.toString();
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url, username, password);		
		con.setAutoCommit(false);//设置不自动提交
		PreparedStatement pstmt = con.prepareStatement(sql); 
		String content = notice.getNoticeContent().replace('\'', '\"');
        
		try {
			pstmt.execute();            
            		con.commit();//插入
            
           		pstmt = con.prepareStatement("select notice_content from notice where notice_id="+notice.getNoticeId()+" for update");//查找刚刚插入的那条记录 for update
            		ResultSet res = pstmt.executeQuery();
            		CLOB clob = null;
            		while(res.next()){
              			clob = (oracle.sql.CLOB)res.getClob(1);
              			clob.putString(1, content);//content为前台提交过来的公告内容，大数据量
            		}
             
            		pstmt = con.prepareStatement("update notice set notice_content = ? where notice_id=?");//修改notice_content字段的内容
            		pstmt.setClob(1, clob);
            		pstmt.setLong(2, notice.getNoticeId());
           		pstmt.executeUpdate();
             
            		con.commit();
			
			if (nps != null && nps.size() > 0) {
				noticePartnerDao.saveOrUpdateAll(nps);
			}

		} catch (Exception e) {
			info.setCode("1");
			info.setFlag(false);
			info.setInfo("发布公告失败!");

			System.out.println(e.getMessage());
			System.out.println(e.getStackTrace());
			FileLog.errorLog(e, "发布公告失败");
			return info;
		} finally {
			if(pstmt!=null) {
				pstmt.close();				
			}
			if(con!=null) {
				con.close();				
			}
		}

关键代码：

//oracle.sql.Clob类型转换成String类型

    public String ClobToString(Clob clob) {
        String reString = "";
        Reader is = null;
        try {
            is = clob.getCharacterStream();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 得到流
        BufferedReader br = new BufferedReader(is);
        String s = null;
        try {
            s = br.readLine();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        StringBuffer sb = new StringBuffer();
        while (s != null) {
            //执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(s);
            try {
                s = br.readLine();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        reString = sb.toString();
        return reString;
    }

String notice_content=ClobToString(rs.getClob("str"));

关键代码：

StringBuffer sqlstr = new StringBuffer();
		sqlstr.append("INSERT INTO notice t (t.notice_id,t.notice_type,t.notice_title," +
				"t.notice_status,t.sender,t.eff_date,t.exp_date,t.creater,t.create_time," +
				"t.notice_content) VALUES (");
		sqlstr.append(+notice.getNoticeId() + ",");
		sqlstr.append("'" + notice.getNoticeType() + "',");
		sqlstr.append("'" + notice.getNoticeTitle() + "',");
		sqlstr.append("'" + notice.getNoticeStatus() + "',");
		sqlstr.append("'" + notice.getSender() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("to_date('" + theForm.getEndDate2() + " 23:59:59','yyyy-MM-dd HH24:mi:ss'),");
		sqlstr.append("'" + notice.getCreater() + "',");
		sqlstr.append("sysdate,");
		sqlstr.append("empty_clob()");//插入空值
		sqlstr.append(")");

		String sql = sqlstr.toString();
		Class.forName(driverClassName);
		Connection con = DriverManager.getConnection(url, username, password);		
		con.setAutoCommit(false);//设置不自动提交
		PreparedStatement pstmt = con.prepareStatement(sql); 
		String content = notice.getNoticeContent().replace('\'', '\"');
        
		try {
			pstmt.execute();            
            		con.commit();//插入
            
           		pstmt = con.prepareStatement("select notice_content from notice where notice_id="+notice.getNoticeId()+" for update");//查找刚刚插入的那条记录 for update
            		ResultSet res = pstmt.executeQuery();
            		CLOB clob = null;
            		while(res.next()){
              			clob = (oracle.sql.CLOB)res.getClob(1);
              			clob.putString(1, content);//content为前台提交过来的公告内容，大数据量
            		}
             
            		pstmt = con.prepareStatement("update notice set notice_content = ? where notice_id=?");//修改notice_content字段的内容
            		pstmt.setClob(1, clob);
            		pstmt.setLong(2, notice.getNoticeId());
           		pstmt.executeUpdate();
             
            		con.commit();
			
			if (nps != null && nps.size() > 0) {
				noticePartnerDao.saveOrUpdateAll(nps);
			}

		} catch (Exception e) {
			info.setCode("1");
			info.setFlag(false);
			info.setInfo("发布公告失败!");

			System.out.println(e.getMessage());
			System.out.println(e.getStackTrace());
			FileLog.errorLog(e, "发布公告失败");
			return info;
		} finally {
			if(pstmt!=null) {
				pstmt.close();				
			}
			if(con!=null) {
				con.close();				
			}
		}