ibatis 里查询select语句写法带分页

startDate  endDate  传如为日期类型

 

<select id="queryProceduresTop" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
  <dynamic prepend="">
    <isNotNull prepend="" property="end">
     Select * From (
       </isNotNull>
  </dynamic>
    Select * From (
   SELECT rownum as rowIndex,
       t.requestid,
       t.requcode,
          pkg_bshis_common.F_GET_ADD('dictoffice',t.requoffice,'officename') as sqdw,
          pkg_bshis_common.F_GET_ADD('dictoper',t.inputor,'opername') as sqr,
          t.requdate as sqrq,
          case t.retu when 1 then '已汇总' else '未汇总' end as hzzt,
          pkg_bshis_common.F_GET_ADD('dictoper',t.retuoperid,'opername') as hzr,
          t.retudate as hzrq
    from request_total t
   where t.forgid = #FORGID#
     and t.frcode = #FRCODE#
   <![CDATA[and substr(t.requcode,1,1) <> 'F' ]]>
     and t.stage = 1
     <dynamic prepend="">
    <isNotNull prepend="AND" property="REQUCODE">
     t.requcode = #REQUCODE#  
       </isNotNull>
    <isNotNull prepend="AND" property="RETU">
     t.retu = #RETU#  
       </isNotNull>
       <isNotNull prepend="AND" property="startDate">
     <![CDATA[ t.requdate >= #startDate# ]]>
          </isNotNull>
    <isNotNull prepend="AND" property="endDate">
     <![CDATA[ t.requdate <= #endDate# ]]>
    </isNotNull>
      </dynamic> 
      )
  <dynamic prepend="WHERE">
   <isNotNull prepend="AND" property="end">
    <![CDATA[ rowIndex<=#end#) where rowIndex>=#start# ]]>    
     </isNotNull>
   </dynamic> 
 </select>
 
 <select id="queryProcedureTopCount" parameterClass="java.util.HashMap" resultClass="int">
  SELECT count(*)
        from request_total t
  where t.forgid = #FORGID#
    and t.frcode = #FRCODE#
  <![CDATA[and substr(t.requcode,1,1) <> 'F' ]]>
    and t.stage = 1
    <dynamic prepend="">
   <isNotNull prepend="AND" property="REQUCODE">
    t.requcode = #REQUCODE#  
      </isNotNull>
   <isNotNull prepend="AND" property="RETU">
    t.retu = #RETU#  
      </isNotNull>
    <isNotNull prepend="AND" property="startDate">
    <![CDATA[ t.requdate >= #startDate# ]]>
         </isNotNull>
   <isNotNull prepend="AND" property="endDate">
    <![CDATA[ t.requdate <= #endDate# ]]>
   </isNotNull>
     </dynamic> 
 </select>

 

前台方法

 

 

 public ModelAndView queryProcedures(HttpServletRequest request, HttpServletResponse response) throws Exception{
    ModelAndView modelAndView = new ModelAndView("his/medicalManagement/ProcedureStatistic/queryProcedures");
    try {
   String isUpper = request.getParameter("ISUPPER");//是否上级
   String forgid = request.getParameter("forgid");
   String frcode = request.getParameter("frcode");
   String organizeName = request.getParameter("organizeName");
   if(frcode.length() > 6)//截取县区编码
   {
     frcode = frcode.substring(0, 6);
   }
   if(!PageMonitor.storeOperCheck(request, modelAndView, getApplicationContext(), GlobalConstant.OFFICE_TYPE_YK)){
     return modelAndView; 
   }
  
  HashMap hashMap = new HashMap();
  
  //用做分页
  int pageIndex = request.getParameter("pageIndex") == null?1:Integer.parseInt(request.getParameter("pageIndex"));
  int pageSize = request.getParameter("pageSize") == null?7:Integer.parseInt(request.getParameter("pageSize"));
  int start = (pageIndex - 1)*pageSize+1;
  int end = pageIndex * pageSize;
  hashMap.put("start",new Integer(start));
  hashMap.put("end",new Integer(end));
  
  //获取当月的第一天和最后一天
  Date date = getCommonImpl().getDBDate();
  Date startDate = Convert.getFirstDayByMonth(date);
  Date endDate = Convert.getLastDayByMonth(date);
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
     String firstDateStr = sdf.format(startDate).concat(" 00:00:00");
     startDate = Convert.toParseDate(firstDateStr);
     String lastDateStr = sdf.format(endDate).concat(" 23:59:59");
     endDate = Convert.toParseDate(lastDateStr);
   //县编码
   hashMap.put("FRCODE", frcode);
   //医院编码
   hashMap.put("FORGID", forgid);
   hashMap.put("startDate", startDate);
   hashMap.put("endDate", endDate);
   if(isUpper==null){
   hashMap.put("RETU", 0);
   }
   int totalRows = getProcedureStatisticImpl().queryProcedureTopCount(hashMap);
   List list = getProcedureStatisticImpl().queryProceduresTop(hashMap);

   modelAndView.addObject("list", list);
   modelAndView.addObject("totalRows", totalRows);
   modelAndView.addObject("start", start);
   modelAndView.addObject("end", end);
   modelAndView.addObject("frcode", frcode);
   modelAndView.addObject("forgid", forgid);
   modelAndView.addObject("organizeName", organizeName);

   modelAndView.addObject("startDate", startDate);
   modelAndView.addObject("endDate", endDate);
   modelAndView.addObject("ISUPPER", isUpper);
   modelAndView.addObject("retu", 0);
  } catch (Exception e) {
   //e.printStackTrace();
   AjaxException.MyException(response, e);
  }
   return modelAndView;
  }