使用 <resultMap>
标签以及<association>
和<collection>
子标签,进行关联查询.
Pojo里面的User类
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private Date birthday;
private String sex;
private List<Role> roles;
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", address='" + address + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
'}';
}
}
Pojo里面的Role类
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
private List<User> users;
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public Integer getRoleId() {
return roleId;
}
public void setRoleId(Integer roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
Dao层的接口
List<Role> findAll();//查询所有
IRoleDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xia.dao.IRoleDao">
<!--别名只是给类取的,他这个resultMap是因为类里面的变量名与数据库字段名不一样-->
<!--定义Role表的resultMap-->
<resultMap id="roleMap" type="com.xia.domo.Role">
<id property="roleId" column="rid"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
<collection property="users" ofType="com.xia.domo.User" >
<id property="id" column="id"></id>
<result column="username" property="username" ></result>
<result column="address" property="address" ></result>
<result column="sex" property="sex" ></result>
<result column="birthday" property="birthday" ></result>
</collection>
</resultMap>
<!--查询所有角色-->
<select id="findAll" resultMap="roleMap">
<!-- select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id=ur.rid
left outer join user u on u.id=ur.uid-->
SELECT u.*,r.id AS rid,r.role_name,r.role_desc FROM USER u
LEFT OUTER JOIN user_role ur ON u.id=ur.uid
LEFT OUTER JOIN role r ON r.id=ur.rid
</select>
</mapper>
user表:
user_role表:
role表:
此处sql查询的结果为:
查询如有不懂参考:SQL 内连接(inner join)与外连接(left outer join 、right outer join )区别.
测试代码:
public class RoleTest {
private InputStream in;
private SqlSession sqlSession;
private IRoleDao iRoleDao;
@Before//用于在测试方法执行之前执行
public void init() throws IOException {
//1.读取配置文件,形成字节输入流
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.获取SqlSessionFactory对象
SqlSessionFactory factory= new SqlSessionFactoryBuilder().build(in);
//3.SqlSession对象
sqlSession=factory.openSession();
//4.获取dao的代理对象
iRoleDao = sqlSession.getMapper(IRoleDao.class);
//5.执行查询所有方法
}
@After//用于在测试方法执行之后执行
public void destroy()throws Exception{
//提交事务
// sqlSession.commit();
//6.释放资源
sqlSession.close();
in.close();
}
/**
* 测试查询所有角色,同时获取用户信息
* @throws IOException
*/
@Test
public void TestFindAll() throws IOException {
List<Role> roles = iRoleDao.findAll();
for (Role role:roles) {
System.out.println(role);
System.out.println(role.getUsers());
}
}
}
结果:
结果相符,很nice!