功能:根据时间段,查询相关数据
一、在java中,拼sql语句
String sql=" SELECT c.c_no, c.car_number, c.c_shelf_number, c.is_urgent, c.business_type, c.is_need_inspect_destory, "
+ " c.is_need_operating_approve, c.is_need_union_approve, c.certificate_approved_status, c.approved_status, c.status, c.input_create_date "
+ " FROM c_info c INNER JOIN c_deliver_info d ON c.c_info_id = d.c_info_id WHERE 1=1 ";
//添加日期范围筛选
SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd");
if(StringUtil.isNotBlank(start_deliver_date)) {
Date startDate = sdFormat.parse(start_deliver_date);
paramsMap.put("StartDate", startDate);
sql += " and d.deliver_date >=:StartDate";
}
if(StringUtil.isNotBlank(end_deliver_date)) {
Date endDate = sdFormat.parse(end_deliver_date);
long timeMills = endDate.getTime();
endDate.setTime(timeMills + 24 * 60 * 60 * 1000);//在原有日期上加上一天
paramsMap.put("EndDate", endDate);
sql += " and d.deliver_date < :EndDate";
}
(注:sql是查询的最终数据值。。。。
paramsMap
:传入的参数)
二、查询数据的总条数
String countSql=" SELECT count(*) FROM c_info c INNER JOIN c_deliver_info d ON c.c_info_id = d.c_info_id WHERE 1=1 ";
if(StringUtil.isNotBlank(start_deliver_date)) {
countSql += " and d.deliver_date >=:StartDate";
}
if(StringUtil.isNotBlank(end_deliver_date)) {
countSql += " and d.deliver_date < :EndDate";
}
// 获取根据条件分页查询的总行数
long rowCount = carInfoDao.countBySql(countSql, paramsMap).longValue();
page.setTotalSize((int) rowCount);
(注:page是传过来参数,Page<CarInfo> page
)
三、开始设置分页
//分页
int pageNo = page.getCurrentPage();
int pageSize = page.getPageSize();
int totalPage = (int) Math.ceil(((double) rowCount / pageSize));
page.setTotalPage(totalPage);
//用sql分页
sql += " limit " + (pageNo - 1) * pageSize + "," + pageSize; //从第几页的第几个数据,到第几条数据
list = carInfoDao.findEntityBySQLQuery(sql, paramsMap).setResultTransformer(Transformers.aliasToBean(CarInfo.class)).list();
(注:
pageNo:表示当前页(默认为1)
pageSize:表示每页有几条(一页显示15条数据)
rowCount:表示数据的总条数(最终是totalSize)
totalPage:表示一共有多少页
limit:是sql的函数,参数表示从第几页的第一条数据到第15条数据
)