java-spring的JdbcTempldate对oracle 的CLob字段进行读和写

相关介绍-

 Spring 让 LOB 数据操作变得简单易行（http://www.ibm.com/developerworks/cn/java/j-lo-spring-lob/）

具体代码：

Blob操作也类似Clob，也是通过LobHandler 类的方法进行操作。

import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;import org.springframework.jdbc.support.lob.DefaultLobHandler;import org.springframework.jdbc.support.lob.LobCreator;import org.springframework.jdbc.support.lob.LobHandler;import org.springframework.stereotype.Repository;import com.commom.model.PageBean;/** * 类：ReportTemplateDaoImpl.java * 时间：2016-1-8 * 说明：模板 */@Repositorypublic class ReportTemplateDaoImpl extends BaseDao{/** * 根据条件获取模板的信息 * @param queryMap：查询条件 * @return */public List<Map<String, Object>> queryReportTemplate(Map<String,Object> filterMap) {return execConditonSql(filterMap,false);}/** * 统计模板总记录数 * @param queryMap：查询条件 * @return */public int countReportTemplate(Map<String,Object> filterMap) {List<Map<String, Object>> retObjMapList =  execConditonSql(filterMap, true);return Integer.valueOf(retObjMapList.get(0).get("count").toString());}/** * 根据条件查询模板信息 * @param filterMap * @return */public  List<Map<String,Object>> execConditonSql(Map<String,Object> filterMap,boolean isCount){StringBuffer sql;if(isCount){//查数据条数sql = new StringBuffer(" select count(*) as count from TEMPLATE_TABLE where 1=1 ");}else{//查数据信息sql = new StringBuffer(" select * from TEMPLATE_TABLE where 1=1 ");}if(filterMap != null ){if(filterMap.containsKey("title")){sql.append("and title like '%"+ filterMap.get("title") +"%'");}if(filterMap.containsKey("label")){sql.append("and label like '%"+ filterMap.get("label") +"%'");}if(filterMap.containsKey("type")){sql.append("and type ="+Integer.valueOf(filterMap.get("type").toString()));}}//分页String execSql = sql.toString();if(!isCount && filterMap.containsKey("pageBean")){PageBean pb = (PageBean)filterMap.get("pageBean");execSql = getPageSql(execSql, pb.getBeginRowNum(), pb.getEndRowNum());}if(!isCount){//求表中数据的信息final LobHandler lobHandle = new DefaultLobHandler();@SuppressWarnings({ "unchecked", "rawtypes" })List<Map<String,Object>> resultList =  this.jdbcTemplate.query(execSql,new RowMapper(){@Overridepublic Object mapRow(ResultSet rs, int rowsNum) throws SQLException{List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();while(rs.next()){long id = rs.getLong("id");String title = rs.getString("title");String label = rs.getString("label");int type = rs.getInt("type");//thumbnail_id 字段要根据ID去获得缩略图的模板 待处理int thumbnail_id = rs.getInt("thumbnail_id");String template_content = lobHandle.getClobAsString(rs, "template_content");Map<String,Object> map = new HashMap<String, Object>();map.put("id", id);map.put("title", title);map.put("label", label);map.put("type", type);//thumbnail_id 字段要根据ID去获得缩略图的模板 待处理map.put("thumbnail_id", thumbnail_id);map.put("template_content", template_content);list.add(map);}rs.close();return list;}});return resultList;}else{//求表中总的记录数return this.jdbcTemplate.queryForList(execSql);}}/** * 根据ID查找模板信息 * @param id * @return */public Map<String,Object> getReportTemplateById(int id){String sql ="select id,title,label,type,thumbnail_id,template_content from TEMPLATE_TABLE  where id =?";final LobHandler lobHandle = new DefaultLobHandler();@SuppressWarnings({ "unchecked", "rawtypes" })Map<String,Object> resultMap = (Map<String, Object>) this.jdbcTemplate.query(sql, new Object[]{id},new RowMapper(){@Overridepublic Object mapRow(ResultSet rs, int rowsNum) throws SQLException{long id = rs.getLong(1);String title = rs.getString("title");String label = rs.getString("label");int type = rs.getInt("type");//thumbnail_id 字段要根据ID去获得缩略图的模板 待处理int thumbnail_id = rs.getInt("thumbnail_id");String template_content = lobHandle.getClobAsString(rs, "template_content");Map<String,Object> map = new HashMap<String, Object>();map.put("id", id);map.put("title", title);map.put("label", label);map.put("type", type);//thumbnail_id 字段要根据ID去获得缩略图的模板 待处理map.put("thumbnail_id", thumbnail_id);map.put("template_content", template_content);return map;}});return resultMap;}/** * 添加模板信息 * @param filterMap * @return * @throws IOException  */public boolean addReportTemplateInfo(final Map<String,Object> filterMap){/* * 通过PreparedStatement预处理语句向SQL语句中依次添加各变量 */int affectNum = 0;if(filterMap.size() > 0){final String title = filterMap.get("title").toString();final String label = filterMap.get("label").toString();final Integer type = Integer.valueOf(filterMap.get("type").toString());//判断是否包含thumbnail_id 字段String strThumbnail_id ="0";if(filterMap.containsKey("thumbnail_id")){ strThumbnail_id = filterMap.get("thumbnail_id").toString();}final Integer thumbnail_id = Integer.valueOf(strThumbnail_id);final String template_content =filterMap.get("template_content").toString(); String sql = " INSERT INTO TEMPLATE_TABLE(id,title,label,type,thumbnail_id,template_content)"     + " VALUES(TEMPLATE_TABLE_id_seq.nextval,?,?,?,?,?)";  LobHandler handle = new DefaultLobHandler();  affectNum =  this.jdbcTemplate.update(     sql,     new AbstractLobCreatingPreparedStatementCallback(     handle) {@Overrideprotected void setValues(PreparedStatement pStat, LobCreator lobCreator)throws SQLException, DataAccessException{    pStat.setString(1,title);      pStat.setString(2,label);      pStat.setInt(3, type);      //判断是否包含缩略图ID,没有插入空内容    if(filterMap.containsKey("thumbnail_id"))    {    pStat.setInt(4, thumbnail_id);    }else    {    pStat.setString(4,"");    }      lobCreator.setClobAsString(pStat,5,template_content );}  });}return affectNum > 0;    }/** * 更新模板信息 * @param filterMap * @return */public  boolean updateReportTemplateInfo(final Map<String,Object> filterMap){int affectNum = 0;if(filterMap.size() > 0){final String title = filterMap.get("title").toString();final String label = filterMap.get("label").toString();final Integer type = Integer.valueOf(filterMap.get("type").toString());//判断是否包含thumbnail_id 字段String strThumbnail_id ="0";if(filterMap.containsKey("thumbnail_id")){ strThumbnail_id = filterMap.get("thumbnail_id").toString();}final Integer thumbnail_id = Integer.valueOf(strThumbnail_id);final String template_content =filterMap.get("template_content").toString();final Integer id = Integer.valueOf(filterMap.get("id").toString());String sql ="update TEMPLATE_TABLE set title =?,label =?,type =?,thumbnail_id =?,template_content=? where id =?";  LobHandler handle = new DefaultLobHandler();  affectNum =  this.jdbcTemplate.execute(     sql,     new AbstractLobCreatingPreparedStatementCallback(     handle) {@Overrideprotected void setValues(PreparedStatement pStat, LobCreator lobCreator)throws SQLException, DataAccessException{    pStat.setString(1,title);      pStat.setString(2,label);      pStat.setInt(3, type);      //判断是否包含缩略图ID,没有插入空内容    if(filterMap.containsKey("thumbnail_id"))    {    pStat.setInt(4, thumbnail_id);    }else    {    pStat.setString(4,"");    }      lobCreator.setClobAsString(pStat,5,template_content );            pStat.setInt(6, id);}  });}return affectNum > 0;}/** * @param id * @return * 删除模板信息 */public boolean deleteReportTemplateInfo(int id){String sql ="delete from TEMPLATE_TABLE where id =?";return this.jdbcTemplate.update(sql,new Object[]{id})>0;}}

相关查考文章：

利用spring的jdbcTemplate处理blob、clobspring定义了一个以统一的方式操作各种数据库的Lob类型数据的LobCreator(保存的时候用),同时提供了一个LobHandler为操作二进制字段和大文本字段提供统一接口访问。 举例，例子里面的t_post表中post_text字段是CLOB类型,而post_attach是BLOG类型：public class PostJdbcDao extends JdbcDaoSupport implements PostDao {private LobHandler lobHandler;private DataFieldMaxValueIncrementer incre;public LobHandler getLobHandler() {   return lobHandler;}public void setLobHandler(LobHandler lobHandler) {   this.lobHandler = lobHandler;}public void addPost(final Post post) {     String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"     + " VALUES(?,?,?,?)";   getJdbcTemplate().execute(     sql,     new AbstractLobCreatingPreparedStatementCallback(       this.lobHandler) {      protected void setValues(PreparedStatement ps,        LobCreator lobCreator) throws SQLException {       ps.setInt(1, incre.nextIntValue());        ps.setInt(2, post.getUserId());        lobCreator.setClobAsString(ps, 3, post.getPostText());       lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());      }     });}}设置相对应的配置文件(Oracle 9i版本),Oracle的数据库最喜欢搞搞特别的东西啦：<bean id="nativeJdbcExtractor"   class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"   lazy-init="true" /><bean id="oracleLobHandler"   class="org.springframework.jdbc.support.lob.OracleLobHandler"   lazy-init="true">   <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" /></bean><bean id="dao" abstract="true">   <property name="jdbcTemplate" ref="jdbcTemplate" /></bean><bean id="postDao" parent="dao"   class="com.baobaotao.dao.jdbc.PostJdbcDao">   <property name="lobHandler" ref="oracleLobHandler" /></bean>Oracle 10g或其他数据库如下设置：<bean id="defaultLobHandler"   class="org.springframework.jdbc.support.lob.DefaultLobHandler"   lazy-init="true" /><bean id="dao" abstract="true">   <property name="jdbcTemplate" ref="jdbcTemplate" /></bean><bean id="postDao" parent="dao"   class="com.baobaotao.dao.jdbc.PostJdbcDao">   <property name="lobHandler" ref="defaultLobHandler" /></bean>读取BLOB/CLOB块,举例：public List getAttachs(final int userId){   String sql = "SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null";   return getJdbcTemplate().query(       sql,new Object[] {userId},       new RowMapper() {        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {        Post post = new Post();        int postId = rs.getInt(1);         byte[] attach = lobHandler.getBlobAsBytes(rs, 2);         post.setPostId(postId);         post.setPostAttach(attach);         return post;        }       });}