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接收：

则：

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

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