前言:在优化代码的过程中我要查询的结果就是数据库中的两列字段的值,其中这两个字段的的关系是参数名:参数默认值。这是我要的结果集。---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的自动以封装类型还得去研究,先记录一下,希望能帮到您,有问题请留言,谢谢!!!