主要运用over函数让每行记录都包括总数这个值
public List<Map> getDevicesByPage(List indexcodes,boolean indexCode,String beginTime,String endTime,int allData,int pageNo,int pageSize) {
String innerDeviceSql = "SELECT '' as c_device_index_code,r.I_RES_TYPE,r.C_INDEX_CODE AS device_id,k.C_INDEX_CODE AS site_id,t.C_IOT_ID,r.I_IS_ONLINE,r.C_NAME,r.I_STATUS FROM "
+ "UOM_ORGANIZATION u,RESOURCE_DEVICE_INFO r,IOT_PUSH_CONFIG t,RESOURCE_KEYSITE k "
+ "WHERE k.C_INDEX_CODE=t.C_INDEX_CODE AND r.I_KEY_PATH_ID = u.I_ID AND k.I_FICTITIOUS_ORG_ID=u.I_ID and t.i_status=0";
String innerCameraSql = "SELECT r.C_DEVICE_INDEX_CODE as c_device_index_code,r.I_RES_TYPE,r.C_INDEX_CODE AS device_id,k.C_INDEX_CODE AS site_id,t.C_IOT_ID,r.I_IS_ONLINE,r.C_NAME,r.I_STATUS "
+ "FROM UOM_ORGANIZATION u,RESOURCE_CAMERA_INFO r,IOT_PUSH_CONFIG t,RESOURCE_KEYSITE k "
+ "WHERE k.C_INDEX_CODE=t.C_INDEX_CODE AND r.I_KEY_PATH_ID = u.I_ID AND k.I_FICTITIOUS_ORG_ID=u.I_ID and t.i_status=0";
List<Object> devParams = new ArrayList<Object>();
List<Object> camParams = new ArrayList<Object>();
List<Object> params = new ArrayList<Object>();
StringBuilder deviceSql = new StringBuilder();
StringBuilder cameraSql = new StringBuilder();
deviceSql.append(innerDeviceSql);
cameraSql.append(innerCameraSql);
if(indexCode){
deviceSql.append(" AND t.C_INDEX_CODE in(");
cameraSql.append(" AND t.C_INDEX_CODE in(");
for(Object indexcode:indexcodes){
deviceSql.append("?,");
cameraSql.append("?,");
params.add(indexcode.toString());
}
deviceSql.deleteCharAt(deviceSql.length() - 1).append(")");
cameraSql.deleteCharAt(cameraSql.length() - 1).append(")");
for(Object indexcode:indexcodes){
params.add(indexcode.toString());
}
}else{
deviceSql.append(" AND t.I_KEYSITE_TYPE=0");
cameraSql.append(" AND t.I_KEYSITE_TYPE=0");
}
if(allData==1){
if (StringUtils.isNotBlank(beginTime)) {
deviceSql.append(" and r.C_CREATE_TIME >= to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss')");
cameraSql.append(" and r.C_CREATE_TIME >= to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss')");
}
if (StringUtils.isNotBlank(endTime)) {
deviceSql.append(" and r.C_CREATE_TIME <= to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss')");
cameraSql.append(" and r.C_CREATE_TIME <= to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss')");
}
if (StringUtils.isNotBlank(beginTime)) {
params.add(beginTime);
}
if (StringUtils.isNotBlank(endTime)) {
params.add(endTime);
}
if (StringUtils.isNotBlank(beginTime)) {
params.add(beginTime);;
}
if (StringUtils.isNotBlank(endTime)) {
params.add(endTime);
}
}
String unionSql = deviceSql.toString()+" Union "+cameraSql.toString();
String sql = "SELECT * FROM ( "
+ "SELECT uu.*, rownum r FROM (select uuu.*, count(1) over () total FROM ("+unionSql+") uuu) uu "
+ "WHERE rownum <= ?) t WHERE t.r > ?";
params.add(pageNo*pageSize);
params.add((pageNo-1)*pageSize);
return queryForList(sql,params.toArray());
}