2022-1-21 MyBatis 查询进阶

一、查询结果一对一映射

eg.在查article顺便也把author对象的值查出来。

方法一：

为了让*里关于author的属性自动映射到author中去，需要这样写：

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;

方法二：resultMap

    <resultMap id="ArticleMap" type="org.kk.mybatis02.model.Article">
        <id property="id" column="id"/>
        <id property="title" column="id"/>
        <id property="context" column="context"/>
        <id property="author.id" column="author_id"/>
        <id property="author.name" column="author_name"/>
        <id property="author.age" column="author_age"/>
    </resultMap>

    <select id="getArticleById2" 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>

方法三：resultMap+association标签

    <resultMap id="ArticleMap2" type="org.kk.mybatis02.model.Article">
        <id property="id" column="id"/>
        <id property="title" column="id"/>
        <id property="context" column="context"/>
        <association property="author" javaType="org.kk.mybatis02.model.Author" columnPrefix="author_"><!--列名前缀，查出来的列名-->
            <id property="id" column="id"/>
            <id property="name" column="name"/>
            <id property="age" column="age"/>
        </association>
    </resultMap>

    <select id="getArticleById2" 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>

方法四：进一步优化
写个Base然后继承

    <resultMap id="BaseArticleMap" type="org.kk.mybatis02.model.Article">
        <id property="id" column="id"/>
        <id property="title" column="id"/>
        <id property="context" column="context"/>
    </resultMap>


    <resultMap id="ArticleMap2" type="org.kk.mybatis02.model.Article" extends="BaseArticleMap">

        <association property="author" javaType="org.kk.mybatis02.model.Author" columnPrefix="author_"><!--列名前缀，查出来的列名-->
            <id property="id" column="id"/>
            <id property="name" column="name"/>
            <id property="age" column="age"/>
        </association>
    </resultMap>

方法五：再次优化 association里面的resultMap选项指向AuthorMap去，注意要写全包名

<?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="org.kk.mybatis02.mapper.AuthorMapper">
    <resultMap id="AuthorMap" type="org.kk.mybatis02.model.Author">
        <id property="id" column="id"/>
        <id property="name" column="name"/>
        <id property="age" column="age"/>
    </resultMap>
</mapper>

    <resultMap id="ArticleMap2" type="org.kk.mybatis02.model.Article" extends="BaseArticleMap">

        <association property="author" javaType="org.kk.mybatis02.model.Author" columnPrefix="author_" resultMap="org.kk.mybatis02.mapper.AuthorMapper.AuthorMap"><!--列名前缀，查出来的列名-->
        </association>
    </resultMap>

二、查询结果一对一映射（懒加载）

    <resultMap id="ArticleMap4" type="org.kk.mybatis02.model.Article" extends="BaseArticleMap">
        <association property="author" javaType="org.kk.mybatis02.model.Author" select="org.kk.mybatis02.mapper.AuthorMapper.getAuthorById" column="{id=aid}" fetchType="lazy"/>
    </resultMap>

    <select id="getArticleById4" resultMap="ArticleMap4">
       select * from article where id=#{id};
    </select>

select 指定执行的方法
column 指定执行方法时要传递的字段参数 {uid=id} 那么uid是定义的的参数名

懒加载就是在调用了article中的author属性时候，再去加载，而不是一次全部加载进所有属性。

三、一对多查询

user表，role表，用来关联两个表的user-role表。
一个user可以对应n个role，一个role也可以对应n个user

select u.*,r.id as role_id,r.name as role_name from t_user u LEFT JOIN user_role ur on u.id=ur.uid LEFT JOIN role r on r.id=ur.rid 

UserMapper

<?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="org.kk.mybatis02.mapper.UserMapper">
    <resultMap id="BaseUserMap" type="org.kk.mybatis02.model.User">
        <id property="id" column="id"/>
        <result property="username" column="t_username"/>
        <result property="address" column="address"/>
    </resultMap>
    <resultMap id="UserMapWithRole" type="org.kk.mybatis02.model.User" extends="BaseUserMap">
        <!--association用来解决1对1 collection解决1对多-->
        <collection property="roles" ofType="org.kk.mybatis02.model.Role" columnPrefix="role_" resultMap="org.kk.mybatis02.mapper.RoleMapper.BaseRoleMap">
        </collection>

    </resultMap>




    <select id="getAllUsersWithRole" resultMap="UserMapWithRole">
        select u.*,r.id as role_id,r.name as role_name from t_user u LEFT JOIN user_role ur on u.id=ur.uid LEFT JOIN role r on r.id=ur.rid

    </select>
</mapper>

RoleMapper：

<?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="org.kk.mybatis02.mapper.RoleMapper">
    <resultMap id="BaseRoleMap" type="org.kk.mybatis02.model.Role">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>
</mapper>

四、一对多查询（懒加载）

UserMapper

    <resultMap id="UserMapWithRole2" type="org.kk.mybatis02.model.User" extends="BaseUserMap">
        <!--association用来解决1对1 collection解决1对多-->
        <collection property="roles" ofType="org.kk.mybatis02.model.Role" columnPrefix="role_" select="org.kk.mybatis02.mapper.RoleMapper.getRolesByUid" column="{uid=id}" fetchType="lazy">
        </collection>
    </resultMap>


    <select id="getAllUsersWithRole2" resultMap="UserMapWithRole2">
        select * from t_user

    </select>

RoleMapper

<?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="org.kk.mybatis02.mapper.RoleMapper">
    <resultMap id="BaseRoleMap" type="org.kk.mybatis02.model.Role">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>
    <select id="getRolesByUid" resultMap="BaseRoleMap">
        SELECT r.* FROM role r,user_role ur WHERE r.id=ur.uid AND ur.uid=#{uid}
    </select>
</mapper>

public interface RoleMapper {
    List<Role> getRolesByUid(Long uid);
}

先查User，在查User的过程中不去查role，需要role的信息了之后再查。