根据所传条件在SQL中添加时间范围:
sql.append("and (stationDateB) >= (\'" + detailreportpojo.getStartdate() + "\')
and (stationDateE) <= (\'" + detailreportpojo.getEnddate() + "\') ");
需要两个时间字段都要有索引才得以快速查询,数据量大的情况下并不是好办法,一方面是表结构大,但是建立索引最好不要超过一定数量,所以尽量不要将索引浪费在多余的字段上面,可以使用以下优化办法:
SQL改为:
sql.append(GetIDRangeCondition(detailreportpojo));
/**
* id范围查询条件
* @param detailreportpojo
* @return
*/
private String GetIDRangeCondition(DetailreportPojo detailreportpojo)
{
String rangeIDSql = "select min(id),max(id) from dh_busoperatrecord where stationDateB >= '"
+ detailreportpojo.getStartdate() + "' and stationDateB < '" + detailreportpojo.getEnddate() + "'";
List rangeIDResult = DhRdispatchroadcheck.findBySql(rangeIDSql);
if(rangeIDResult.size() > 0)
{
Object[] objects = (Object[]) rangeIDResult.get(0);
BigInteger minID = (BigInteger)objects[0];
BigInteger maxID = (BigInteger)objects[1];
return " AND id between " + minID + " and " + maxID + " ";
}
return "";
}
这样根据日期,仅返回一个id的范围,主键本身就带有索引,一来使用已存在的索引,二来节省索引的额外开支。