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

最新推荐文章于 2021-05-17 20:28:20 发布

追梦java

最新推荐文章于 2021-05-17 20:28:20 发布

阅读量1.2k

点赞数

文章标签：数据库 python java

1. 插入方法，首先需要先插入一个空的CLOB对象，之后查询出该条对象，并进行更新操作： view plaincopy to clipboardprint? 01.@Override 02.public void add(Object obj) throws DAOException { 03. 04. Connection conn = null; 05. PreparedStatement pst = null; 06. try { 07. conn = getJdbcTemplate().getDataSource().getConnection(); 08. conn.setAutoCommit(false); 09. //获得一个主键 10. String seqSql ="select cms.SEQ_TERMSERVICE_ID.nextval from dual"; 11. Long id = this.getJdbcTemplate().queryForLong(seqSql); 12. //执行插入操作 13. Statement st = conn.createStatement(); 14. String sql1 = "insert into cms.s_termservice(TERMSERVICE_ID,CREATETIME,UPDATETIME,CONTENT) values(" + id + ",sysdate,sysdate,empty_clob())"; 15. st.executeUpdate(sql1); 16. //从数据库中重新获取CLOB对象写入数据 17. String sql2 = "select CONTENT from cms.s_termservice where TERMSERVICE_ID=" + id + " for update"; 18. //锁定数据行进行更新，注意“for update”语句 19. ResultSet rs = st.executeQuery(sql2); 20. while(rs.next()) { 21. CLOB clob = (CLOB)rs.getClob("CONTENT"); 22. clob.putString(1, ((TermService)obj).getContent()); 23. String sql3 = "update cms.s_termservice set CONTENT=? where TERMSERVICE_ID=" + id; 24. pst = conn.prepareStatement(sql3); 25. pst.setClob(1, clob); 26. pst.execute(); 27. } 28. //最后一步自己提交 29. conn.commit(); 30. conn.setAutoCommit(true); 31. } catch (SQLException e) { 32. logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage()); 33. } finally { 34. try { 35. if(conn != null) { 36. conn.close(); 37. } 38. if(pst != null) { 39. pst.close(); 40. } 41. } catch (SQLException e1) { 42. logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage()); 43. } 44. } 45.} @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字段，在从新更新该字段，如果没这样做，在更新时，如果更新数据比数据库中的数据短时，并不能完全更新，本人是这样操作的以下是代码： view plaincopy to clipboardprint? 01.@Override 02.public void update(Object obj) throws DAOException { 03. 04. Connection conn = null; 05. PreparedStatement pst = null; 06. try { 07. conn = getJdbcTemplate().getDataSource().getConnection(); 08. conn.setAutoCommit(false); 09. Statement st = conn.createStatement(); 10. int id = ((TermService)obj).getTermserviceId(); 11. //从数据库中重新获取CLOB对象写入数据 12. String sql1 = "update cms.s_termservice s set s.CONTENT=' ' where TERMSERVICE_ID=" + id; //这边需要设置一个空的字段，后面就不会出现空指针 13. st.execute(sql1); 14. String sql2 = "select CONTENT from cms.s_termservice s where s.TERMSERVICE_ID=" + id; 15. //锁定数据行进行更新，注意“for update”语句 16. ResultSet rs = st.executeQuery(sql2); 17. while(rs.next()) { 18. CLOB clob = (CLOB)rs.getClob("CONTENT"); 19. if(clob != null) { 20. clob.putString(1, ((TermService)obj).getContent()); 21. String sql3 = "update cms.s_termservice set CONTENT=?,UPDATETIME=sysdate where TERMSERVICE_ID=" + id; 22. pst = conn.prepareStatement(sql3); 23. pst.setClob(1, clob); 24. pst.execute(); 25. } 26. } 27. //最后一步自己提交 28. conn.commit(); 29. conn.setAutoCommit(true); 30. } catch (SQLException e) { 31. logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage()); 32. } finally { 33. try { 34. if(conn != null) { 35. conn.close(); 36. } 37. if(pst != null) { 38. pst.close(); 39. } 40. } catch (SQLException e1) { 41. logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage()); 42. } 43. } 44.} @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的转化 view plaincopy to clipboardprint? 01.private RowMapper showTSMapper = new RowMapper() { 02. public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 03. TermService termService = new TermService(); 04. termService.setTermserviceId(rs.getInt("TERMSERVICE_ID")); 05. termService.setCreatetime(rs.getDate("CREATETIME")); 06. termService.setUpdatetime(rs.getDate("UPDATETIME")); 07. StringBuffer sb = new StringBuffer(); 08. try { 09. CLOB clob = (CLOB)rs.getClob("CONTENT"); 10. if(clob != null) { 11. Reader is = clob.getCharacterStream(); 12. BufferedReader br = new BufferedReader(is); 13. String s = br.readLine(); 14. while (s != null) { 15. sb.append(s); 16. s = br.readLine(); 17. } 18. } 19. if(sb.length()>10) { 20. termService.setContent(sb.substring(0,10) + "......."); 21. } else { 22. termService.setContent(sb.toString()); 23. } 24. } catch (IOException e) { 25. logger.error("in TermserviceDaoImpl.showTSMapper fail, e is " + e.getMessage()); 26. } 27. return termService; 28. } 29.}; 30. 31.private RowMapper modifyMapper = new RowMapper() { 32. public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 33. TermService termService = new TermService(); 34. termService.setTermserviceId(rs.getInt("TERMSERVICE_ID")); 35. termService.setCreatetime(rs.getDate("CREATETIME")); 36. termService.setUpdatetime(rs.getDate("UPDATETIME")); 37. StringBuffer sb = new StringBuffer(); 38. try { 39. CLOB clob = (CLOB)rs.getClob("CONTENT"); 40. if(clob != null) { 41. Reader is = clob.getCharacterStream(); 42. BufferedReader br = new BufferedReader(is); 43. String s = br.readLine(); 44. while (s != null) { 45. sb.append(s); 46. s = br.readLine(); 47. } 48. } 49. termService.setContent(sb.toString()); 50. } catch (IOException e) { 51. logger.error("in TermserviceDaoImpl.tsMapper fail, e is " + e.getMessage()); 52. } 53. return termService; 54. } 55.}; 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; } };