4.30工作项目笔记

项目需求&＃xff1a;

后面根据登录session的行业id来显示对应的心跳统计数据

就是加where子查询啦
页面实现&＃xff1a;

最后效果&＃xff1a;

数据库

-------心跳统计-相关sql----------
–查询全部
SELECT dht_id, dht01, dht02, dht03, dht04, dht05, dht06, dht07 FROM dtm_heart_tongji;

–分页查询总条数
SELECT count(1) from dtm_heart_tongji where 1&＃61;1

–oracle查询时间段
Select * from dtm_heart_tongji where dht05 between to_date(‘2020-3-10’, ‘yyyy-mm-dd’) and to_date(‘2020-4-15’,‘yyyy-mm-dd’)

模糊查询以及分页列表

service

controller

**注意&＃xff1a;**分页的这里总页数前台默认页数给了1
我们思考下为什么&＃xff1f;

service发现这里查询条数的时候做了处理

strTable &＃61; strTable &＃43; "" &＃43; formatDate(new Date((data.listDtmHeartTongJiFind[i].dht05).replace(/-/g, "/"))) &＃43; "";

发生的问题

涉及到的demo

jsp


<%&＃64; page language&＃61;"java" pageEncoding&＃61;"UTF-8"%>
<%&＃64;taglib prefix&＃61;"c" uri&＃61;"http://java.sun.com/jsp/jstl/core"%>
<%&＃64; taglib prefix&＃61;"fn" uri&＃61;"http://java.sun.com/jsp/jstl/functions"%>













TongJiJsonService

