【SSM框架】MyBatis的各种查询功能

布满荆棘的人生 2024-03-30 09:31 62阅读 0赞

## 1.查询一个实体类对象 ##

> /\*\*
> 
>  *  根据用户id查询用户信息
>  *  @param id
>  *  @return  
>     \*/  
>     User getUserById(@Param("id") int id);

<select id="getUserById" resultType="User">
           select * from t_user where id=#{id};
        </select>

@Test
        public void test() {
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            User user = mapper.getUserById(2);
            System.out.println(user);
     
        }

表：

![c2d90aea1805f8136126c5ada0f61902.png][]

查询结果：

![d2cffdf2cfcaa5c122454fd3c6a771a4.png][]

假如，我把后面的条件删除，会发生什么情况呢？

![c251be868d041b44dbd39f865b511df9.png][]

![a84d88f985e4a16460d4395422a85456.png][]

> org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
> 
> 当查询的数据为多条时，不能使用实体类作为返回值，否则会抛出异常
> 
> TooManyResultsException；但是若查询的数据只有一条，可以使用实体类或集合作为返回值

### 2.查询一个list集合 ###

> /\*\*
> 
>  *  查询所有用户信息
>  *  @return
> 
> \*/
> 
> List<User> getUserList();

<select id="getAllUser" resultType="User">
    select * from t_user;
        </select>
    ```
    测试类：
    ```
       @Test
        public void test2(){
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            List<User> allUser = mapper.getAllUser();
            allUser.forEach(System.out::println);
     
        }
    ```
     查询结果：
    ![](https://upload-images.jianshu.io/upload_images/28070583-41734c182ed25fb6.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
    
    #3.查询单个数据
    
    >/**
    >* 查询用户的总记录数
    >* @return
    >* 在 MyBatis 中，对于 Java 中常用的类型都设置了类型别名
    >* 例如： java.lang.Integer-->int|integer
    >* 例如： int-->_int|_integer
    >* 例如： Map-->map,List-->list
    >*/
    ```
    int getCount ();
        <select id="getCount" resultType="java.lang.Integer">
            select count(id) from t_user;
        </select>
    ```
    测试类：
    ```
        @Test
        public void test3(){
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            Integer count = mapper.getCount();
            System.out.println(count);
     
        }
     ```
    
    表：
    ![](https://upload-images.jianshu.io/upload_images/28070583-ade23613e529caa3.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
    
    查询结果：
    ![](https://upload-images.jianshu.io/upload_images/28070583-dd7ae77c02b2d0c4.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
    
    #4.查询一条数据为map集合
    >/**
    >* 根据用户 id 查询用户信息为 map 集合
    >* @param id
    >* @return
    >*/
    >Map < String , Object > getUserToMap ( @Param ( "id" ) int id );
     
     ```
     <select id="getUserToMap" resultType="map"> 
    select * from t_user where id = #{id} 
    </select>
    ```
     测试类：
    ```
      @Test
        public  void  test4(){
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            Map<String, Object> map = mapper.getUserToMap(4);
            System.out.println(map);
        }
    ```
    ![](https://upload-images.jianshu.io/upload_images/28070583-d89269afe54cca0a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
    
    #5.查询多条数据为map集合
    ①方式一
    
    >/**
    >* 查询所有用户信息为 map 集合
    >* @return
    >* 将表中的数据以 map 集合的方式查询，一条数据对应一个 map ；若有>多条数据，就会产生多个 map 集合，此
    >时可以将这些 map 放在一个 list 集合中获取
    >*/
    >List < Map < String , Object >> getAllUserToMap ();
    ```
    
     <select id="getAllUserToMap" resultType="map"> 
    select * from t_user
     </select>
    ```
    测试类：
    ```
     @Test
        public  void  test5(){
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            List<Map<String, Object>> allUserToMap = mapper.getAllUserToMap();
            System.out.println(allUserToMap);
        }
    ```
    ![](https://upload-images.jianshu.io/upload_images/28070583-7e3a58dbfb84597b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
    
    ②方式二
    /**
    * 查询所有用户信息为 map 集合
    * @return
    * 将表中的数据以 map 集合的方式查询，一条数据对应一个 map ；若有多条数据，就会产生多个 map 集合，并
    且最终要以一个 map 的方式返回数据，此时需要通过 @MapKey 注解设置 map 集合的键，值是每条数据所对应的
    map 集合
    */
    @MapKey ( "id" )
    Map < String , Object > getAllUserToMap ();
     ```
     <select id="getAllUserToMap" resultType="map"> 
    select * from t_user
     </select>
    ```
     测试类：
    ```
        @Test
        public  void  test6(){
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            Map<String, Object> allUserToMap = mapper.getAllUserToMap();
            System.out.println(allUserToMap);
     
        }
    ```
    运行结果：
    
    >{1={password=123, sex=男, id=1, age=23, email=11111@qq.com, username=张三},
    >
    >2={password=root, sex=女, id=2, age=23, email=11111@qq.com, username=root},
    >
    >3={password=789123, sex=女, id=3, age=28, email=9090889@qq.com, username=小王}, 4={password=123

[c2d90aea1805f8136126c5ada0f61902.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/6b66a4bf3d1f4979b34cfbf5c4631ee1.png
[d2cffdf2cfcaa5c122454fd3c6a771a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ba48ae9a0f464495ae0d24ce7017fd3a.png
[c251be868d041b44dbd39f865b511df9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fd64e93d88ff4e5a8df14fbc12f72963.png
[a84d88f985e4a16460d4395422a85456.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d6cdc80e117641d1ae5214e1ceeae6fb.png