mybatis各种查询功能

肥皂火炉

已于 2023-07-25 21:06:15 修改

阅读量724

点赞数

分类专栏： mybatis 文章标签： mybatis java xml 数据库

于 2023-07-22 20:02:21 首次发布

本文链接：https://blog.youkuaiyun.com/m0_70574094/article/details/131872061

版权

mybatis 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了MyBatis如何返回多种查询结果，包括返回list集合、integer数据类型、单个及多个map，并强调了返回map在多表查询中的灵活性。同时，还展示了如何进行模糊查询，讲解了参数化的sql语法和调用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

返回多条查询结果

mapper接口返回list集合

List<User> select();

mapper.xml

<select id="select" resultType="User">
        select * from fz03;
</select>

调用方法List集合接收

@Test
    public void a5() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_text02.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);

        List<User> list = usermapper.select();

        list.forEach(System.out::println);

        session.close();
        inputStream.close();
    }

结果

返回integer数据类型,引出别名大全

先以integer型为例

Integer返回类型可以写为Integer int不区分大小写

<select id="selectcount" resultType="integer">
        select count(*) from fz03;
</select>

mapper接口方法

int selectcount();

调用方法

@Test
    public void a6() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_text02.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);

        int a = usermapper.selectcount();

        System.out.println(a);

        session.close();
        inputStream.close();
    }

返回类型resultType="integer"是因为mybatis使用了别名,在使用手册中可以找到java常用类型的别名

因此该类型还可以写为int

<select id="selectcount" resultType="int">

返回结果为map

返回原理是将得到的结果用map接收,key为接收的属性,values为接收的值

单个map返回

mapper接口

Map<String,Object> selectmapp();

mapper.xml设置返回值为map

<select id="selectmapp" resultType="map">
        select * from fz03 where id = 1;
</select>

用map接收

@Test
    public void a7() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_text02.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);

        Map<String,Object> map = usermapper.selectmapp();

        System.out.println(map);

        session.close();
        inputStream.close();
}

运行结果

多个map返回

将map集合包裹到list集合中就行

mapper接口

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

mapper.xml

<select id="selectmaps" resultType="map">
        select * from fz03
</select>

调用

@Test
    public void a8() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_text02.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);

        List<Map<String,Object>> map = usermapper.selectmaps();

        System.out.println(map);

        session.close();
        inputStream.close();
}

运行结果

返回map的优点在于多表查询的时候不需要重写对象,更加灵活

mapper.xml

<select id="select_" resultType="map">
        select fz06.*,fz05.pass_word from fz05 left join fz06
        on fz05.user_name = fz06.user_name
        where name = #{name}
</select>

mapper接口

List<Map<String,Object>> select_(String name);

调用

    @Test
    public void a1() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_x.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);
        List<Map<String,Object>> list = usermapper.select_("小明");
        for (Map<String,Object> map: list){
            System.out.println("=======");
            System.out.println(map.get("name"));
            System.out.println(map.get("user_name"));
            System.out.println(map.get("pass_word"));
            System.out.println("=======");
        }

        session.close();
        inputStream.close();
    }

运行结果

模糊查询

sql语法并将其参数化

<select id="selectmo" resultType="map">
        select * from fz03 where name like '%${str}%'
        <!-- 或者这样拼接 -->
        <!-- select * from fz03 where name like "%"#{str}"%" -->
</select>

定义param键

List<Map<String,Object>> selectmo(@Param("str")String str);

调用方法

@Test
    public void a9() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_text02.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);
        List<Map<String,Object>> list = usermapper.selectmo("明");
        System.out.println(list);

        inputStream.close();
        session.close();
    }