(1)案例:查询当前用户的角色信息
分析:首先一个用户是有多个角色,一个角色对应多个用户,在这个中间创建一张中间表,这张中间表对应的关系就是两个一对多。
1.逆向生成用户表,角色表,中间表
moke数据:
用户表:
personModel文件添加 生成get set:
private List<Role> roleList;
personMapper文件配置:
<resultMap id="selectRoleByPersonIdRM" type="person" extends="BaseResultMap">
<!--
collection:一对多的关联查询
property:"一中"多的属性名
ofType: 指的多的数据类型
-->
<collection property="roleList" ofType="zhou.model.Role">
<id column="role_id" jdbcType="INTEGER" property="roleId"/>
<result column="role_name" jdbcType="VARCHAR" property="roleName"/>
<result column="descprit" jdbcType="VARCHAR" property="descprit"/>
</collection>
</resultMap>
<select id="selectRoleByPersonId" parameterType="int" resultMap="selectRoleByPersonIdRM">
select * from person p , person_role pr, role r where
p.id = pr.pid and pr.rid = r.role_id and p.id = #{id}
</select>
(2)案例:查询当前角色对应的用户
roleModel文件添加 生成get set
private List<Person> personList;
roleMapper文件配置:
<resultMap id="selectPersonByRoleIdRM" type="zhou.model.Role" extends="BaseResultMap">
<collection property="personList" ofType="person">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="gender" jdbcType="INTEGER" property="gender"/>
<result column="address" jdbcType="VARCHAR" property="address"/>
<result column="birthday" jdbcType="DATE" property="birthday"/>
</collection>
</resultMap>
<select id="selectPersonByRoleId" parameterType="int" resultMap="selectPersonByRoleIdRM">
select * from person p , person_role pr, role r where
p.id = pr.pid and pr.rid = r.role_id and r.role_id = 1
</select>
测试类:
@Test
public void selectRoleByPersonId() {
SqlSession session = sessionFactory.openSession();
try {
Person person = session.selectOne("mappings.PersonMapper.selectRoleByPersonId", 1);
System.out.println(person.toString());
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Test
public void selectPersonByRoleIdRM() {
SqlSession session = sessionFactory.openSession();
try {
Role role = session.selectOne("mappings.RoleMapper.selectPersonByRoleId", 1);
System.out.println(role.toString());
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}