mybatis_SQL映射(3)

文章摘录自：http://blog.csdn.net/y172158950/article/details/17304645

1. 表关联

a) 嵌套查询(传说中的1+N问题)

<resultMap id="userResult3" type="User">
    <association property="role" column="role_id" javaType="Role" select="selectRole"/>
</resultMap>

<select id="selectUser2" parameterType="int" resultMap="userResult3">
    select _id id, _name name, _password password, _role_id role_id from _user where _id = #{id};
</select>
<select id="selectRole" parameterType="int" resultType="Role">
    select _id id, _name name, _grade grade from _role where _id = #{id};
</select>

i. 是role_id[sleect结果集column别名],不是_role_id[_user表column]。

b) 嵌套结果

<resultMap id="userResult" type="User">
    <constructor>
        <idArg column="u_id" javaType="int"/>
    </constructor>
    <result property="name" column="u_name" />
        <result property="password" column="u_password" />
    <association property="role" column="r_id" javaType="Role">
        <id property="id" column="r_id"/>
        <result property="name" column="r_name"/>
        <result property="grade" column="r_grade"/>
    </association>
</resultMap>

<select id="selectUser" parameterType="int" resultMap="userResult">
    select u._id u_id, u._name u_name, u._password u_password,
    r._id r_id, r._name r_name, r._grade r_grade from _user u
    left join _role r on u._role_id=r._id where u._id =#{id};
</select>

i. 别名的意义：每个字段名称必须唯一，重名的情况会照成错误的返回。[太土了]

ii.蛋疼的构造方法：<constructor>定义了javabean的构造方法

public User(Integer id) {  //此处必须写Integer,写int报错
    this.id = id;
}

iii. 另外一种写法：resultMap的重用

<resultMap id="userResult" type="User">
    <constructor>
        <idArg column="u_id" javaType="int"/>
    </constructor>
    <result property="name" column="u_name" />
    <result property="password" column="u_password" />
    <association property="role" column="r_id" resultMap="roleResult">
    </association>
</resultMap>

<resultMap id="roleResult" type="Role">
    <id property="id" column="r_id" />
    <result property="name" column="r_name"/>
    <result property="grade" column="r_grade"/>
</resultMap>

2. 集合的用法

<select id="selectUser4" parameterType="int" resultType="User">
     select _id id, _name name, _password password, _role_id role_id from _user where _role_id = #{id};
</select>
<select id="selectRole4" parameterType="int" resultMap="selectRole4">
     select _id id, _name name, _grade grade from _role where _id = #{id};
</select>
<resultMap id="selectRole4" type="Role">
     <collection property="users" column="id" ofType="User" select="selectUser4"/>
</resultMap>

i. 仍要注意，resultMap中column都是查询结果集的别名