使用Example及Example[Criteria]

mybatis-generator会为每个字段产生Criterion，为底层的mapper.xml创建动态sql。如果表的字段比较多,产生的example类会十分庞大。理论上通过example类可以构造你想到的任何筛选条件。在mybatis-generator中加以配置，配置数据表的生成操作就可以自动生成example了。

它提供了一套sql操作的相关拼接方法
如：select * from user where name={#user.name} and sex={#user.sex} order by age asc;

example.setOrderByClause(“age asc"); //升序
 example.setDistinct(false); //不去重
 if(!StringUtils.isNotBlank(user.getName())){
 Criteria.andNameEqualTo(user.getName());
 }
 if(!StringUtils.isNotBlank(user.getSex())){
 Criteria.andSexEqualTo(user.getSex());
 }
 List<User> userList=userMapper.selectByExample(example);

开发时为更灵活实现查询功能，前端vue拼接一套自设的json对象为后端解析，创建PageRequest 对象如下：

package net.xxx.knmarket.model.util;


import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

import java.util.List;

/**
 * @author xxx
 * 分页查询请求模型类
 */
@ApiModel(description = "分页查询请求模型")
public class PageRequest {

    @ApiModelProperty(value = "页码", example = "1", required = true)
    int pageNo;
    @ApiModelProperty(value = "每页返回记录条数", example = "10", required = true)
    int pageSize;
    @ApiModelProperty(value = "排序字段")
    String orderBy;
    @ApiModelProperty(value = "查询字段")
    String query;
    @ApiModelProperty(value = "查询条件")
    String condition;
    @ApiModelProperty(value = "查询类型")
    String type;

    @ApiModelProperty(value = "queryList查询条件")
    List<Querys> querys;


    public void setType(String type) {
        this.type = type;
    }
    public String getCondition() {
        return condition;
    }

    public void setCondition(String condition) {
        this.condition = condition;
    }

    public String getQuery() {
        return query;
    }

    public void setQuery(String query) {
        this.query = query;
    }

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public List<Querys> getQuerys() {
        return querys;
    }
    public String getType() {
        return type;
    }

    public void setQuerys(List<Querys> querys) {
        this.querys = querys;
    }
}

package net.xxx.knmarket.model.util;


import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

import java.util.List;

/**
 * @author xxx
 * Querys对象
 */
@ApiModel(description = "分页查询Querys对象模型")
public class Querys {

    @ApiModelProperty(value = "查询字段")
    String query;
    @ApiModelProperty(value = "查询条件")
    String condition;
    @ApiModelProperty(value = "查询条件")
    String condition2;
    @ApiModelProperty(value = "查询类型")
    String type;
    @ApiModelProperty(value = "查询关系")
    String relate;

    public String getCondition2() {
        return condition2;
    }

    public void setCondition2(String condition2) {
        this.condition2 = condition2;
    }

    public String getQuery() {
        return query;
    }

    public void setQuery(String query) {
        this.query = query;
    }

    public String getCondition() {
        return condition;
    }

    public void setCondition(String condition) {
        this.condition = condition;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getRelate() {
        return relate;
    }

    public void setRelate(String relate) {
        this.relate = relate;
    }
}

项目实例：

    @PostMapping("/")
    @ApiOperation(value="翻页查询供应商信息")
    public Result pageListByPage(@Valid @RequestBody PageRequest pageRequest) {
        //添加分页及排序
        PageHelper.startPage(pageRequest.getPageNo(), pageRequest.getPageSize(), pageRequest.getOrderBy());
        //创建对象
        Example example = new Example(SupplierBaseinfo.class);
        Example.Criteria criteria = example.createCriteria();
        //为对象赋值
        if (pageRequest.getQuerys()!= null&&pageRequest.getQuerys()!=null && !pageRequest.getQuerys().isEmpty()){
            pageRequest.getQuerys().forEach(item->{
                if(item.getRelate().equals("and")){
                    switch (item.getType()){
                        case "=":
                            criteria.andEqualTo(item.getQuery(),item.getCondition());
                            break;
                        case "between":
                            criteria.andBetween(item.getQuery(),item.getCondition(),item.getCondition2());
                            break;
                        case "like":
                            criteria.andLike(item.getQuery(),"%" + item.getCondition() + "%");
                            break;
                        default:
                            criteria.andEqualTo(item.getQuery(),item.getCondition());
                            break;
                    }
                }
                if(item.getRelate().equals("or")){
                    switch (item.getType()){
                        case "=":
                            criteria.orEqualTo(item.getQuery(),item.getCondition());
                            break;
                        case "between":
                            criteria.orBetween(item.getQuery(),item.getCondition(),item.getCondition2());
                            break;
                        case "like":
                            criteria.orLike(item.getQuery(),"%" + item.getCondition() + "%");
                            break;
                        default:
                            criteria.orEqualTo(item.getQuery(),item.getCondition());
                            break;
                    }
                }
            });
        }
        //查询
        List<SupplierBaseinfo> supplierBaseinfos = this.supplierBaseinfoMapper.selectByExample(example);
        return ResultFactory.buildResult(CommonCode.SUCCESS, new PageInfo(supplierBaseinfos));
    }

Criteria包含一个Cretiron的集合,每一个Criteria对象内包含的Cretiron之间是由AND连接的,是逻辑与的关系。

image.png

Ecample包中提供相关拼接sql方法。