mybatis动态查询条件

1. 需求：动态传递表名，列名以及条件，然后根据传递的数据返回数据

2.项目结构：

 

3. 实现：

3.1. 加入配置文件mybatis-config.xml以及CommonMapper.xml

CommonMapper.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.xk.mybatis.mapper.CommonMapper">

       
           <select id="getData"  resultType="java.util.Map" parameterType="java.util.Map">

                       SELECT
                             <if test="property==null">
                                  *
                             </if>
                             <if test="property!=null">
                                   ${property}
                             </if>
                        FROM ${tableName}
                        <where>
                                <if test="conds!=null">
                                   <foreach collection="conds" item="cond"  separator="and" >
                                       ${cond.column} ${cond.oper} ${cond.value}
                                   </foreach>
                                </if>
                        </where>

                       <if test="sorts!=null ">
                           order by
                           <foreach collection="sorts" item="sort"  separator="," >
                              ${sort.property} ${sort.direction}
                           </foreach>
                       </if>

           </select>

  </mapper>

3.2 控制层

@Controller
@RequestMapping("/api/base/common")
public class NormalController {

    @Autowired
    private CommonService commonService ;

     
    @RequestMapping(value = "/", method = {RequestMethod.GET})
    @ResponseBody
    public APIResponse<List<Map<String,String>>> getData(QueryDto queryDto){
        return new APIResponse<List<Map<String,String>>>(commonService.getData(queryDto));
    }


}

3.3 service层

@Service
public class CommonService {

    @Autowired
    private CommonMapper commonMapper;

    @Transactional(readOnly=true)
    public List<Map<String,String>> getData(QueryDto queryDto){
        Map<String,Object> map  = new HashMap<>();
        if(queryDto==null){
            return null;
        }
        // 设置表名
        map.put("tableName",queryDto.getTname());
        // 设置获取的属性名
        String[] properties = queryDto.getProperty() ;
        String property = null;
        if(properties!=null){
            property = String.join(",",queryDto.getProperty());
        }
        map.put("property",property);
        // 设置条件
        map.put("conds",queryDto.generatorConds());
        // 设置排序
        map.put("sorts",queryDto.generatorSort());
        return  commonMapper.getData(map);
    }
}

3.4 通用查询类：

public class QueryDto implements Serializable {
    private String tname;
    private String[] property;
    private String searchConds;
    /**
     * 排序字段_排序规则
     */
    private String[] sorts;

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public String[] getProperty() {
        return property;
    }

    public void setProperty(String[] property) {
        this.property = property;
    }

    public String getSearchConds() {
        return searchConds;
    }

    public void setSearchConds(String searchConds) {
        this.searchConds = searchConds;
    }


    public String[] getSorts() {
        return sorts;
    }

    public void setSorts(String[] sorts) {
        this.sorts = sorts;
    }

    /**
     * 生成排序
     *
     * @return List<Sort>
     */
    public List<Sort> generatorSort() {
        List<Sort> list = new ArrayList<Sort>();
        if (sorts != null && sorts.length > 0) {
            for (String sort : sorts) {
                String[] sortArr = sort.split("_");
                if (sortArr.length == 1) { //默认升序
                    list.add(new Sort(sort, Direction.ASC));
                }

                // 排序
                if (sortArr.length > 1) {
                    if (Direction.ASC.equalsIgnoreCase(sortArr[1]) || Direction.DESC.equalsIgnoreCase(sortArr[1])) {
                        list.add(new Sort(sortArr[0], sortArr[1]));
                    }
                }
            }
            return list;
        }
        return null;
    }

    /**
     * 将条件返回封装成list
     *
     * @return
     */
    public List<Conds> generatorConds() {
        // 获取条件，存储Map
        List<Conds> list = new ArrayList<>();
        if (!StringUtils.isEmpty(this.searchConds)) {
            JSONArray json = JSONArray.fromObject(this.searchConds);
            for (int i = 0; i < json.size(); i++) {
                JSONObject jsonOne = json.getJSONObject(i);
                // 获取属性
                String fieldName = (String) jsonOne.get("name");
                // 获取值
                String value = (String) jsonOne.get("value");
                if (!StringUtils.isEmpty(value)) {
                    String[] fieldArr = fieldName.split("_");
                    // 如果长度为1，默认为精确查询
                    if (fieldArr.length == 1) {
                        //  ssb.add(fieldName, "=", (String) jsonOne.get("value"));
                        list.add(new Conds(fieldName, "=", value));
                    }
                    // 如果长度超过1
                    if (fieldArr.length > 1) {
                        list.add(new Conds(fieldArr[1], CondOper.getCond(fieldArr[0]), value));
                    }
                }
            }
            return list;
        }
        return null;
    }
}

**
 * Created by Administrator on 2017/11/30.
 * 条件封装类
 */
public class Conds {
    /**
     * 属性
     */
    private String column;
    /**
     * 操作符
     */
    private String oper;
    /**
     * 条件值
     */
    private String value;

    public Conds() {
    }

    public Conds(String column, String oper, String value) {
        this.column = column;
        this.oper = oper;
        this.value = value;
    }



    public String getOper() {

        if ("like".equalsIgnoreCase(oper)){
            this.value = "'%"+value+"%'";
        }
        if ("llike".equalsIgnoreCase(oper)){
            this.oper ="like";
            this.value = "'%"+value+"'";
        }

        if ("rlike".equalsIgnoreCase(oper)){
            this.oper ="like";
            this.value = "'"+value+"%'";
        }

        return oper;
    }

    public void setOper(String oper) {
        this.oper = oper;
    }

    public String getValue() {

        if ("like".equalsIgnoreCase(oper)){
            return this.value;
        }
        return "'"+value+"'";
    }

    public void setValue(String value) {
        this.value = value;
    }


    public String getColumn() {
        return column;
    }

    public void setColumn(String column) {
        this.column = column;
    }

    @Override
    public String toString() {
        return "Conds{" +
                "column='" + column + '\'' +
                ", oper='" + oper + '\'' +
                ", value='" + value + '\'' +
                '}';
    }
}

其余的未列出...

3.4 测试

3.5 注意：

get请求传递JSON需要进行编码

用js默认的encodeURI即可。

3.6 打印的SQL语句