我遇到的问题是排序没有作用。
原代码
public Page<House> searchIzz(int pageNum, Integer pageSize, String keyword, Integer cityId, Integer areaId, Integer leaseType, String roomNumType, String hallNumType
, String minPrice, String maxPrice, String longitude, String latitude, Integer limitDistance, Integer orderBy){
List<Object> params = new ArrayList<Object>();
StringBuilder builder = new StringBuilder("select a.id,a.image,a.leaseType,a.area,a.money,a.roomNum,a.hallNum,a.numberPlate,b.landlordAccountId,b.regionName,b.address,b.communityName,b.buildNo");
builder.append(" from fwzl_house a inner join fwzl_building b on a.buildingId = b.id");
builder.append(" where a.delFlag = 0 and b.delFlag = 0");
builder.append(" and a.rentStatus = ?");
params.add(FwzlConstant.HOUSE_RENT_STATUS_UPPER);
if(StrUtil.isNotBlank(keyword)){
builder.append(" and (a.title like ? or b.communityName like ? or b.buildNo like ? or a.numberPlate like ?)");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
}
if(cityId != null){
builder.append(" and b.cityId = ?");
params.add(cityId);
}
if(areaId != null){
builder.append(" and b.areaId = ?");
params.add(areaId);
}
if(leaseType != null){
builder.append(" and a.leaseType = ?");
params.add(leaseType);
}
if(StrUtil.isNotBlank(roomNumType)){
builder.append(" and (");
String[] str = roomNumType.split(",");
for(int i=0; i <str.length; i++){
if(i>0){
builder.append(" or ");
}
builder.append("a.roomNum");
Integer room = Integer.valueOf(str[i]);
builder.append(room < 5 ? " = " : " >= ");
builder.append(room);
}
builder.append(")");
}
if(StrUtil.isNotBlank(hallNumType)){
builder.append(" and (");
String[] str = hallNumType.split(",");
for(int i=0; i <str.length; i++){
if(i>0){
builder.append(" or ");
}
builder.append("a.hallNum");
Integer room = Integer.valueOf(str[i]);
builder.append(room < 2 ? " = " : " >= ");
builder.append(room);
}
builder.append(")");
}
if(StrUtil.isNotBlank(minPrice)){
if(!CalculateKit.isMoney(minPrice)){
return null;
}
builder.append(" and a.money >= ?");
params.add(minPrice);
}
if(StrUtil.isNotBlank(maxPrice)){
if(!CalculateKit.isMoney(maxPrice)){
return null;
}
builder.append(" and a.money <= ?");
params.add(maxPrice);
}
if(limitDistance != null){
if(!CalculateKit.isDouble(longitude) || !CalculateKit.isDouble(latitude)){
return null;
}
builder.append(" and round((6371 * acos (cos(radians("+latitude+")) * cos(radians(b.latitude)) * cos(radians(b.longitude) - radians("+longitude+"))+sin(radians("+latitude+")) * sin(radians(b.latitude))) * 1000)) <= ?");
params.add(limitDistance);
}
if(orderBy != null && orderBy > 0){
if(orderBy == 1){
builder.append(" order by a.money asc");
}else if(orderBy == 2){
builder.append(" order by a.money desc");
}else if(orderBy == 3){
builder.append(" order by a.upperTime desc");
}
}else{
builder.append(" order by a.createTime desc");
}
return getDao().paginate(pageNum, pageSize, "select *", "from("+builder.toString()+") as temp", params.toArray());
}
修改后代码:
public Page<House> searchIzz2(int pageNum, Integer pageSize, String keyword, Integer cityId, Integer areaId, Integer leaseType, String roomNumType, String hallNumType
, String minPrice, String maxPrice, String longitude, String latitude, Integer limitDistance, Integer orderBy){
List<Object> params = new ArrayList<Object>();
String b = "select a.id,a.image,a.leaseType,a.area,a.money,a.roomNum,a.hallNum,a.numberPlate,b.landlordAccountId,b.regionName,b.address,b.communityName,b.buildNo";
StringBuilder builder = new StringBuilder(" from fwzl_house a inner join fwzl_building b on a.buildingId = b.id");
builder.append(" where a.delFlag = 0 and b.delFlag = 0");
builder.append(" and a.rentStatus = ?");
params.add(FwzlConstant.HOUSE_RENT_STATUS_UPPER);
if(StrUtil.isNotBlank(keyword)){
builder.append(" and (a.title like ? or b.communityName like ? or b.buildNo like ? or a.numberPlate like ?)");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
params.add("%"+keyword+"%");
}
if(cityId != null){
builder.append(" and b.cityId = ?");
params.add(cityId);
}
if(areaId != null){
builder.append(" and b.areaId = ?");
params.add(areaId);
}
if(leaseType != null){
builder.append(" and a.leaseType = ?");
params.add(leaseType);
}
if(StrUtil.isNotBlank(roomNumType)){
builder.append(" and (");
String[] str = roomNumType.split(",");
for(int i=0; i <str.length; i++){
if(i>0){
builder.append(" or ");
}
builder.append("a.roomNum");
Integer room = Integer.valueOf(str[i]);
builder.append(room < 5 ? " = " : " >= ");
builder.append(room);
}
builder.append(")");
}
if(StrUtil.isNotBlank(hallNumType)){
builder.append(" and (");
String[] str = hallNumType.split(",");
for(int i=0; i <str.length; i++){
if(i>0){
builder.append(" or ");
}
builder.append("a.hallNum");
Integer room = Integer.valueOf(str[i]);
builder.append(room < 2 ? " = " : " >= ");
builder.append(room);
}
builder.append(")");
}
if(StrUtil.isNotBlank(minPrice)){
if(!CalculateKit.isMoney(minPrice)){
return null;
}
builder.append(" and a.money >= ?");
params.add(minPrice);
}
if(StrUtil.isNotBlank(maxPrice)){
if(!CalculateKit.isMoney(maxPrice)){
return null;
}
builder.append(" and a.money <= ?");
params.add(maxPrice);
}
if(limitDistance != null){
if(!CalculateKit.isDouble(longitude) || !CalculateKit.isDouble(latitude)){
return null;
}
builder.append(" and round((6371 * acos (cos(radians("+latitude+")) * cos(radians(b.latitude)) * cos(radians(b.longitude) - radians("+longitude+"))+sin(radians("+latitude+")) * sin(radians(b.latitude))) * 1000)) <= ?");
params.add(limitDistance);
}
String orderByStr = "";
if(orderBy != null && orderBy > 0){
if(orderBy == 1){
orderByStr = " order by a.money asc";
}else if(orderBy == 2){
orderByStr = " order by a.money desc";
}else if(orderBy == 3){
orderByStr = " order by a.upperTime desc";
}
}else{
orderByStr = " order by a.createTime desc";
}
return getDao().paginateByFullSql(pageNum, pageSize, "select count(*) " + builder.toString(), b+builder.toString()+orderByStr, params.toArray());
}
具体解决办法是看了看操作手册说复杂排序的sql可以换一种解决办法,虽然我这个不复杂,但换一种方式能解决也挺好。
如下: