[Mybatis] Mybatis 关联查询之一对一查询

 

一、背景

订单：用户 = 1：n

用户：订单 = n：1

二、一对一查询

需求 ：查询所有订单信息，关联查询下单用户信息

注意：一个订单只有由一个用户下，但是一个用户可以下多个订单。所以这是一个一对一的查询。

首先我们准备两个POJO类

User.java

public class User {

	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	public List<Order> getOrder() {
		return order;
	}
	public void setOrder(List<Order> order) {
		this.order = order;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
	public User() {
	}
	public User(int id, String username, String sex) {
		super();
		this.id = id;
		this.username = username;
		this.sex = sex;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
				+ address + "]";
	}

}

Order.java 

public class Order {

	// 订单id
	private int id;
	// 用户id
	private Integer userId;
	// 订单号
	private String number;
	// 订单创建时间
	private Date createtime;
	// 备注
	private String note;
	
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Integer getUserId() {
		return userId;
	}
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public Date getCreatetime() {
		return createtime;
	}
	public void setCreatetime(Date createtime) {
		this.createtime = createtime;
	}
	public String getNote() {
		return note;
	}
	public void setNote(String note) {
		this.note = note;
	}
	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
				+ ", note=" + note + "]";
	}
}

完成此需求我们有以下两种方法。

方法一：使用resultType

我们可以改造订单类，让它包含订单信息和用户信息

 OrderUser.java

public class OrderUser extends Order {

	private String username;
	private String address;
	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;
	}
}

接下来我们把Mapper.xml 和Mapper接口文件完成

	<select id="findOrderAll" resultType="OrderUser">
		SELECT
			o.*,
			u.id uid,
			u.username,
			u.address
			FROM
			`orders` o
			LEFT JOIN `user` u ON o.user_id = u.id

	</select>

 

//查询所有订单信息，关联查询下单用户信息
public List<OrderUser> findOrderAll();

测试

@Test
	public void findOrderAll() throws Exception {
		
		// 创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
		
		SqlSession session = sqlSessionFactory.openSession();
		OrderMapper orderMapper = session.getMapper(OrderMapper.class);
		List<OrderUser> list = orderMapper.findOrderAll();
		for (OrderUser orderUser : list) {
			System.out.println(orderUser.getNumber() + orderUser.getUsername());
		}
	}

运行结果

此处 address 和 username 都被封装进去，说明查询成功。

第二种方法：使用resultMap

改造User.java 和 Order.java

 接下来我们把 Mapper.xml 和 Mapper 接口文件完成

<!-- //查询所有订单信息，关联查询下单用户信息 -->
	<!-- 以订单表为中心，关联用户-->
	<!-- 
		property：属性名
		column：数据库字段名
		id：声明主键
		result：普通属性
	-->
	<resultMap type="Order" id="OrderResultMap">
		<id property="id" column="id" />
		<result property="userId" column="user_id" />
		<result property="number" column="number" />
		<result property="createtime" column="createtime" />
		<result property="note" column="note" />
		<!-- 一对一-->
		<association property="user" javaType="User">
		    <!-- 
				id:声明主键，column:数据库字段名，此处使用了别名	
			-->
			<id property="id" column="uid"/>
			<result property="username" column="username"/>
			<result property="address" column="address"/>
		</association>
	</resultMap>
	<select id="findOrderAll" resultMap="OrderResultMap">
		SELECT
			o.*,
			u.id uid,
			u.username,
			u.address
			FROM
			`orders` o
			LEFT JOIN `user` u ON o.user_id = u.id
	</select>

 

public List<Order> findOrderAll();

测试

@Test
	public void findOrderAll() throws Exception {
		// 创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
		
		SqlSession session = sqlSessionFactory.openSession();
		OrderMapper orderMapper = session.getMapper(OrderMapper.class);
		List<Order> list = orderMapper.findOrderAll();
		for (Order order : list) {
			System.out.println(order.getNumber() +order.getUser().getAddress() );
		}
	}

 运行结果

在Order 对象中封装了 User 对象，查询成功。