MyBatis-各种查询功能

1. 获取总记录数count

SelectMapper.java

public interface SelectMapper {
    /**
     * 查询用户的总数量
     * @return
     */
    Integer getCount();
}

 SelectMapper.xml

        MyBatis中为Java中常用的类型设置了类型别名
        Integer：Integer，int
        int：_int,_integer
        Map：map
        String：string

<?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.mybatis.mapper.SelectMapper">
    <!--Integer getCount();-->
    <select id="getCount" resultType="int">
        select count(*) from t_user
    </select>
</mapper>

测试文件：

    @Test
    public void testGetCount(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Integer count = mapper.getCount();
        System.out.println(count);
    }

2. 查询一条数据为Map集合

若查询的结果没有对应的实体类，可将其转换为map

SelectMapper.java

public interface SelectMapper {
   /**
     * 根据id查询用户信息为map集合
     * @param id
     * @return
     */
    Map<String, Object> getUserByIdToMap(@Param("id") Integer id);
}

 SelectMapper.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.mybatis.mapper.SelectMapper">
    <!--Map<String, Object> getUserByIdToMap(@Param("id") Integer id);-->
    <select id="getUserByIdToMap" resultType="map">
        select * from t_user where id = #{id}
    </select>
</mapper>

测试文件：

    @Test
    public void testGetUserByIdToMap(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Map<String, Object> map = mapper.getUserByIdToMap(4);
        //{password=123456, gender=男, id=1, age=23, email=12345@qq.com, username=admin}
        System.out.println(map);
    }

 3. 查询所有的信息为map集合

若查询的数据有多条时，并且要将每条数据转换为map集合

此时有两种解决方案：

    1、将mapper接口方法的返回值设置为泛型是map的list集合

       List<Map<String, Object>> getAllUserToMap();

      结果：{password=123456, gender=男, id=1, age=23, email=12345@qq.com, username=admin},{password=123456, gender=男, id=2, age=27, email=12345@qq.com, username=张三}

SelectMapper.java

public interface SelectMapper {
  List<Map<String, Object>> getAllUserToMap();
}

 SelectMapper.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.mybatis.mapper.SelectMapper">
    <!--Map<String, Object> getAllUserToMap();-->
    <select id="getAllUserToMap" resultType="map">
        select * from t_user
    </select>
</mapper>

测试文件：

     @Test
    public void testGetAllUserToMap(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<Map<String, Object>> list = mapper.getAllUserToMap();
        System.out.println(list);
    }

       2、可以将每条数据转换的map集合放在一个大的map中，但是必须要通过@MapKey注解，将查询的某个字段的值作为大的map的键

     @MapKey("id")

     Map<String, Object> getAllUserToMap();

  结果：

   {  1={password=123456, gender=男, id=1, age=23, email=12345@qq.com, username=admin},
          2={password=123, gender=男, id=2, age=23, email=12345@qq.com, username=zhangsan},
          3={password=123456, gender=女, id=3, age=33, email=123@qq.com, username=root},
               4={password=123, id=4, username=lisi}
         }

SelectMapper.java

public interface SelectMapper {
    @MapKey("id")
    Map<String, Object> getAllUserToMap();
}

 SelectMapper.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.mybatis.mapper.SelectMapper">
    <!--Map<String, Object> getAllUserToMap();-->
    <select id="getAllUserToMap" resultType="map">
        select * from t_user
    </select>
</mapper>

测试文件：

@Test
    public void testGetAllUserToMap(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Map<String, Object> map = mapper.getAllUserToMap();
        System.out.println(map);
    }