一：实体类
public class PowerBean implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private Integer ID;//主键id private String AREA_NAME;//区域名称 private String STATION_NAME;//站点名称 private String CP_NUMBER;//充电桩号 private String GUN_NO;//枪号 private String POWER_TYPE;//充电类型 private String VOLTAGE;//输入电压 private String ELECTRIC;//输入电流 private String SPOWER;//输入功率 private String ENERGY;//输入电能 private String CMODEL;//通讯方式 private String REPORT_TIME;//上报时间
二：jsp页面
<div class="pager"> <div class="btn-group"> <button type="button" class="pageBtn " οnclick="fenye(1)"><i class="fa fa-step-backward"></i> </button> <button type="button" class="pageBtn " <c:if test="${pageNow>1}"> οnclick="fenye(${pageNow-1});"</c:if>><i class="fa fa-caret-left"></i> </button> <div class="form-group "> <select name="" class="form-control"> <option value="">${pageNow}</option> </select> </div> <button type="button" class="pageBtn " <c:if test="${pageNow+1<=myPageCount}">οnclick="fenye(${pageNow+1});"</c:if>><i class="fa fa-caret-right"></i> </button> <button type="button" class="pageBtn " οnclick="fenye(${myPageCount})"><i class="fa fa-step-forward"></i> </button> <button type="button" class="pageBtn "><i class="fa fa-repeat" οnclick="refresh();"></i> </button> <span style="color: #00fbff;margin-left: 20px;">共有:${myrows}条记录,当前页面${pageNow}/${myPageCount}页</span> </div> </div> </div>
三：@Controller
@RequestMapping public ModelAndView list(PowerBean powerBean,String pageNow) { ModelAndView mv = new ModelAndView(); Map<String,Object> param=new HashMap<String, Object>(); param.put("area_name",powerBean.getAREA_NAME()); param.put("station_name",powerBean.getSTATION_NAME()); param.put("cp_number",powerBean.getCP_NUMBER()); if(pageNow==null||pageNow.isEmpty()){ pageNow="1"; } param.put("begin",((Integer.valueOf(pageNow)-1)*pageSize)); param.put("end",Integer.valueOf(pageNow)*pageSize); param.put("myrows",OracleTypes.NUMERIC); param.put("myPageCount",OracleTypes.NUMERIC); param.put("pageSize",pageSize); param.put("powers",OracleTypes.CURSOR); param.put("areas",OracleTypes.CURSOR); Map<String,Object>map=monitoringPowerService.findPowerBeans(param); // List<PowerBean> powerBeans=monitoringPowerService.findPowerBeans(param); mv.addObject("powerBean", powerBean); mv.addObject("powerBeans",map.get("powerBeans")); mv.addObject("areas",map.get("areas")); mv.addObject("myPageCount",map.get("myPageCount")); mv.addObject("myrows",map.get("myrows")); mv.addObject("pageNow",pageNow); mv.setViewName("system/monitoringRun/monitoring_power"); return mv; }
四：mybatis.xml
<resultMap type="powerBean" id="powerBeanMap"> <id property="ID" column="ID"/> <result property="AREA_NAME" column="AREA_NAME"/> <result property="STATION_NAME" column="STATION_NAME"/> <result property="CP_NUMBER" column="CP_NUMBER"/> <result property="GUN_NO" column="GUN_NO"/> <result property="POWER_TYPE" column="POWER_TYPE"/> <result property="VOLTAGE" column="VOLTAGE"/> <result property="ELECTRIC" column="ELECTRIC"/> <result property="SPOWER" column="SPOWER"/> <result property="ENERGY" column="ENERGY"/> <result property="CMODEL" column="CMODEL"/> <result property="REPORT_TIME" column="REPORT_TIME"/> </resultMap> <select id="findPowerBeans" parameterType="java.util.Map" statementType="CALLABLE"> <![CDATA[ call fenyPower(#{areas,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=powerBeanMap}, #{powers,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=powerBeanMap}, #{area_name,mode=IN,jdbcType=VARCHAR}, #{station_name,mode=IN,jdbcType=VARCHAR}, #{cp_number,mode=IN,jdbcType=VARCHAR}, #{begin,mode=IN,jdbcType=NUMERIC}, #{end,mode=IN,jdbcType=NUMERIC}, #{myrows,mode=OUT,jdbcType=NUMERIC}, #{myPageCount,mode=OUT,jdbcType=NUMERIC}, #{pageSize,mode=IN,jdbcType=NUMERIC}) ]]> </select>
五：分页存储过程及游标
create or replace procedure fenyPower ( areas out sys_refcursor,--返回areas结果集 powers out sys_refcursor,--返回powers结果集 area_name in varchar2, --查询条件区域 station_name in varchar2, --查询条件站点 cp_number in varchar2,--查询条件充电桩号 v_start in number, --开始显示记录条数 v_end in number, --结束显示记录条数 myrows out number,--总记录数 myPageCount out number,--总页数 pageSize in number --每页显示10条 ) is v_sql varchar2(1000);--SQL语句 v_wherecase varchar2(300):=''; --查询条件 v_n number; --计算myPageCount用到数值赋值 begin if area_name is not null then v_wherecase:=' and area_name like %'||area_name||'%'; end if; if station_name is not null then v_wherecase:=v_wherecase||' and station_name like %'||station_name||'%'; end if; if cp_number is not null then v_wherecase:=v_wherecase||' and cp_number like %'||cp_number||'%'; end if; v_sql:='select * from (select t1.* ,rownum rn from(select * from monitoring_power where 1=1'||v_wherecase||' order by id desc) t1 where rownum<='|| v_end ||') where rn>'|| v_start; --打开游标，让游标指向结果集powers open powers for v_sql; --打开游标，让游标指向结果集areas v_sql:='select distinct area_name from monitoring_power'; open areas for v_sql; --计算myrows和myPageCount --查询共有多少条记录 v_sql:='select count(*) from monitoring_power where 1=1'||v_wherecase; execute immediate v_sql into myrows; --计算myPageCount，统计多少页记录 if mod(myrows,pageSize)=0 then myPageCount:=myrows/pageSize; else v_n:=round(myrows/pageSize); if(v_n>myrows/pageSize) then myPageCount:=v_n; else myPageCount:=round(myrows/pageSize)+1; end if; end if; end;
