getSimpleJdbcTemplate的全面用法

package com.ztev.fipmis.baseinfo;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.ztev.fipmis.DefaultJdbcBaseDao;
import com.ztev.fipmis.StringUtils;
import com.ztev.pagination.Page;
import com.ztev.pagination.PageInfo;
import com.ztev.pagination.PageParameter;

/**
 * 灌区数据访问。
 * @author 朱志国
 * @modified 朱志国
 */
@Repository
public class PrnmsrDao extends DefaultJdbcBaseDao{

 /**
  * 查询灌区信息并分页显示。
  * @param pp 分页参数。
  * @param prnmsrBean 查询条件。
  * @return 当前页的数据。
  */
 public PageInfo findPage(PageParameter pp, PrnmsrBean prnmsrBean, String adcdCodes)
 {
  //拼接SQL语句
  StringBuffer sb = new StringBuffer();
  sb.append("select a.ennmcd, a.ennm, b.dsc, a.irType, a.sRiver, a.irScope, a.adunnm, a.irArea " +
    "from TB0001_prnmsr a left join adcd_b b on a.adcd = b.adcd where 1=1 ");
        if(prnmsrBean!=null){
         if(StringUtils.isNotBlank(prnmsrBean.getEnnmcd())){
          sb.append(" and a.ennmcd like '"+prnmsrBean.getEnnmcd()+"%'");
         }
         if(StringUtils.isNotBlank(prnmsrBean.getEnnm())){
          sb.append("and a.ennm like '"+prnmsrBean.getEnnm()+"'");
         }
         if(StringUtils.isNotBlank(adcdCodes)){
          sb.append("and a.adcd in(").append(adcdCodes).append(")");
         }
        }
  
  Page page = getPagination();
  page.setPageParameter(pp);
  page.setDataFromSql(sb.toString());
  RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
  return page.execute(rm);
 }// end findPage
 
 /**
  * 统计当前行政区划下灌区信息
  * @param irCurrent
  * @param adcdCodes
  * @return
  */
 public List<Map<String, Object>> statAll(String... adcdCodes){
  //拼接SQL语句
  StringBuffer sql = new StringBuffer();

  String adcdSql = "";

  //所在行政区域
  if(adcdCodes != null && adcdCodes.length != 0){
   String codesStr = StringUtils.join(adcdCodes,",");
   if(StringUtils.isNotBlank(codesStr)){
    adcdSql = " and B.adcd in ("+codesStr+")";
   }
  }

  sql.append("select * from (");
  //全统计 
  sql.append(" select '0' as irArea,count(*) as rows ");
  sql.append(" from TB0001_prnmsr as A,adcd_b as B where  A.adcd = B.adcd");
  sql.append(adcdSql);
  sql.append(" union ");
  
  //规模分类统计
  sql.append(" select isnull(A.irArea,'5') as irArea,count(*) as rows ");
  sql.append(" from TB0001_prnmsr as A,adcd_b as B where  A.adcd = B.adcd");
  sql.append(adcdSql);
  sql.append(" group by A.irArea");
  sql.append(" ) as C order by C.irArea");

  List<Map<String, Object>> stats = getSimpleJdbcTemplate().queryForList(sql.toString());
  //全统计行数为0,则清空结果集
  if(stats.size() == 1 && (Integer)stats.get(0).get("rows") == 0)stats.clear();
  return stats;
 }
 
 /**
  * 插入一个灌区信息。
  * @param prnmsrBean 灌区信息。
  * @return 灌区的唯一标识。
  */
 public Integer insert(PrnmsrBean prnmsrBean) {
  SimpleJdbcInsert sji = newSimpleJdbcInsert("TB0001_prnmsr");
  int num = sji.execute(new BeanPropertySqlParameterSource( prnmsrBean ));
  return num;
  
 }//end insert
 
 /**
  * 判断是否存在指定灌区。
  * @param ennmcd 灌区标识。
  * @return 如果存在返回true，否则返回false。
  */
 public boolean exist(String ennmcd) {
  String sql = "select count(ennmcd) from TB0001_prnmsr where ennmcd = ?";
  return getSimpleJdbcTemplate().queryForInt(sql, ennmcd) > 0;
 }
 
 /**
  * 根据灌区标识查找一个灌区的信息。
  * @param ennmcd 灌区唯一标识。
  * @return 灌区信息。
  */
 public PrnmsrBean find(String ennmcd) {
  String sql = "select * from TB0001_prnmsr where ennmcd = ? ";
  RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
  return getSimpleJdbcTemplate().queryForObject(sql, rm, ennmcd);
  
 }//end find
 
