oracle clob 增 改 查

package com.dao.webSite;

// default package

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;
import org.springframework.stereotype.Repository;

import com.base.generic.GenericDao;
import com.model.webSite.ModelModel;

@Repository("modelDao")
public class ModelDao extends GenericDao {
 private static final Log log = LogFactory.getLog(ModelDao.class);
 @Resource(name="lobHandler")
 private LobHandler lobHandler;
 public boolean save(ModelModel transientInstance) {
  try {
   final ModelModel md = transientInstance;
   String sql = " INSERT INTO "+ModelModel.tableName+"(id,userId,siteId,name,type,codeHeadLeft," +
     "codeHeadRight,code,codeEdit,codeWeb,model,bgColor,style) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
      super.execute( sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
         protected void setValues(PreparedStatement ps,  LobCreator lobCreator) throws SQLException ,DataAccessException{
          ps.setLong(1, md.getId());
          ps.setLong(2, md.getUserId());
          ps.setLong(3, md.getSiteId());
          ps.setString(4, md.getName());
          ps.setString(5, md.getType());
          ps.setString(6, md.getCodeHeadLeft());
          ps.setString(7, md.getCodeHeadRight());
          lobCreator.setClobAsString(ps, 8, md.getCode());
          lobCreator.setClobAsString(ps, 9, md.getCodeEdit());
          lobCreator.setClobAsString(ps, 10, md.getCodeWeb());
          ps.setString(11, md.getModel());
          ps.setString(12, md.getBgColor());
          ps.setInt(13, md.getStyle());
         }
      });
  } catch (Exception ex) {
   log.error("save failed", ex);
   ex.printStackTrace();
   return false;
  }
  return true;
 }

 public boolean delete(long id) {
  try {
   super.delete("id", id, ModelModel.tableName);
  } catch (Exception ex) {
   log.error("delete failed", ex);
   ex.printStackTrace();
   return false;
  }
  return true;
 }

 public ModelModel findById(Long id) {
  try {
   String query = "select * from "+ModelModel.tableName +" where id="+ id;
   List list =  super.query(query, new ModelMapper());
   if(list.size()>0){
    return (ModelModel)list.get(0);
   }else return new ModelModel();
  } catch (Exception ex) {
   log.error("findBySql failed", ex);
   ex.printStackTrace();
   return null;
  }
 }

 public List findByProperty(String propertyName, Object value) {
  try {
   String query = "select * from "+ModelModel.tableName +" where "+ propertyName + "='" + value + "'";
   return super.query(query, new ModelMapper());
  } catch (Exception ex) {
   log.error("findByProperty failed", ex);
   ex.printStackTrace();
   return null;
  }
 }

 public boolean attachDirty(ModelModel instance) {
  try {
   if (super.searchById(new ModelModel(), "id", instance.getId(), ModelModel.tableName) == null) {
    save(instance);
   } else {
    String sql = "update "+ModelModel.tableName+" set name=?,type=?,codeHeadLeft=?," +
     "codeHeadRight=?,code=?,codeEdit=?,codeWeb=?,model=?,bgColor=?,style=? where id='"+ instance.getId() +"'";
    final ModelModel md = instance;
    super.update(sql, new PreparedStatementSetter(){
     public void setValues(PreparedStatement ps) throws SQLException {
      ps.setString(1, md.getName());
      ps.setString(2, md.getType());
      ps.setString(3, md.getCodeHeadLeft());
      ps.setString(4, md.getCodeHeadRight());
      lobHandler.getLobCreator().setClobAsString(ps, 5, md.getCode());
      lobHandler.getLobCreator().setClobAsString(ps, 6, md.getCodeEdit());
      lobHandler.getLobCreator().setClobAsString(ps, 7, md.getCodeWeb());
      ps.setString(8, md.getModel());
      ps.setString(9, md.getBgColor());
      ps.setInt(10, md.getStyle());
     }
    });
   }
  } catch (Exception ex) {
   log.error("attachDirty failed", ex);
   ex.printStackTrace();
   return false;
  }
  return true;
 }

 public List findBySql(String queryString) {
  try {
   String query = "select * from "+ModelModel.tableName +" where "+ queryString;
   return super.query(query, new ModelMapper());
  } catch (Exception ex) {
   log.error("findBySql failed", ex);
   ex.printStackTrace();
   return null;
  }
 }
 protected class ModelMapper implements RowMapper {
  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
      ModelModel modelMd = new ModelModel(false);
   modelMd.setId(rs.getLong("ID"));
   modelMd.setUserId(rs.getLong("USERID"));
   modelMd.setSiteId(rs.getLong("SITEID"));
   modelMd.setName(rs.getString("NAME"));
   modelMd.setType(rs.getString("TYPE"));
   modelMd.setCodeHeadLeft(rs.getString("CODEHEADLEFT"));
   modelMd.setCodeHeadRight(rs.getString("CODEHEADRIGHT"));
   modelMd.setCode(lobHandler.getClobAsString(rs, "code"));
   modelMd.setCodeWeb(lobHandler.getClobAsString(rs, "codeWeb"));
   modelMd.setCodeEdit(lobHandler.getClobAsString(rs, "codeEdit"));
   modelMd.setModel(rs.getString("MODEL"));
   modelMd.setBgColor(rs.getString("BGCOLOR"));
   modelMd.setStyle(rs.getInt("STYLE"));
   ModelModel.confirmValues(modelMd);
   return modelMd;
     }
 }
}

 

 

<bean id="nativeJdbcExtractor"
   class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
   lazy-init="true" />
<bean id="lobHandler"
   class="org.springframework.jdbc.support.lob.OracleLobHandler"
   lazy-init="true">
   <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
</bean>