GAT项目新需求：保险管理增加查询统计-传日期参数问题

需求：不分页，默认合计今年的签单保费和车船费

1.CarSecureController

2.查询条件

3.接口

4.接口的实现

5.Dao接口

6.Dao实现

查询条件：generateConditionTotal

private void generateConditionTotal(CarSecureListReqFrist req, HibernateParams hParams) throws Exception{

    StringBuilder sb = new StringBuilder();
    
    Integer querySysId = 0;
    Integer queryDeptId = 0;

    if(Util.isEmpty(req.getAppSysId()) || Util.isEmpty(req.getAppDeptId())){
        //未传值 查询当前登录人的机构部门信息
        UserSysDeptRes userSysDeptRes = Common.getLoginInfo().getUserSysDept();
        querySysId = userSysDeptRes.getAppSysId();
        queryDeptId = userSysDeptRes.getAppDeptId();
    }else{
        //传值 查询输入机构部门信息
        querySysId = req.getAppSysId();
        queryDeptId = req.getAppDeptId();
    }
    // 包含下级
    OrgDeptSearchFilter helper = new OrgDeptSearchFilter();
    sb.append(" and((po.appsysid,po.appdeptid) in(");
    sb.append(helper.getSubDeptSql(querySysId, queryDeptId, hParams));
    sb.append("))");
    
    //车牌号码
    if(!Util.isEmpty(req.getCarHostNO())){
        sb.append(" and t.HOST_NO like ? ");
        hParams.addParamObj(likeValue(req.getCarHostNO()));
    }
    
    //查询范围
    if(Util.isEmpty(req.getQueryQbBeginTime()) && Util.isEmpty(req.getQueryZbEndTime()) &&  Util.isEmpty(req.getQueryZbBeginTime()) && Util.isEmpty(req.getQueryZbEndTime())){
        //什么都不填，默认查询当年的
        sb.append(" and t.SECURE_START_TIME >= (select trunc(sysdate,'yyyy') FROM DUAL) ");
        sb.append(" and t.SECURE_START_TIME <= (select add_months(trunc(sysdate,'yyyy'),12)-1 from dual) ");
    }
    if(!Util.isEmpty(req.getQueryQbBeginTime())){
        sb.append(" and t.SECURE_START_TIME >= ? ");
        hParams.addParamObj(req.getQueryQbBeginTime());
    }
    
    if(!Util.isEmpty(req.getQueryQbEndTime())){
        sb.append(" and t.SECURE_START_TIME <= ? ");
        hParams.addParamObj(req.getQueryQbEndTime());
    }
    
    if(!Util.isEmpty(req.getQueryZbBeginTime())){
        sb.append(" and t.SECURE_END_TIME >= ? ");
        hParams.addParamObj(req.getQueryZbBeginTime());
    }
    
    if(!Util.isEmpty(req.getQueryZbEndTime())){
        sb.append(" and t.SECURE_END_TIME <= ? ");
        hParams.addParamObj(req.getQueryZbEndTime());
    }
    
    hParams.addSqlStrBuffer(sb.toString());
}

测试OK：

SQL语句：

select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.HOST_VIN as hostVin,
       p.ENGINE_ID as engineId,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.SECURE_TAX as secureTax,
       t.SECURE_REMARK as secureRemark
  from CAR_SECURE t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid
   and ((po.appsysid, po.appdeptid) in
       (select appsysid, appdeptid from table(get_subdept(-1, 2000, 0))))
   and t.HOST_NO like '%人a1002%'
   and t.SECURE_START_TIME >= to_date('2017-1-1','yyyy-mm-dd')
   and t.SECURE_START_TIME <= to_date('2017-2-25','yyyy-mm-dd')
   and t.SECURE_END_TIME >= to_date('2017-3-20','yyyy-mm-dd')
   and t.SECURE_END_TIME <= to_date('2017-4-25','yyyy-mm-dd')
 order by t.UPDATE_TIME desc

注意：

使用注解把接收到的日期String类型"queryQbBeginTime":"2017-1-1"转成Date类型

所以sql语句要用to_date转：

select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.HOST_VIN as hostVin,
       p.ENGINE_ID as engineId,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.SECURE_TAX as secureTax,
       t.SECURE_REMARK as secureRemark
  from CAR_SECURE t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid
   and ((po.appsysid, po.appdeptid) in
       (select appsysid, appdeptid from table(get_subdept(-1, 2000, 0))))
   and t.HOST_NO like '%人a1002%'
   and t.SECURE_START_TIME >= to_date('2017-1-1','yyyy-mm-dd')
   and t.SECURE_START_TIME <= to_date('2017-2-25','yyyy-mm-dd')
   and t.SECURE_END_TIME >= to_date('2017-3-20','yyyy-mm-dd')
   and t.SECURE_END_TIME <= to_date('2017-4-25','yyyy-mm-dd')
 order by t.UPDATE_TIME desc

-------------------------------------------------------------------------------------------------------------------
如果用String接收：

则：

-------------------------------------------------------------------------------------------------------------------

如果用String接收：

则：

-------------------------------------------------------------------------------------------------------------------

看到其他模块还有这种写法的：