jeecg-boot中分页接口用自定义sql和list实现
1、controller中
@ApiOperation(value="分析仪工作状态和报警-3列-分页", notes="分析仪工作状态和报警状态-分页") @ApiImplicitParams({ @ApiImplicitParam(name = "portId", value = "监测点id", required = true, dataType = "String",paramType = "query",defaultValue="130500000193-10"), @ApiImplicitParam(name = "polId", value = "监测因子id", required = true, dataType = "String",paramType = "query",defaultValue="a21002"), @ApiImplicitParam(name = "startTime", value = "开始时间", required = true, dataType = "String",paramType = "query",defaultValue ="2021-11-29 00:00:00" ), @ApiImplicitParam(name = "endTime", value = "结束时间", required = true, dataType = "String",paramType = "query",defaultValue="2021-11-30 00:00:00"), @ApiImplicitParam(name = "portType", value = "监测点类型1废水2废气dgi数采仪", required = true, dataType = "String",paramType = "query",defaultValue="2") }) @GetMapping(value = "/getDeviceStatusWarn") public Result<?> getDeviceStatusWarn(String portId,String polId,String startTime,String endTime,String portType, @RequestParam(name="pageNo", defaultValue="1") Integer pageNo, @RequestParam(name="pageSize", defaultValue="10") Integer pageSize, HttpServletRequest req) { Page<Map<String,Object>> page = new Page<Map<String,Object>>(pageNo, pageSize); IPage<Map<String,Object>> pagelist = wwDeviceChangeparamService.getDeviceStatusWarn(page,portId,polId,startTime,endTime,portType); return Result.ok(pagelist); }
2、interface中
IPage<Map<String,Object>> getDeviceStatusWarn(Page<Map<String,Object>> page, String portId, String polId, String startTime, String endTime, String portType);
3、实现类中
@Override public IPage<Map<String,Object>> getDeviceStatusWarn(Page<Map<String,Object>> page, String portId, String polId, String startTime, String endTime, String portType) { Date startTime1= DateUtil.strToDate(startTime,"yyyy-MM-dd HH:mm:ss"); Date endTime1= DateUtil.strToDate(endTime,"yyyy-MM-dd HH:mm:ss"); if(portType.equals("2")) { return wwDeviceChangeparamMapper.getDeviceStatusWarn_gas(page,portId,polId,startTime1,endTime1); } else if(portType.equals("1")) { return wwDeviceChangeparamMapper.getDeviceStatusWarn_water(page,portId,polId,startTime1,endTime1); } else { return wwDeviceChangeparamMapper.getDeviceStatusWarn_dgi(page,portId,polId,startTime1,endTime1); } }
4、mapper中
IPage<Map<String,Object>> getDeviceStatusWarn_dgi(Page<Map<String,Object>> page, String portId, String polId, Date startTime, Date endTime);
5、xml中
<select id="getDeviceStatusWarn_dgi" resultType="java.util.Map"> select to_char(p.datatime,'YYYY-MM-DD HH24:MI:SS') datatime, String_agg(CASE state_id WHEN'i22001' THEN state_info ELSE '' END,',') as workstatus, String_agg(CASE WHEN state_id='i22004' and pol_id=#{polId} THEN state_info ELSE '' END,',') as warnstatus, String_agg(CASE state_id WHEN'i22005' THEN state_info ELSE '' END,',') as warninfo from ww_device_param p , v_ps_port port where p.mn=port.dgi_mn and p.dt='1' and port.port_id=#{portId} and p.datatime>=#{startTime} and p.datatime<=#{endTime} group by p.datatime order by p.datatime </select>