MyBatis 嵌套查询

1.association：一对一嵌套查询

第一种方式：执行效率比较低；会查询2次数据库、第一次先查一次role；第二次查roleone；所以引起效率较低；

property：映射到列结果的字段或属性；

column：数据库的类名,或重命名的列标签;

select：映射语句的 ID,可以加载这个属性映射需要的复杂类型；

javaType：Java 类的完全限定名,或一个类型别名(参考上面内建类型别名的列 表) 。如果你映射到一个 JavaBean,MyBatis 通常可以断定类型；

RoleMapper.xml

<resultMap id="resultMapAss" type="com.study.demo.web.dto.RoleTestDto" extends="BaseResultMap">

    <association property="roleOne" column="id" select="com.study.demo.web.mapper.RoleOneMapper.findByRoleId" />
</resultMap>

<select id="findByRoleAss" resultMap="resultMapAss">
    select * from am_role where id=#{id} and is_del = 1
</select>

<select id="findByRoleAss" resultMap="resultMapAss">
    select * from am_role where id=#{id} and is_del = 1
</select>

RoleOneMapper.xml 测试

<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.study.demo.web.model.RoleOne">
    <id column="id" property="id" />
    <result column="role_id" property="roleId" />
    <result column="name" property="name" />
    <result column="tests" property="tests" />
</resultMap>

<select id="findByRoleId" resultMap="BaseResultMap">
    SELECT * from role_one where role_id = #{roleId}
</select>

 

第二种方式：效率较高：一次查询出嵌套关联关系；

RoleMapper.xml

<resultMap id="resultMapAssTwo" type="com.study.demo.web.dto.RoleTestDto" extends="BaseResultMap">

    <association property="roleOne" javaType="com.study.demo.web.model.RoleOne">
        <id column="id" property="id" />
        <result column="role_id" property="roleId" />
        <result column="name" property="name" />
        <result column="tests" property="tests" />
    </association>
</resultMap>

<select id="findByRoleAssTwo" resultMap="resultMapAssTwo">
  SELECT
        role.*,
        one.id id_one,
        one.role_id,
        one. NAME name_one,
        one.tests
    FROM
        am_role role
    LEFT JOIN role_one one ON role.id = one.role_id
    WHERE
        role.id = #{id}
</select>

 

2.collection：集合嵌套循环

第一种方式：查询效率较低、应该要查询2次数据库；先查询role表再查permission；

property：映射到列结果的字段或属性；    

column：来自数据库的类名,或重命名的列标签；

select：另外一个映射语句的 ID,可以加载这个属性映射需要的复杂类型；

javaType：一个 Java 类的完全限定名,或一个类型别名(参考上面内建类型别名的列 表) 。如果你映射到一个 JavaBean,MyBatis 通常可以断定类型；

ofType： Java 类的完全限定名；

RoleMapper.xml

<!-- 1对多关联关系 -->
<resultMap id="roleToList" type="com.study.demo.web.dto.RoleDto" extends="BaseResultMap">
    <collection property="permissionArrayList" column="id" select="com.study.demo.web.mapper.PermissionMapper.findPermissionByRolerId"/>
</resultMap>

<select id="findByRoleId" resultMap="roleToList">
    select * from am_role where id=#{id} and is_del = 1
</select>

PermissionMapper.xml

<select id="findPermissionByRolerId" resultType="com.study.demo.web.model.Permission">
    SELECT
        per.*
    FROM
        am_permission per
    LEFT JOIN am_role_permission rper ON per.id = rper.permission_id
    WHERE
        rper.role_id = #{id}
</select>

 

第二种方式：效率高，一次查询;

<resultMap id="roleToListTwo" type="com.study.demo.web.dto.RoleTwoDto" extends="BaseResultMap">

    <collection property="permissionArrayList" javaType="list" ofType="com.study.demo.web.model.Permission">
        <id column="id_one" property="id"/>
        <result column="system_id" property="systemId"/>
        <result column="pid" property="pid"/>
        <result column="name_one" property="name"/>
        <result column="type" property="type"/>
        <result column="permission_value" property="permissionValue"/>
        <result column="uri" property="uri"/>
        <result column="icon" property="icon"/>
        <result column="status" property="status"/>
        <result column="sequence" property="sequence"/>
        <result column="is_del_one" property="isDel"/>
        <result column="create_time_one" property="createTime"/>
        <result column="update_time_one" property="updateTime"/>
    </collection>
</resultMap>

<select id="findByRoleTwo" resultMap="roleToListTwo">
    SELECT
        role.*,
        per.id id_one,
        per.pid pid,
        per.name name_one,
        per.type type,
        per.permission_value permission_value,
        per.uri uri,
        per.icon icon,
        per.status status_one,
        per.sequence sequence_one,
        per.is_del is_del_one,
        per.create_time crete_time_one,
        per.update_time update_time_one
    FROM
        am_role role
    LEFT JOIN am_role_permission rper ON role.id = rper.role_id
    LEFT JOIN am_permission per ON per.id = rper.permission_id
    WHERE
        role.id = #{id}
</select>

 

 

 

转载于:https://my.oschina.net/studyjing/blog/1542431