Mybatis学习总结（四）---一对多映射

例子：承接上文：https://blog.csdn.net/aKuang_JH/article/details/82765030

创建house表，一个用户有多个房子，所以一个用户对应多条房屋信息。

以下sql查询出用户和所在部门信息以及其房屋信息

		SELECT
  		user.*,
  		dept.deptname,
  		dept.description,
  		house.hid,
  		house.housename,
  		house.address,
  		house.uid
  		FROM
  		user,
  		dept,
  		house
  		WHERE user.deptid = dept.did AND house.uid = user.id

结果：

待解决问题：id为1的用户，重复出现，因为他又多条房屋记录。

解决办法：获取信息的时候把多条房屋记录封装到HouseList里

代码：

House pojo类：

package cn.mybatis.model;

public class House {
    private Integer hid;

    private String housename;

    private String address;

    private Integer uid;

    public Integer getHid() {
        return hid;
    }

    public void setHid(Integer hid) {
        this.hid = hid;
    }

    public String getHousename() {
        return housename;
    }

    public void setHousename(String housename) {
        this.housename = housename == null ? null : housename.trim();
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address == null ? null : address.trim();
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }
}

User pojo类中增加List<House> houses属性

package cn.mybatis.model;

import java.util.List;

public class User {
    private Integer id;

    private String username;

    private String password;

    private Integer age;

    private Integer deptid;

    private Dept dept;
    
    private List<House> houses;
    
    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 == null ? null : username.trim();
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getDeptid() {
        return deptid;
    }

    public void setDeptid(Integer deptid) {
        this.deptid = deptid;
    }

    
	public Dept getDept() {
		return dept;
	}

	public void setDept(Dept dept) {
		this.dept = dept;
	}

	public List<House> getHouses() {
		return houses;
	}

	public void setHouses(List<House> houses) {
		this.houses = houses;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password="
				+ password + ", age=" + age + ", deptid=" + deptid + "]";
	}
    
    
}

1.编写UserMapper.xml，新建一个resultMap2 id为BaseResultMap2，继承BaseResultMap

  <resultMap type="cn.mybatis.model.User" id="BaseResultMap">
  	<id column="id" property="id"/>
  	<result column="username" property="username"/>
  	<result column="password" property="password"/>
  	<result column="age" property="age"/>
  	<result column="deptid" property="deptid"/>
  	
  	<!-- 配置映射的关联部门信息 -->
  	<!-- association用于映射关联查询单个对象的信息 
  	property:要将关联查询的用户信息映射到表中对应属性
  	-->
  	<association property="dept" javaType="cn.mybatis.model.Dept">
  		<!-- id:关联查询的部门的唯一标志 -->
  		<!-- column:指定唯一标志部门的列 -->
  		<!-- javaTypey:映射到dept对应属性 -->
  		<id column="did" property="did"/>
  		<result column="deptname" property="deptname"/>
  		<result column="description" property="description"/>
  	</association>
  	
  </resultMap>
 
 
  <resultMap type="cn.mybatis.model.User" id="BaseResultMap2" extends="BaseResultMap">
  	<!-- 使用extends继承,不用在此中配置用户信息和部门信息的映射 -->
  	
  	<!-- 一个用户关联查询出多条房屋信息 -->	
  	<!-- collection：对关联查询出的多条信息映射到集合对象中 -->
  	<!-- property：将关联查询的信息映射到cn.mybatis.model.User对应属性 -->
  	<!-- ofType:指定映射到集合属性中pojo的类型 -->
  	<collection property="houses" ofType="cn.mybatis.model.House">
  		<!-- id:房屋信息唯一标识 -->
  		<id column="hid" property="hid"/>
  		<result column="housename" property="housename"/>
  		<result column="address" property="address"/>
  		<result column="uid" property="uid"/>
  	</collection>
  		
  		
  </resultMap>

2.编写UserMapper.xml,增加statement---findAllByResultMap2

<select id="findAllByResultMap2" resultMap="BaseResultMap2">
  		SELECT
  		user.*,
  		dept.deptname,
  		dept.description,
  		house.hid,
  		house.housename,
  		house.address,
  		house.uid
  		FROM
  		user,
  		dept,
  		house
  		WHERE user.deptid = dept.did AND house.uid = user.id
  </select>

3.编写UserMapper接口，增加findAllByResultMap2方法

//	查询用户、部门和用户房屋信息使用resultmap
	public List<User> findAllByResultMap2()throws Exception;

 

4.编写测试类方法

	@Test
	public void testFindAllByResultMap2() throws Exception {
		
		SqlSession sqlSession =  sqlSessionFactory.openSession();
		
//		创建usermapper对象，mybatis自动生成mapper代理对象
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
//		调用UserMapper的方法
		List<User> userList = userMapper.findAllByResultMap2();
		
		System.out.println(userList);
		
		sqlSession.close();
	}

打断点，可看到userList中有对应结果

 

mybatiss使用resultMap的collectiond对关联查询多条记录映射到一个list集合属性中。

如果使用resultType实现

需要将房屋信息映射到用户中的houselists中，自己处理，双重循环遍历去重，将房屋信息存储在houselists。