需求:不分页,默认合计今年的签单保费和车船费
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接收:
则:
-------------------------------------------------------------------------------------------------------------------
看到其他模块还有这种写法的: