CLOB字段的插入以及更新操作

1.  插入方法，首先需要先插入一个空的CLOB对象，之后查询出该条对象，并进行更新操作：

	@Override
	public void add(Object obj) throws DAOException {

		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			//获得一个主键
			String seqSql ="select cms.SEQ_TERMSERVICE_ID.nextval from dual";
			Long id = this.getJdbcTemplate().queryForLong(seqSql);
			//执行插入操作
			Statement st = conn.createStatement();
			String sql1 = "insert into cms.s_termservice(TERMSERVICE_ID,CREATETIME,UPDATETIME,CONTENT) values(" + id + ",sysdate,sysdate,empty_clob())";
			st.executeUpdate(sql1);
			//从数据库中重新获取CLOB对象写入数据
			String sql2 = "select CONTENT from cms.s_termservice where TERMSERVICE_ID=" + id + " for update";
			//锁定数据行进行更新，注意“for update”语句
			ResultSet rs = st.executeQuery(sql2);
			while(rs.next()) {
				CLOB clob = (CLOB)rs.getClob("CONTENT");  
	            clob.putString(1, ((TermService)obj).getContent());
				String sql3 = "update cms.s_termservice set CONTENT=? where TERMSERVICE_ID=" + id;
	            pst = conn.prepareStatement(sql3);  
	            pst.setClob(1, clob);  
	            pst.execute();  
			}
            //最后一步自己提交
            conn.commit();
            conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage());
		} finally {
            try {
            	if(conn != null) {
                    conn.close();  
            	}
            	if(pst != null) {
            		pst.close();
            	}
            } catch (SQLException e1) {  
            	logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage());
            }
		}
	}

2. 更新操作，我个人是先置空CLOB字段，在从新更新该字段，如果没这样做，在更新时，如果更新数据比数据库中的数据短时，并不能完全更新，本人是这样操作的

以下是代码：

	@Override
	public void update(Object obj) throws DAOException {

		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			int id = ((TermService)obj).getTermserviceId();
			//从数据库中重新获取CLOB对象写入数据
			String sql1 = "update cms.s_termservice s set s.CONTENT=' ' where TERMSERVICE_ID=" + id; //这边需要设置一个空的字段，后面就不会出现空指针
			st.execute(sql1);
			String sql2 = "select CONTENT from cms.s_termservice s where s.TERMSERVICE_ID=" + id;
			//锁定数据行进行更新，注意“for update”语句
			ResultSet rs = st.executeQuery(sql2);
			while(rs.next()) {
				CLOB clob = (CLOB)rs.getClob("CONTENT");
				if(clob != null) {
					clob.putString(1, ((TermService)obj).getContent());
					String sql3 = "update cms.s_termservice set CONTENT=?,UPDATETIME=sysdate where TERMSERVICE_ID=" + id;
		            pst = conn.prepareStatement(sql3);  
		            pst.setClob(1, clob);
		            pst.execute(); 
				}
			}
            //最后一步自己提交
            conn.commit();
            conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage());
		} finally {
            try {
            	if(conn != null) {
                    conn.close();  
            	}
            	if(pst != null) {
            		pst.close();
            	}
            } catch (SQLException e1) {  
            	logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage());
            }
		}
	}

3.  获取CLOB，并进行STRING的转化

	private RowMapper showTSMapper = new RowMapper() {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			TermService termService = new TermService();
			termService.setTermserviceId(rs.getInt("TERMSERVICE_ID"));
			termService.setCreatetime(rs.getDate("CREATETIME"));
			termService.setUpdatetime(rs.getDate("UPDATETIME"));
			StringBuffer sb = new StringBuffer();
            try {
    			CLOB clob = (CLOB)rs.getClob("CONTENT");
    			if(clob != null) {
	    				Reader is = clob.getCharacterStream();
	    				BufferedReader br = new BufferedReader(is);
	    				String s = br.readLine();
	    				while (s != null) {
		    				sb.append(s);
		    				s = br.readLine();
	    				}
    			}
	    		if(sb.length()>10) {
	    			termService.setContent(sb.substring(0,10) + ".......");
	    		} else {
	    			termService.setContent(sb.toString());
	    		}
			} catch (IOException e) {
				logger.error("in TermserviceDaoImpl.showTSMapper fail, e is " + e.getMessage());
			}  
			return termService;
		}
	};
	
	private RowMapper modifyMapper = new RowMapper() {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			TermService termService = new TermService();
			termService.setTermserviceId(rs.getInt("TERMSERVICE_ID"));
			termService.setCreatetime(rs.getDate("CREATETIME"));
			termService.setUpdatetime(rs.getDate("UPDATETIME"));
			StringBuffer sb = new StringBuffer();
            try {
    			CLOB clob = (CLOB)rs.getClob("CONTENT");
    			if(clob != null) {
	    				Reader is = clob.getCharacterStream();
	    				BufferedReader br = new BufferedReader(is);
	    				String s = br.readLine();
	    				while (s != null) {
		    				sb.append(s);
		    				s = br.readLine();
	    				}
    			}
	    		termService.setContent(sb.toString());
			} catch (IOException e) {
				logger.error("in TermserviceDaoImpl.tsMapper fail, e is " + e.getMessage());
			}  
			return termService;
		}
	};