mybatis(ssm,springboot+mybatis)通用查询

有时候为了开发效率,特别讨厌去添加各种文件,写各种sql语句,而这些语句可能只使用一次,于是就有了做通用查询的想法.在这里,我不做技术说明,只发实现代码实例.只是为了简介

这是Mapper文件

<?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="CommonMapper">

    <!-- 动态查询语句.缺点是效率低,理论上有安全风险.试用场景:读取数据库配置 -->
    <select id="commonFind" resultType="java.util.Map" parameterType="java.util.Map" statementType="STATEMENT">
        SELECT ${_fields} FROM ${_table}
        WHERE 1=1
        <if test=" condition0!='' and condition0!=null ">
            AND ${_field0}=${condition0}
        </if>
        <if test=" condition1!='' and condition1!=null ">
            AND ${_field1}=${condition1}
        </if>
        <if test=" condition2!='' and condition2!=null ">
            AND ${_field2}=${condition2}
        </if>
        <if test=" condition3!='' and condition3!=null ">
            AND  ${_field3}=${condition3}
        </if>
        <if test=" condition4!='' and condition4!=null ">
            AND ${_field4}=${condition4}
        </if>
    </select>

</mapper>

对应的数据表:

下面是service

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * Created by LiuDezhen on 2018-04-20.
 */
@Service("commonService")
public class CommonService {

    @Resource(name = "sqlSessionTemplate")
    private SqlSessionTemplate sqlSessionTemplate;

    // ============================获取列表============================
    public List<Map> getList(Map m, String sql) {
        try {
            return sqlSessionTemplate.selectList(sql, m);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // ============================获取一个对象============================
    public Map getObj(Map m, String sql) {
        try {
            return (Map) sqlSessionTemplate.selectOne(sql, m);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

下面是具体的使用方法 , 其中用到的工具类SqlCommonFindTool在下面贴出

   @RequestMapping(value = "/getDevForXcx", produces = "application/json;charset=utf-8")
    @ResponseBody
    public String getDevForXcx() {
        /**
         * 读取全部配置
         */
        List list = commonService.getList(
                new SqlCommonFindTool().generator(
                        "zt_properties",//要操作的表
                        "*",//要查询的字段
                        new String[]{},//where条件的字段
                        new String[]{}),//where条件字段对应的值
                "CommonMapper.commonFind");
        Map<String, String> properties = new HashMap<String, String>();
        for (int i = 0; i < list.size(); i++) {
            Map m = (Map) list.get(i);
            properties.put(m.get("key").toString(), m.get("val").toString());
        }
        String isDev = properties.get("is_dev_environment");

        Map result = new HashMap<>();
        if (isDev.equals("1")) { // 测试环境
            result.put("comId", 2);
        }
        return JsonResultTool.mapToJson(0, result);
    }

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by LiuDezhen on 2018-05-08.
 */
public class SqlCommonFindTool {

    public Map generator(String table, String fields, String[] conditionField, String[] conditionVal) {
        Map param = new HashMap<>();
        param.put("_table", table);
        param.put("_fields", fields);
        for (int i = 0; i < conditionField.length; i++) {
            param.put("_field" + i, "`"+conditionField[i]+"`");
            param.put("condition" + i, "'"+conditionVal[i]+"'");
        }
        return param;
    }
}

将mapper.xml配置给mybatis中就可以了. 但是要说明的是: 没有预编译,所以效率稍低,所以适用于使用率小的查询. 而且使用的是$ 而非#  , 所以有安全风险, 最好不要用在有用户输入的使用场景下.