mybatis学习笔记（一对一查询&一对多查询）_mybatis 一对多查询学习建议-CSDN博客

本文链接：https://blog.csdn.net/JayBillions/article/details/81517390

（一）以订单为中心一对一查询

订单表：

用户表：

/* （a）以订单为中心时，一个订单只对应一个用户，一对一

* 若果在收集结果集时只想返回一个对象，那么此时在订单对象中创建

* 一个用户对象

* （b）以用户为中心时，一个用户可以对应多个订单，一对多

* 如果在收集结果集时只想返回一个对象，那么此时在用户对象中应该增加

* 一个list集合

* inner join：内关联，查出左右两张表公共关联的部分

* left join：左关联查询，以左边的表为中心，左边表的字段全查出来，

* 右边的表只查关联的的字段

* right join：与左关联相反

* full join：左右两张表全查出来，不论是否关联

* */

（1）Orders.java 

package cn.shu.pojo;

import java.io.Serializable;
import java.util.Date;

public class Orders implements Serializable{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer id;
	private Integer userId;
	private String number;
	private Date createtime;
	private String note;
	
	
	/*以订单为中心时，一个订单只对应一个用户，一对一
	 * 
	 * 若果在收集结果集时只想返回一个对象，那么此时在订单对象中创建
	 * 一个用户对象
	
	
	//附加对象
	
	private User user;
/*setters and getters*/

(2)User.java

package cn.shu.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{
	//实现序列化接口
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private Integer id;
	private String username;
	private String sex;
	private Date birthday;
	private String address;
/*getters and setters*/

(3)sqlMapperConfig.xml

<mappers>
	<package name="cn.shu.mapper"/>   
</mappers>

(4)OrderMapper.java

package cn.shu.mapper;

import java.util.List;

import cn.shu.pojo.Orders;

public interface OrderMapper {
	
	
	//一对一关联查询，以订单为中心关联用户
	public List<Orders> selectOrders();
	
}

(5)OrderMapper.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="cn.shu.mapper.OrderMapper">
	
	<!-- 一对一关联查询，以订单为中心关联用户 -->
	<resultMap type="Orders" id="order">
	    <id column="id" property="id"/>
		<!-- Orders中与数据库表无法对应的映射 -->
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		
		<!-- 映射Orders类中的User对象，一对一映射 -->
		<association property="user" javaType="cn.shu.pojo.User">
			<id column="id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="birthday" property="birthday"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	<select id="selectOrders" resultMap="order"> 
		SELECT 
		o.id,
		o.user_id,
		o.number,
		o.createtime,
		o.note,
		u.id,
		u.username,
		u.sex,
		u.birthday,
		u.address 
        FROM  orders o 
        LEFT JOIN USER u 
        on o.user_id = u.id 
	</select>	
</mapper>

(6)测试类

//一对一查询
		@Test
		public void testOrdersList() throws Exception {
			// 加载核心配置文件
			String resource = "sqlMapConfig.xml";
			InputStream in = Resources.getResourceAsStream(resource);

			// 创建SqlSessionFactory
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
			// 创建SqlSession
			SqlSession sqlSession = sqlSessionFactory.openSession();
			
			//sqlSession帮助生成实现类（给接口,接口遵循四大原则）
			OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
			List<Orders> selectOrdersList = orderMapper.selectOrders();
			for (Orders orders : selectOrdersList) {
				System.out.println(orders);
			}
		}

结果：

（二）以用户为中心一对多查询

(1)User.java

package cn.shu.pojo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable{
	//实现序列化接口
	
	private static final long serialVersionUID = 1L;
	
	private Integer id;
	private String username;
	private String sex;
	private Date birthday;
	private String address;
	
	//附加对象
	private List<Orders> ordersList;
/*getters and setters*/

(2)UserMapper.java

//一对多查询
	public List<User> findListOrdersByUser();

(3)UserMapper.xml
<mapper namespace="cn.shu.mapper.UserMapper">

<!-- 一对多查询，以用户为中心 -->
	
	<resultMap type="User" id="user">
	    <id column="id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="birthday" property="birthday"/>
			<result column="address" property="address"/>
			<!-- 一个用户对应多个订单（集合） ofType泛型-->
		<collection property="ordersList" ofType="cn.shu.pojo.Orders">
			<id column="id" property="id"/>
			<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		</collection>
	</resultMap>
	<select id="findListOrdersByUser" resultMap="user"> 
		SELECT 
		o.id,
		o.user_id,
		o.number,
		o.createtime,
		o.note,
		u.id,
		u.username,
		u.sex,
		u.birthday,
		u.address 
        FROM   user u
        LEFT JOIN  orders o
        on o.user_id = u.id 
	</select>	
	
</mapper>

（4）sqlMapperConfig.xml

<package name="cn.shu.mapper"/>

(5)测试类

//一对多查询
@Test
public void test7() throws Exception {
// 加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);

// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
					
					
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> findListOrdersByUser = userMapper.findListOrdersByUser();
for (User user : findListOrdersByUser) {
						
      System.out.println(user);
}
					
}

控制台输出结果：