4.一对一查询和一对多查询

1.一对一查询

property代表pojo中的属性名
column代表查询出来的列名

User.java

UserRole.java

UserDao.java

//一对一
public List<User> getAllUsers3();

UserMapper.xml

	 <!-- 一对一查询关联映射(常用) -->
	 <resultMap type="User" id="userMap2">
	 	<id property="id" column="id"/>
	 	<result property="userCode" column="userCode"/>
	 	<result property="userName" column="userName"/>
	 	<!-- 应用UserRole的映射 -->
	 	<association property="userRole2" javaType="UserRole" resultMap="userRoleMap"></association>
	 </resultMap> 
	 <resultMap type="UserRole" id="userRoleMap">
	 	<id property="id" column="roleId"/>
	 	<result property="roleCode" column="roleCode"/>
	 	<result property="roleName" column="roleName"/>
	 </resultMap>
	 <select id="getAllUsers3" resultMap="userMap2">
	 	select u.id,userCode,userName,userPassword,
	 			r.id as roleId,roleCode,roleName
	 	from smbms_user u
	 	inner join smbms_role r on u.userRole=r.id
	 </select>

UserService.java

	//一对一
	public List<User> getAllUsers3(){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getAllUsers3();
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}

测试类

	//一对一
	public void getAllusers3() {
		List<User> lists = us.getAllUsers3();
		for (User user : lists) {
			//user.getUserRole2().getRoleCode()   先获取role对象,再获取其中的属性
			System.out.println(user.getId()+"\t"+user.getUserName()+"\t"+user.getUserRole2().getRoleCode()+
					"\t"+user.getUserRole2().getRoleName());
		}
	}

---

2.一对多查询

UserDao.java

	//一对多
	public User getAllUsers4(int uid);

UserMapper.xml

	 <resultMap type="User" id="userMap3">
	 	<id property="id" column="uid"/>
	 	<result property="userName" column="userName"/>
	 	<result property="userCode" column="userCode"/>
	 	<!-- 多个地址映射 -->
	 	<collection property="addresses" ofType="Address"
	 	 resultMap="addressMap"></collection>
	 </resultMap>
	  <!-- 一对多查询关联映射(常用) Address-->
	 <resultMap type="Address" id="addressMap">
	 	<id property="id" column="aid"/>
	 	<result property="addressDesc" column="addressDesc"/>
	 	<result property="contact" column="contact"/>
	 </resultMap>
	 
	 <select id="getAllUsers4" parameterType="int" resultMap="userMap3">
	 	select u.id as uid,userName,userCode,a.contact,a.addressDesc
	 	from smbms_user u
	 	left join smbms_address a on a.userId=u.id
	 	where u.id=#{uid}
	 </select>

UserService.java

	//一对多
	public User getAllUsers4(int uid){
		SqlSession session = MyBatisUtil.getSqlSession();
		User user = session.getMapper(UserDao.class).getAllUsers4(uid);
		MyBatisUtil.closeSqlSession(session);
		return user;
	}

测试类

	//一对多
	public void showAllUsers4() {
		User user = us.getAllUsers4(1);
		for (Address ad : user.getAddresses()) {
			System.out.println(user.getUserCode()+"\t"+user.getUserName()+
					ad.getContact()+"\t"+ad.getAddressDesc());
		}
	}