Mybatis—一对一和一对多查询

重新记录mybatis表查询

Mybatis的查询

一对一查询

连接词：association

数据表：作者表author 文章表article

描述：一个作者发表一篇文章

1. xml:

<mapper namespace="com.liwang.mybatis02.mapper.AuthorMapper">

<resultMap id="AuthorMap" type="com.liwang.mybatis02.model.Author">  
    <id property="id" column="id"/>  
    <result property="name" column="name"/>  
    <result property="age" column="age"/>  
</resultMap>  

<select id="getAuthorById" resultMap="AuthorMap">  
    select * from author where id = #{id};  
</select>  

</mapper>

<mapper namespace="com.liwang.mybatis02.mapper.ArticleMapper">

<resultMap id="BaseArticleMap" type="com.liwang.mybatis02.model.Article">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="content" property="content"/>
</resultMap>

<resultMap id="ArticleMap" type="com.liwang.mybatis02.model.Article" extends="BaseArticleMap">
        <association property="author" javaType="com.liwang.mybatis02.model.Author" columnPrefix="author_"
        resultMap="com.liwang.mybatis02.mapper.AuthorMapper.AuthorMap">
<!--            <id property="id" column="id"/>-->
<!--            <result property="name" column="name"/>-->
<!--            <result property="age" column="age"/> 替换成上面的 resultMap中的AuthorMap-->
        </association>
</resultMap>

<select id="getArticleById3" resultMap="ArticleMap">
        SELECT a.*, au.id as author_id,au.name as author_name, au.age as author_age
        from article a, author au
        WHERE a.aid = au.id and a.id = #{id};
</select>

</mapper>

2. 结果

#

一对多查询

连接词：collection

数据表：用户表user 角色表role 中间表user_role

描述：一个用户可以有多个角色，一个角色可以对应多个用户

1. xml:

<mapper namespace="com.liwang.mybatis02.mapper.RoleMapper">  

    <resultMap id="BaseRoleMap" type="com.liwang.mybatis02.model.Role">  
        <id property="id" column="id"/>  
        <result property="name" column="name"/>  
    </resultMap> 

</mapper>

<mapper namespace="com.liwang.mybatis02.mapper.UserMapper">

    <resultMap id="BaseUserMap" type="com.liwang.mybatis02.model.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
    </resultMap>

    <resultMap id="UserMapWithRole" type="com.liwang.mybatis02.model.User" extends="BaseUserMap">
        <collection property="roles" ofType="com.liwang.mybatis02.model.Role" columnPrefix="role_"
                    resultMap="com.liwang.mybatis02.mapper.RoleMapper.BaseRoleMap">
        </collection>
    </resultMap>

    <select id="getUserWithRole" resultMap="UserMapWithRole">
        select u.*,r.id as role_id,r.name as role_name
        from user as u
        left join user_role ur on u.id = ur.uid
        left join role r on ur.rid = r.id;
    </select>
</mapper>

2. mapper:

public interface UserMapper {
    // 获取用户信息及角色信息
    List getUserWithRole();
}

3. test

@Test
public void getUserWithRole() {
    List<User> users = userMapper.getUserWithRole();
        for (User user : users) {
            System.out.println("user = " + user);
        }
}

4. 结果

可以看看我的学习——基于Hexo的个人博客： 网站：https://liwangc.gitee.io/

—————————————————————————

作为初学者，很多知识都没有掌握，见谅，如有错误请指出，以期进步，感谢！。后续有新的学习，继续补充上来。