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

最新推荐文章于 2023-02-24 21:59:43 发布

原创最新推荐文章于 2023-02-24 21:59:43 发布 · 403 阅读

0 ·

CC 4.0 BY-SA版权

Mybatis框架专栏收录该内容

10 篇文章

订阅专栏

本文详细解析了MyBatis框架中的一对一和一对多查询实现方法，包括如何通过resultMap配置关联映射，实现User与UserRole、Address之间的数据查询。通过具体的代码示例，展示了如何在UserMapper.xml文件中定义resultMap和select语句，以及在UserService.java中调用相应的方法。

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());
		}
	}