db2查询条件为空,sql查询条件为空

当我们查询条件中的值不确定是否有空值时，我们期待查询时，Mybatis+能自动帮我们判空。

比如这样：

this.page( new Query<OperatorsEntity>().getPage(queryFields), new QueryWrapper<OperatorsEntity>() .eq("province_id",queryFields.getProvinceId()) .eq("city_id",queryFields.getCityId()) .eq("area_id",queryFields.getAreaId()) .like("name",queryFields.getNameLike()));

我们期待的结果是：“province_id”、“city_id”、“area_id”、"name"等几个字段中，如果有空的(比如查询表单中没有填这个查询条件)，就不查询。
然而事实上，如果这样去写，结果会是：Mybatis+把所有条件都构造出来，为空的就按null查询，like则是查询: %null%

解决方案:

提供一个有条件排空功能的ExcludeEmptyQueryWrapper(后附代码)，有类似需求的用该queryWrapper来构建条件，一劳永逸解决问题，使用： this.page( new Query<OperatorsEntity>().getPage(queryFields), new ExcludeEmptyQueryWrapper<OperatorsEntity>() .eq("province_id",queryFields.getProvinceId()) .eq("city_id",queryFields.getCityId()) .eq("area_id",queryFields.getAreaId()) .like("name",queryFields.getNameLike())); ExcludeEmptyQueryWrapper 代码如下： /** * 继承QueryWrapper，重写其构造条件方法，使条件为null的查询条件，不再执行。 * @since Mybatis+ version: 3.x.x * @author firewolf */public class ExcludeEmptyQueryWrapper<T> extends QueryWrapper<T> { @Override public QueryWrapper<T> eq(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.eq(condition, column, val); } @Override public QueryWrapper<T> ne(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.ne(condition, column, val); } @Override public QueryWrapper<T> gt(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.gt(condition, column, val); } @Override public QueryWrapper<T> ge(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.ge(condition, column, val); } @Override public QueryWrapper<T> lt(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.lt(condition, column, val); } @Override public QueryWrapper<T> le(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.le(condition, column, val); } @Override public QueryWrapper<T> like(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.like(condition, column, val); } @Override public QueryWrapper<T> notLike(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.notLike(condition, column, val); } @Override public QueryWrapper<T> likeLeft(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.likeLeft(condition, column, val); } @Override public QueryWrapper<T> likeRight(boolean condition, String column, Object val) { condition = !Objects.isNull(val); return super.likeRight(condition, column, val); } @Override public QueryWrapper<T> between(boolean condition, String column, Object val1, Object val2) { if(Objects.isNull(val1) && Objects.isNull(val2)){ condition = false; }else if(Objects.isNull(val1)){ return super.lt(true,column,val2); }else if(Objects.isNull(val2)){ return super.ge(true,column,val1); } return super.between(condition, column, val1, val2); } @Override public QueryWrapper<T> notBetween(boolean condition, String column, Object val1, Object val2) { if(Objects.isNull(val1) && Objects.isNull(val2)){ condition = false; }else if(Objects.isNull(val1)){ return super.lt(true,column,val2); }else if(Objects.isNull(val2)){ return super.ge(true,column,val1); } return super.notBetween(condition, column, val1, val2); }}