package svs.service;import java.util.HashMap;
import java.util.List;
import java.util.Map;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;import org.springframework.stereotype.Service;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;import svs.dao.ISearchDao;
import svs.dao.IUserSearchDao;
import svs.dto.DtmHeartTongji;
import svs.dto.userDto.UserDtmHeartTongji;
import svs.dto.userDto.UserDtmNeiBaoUser;/*** 心跳统计* * &＃64;author aolanjie01**/
&＃64;Service("TongJiJsonService")
public class TongJiJsonService {&＃64;Resourceprivate IUserSearchDao objUserSearchDao;&＃64;Resourceprivate ISearchDao objSearchDao;&＃64;Resourceprivate CommonJsonService objCommonJsonService;/*** 心跳统计 分页查询list* * &＃64;param startDate 开始时间* &＃64;param endDate 结束时间* &＃64;param intPageNum 页数* &＃64;param intCpage 当前页* &＃64;return*/public List selectUserDtmHeartTongjiInfoSearch(String dht05Start, String dht05End,int intPageNum, int intCpage, String dnu08) {try {String strSql &＃61; SqlConstant.sqlUserHeartTongJiSearch;// 行业IDif (dnu08 !&＃61; null && "".equals(dnu08) &＃61;&＃61; false) {// 用户角色idstrSql &＃61; strSql &＃43; " and dht01&＃61;" &＃43; dnu08 &＃43; "";}// 开始时间if (dht05Start !&＃61; null && "".equals(dht05Start) &＃61;&＃61; false) {strSql &＃61; strSql &＃43; " and dht05 >&＃61;&＃39;" &＃43; dht05Start &＃43; " 00:00:00&＃39;";}// 结束时间if (dht05End !&＃61; null && "".equals(dht05End) &＃61;&＃61; false) {strSql &＃61; strSql &＃43; " and dht05 <&＃61;&＃39;" &＃43; dht05End &＃43; " 23:59:59&＃39;";}strSql &＃61; strSql &＃43; " limit " &＃43; intPageNum &＃43; " offset " &＃43; (intCpage - 1) * intPageNum;return objUserSearchDao.selectUserDtmHeartTongjiSearch(strSql);} catch (Exception e) {// TODO: handle exceptionreturn null;}}/*** 心跳统计 分页 查询条数* * &＃64;param startDate 开始时间* &＃64;param endDate 结束时间* &＃64;param totalNum* &＃64;return*/public int HeartTongjiSearchCount(String dht05Start, String dht05End, String totalNum, String dnu08) {try {int intTotalNum &＃61; Integer.valueOf(totalNum);if (intTotalNum > 0) {return intTotalNum;} else {String strSql &＃61; "SELECT count(1) from dtm_heart_tongji where 1&＃61;1";// 开始时间if (dnu08 !&＃61; null && "".equals(dnu08) &＃61;&＃61; false) {if (dht05Start !&＃61; null && "".equals(dht05Start) &＃61;&＃61; false && dht05End !&＃61; null&& "".equals(dht05End) &＃61;&＃61; false) {strSql &＃61; strSql &＃43; " and dht05 between to_date(&＃39;" &＃43; dht05Start &＃43; "&＃39;, &＃39;yyyy-mm-dd&＃39;) and to_date(&＃39;"&＃43; dht05End &＃43; "&＃39;,&＃39;yyyy-mm-dd&＃39;) and dht01&＃61;" &＃43; dnu08 &＃43; "";}}return objSearchDao.selectCountList(strSql);}} catch (Exception e) {// TODO: handle exceptionreturn -1;}}
}

TongJiController&＃xff1a;做页面跳转

package svs.controller;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;import svs.service.CommonJsonService;&＃64;Controller
public class TongJiController {&＃64;Resourceprivate CommonJsonService objCommonJsonService;/*** 心跳统计初始化* * &＃64;return*/&＃64;RequestMapping("/heartTongjiIndex")public String heartTongjiIndex(HttpSession session,HttpServletRequest request,Model model){ try {//判断Session是否失效if (!objCommonJsonService.SystemBool(session)) {request.setAttribute("strErrorMsg", "Session失效,请重新登录!");return "communitySessionError";}return "tongji/heartTongji";} catch (Exception e) {e.printStackTrace();return "communitySessionError";}}/*** 事件统计初始化* * &＃64;return*/&＃64;RequestMapping("/eventTongjiIndex")public String eventTongjiIndex(HttpSession session,HttpServletRequest request,Model model){ try {//判断Session是否失效if (!objCommonJsonService.SystemBool(session)) {request.setAttribute("strErrorMsg", "Session失效,请重新登录!");return "communitySessionError";}return "tongji/eventTongji";} catch (Exception e) {e.printStackTrace();return "communitySessionError";}}
}

TongJiJsonController&＃xff1a;主要业务处理逻辑

package svs.controller;import java.util.HashMap;
import java.util.List;
import java.util.Map;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;import svs.dao.ISearchDao;
import svs.dto.DtmHeartTongji;
import svs.dto.DtmSvssystemLog;
import svs.dto.userDto.UserDtmHeartTongji;
import svs.service.CommonJsonService;
import svs.service.TongJiJsonService;&＃64;Controller
public class TongJiJsonController {&＃64;Resourceprivate ISearchDao objSearchDao;&＃64;Resourceprivate CommonJsonService objCommonJsonService;&＃64;Resourceprivate TongJiJsonService objTongjiJsonService;// 每页数量private int intPageNum;// 总记录数private int intAllCount;// 当前页private int intCpage;private String jsonMsg;private int jsonFlag;/*** 心跳统计查询* * &＃64;param session* &＃64;param request* &＃64;param model* &＃64;return*/&＃64;RequestMapping("/heartTongJiIndexJsonAciton")&＃64;ResponseBodypublic Map heartTongJiIndexJsonAciton(HttpSession session, HttpServletRequest request) {Map mpList &＃61; new HashMap();// 判断Session是否失效if (!objCommonJsonService.SystemBool(session)) {mpList.put("jsonFlag", -2);mpList.put("jsonMsg", "Session失效,请重新登录!");return mpList;}try {// 创建应用系统日志对象,并且已经填写好了一些通用参数// DtmSvssystemLog objDtmSvssystemLog &＃61;// objCommonJsonService.createNewDtmSvssystemLog(session, request);// 用户角色String dnu08 &＃61; session.getAttribute("dnu08").toString();// 开始时间String dht05Start &＃61; request.getParameter("dht05Start");// 结束时间String dht05End &＃61; request.getParameter("dht05End");// 当前页intCpage &＃61; Integer.valueOf(request.getParameter("Cpage"));// 总数据量String totalNum &＃61; request.getParameter("totalNum");List listUserDtmHeartTongjiInfoSearch &＃61; objTongjiJsonService.selectUserDtmHeartTongjiInfoSearch(dht05Start, dht05End, intPageNum, intCpage,dnu08);// 心跳数据条数intAllCount &＃61; objTongjiJsonService.HeartTongjiSearchCount(dht05Start, dht05End, totalNum,dnu08);// 每页数据量intPageNum &＃61; objCommonJsonService.PAGE_COUNT;mpList.put("listDtmHeartTongJiFind", listUserDtmHeartTongjiInfoSearch);mpList.put("intAllCount", intAllCount);mpList.put("intPageNum", intPageNum);mpList.put("jsonFlag", 1);mpList.put("strMsg", "成功");return mpList;} catch (Exception e) {e.printStackTrace();mpList.put("jsonFlag", -1);return mpList;}}}

package svs.dto.userDto;import java.util.Date;public class UserDtmHeartTongji {/*** This field was generated by MyBatis Generator. This field corresponds to the database column public.dtm_heart_tongji.dht_id* &＃64;mbg.generated Wed Apr 29 15:10:03 CST 2020*/private Integer dhtId;/*** This field was generated by MyBatis Generator. This field corresponds to the database column public.dtm_heart_tongji.dht02* &＃64;mbg.generated Wed Apr 29 15:10:03 CST 2020*/private Integer dht02;/*** This field was generated by MyBatis Generator. This field corresponds to the database column public.dtm_heart_tongji.dht03* &＃64;mbg.generated Wed Apr 29 15:10:03 CST 2020*/private Integer dht03;/*** This field was generated by MyBatis Generator. This field corresponds to the database column public.dtm_heart_tongji.dht04* &＃64;mbg.generated Wed Apr 29 15:10:03 CST 2020*/private Integer dht04;/*** This field was generated by MyBatis Generator. This field corresponds to the database column public.dtm_heart_tongji.dht05* &＃64;mbg.generated Wed Apr 29 15:10:03 CST 2020*/private String dht05;public Integer getDhtId() {return dhtId;}public void setDhtId(Integer dhtId) {this.dhtId &＃61; dhtId;}public Integer getDht02() {return dht02;}public void setDht02(Integer dht02) {this.dht02 &＃61; dht02;}public Integer getDht03() {return dht03;}public void setDht03(Integer dht03) {this.dht03 &＃61; dht03;}public Integer getDht04() {return dht04;}public void setDht04(Integer dht04) {this.dht04 &＃61; dht04;}public String getDht05() {return dht05;}public void setDht05(String dht05) {this.dht05 &＃61; dht05;}
}


package svs.dao;import java.util.List;import org.apache.ibatis.annotations.Param;import svs.dto.userDto.*;public interface IUserSearchDao {//派出所权限public List selectUserDtmNeibaouserPolice(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//菜单权限public List selectUserDtmSystemMenu(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//内保用户权限public List selectUserDtmNeiBaoUser(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//单位信息public List selectUserDtmUsecompanyInfo(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//派出所public List selectUserDictionaryPoliceStation(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//用户列表public List selectUserDtmNeiBaoUserSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//单位信息public List selectUserDtmUsecompanyInfoSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//小区信息public List selectUserDtmCommunity(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//实有装备信息public List selectUserDtmCommunityEquipment(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//实有力量信息public List selectUserDtmCommunityPower(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//人员信息列表public List selectUserDtmPersonInfoSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//人员信息public List selectUserDtmPersonInfo(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//人员标签信息public List selectUserDtmPersonLabelSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//区域出入口信息表public List selectUserDtmCommunityEntrance(&＃64;Param(value&＃61;"value") String strSql) throws Exception;//系统输入信息表&＃xff08;设备信息&＃xff09;public List selectUserAgentSystemEnterSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception; //用户心跳信息表public List selectUserDtmHeartTongjiSearch(&＃64;Param(value&＃61;"value") String strSql) throws Exception; }

1.js时间戳的问题
后台的实体类的用string来接收就无需转换
2.时间范围的问题
我的这种写法就有问题

举例&＃xff1a;假如我可能用户只想按开始时间&＃xff08;结束时间&＃xff09;搜索&＃xff0c;那么我这个逻辑&＃xff0c;啥数据就没有

不&＃xff1a;其实它是在查询全部

这是一个bug

注意&＃xff1a;前台的数据得是动态的

解决办法

提高逻辑思维

这种写法灵活且通用

// 行业IDif (dnu08 !&＃61; null && "".equals(dnu08) &＃61;&＃61; false) {// 用户角色idstrSql &＃61; strSql &＃43; " and dht01&＃61;" &＃43; dnu08 &＃43; "";}// 开始时间if (dht05Start !&＃61; null && "".equals(dht05Start) &＃61;&＃61; false) {strSql &＃61; strSql &＃43; " and dht05 >&＃61;&＃39;" &＃43; dht05Start &＃43; " 00:00:00&＃39;";}// 结束时间if (dht05End !&＃61; null && "".equals(dht05End) &＃61;&＃61; false) {strSql &＃61; strSql &＃43; " and dht05 <&＃61;&＃39;" &＃43; dht05End &＃43; " 23:59:59&＃39;";}