最近有个需求,需要通过mysql 中 某张表的 varchar2 类型字段(保存数据为金额)进行 asc 或 desc 排序。项目框架为 springboot + mybatisplus 。
优化方案有:
一:重新设计表结构,将金额字段设计为 DECIMAL(18,4)。重写相关代码,最后进行测试,周期较长。由于本项目相关字段的业务不是很重。所以放弃!
二:使用 cast(column as DECIMAL)进行转换 后 order by。采用此种写法。
实现代码:
public BsPage<Entity> queryList(Integer page, Integer rows, Map<String, Object> params) {
BsPage<Entity> bsPage = new BsPage<>(page, rows);
String objId = params.get("objId") != null ? params.get("objId").toString() : null;
String keyWord = params.get("keyWord") != null ? params.get("keyWord").toString() : null;
String sortType = params.get("sortKey") != null ? params.get("sortKey").toString() : null;
QueryWrapper<Entity> wrapper = new QueryWrapper<>();
wrapper.eq(objId != null, "obj_id", objId);
wrapper.and(StrUtil.isNotBlank(keyWord), i -> i.like("title", keyWord).or().like("purchaser", keyWord));
wrapper.orderByAsc(sortType!=null && "publishDate_asc".equals(sortType), "publish_date");
wrapper.orderByDesc(sortType!=null && "publishDate_desc".equals(sortType), "publish_date");
wrapper.orderByAsc( sortType!=null && "bidAmount_asc".equals(sortType)," cast(bid_amount as DECIMAL) ");
wrapper.orderByDesc(sortType!=null && "bidAmount_desc".equals(sortType), " cast(bid_amount as DECIMAL) ");
return baseMapper.selectPage(bsPage, wrapper);
}
主要有:
wrapper.orderByAsc( sortType!=null && "bidAmount_asc".equals(sortType)," cast(bid_amount as DECIMAL) ");
wrapper.orderByDesc(sortType!=null && "bidAmount_desc".equals(sortType), " cast(bid_amount as DECIMAL) ");
运行结果报错:
SELECT * FROM table WHERE (obj_id = ?) ORDER BY cast(bid_amountasDECIMAL) DESC
追源码,发现 mybatisplus 底层将 cast(bid_amount as DECIMAL) 以 单个字段名的方式进行去空格。导致 mysql 无法识别。
源码位置:
AbstractWrapper.java 中使用 columnSqlInjectFilter 方法
@Deprecated
@Override
public Children orderBy(boolean condition, boolean isAsc, R column, R... columns) {
return maybeDo(condition, () -> {
final SqlKeyword mode = isAsc ? ASC : DESC;
appendSqlSegments(ORDER_BY, columnToSqlSegment(columnSqlInjectFilter(column)), mode);
if (ArrayUtils.isNotEmpty(columns)) {
Arrays.stream(columns).forEach(c -> appendSqlSegments(ORDER_BY,
columnToSqlSegment(columnSqlInjectFilter(c)), mode));
}
});
}
具体实现方法:
QueryWrapper.java
@Override
protected String columnSqlInjectFilter(String column) {
return StringUtils.replaceBlank(column);
}
因此:需要自己覆盖 QueryWrapper 的 columnSqlInjectFilter 方法。使用时在业务层 使用 即可。
代码:
/**
* 重写 mybatisplus 的查询类,mybatisplus 查询时 orderby 不可使用函数 。
* 如 :order by cast(bid_amount as DECIMAL) 。mybatisplus 会将其转换为 ORDER BY cast(bid_amountasDECIMAL) ASC
*
*/
public class PcQueryWrapper<T> extends QueryWrapper<T> {
@Override
protected String columnSqlInjectFilter(String column) {
return column;
}
}
业务层使用 new PcQueryWrapper 的方式使用。
@Override
public BsPage<Entity> queryList(Integer page, Integer rows, Map<String, Object> params) {
BsPage<Entity> bsPage = new BsPage<>(page, rows);
String objId = params.get("objId") != null ? params.get("objId").toString() : null;
String keyWord = params.get("keyWord") != null ? params.get("keyWord").toString() : null;
String sortType = params.get("sortKey") != null ? params.get("sortKey").toString() : null;
// QueryWrapper<Entity> wrapper = new QueryWrapper<>();
PcQueryWrapper<Entity> wrapper = new PcQueryWrapper<>();
wrapper.eq(objId != null, "obj_id", objId);
wrapper.and(StrUtil.isNotBlank(keyWord), i -> i.like("title", keyWord).or().like("purchaser", keyWord));
wrapper.orderByAsc(sortType!=null && "publishDate_asc".equals(sortType), "publish_date");
wrapper.orderByDesc(sortType!=null && "publishDate_desc".equals(sortType), "publish_date");
wrapper.orderByAsc( sortType!=null && "bidAmount_asc".equals(sortType)," cast(bid_amount as DECIMAL) ");
wrapper.orderByDesc(sortType!=null && "bidAmount_desc".equals(sortType), " cast(bid_amount as DECIMAL) ");
return baseMapper.selectPage(bsPage, wrapper);
}
执行结果:
SELECT * FROM table WHERE (obj_id = ?) ORDER BY cast(bid_amount as DECIMAL) ASC
完结!!!