MyBatis 返回 Map，查询字段两列分别作为 key 和 value

前言：在优化代码的过程中我要查询的结果就是数据库中的两列字段的值，其中这两个字段的的关系是参数名：参数默认值。这是我要的结果集。---mybatis叼，记录一下。

1、先列出我的sql和结果集封装：

<resultMap id="mapResult" type="java.util.HashMap">
    <result property="key" column="paramName" javaType="java.lang.String" jdbcType="VARCHAR"/>
    <result property="value" column="defaultValue" javaType="java.lang.String" jdbcType="VARCHAR" />
</resultMap>

<--sql语句-->
<select id="getInfoByFlagToDef" parameterType="com.zte.st.dailybuild.domain.TooBox.OperationModualType"
        resultMap="mapResult">
    select
    om.param_name AS paramName,
    om.default_value  AS defaultValue
    from
    operation_modual_type om
    <where>
        <if test="operationName != null and operationName != ''">
            and om.operation_name = #{operationName,jdbcType=VARCHAR}
        </if>
        <if test="operationId != null and operationId != 0">
            and  om.operation_id = #{operationId,jdbcType=BIGINT}
        </if>
        <if test='componentType != null and componentType != "" '>
            and  om.component_type = #{componentType,jdbcType=VARCHAR}
        </if>
        <if test="componentDesc != null and componentDesc != ''">
            and  om.component_desc Like concat ('%', #{componentDesc,jdbcType=VARCHAR},'%')
        </if>
        <if test="componentPlacehold != null and componentPlacehold != ''">
            and  om.component_placehold = #{componentPlacehold,jdbcType=VARCHAR}
        </if>
        <if test="defaultValue != null and defaultValue != ''">
            and  om.default_value = #{defaultValue,jdbcType=VARCHAR}
        </if>
        <!--<if test="paramName != null and paramName != ''">-->
            <!--and  om.param_name = #{paramName,jdbcType=VARCHAR}-->
        <!--</if>-->
        <if test="selectContents != null and selectContents != ''">
            and  om.select_contents = #{selectContents,jdbcType=VARCHAR}
        </if>
            and  om.flag = false
    </where>
</select>

2、mybatis自定义结果集的封装：

package com.zte.st.dailybuild.common.handler;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

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

/**
 * Created by 6092002943 on 2020/7/14.
 * 将mybatis中的结果集封装成key value
 */
public class MyResultHandler implements ResultHandler {

    @SuppressWarnings("rawtypes")
    private final HashMap mappedResults = new HashMap();

    @SuppressWarnings("unchecked")
    @Override
    public void handleResult(ResultContext context) {
        @SuppressWarnings("rawtypes")
        Map map = (Map) context.getResultObject();
        mappedResults.put(map.get("key"), map.get("value")); // xml配置里面的property的值，对应的列
    }

    @SuppressWarnings("rawtypes")
    public HashMap getMappedResults() {
        return mappedResults;
    }
}

 

3、SessionMapper的编写：

@Repository
public class SessionMapper extends SqlSessionDaoSupport {
    @Autowired
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }
    /**
     * @return
     */
    @SuppressWarnings("unchecked")
    public HashMap<String, String> selectKeyValue(OperationModualType operationModualType) {
        MyResultHandler handler = new MyResultHandler();
        //第一个参数：Dao层或者Mapper的类路径 namespace : XxxMapper.xml 中配置的地址（XxxMapper.xml的qualified name）
        //第二个参数：dao层方法的参数.selectXxxxNum : XxxMapper.xml 中配置的方法名称
        //第三个参数是用咱们自己编写的结果集进行处理
        //this.getSqlSession().select(namespace+".selectXxxxNum", handler);
        this.getSqlSession().select(OperationModualTypeDao.class.getName() + ".getInfoByFlagToDef", operationModualType, handler);
        HashMap<String, String> map = handler.getMappedResults();
        return map;
    }
}

4、测试程序：

 

@SpringBootTest(classes = TestApplication.class)
@RunWith(SpringRunner.class)
public class TestDamo {

    @Autowired
    private OperationModualTypeDao operationModualTypeDao;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
   
    @Autowired
    private SessionMapper sessionMapper;//自己编写的sessionMapper注入进来

    @Test
    public void testFun(){

        OperationModualType modualType = new OperationModualType();
        modualType.setOperationName("创建OSPF路由进程");


//        SqlSession sqlSession = sqlSessionFactory.openSession(true);
//        MyResultHandler handler = new MyResultHandler();
//
//        sqlSession.select("src.st.Dao.OperationModualTypeDao.getInfoByFlagToDef",modualType,handler);
//        //获取结果
//        handler.getMappedResults().forEach((k, v) -> System.out.println("k:" + k + ",v:" + v));

        //key 可选字段paramname  value 可选字段默认值
        HashMap<String, Object> map = sessionMapper.selectDeviceHost(modualType);
        map.forEach((k, v) -> System.out.println("k:" + k + ",v:" + v));
        System.out.println(map.toString());
    }

}

5、结果输出：

==>  Preparing: select om.param_name AS paramName, if(om.default_value = '' ,"",om.default_value) AS defaultValue from operation_modual_type_copy om WHERE om.operation_name = ? and om.flag = false 
==> Parameters: 创建OSPF路由进程(String)
<==    Columns: paramName, defaultValue
<==        Row: vrf, 
<==        Row: AreaInterList, 
<==        Row: Area, 0
<==        Row: MaxPaths, 
<==        Row: version, ipv4
<==      Total: 5 

心得：根据我的需求，我这样的去封装结果集省了不少事，一次查询，一次结果的对比，优化了不少的逻辑代码。这mybatis的自动以封装类型还得去研究，先记录一下，希望能帮到您，有问题请留言，谢谢！！！