Mybatis的关联查询

一、关联查询

一对一查询关联

• 第一种resultType方法

package mybatis.pojo;

//订单关联用户的POJO
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;
	}

	@Override
	public String toString() {
		return "OrderUser [username=" + username + ", address=" + address + ", getId()=" + getId() + ", getUserId()="
				+ getUserId() + ", getNumber()=" + getNumber() + ", getCreatetime()=" + getCreatetime() + ", getNote()="
				+ getNote() + "]";
	}
}

 <!--一对一的关联查询-->
<select id="getOrderUser" resultType="orderuser">
  select o.id,o.user_id userId,o.number,o.createtime,o.note,u.username,u.address from `order` o left join `user` u on u.id=o.user_id
</select>

List<OrderUser> getOrderUser();

@Test
public void testgetOrderUser() {
	SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
	OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
	List<OrderUser> list = orderMapper.getOrderUser();
	for (OrderUser orderUser : list) {
		System.out.println(orderUser);
	}
	sqlSession.close();
}

• 第二种resultMap方法

package mybatis.pojo;

import java.util.Date;

public class Order {
	private Integer id;

	private Integer userId;

	private String number;

	private Date createtime;

	private String note;
	
	private User user;

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer 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 == null ? null : number.trim();
	}

	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 == null ? null : note.trim();
	}

	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime + ", note="
				+ note + "]";
	}
}

<!--一对一的resultMap关联查询-->
<resultMap type="order" id="order_user_map">
<!--配置主键，映射主键  -->
<id property="id" column="id"/>
<!--普通字段用result映射  -->
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<!--association用于配置一对一关系 property javaType:user的数据类型，支持别名 -->
<association property="user" javaType="mybatis.pojo.User">
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
</association>
</resultMap>

<select id="getOrderUserMap" resultMap="order_user_map">
  select o.id,o.user_id userId,o.number,o.createtime,o.note,u.username,u.address,u.birthday,u.sex from `order` o left join `user` u on u.id=o.user_id
</select>

package mybatis.mapper;

import java.util.List;

import mybatis.pojo.Order;
import mybatis.pojo.OrderUser;

//订单的持久化接口
public interface OrderMapper {
	List<Order> getOrderList();

//resultMap的使用
	List<Order> getOrderListMap();

//一对一关联resultType的使用
	List<OrderUser> getOrderUser();

//一对一关联resultMap的使用
	List<Order> getOrderUserMap();
}

@Test
public void testgetOrderUserMap() {
	SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
	OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
	List<Order> list = orderMapper.getOrderUserMap();
	for (Order order : list) {
		System.out.println(order);
		System.out.println("此订单的用户为"+order.getUser());
	}
	sqlSession.close();
}

一对多关联查询

package mybatis.pojo;

import java.util.Date;
import java.util.List;

public class User {
	private Integer id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	private List<Order> orders;

	public List<Order> getOrders() {
		return orders;
	}

	public void setOrders(List<Order> orders) {
		this.orders = orders;
	}

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

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

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
				+ address + "]";
	}

}

<!-- 一对多的映射 -->
<resultMap type="user" id="order_user_map">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<!--collection用户配置一对多关联， ofType配置哪个对象，支持别名 -->
<collection property="orders" ofType="mybatis.pojo.Order">
<!--配置主键，映射主键  -->
<id property="id" column="oId"/>
<!--普通字段用result映射  -->
<result property="userId" column="id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</collection>
</resultMap>

<select id="getUserOrderMap" resultMap="order_user_map">
  select u.id,u.username,u.birthday,u.sex,u.address,o.id oId,o.user_id userId,o.number,o.createtime,o.note from `user` u left join `order` o on u.id=o.user_id
</select>

// 演示一对多关联查询
List<User> getUserOrderMap();

@Test
public void testGetUserOrderMap() {
	SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<User> list = userMapper.getUserOrderMap();
	for (User user2 : list) {
		System.out.println(user2);
		for (Order order : user2.getOrders()) {
			System.out.println("此用户的订单有" +order);
		}
	}
	sqlSession.close();
}