mybatis嵌套查询报错TooManyResultsException

Question

//com.arnold.rbac.dao.SysRoleMapper#getRoleWithPermission
SysRole getRoleWithPermission(Integer id);

public class SysRole {
    //省略其他
    private List<SysPermission> permission;
}

dao方法报错

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3

<resultMap id="RoleWithPermissionResultMap" type="com.arnold.rbac.model.SysRole">
    <id column="sr_id" jdbcType="INTEGER" property="id"/>
    <result column="role_name" jdbcType="VARCHAR" property="roleName"/>
    <result column="status" jdbcType="VARCHAR" property="status"/>
    <result column="remark" jdbcType="VARCHAR" property="remark"/>
    <result column="creater" jdbcType="VARCHAR" property="creater"/>
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="editor" jdbcType="VARCHAR" property="editor"/>
    <result column="editor_time" jdbcType="TIMESTAMP" property="editorTime"/>
    <collection property="permission" javaType="java.util.ArrayList" column="permissionId" ofType="com.arnold.rbac.model.SysPermission" select="com.arnold.rbac.dao.SysPermissionMapper.selectByPrimaryKey">

    </collection>
</resultMap>
    
    
<select id="getRoleWithPermission" resultMap="RoleWithPermissionResultMap">
    select
    sr.id as sr_id, role_name, status, remark, creater, create_time, editor, editor_time,
    srp.permission_id as permissionId
    from sys_role sr
    LEFT JOIN sys_role_permission srp ON srp.role_id = sr.id
    where sr.id = #{id,jdbcType=INTEGER}
</select>

<mapper namespace="com.arnold.rbac.dao.SysPermissionMapper">
    <resultMap id="BaseResultMap" type="com.arnold.rbac.model.SysPermission">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="permission_name" jdbcType="VARCHAR" property="permissionName"/>
        <result column="permission_str" jdbcType="VARCHAR" property="permissionStr"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="is_del" jdbcType="BIT" property="isDel"/>
        <result column="creater" jdbcType="VARCHAR" property="creater"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="editor" jdbcType="VARCHAR" property="editor"/>
        <result column="edit_time" jdbcType="TIMESTAMP" property="editTime"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, permission_name, permission_str, status, is_del, creater, create_time, editor,
        edit_time
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from sys_permission
        where id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

如果将dao变为
List<SysRole> getRoleWithPermission(Integer id);
则返回3条记录，每个role对应一个permission。

Answer 1

因为确实sql语句查出3条记录
如果使用<collection select="">来查找的话，只能传入一个值。所以结果只能是一条记录。
如果使用<collection resultMap> 的话，mybatis会将查出的多条结果按主键id合并到collection。

<resultMap id="RoleWithPermissionResultMap" type="com.arnold.rbac.model.SysRole">
        <id column="sr_id" jdbcType="INTEGER" property="id"/>
        <result column="role_name" jdbcType="VARCHAR" property="roleName"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="creater" jdbcType="VARCHAR" property="creater"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="editor" jdbcType="VARCHAR" property="editor"/>
        <result column="editor_time" jdbcType="TIMESTAMP" property="editorTime"/>
        <collection property="permissionIds" javaType="java.util.ArrayList" ofType="java.lang.Integer">
            <result column="permissionId" />
        </collection>
        <collection property="permission" javaType="java.util.ArrayList" columnPrefix="sp_" ofType="com.arnold.rbac.model.SysPermission" resultMap="com.arnold.rbac.dao.SysPermissionMapper.BaseResultMap">
        </collection>
    </resultMap>