String sql = "select distinct a";
String fromSql = " from Aqyjfb a";
String whereSql = " where 1=1 and a.delete=false";
String orderSql = " order by fbsj desc";
String id = pb.decode(pb.getParamete("id"));
String zch = pb.decode(pb.getParamete("zch"));
String xxmc = pb.decode(pb.getParamete("xxmc"));
String orgId =pb.decode( pb.getParamete("orgId"));
String ssj = pb.decode(pb.getParamete("ssj"));
String esj = pb.decode(pb.getParamete("esj"));
if(id!=null && !id.equalsIgnoreCase("")){
whereSql+=" and a.id = '"+id+"'";
}
if(zch!=null && !zch.equalsIgnoreCase("")){
whereSql+=" and a.qyhznr.id like '%"+zch+"%'";
}
if(xxmc!=null && !xxmc.equalsIgnoreCase("")){
whereSql+=" and a.xxmc like '%"+xxmc+"%'";
}
if(orgId!=null && !orgId.equalsIgnoreCase("")){
whereSql+=" and (a.organization.id = '"+orgId+"')";
}
if(ssj!=null && !esj.equalsIgnoreCase("")){
whereSql+=" and a.fbsj between (to_date('"+ssj+"','yyyy-mm-dd')) and (to_date('"+esj+"','yyyy-mm-dd'))";
}
String exeSql = sql + fromSql + whereSql + orderSql;
String countSql = "select COUNT(distinct a) " + fromSql + whereSql;
String fromSql = " from Aqyjfb a";
String whereSql = " where 1=1 and a.delete=false";
String orderSql = " order by fbsj desc";
String id = pb.decode(pb.getParamete("id"));
String zch = pb.decode(pb.getParamete("zch"));
String xxmc = pb.decode(pb.getParamete("xxmc"));
String orgId =pb.decode( pb.getParamete("orgId"));
String ssj = pb.decode(pb.getParamete("ssj"));
String esj = pb.decode(pb.getParamete("esj"));
if(id!=null && !id.equalsIgnoreCase("")){
whereSql+=" and a.id = '"+id+"'";
}
if(zch!=null && !zch.equalsIgnoreCase("")){
whereSql+=" and a.qyhznr.id like '%"+zch+"%'";
}
if(xxmc!=null && !xxmc.equalsIgnoreCase("")){
whereSql+=" and a.xxmc like '%"+xxmc+"%'";
}
if(orgId!=null && !orgId.equalsIgnoreCase("")){
whereSql+=" and (a.organization.id = '"+orgId+"')";
}
if(ssj!=null && !esj.equalsIgnoreCase("")){
whereSql+=" and a.fbsj between (to_date('"+ssj+"','yyyy-mm-dd')) and (to_date('"+esj+"','yyyy-mm-dd'))";
}
String exeSql = sql + fromSql + whereSql + orderSql;
String countSql = "select COUNT(distinct a) " + fromSql + whereSql;
return this.getPageBody(pb, countSql, exeSql);
如果是mysql或者mssql数据库,between and 是可以直接中使用的,但是oracle有自己的规范,故需要做下转换
whereSql+=" and a.fbsj between (to_date('"+ssj+"','yyyy-mm-dd')) and (to_date('"+esj+"','yyyy-mm-dd'))";