mybatis一对多查询

阿超

发布于 2023-03-07 20:38:56

1.4K0

发布于 2023-03-07 20:38:56

文章被收录于专栏：快乐阿超快乐阿超

想认识人，解剖自己就好——杜克罗

官方文档

书接上文：mybatis流式查询

分享mybatis的结果映射配置的一对多查询方式：

<?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.ruben.mapper.UserMapper">
    <resultMap id="userInfo" type="com.ruben.pojo.po.UserInfo">
        <collection property="userRoles"
                    column="userRole.userId=id"
                    javaType="arraylist"
                    select="com.ruben.mapper.UserRoleMapper.selectListCursor"
                    fetchType="lazy"/>
    </resultMap>

    <select id="selectPageCursor" resultMap="userInfo">
        select *
        from user_info
    </select>
</mapper>

然后是UserRoleMapper对应的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.ruben.mapper.UserRoleMapper">
    <resultMap id="userRole" type="com.ruben.pojo.po.UserRole">
        <result column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="role_id" property="roleId"/>
        <association property="role"
                     column="id = role_id"
                     javaType="com.ruben.pojo.po.RoleInfo"
                     select="com.ruben.mapper.RoleMapper.getById"
                     fetchType="lazy"/>
    </resultMap>

    <select id="selectListCursor" resultMap="userRole">
        select *
        from user_role
        <where>
            <if test="userRole.userId != null">
                AND user_id = #{userRole.userId}
            </if>
            <if test="userRole.roleId != null">
                AND role_id = #{userRole.roleId}
            </if>
        </where>
    </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="com.ruben.mapper.RoleMapper">
    <resultMap id="roleInfo" type="com.ruben.pojo.po.RoleInfo">
        <result column="id" property="id"/>
        <result column="role_name" property="roleName"/>
    </resultMap>

    <select id="getById" resultMap="roleInfo">
        select *
        from role_info
        where id = #{id}
    </select>
</mapper>

直接执行：

@Test
@SneakyThrows
void sqlSessionWay() {
    Assertions.assertAll(() -> {
        try (SqlSession session = sqlSessionFactory.openSession(); Cursor<UserInfo> userCursor = session.getMapper(UserMapper.class).selectPageCursor(new RowBounds(0, 5))) {
            StreamSupport.stream(userCursor.spliterator(), true).forEach(System.out::println);
        }
    });
}

输出：