 /**
  * 根据灌区标识查找一个灌区的信息。
  * @param ennmcd 灌区唯一标识。
  * @return 灌区信息。
  */
 public MapView findMapView(String ennmcd) {
  MapView map=new MapView();
  String sql = "select top 1 a.ennm as irName,a.irType,(select tPopulation from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as tPopulation,(select effectArea from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as effectArea,(select designArea from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as designArea,(select layYear from tb_irbuild_layout c where c.ennmcd=a.ennmcd) as layYear,(select layMoney from tb_irbuild_layout c where c.ennmcd=a.ennmcd) as layMoney,(select  top 1   source from tb_capital_b b,tb_capital_use c  where b.serialNo =c.serialNo and c.pId=(select  pId from tb_project_b where  ennmcd=a.ennmcd)) as layMoneyFrom from TB0001_prnmsr a where ennmcd =? ";
  RowMapper<MapView> rm = new BeanPropertyRowMapper<MapView>(MapView.class);
  map=getSimpleJdbcTemplate().queryForObject(sql, rm, ennmcd);
  sql="select pId from tb_project_b where ennmcd=?";
  RowMapper<ProjectBean> rm1= new BeanPropertyRowMapper<ProjectBean>(ProjectBean.class);
  List<ProjectBean> list=getSimpleJdbcTemplate().query(sql,rm1,ennmcd);
  for(ProjectBean bean:list){
   map.getMutimedia().add(bean.getpId());
  }
  return map;
  
 }//end find
 
 /**
  * 更新灌区信息。
  * @param prnmsrBean 灌区信息。
  * @return 更新的行数。
  */
 public int update(PrnmsrBean prnmsrBean)
 {
  String sql = "update TB0001_prnmsr set ennmcd=:ennmcd,ennm=:ennm, "
   +"adcd=:adcd,irType=:irType,irArea=:irArea,sRiver=:sRiver,irScope=:irScope,adunnm=:adunnm where ennmcd=:ennmcd";
  BeanPropertySqlParameterSource bpsq = new BeanPropertySqlParameterSource(prnmsrBean);
  return getSimpleJdbcTemplate().update(sql, bpsq);
 }
 
 /**
  * 删除指定的灌区信息。
  * @param ennmcd 灌区唯一标识。
  * @return 删除的行数。
  */
 public int delete(String ennmcd) {
  String sql = "delete from TB0001_prnmsr where ennmcd=? ";
  return getSimpleJdbcTemplate().update(sql, ennmcd);
  
 }//end delete
 
 /**
  * 得到所有的灌区信息。
  * @return 所有的灌区信息。
  */
 public List<PrnmsrBean> findAll() {
  String sql = "select * from TB0001_prnmsr order by ennmcd ";
  RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
  return getSimpleJdbcTemplate().query(sql, rm);
 }
 
 /**
  * 根据传入的参数查询灌区信息。
  * @return 所查询到的灌区信息。
  */
 public List<PrnmsrBean> findAll(List<AdcdBean> filterAdcd)
 {
  String adcdStr = "";
  String sql = "";
  if(filterAdcd != null && filterAdcd.size() > 0)
  {
   for(int i = 0; i < filterAdcd.size(); i++)
   {
    AdcdBean adcdBean = new AdcdBean();
    adcdBean = (AdcdBean)filterAdcd.get(i);
    if(i == 0)
    {
     adcdStr = adcdBean.getAdcd();
    }
    else
    {
     adcdStr += ", " + adcdBean.getAdcd();
    }
   }
  }
  //System.out.println(adcdStr);
  if(adcdStr == null || adcdStr.equals("") || adcdStr.length() <= 0)
  {
   sql = "select * from TB0001_prnmsr order by ennmcd ";
  }
  else
  {
   sql = "select * from TB0001_prnmsr where adcd in ("+adcdStr+") order by ennmcd ";
  }
  RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
  return getSimpleJdbcTemplate().query(sql, rm);
 }

 public List<PrnmsrBean> findAll(String adcd)
  {
      //System.out.println(adcd);
   String sql = "";
   if(adcd != null && adcd.length() > 0)
   {
    if(!adcd.substring(4, 6).equals("00"))
    {
     sql = "select * from TB0001_prnmsr where adcd = '" + adcd + "' order by ennmcd ";
    }
    else
    {
     if(!adcd.substring(2, 4).equals("00"))
     {
      sql = "select * from TB0001_prnmsr where adcd like '" + adcd.subSequence(0, 4) + "%' order by ennmcd ";
     }
     else
     {
      sql = "select * from TB0001_prnmsr where adcd like '" + adcd.subSequence(0, 2) + "%' order by ennmcd ";
     }
    }
   }
   else
   {
    sql = "select * from TB0001_prnmsr order by ennmcd ";
   }
   //System.out.println(adcdStr);
   RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
   return getSimpleJdbcTemplate().query(sql, rm);
  }
 
 
 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
        PrnmsrDao prnmsrDao = new PrnmsrDao();
        List<PrnmsrBean> result = prnmsrDao.findAll("361027");
        System.out.println(result.size());
 }

}