mybatis_day05_关联映射

一、一对一

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

1.1 使用resultType

新建一个POJO对象,里面存储要查询的属性

package com.itheima.pojo;

public class OrderUser extends Order {

	private String username;

	private String addredss;

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getAddredss() {
		return addredss;
	}

	public void setAddredss(String addredss) {
		this.addredss = addredss;
	}

	@Override
	public String toString() {
		return "OrderUser [username=" + username + ", addredss=" + addredss + "]";
	}

}

package com.itheima.mapper;

import java.util.List;

import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;

public interface OrderMapper {
	
	/**
	 * @MethodName:findOrderUserList
	 * @Description:
	 * @return
	 */
	public List<OrderUser> findOrderUserList();
	
}

 

<?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="com.itheima.mapper.OrderMapper">
	
	<select id="findOrderUserList" resultType="com.itheima.pojo.OrderUser">
		SELECT
			o.id,
			o.user_id as userId,
			o.number,
			o.createtime,
			o.note,
			u.username,
			u.address
		FROM
			orders o
		LEFT JOIN USER u ON u.id = o.user_id;
	</select>

</mapper>

package com.itheima.test;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itheima.mapper.OrderMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;

public class OrderMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void init() throws Exception {
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		this.sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void findOrderUserList() throws Exception {
		// 获取sqlSession
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		// 获取OrderMapper
		OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

		List<OrderUser> list = mapper.findOrderUserList();

		for (OrderUser orderUser : list) {
			System.err.println(orderUser);
		}
	}
}

1.2 使用resultMap

Order.java

package com.itheima.pojo;

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

public class Order implements Serializable {

	private static final long serialVersionUID = 1L;
	// 订单id
	private Integer id;
	// 用户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 + ", user=" + user + "]";
	}

}
}

 OrderMapper.java

package com.itheima.mapper;

import java.util.List;

import com.itheima.pojo.Order;

public interface OrderMapper {

	/**
	 * @MethodName:findOrderUserList
	 * @Description:
	 * @return
	 */
	public List<Order> findOrderUserList();
	
}

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="com.itheima.mapper.OrderMapper">

	<resultMap type="com.itheima.pojo.Order" id="order_user_map">
		<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"/>
		
		<!-- 
			association:一对一关联映射
				property:主表中附表对应的POJO属性名
				javaType:POJO全限定类名
		 -->
		<association property="user" javaType="com.itheima.pojo.User">
			<!-- 
				id:关联映射的ID
					column:查询列表字段的外键名称
					property:对应着POJO对象中主键的属性名
			 -->
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	
	<select id="findOrderUserList" resultMap="order_user_map">
		SELECT
			o.id,
			o.user_id,
			o.number,
			o.createtime,
			o.note,
			u.username,
			u.address
		FROM
			orders o
		LEFT JOIN USER u ON u.id = o.user_id;
	</select>

</mapper>

 测试类

package com.itheima.test;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itheima.mapper.OrderMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;

public class OrderMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void init() throws Exception {
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		this.sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void findOrderUserList() throws Exception {
		// 获取sqlSession
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		// 获取OrderMapper
		OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

		List<Order> list = mapper.findOrderUserList();

		for (Order order : list) {
			System.err.println(order);
		}
	}
}

二、一对多

查询所有用户信息及用户关联的订单信息

package com.itheima.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<Order> 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 + ", orders=" + orders + "]";
	}

}

<?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">
<!-- namespace：命名空间,作用:1.用于隔离sql 2.使用动态代理开发DAO,namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.itheima.mapper.UserMapper">

	<resultMap type="com.itheima.pojo.User" id="user_order_map">
		<id property="id" column="id" />
		<result property="username" column="username" />
		<result property="birthday" column="birthday" />
		<result property="sex" column="sex" />
		<result property="address" column="address" />

		<!-- 一对多关联映射 -->
		<collection property="orders" ofType="com.itheima.pojo.Order">
			<id column="oid" property="id"/>
			<result column="number" property="number"/>
			<result column="createtime" property="createtime"/>
			<result column="note" property="note"/>
		</collection>
	</resultMap>
	
	<select id="findUserOrderList" resultMap="user_order_map">
		SELECT
			u.id,
			u.username,
			u.birthday,
			u.sex,
			u.address,
			o.id AS oid,
			o.number,
			o.createtime,
			o.note
		FROM
			USER u
		LEFT JOIN orders o ON o.user_id = u.id
	</select>

</mapper>

package com.itheima.mapper;

import java.util.List;

import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;

public interface UserMapper {

	/**
	 * @MethodName:findUserOrderList
	 * @Description:
	 * @return
	 */
	public List<User> findUserOrderList();

}

package com.itheima.test;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itheima.mapper.UserMapper;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;

public class UserMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void init() throws Exception {
		String resource = "SqlMapConfig.xml";
		// 读取配置文件
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 创建工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void findUserOrderList() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		List<User> list = mapper.findUserOrderList();
		for (User user : list) {
			System.err.println(user);
		}
	}
}