Mybatis多对多关联查询
1、在perosn中配置和role一对多关系2、在person的映射文件PersonMapper.xml中配置和role一对多关系3、测试类
本例用peron和role的例子进行说明
1、在perosn中配置和role一对多关系
//指定和role之间一对多关系(和role是多对多关系但是从单端看是一对多 ，多对多是由两个一对多构成)
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
person和role是多对多关系但是从单端看是一对多 ，多对多本质是由两个一对多构成
2、在person的映射文件PersonMapper.xml中配置和role一对多关系
<resultMap id="selectRoleByPersonIdRm" type="com.model.Person" extends="BaseResultMap">
<collection property="roleList" ofType="com.model.Role">
<id column="role_id" property="roleId" jdbcType="INTEGER" />
<result column="role_name" property="roleName" jdbcType="VARCHAR" />
<result column="description" property="description" jdbcType="VARCHAR" />
</collection>
</resultMap>
<select id="selectRoleByPersonId" parameterType="int" resultMap="selectRoleByPersonIdRm">
select *from person p,person_role pr,role r where pr.person_id=p.PERSON_ID and
pr.role_id=r.role_id and p.PERSON_ID=#{identity }
</select>
3、测试类
@Test
public void testSelectPerson() {
SqlSession session = sf.openSession();
try {
Person person = session.selectOne("mapper.PersonMapper.selectRoleByPersonId", 1);
System.out.println(person);
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
session.close();
}
}
多对多本质就是两个一对多 此时还需要在role的一端指定和person一对多的关系，此时只需要在rolemapper.xml中配置就可以了
<resultMap id="selectPersonByRoleIdRm" type="com.model.Role" extends="BaseResultMap">
<collection property="personList" ofType="com.model.Person">
<id column="PERSON_ID" property="personId" jdbcType="INTEGER" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="GENDER" property="gender" jdbcType="INTEGER" />
<result column="PERSON_ADDR" property="personAddr" jdbcType="VARCHAR" />
<result column="BIRTHDAY" property="birthday" jdbcType="DATE" />
</collection>
</resultMap>
<select id="selectPersonByRoleId" parameterType="int" resultMap="selectPersonByRoleIdRm">
select *from role r,person_role pr,person p where pr.person_id=p.PERSON_ID
and pr.role_id=r.role_id and r.role_id=#{identity }
</select>
