mybatis笔记(九)——高级映射

一、数据模型分析思路：

1.1 表内容（单表）

1. 数据内容：分模块对每张表的内容进行熟悉
2. 重要字段：非空字段、外键字段

1.2 表关系（多表）

1. 数据库级别：外键关系

2. 业务级别：在某个实际业务意义基础上去分析表与表之间的业务关系

   (1)分析数据级别有关系的表之间的业务关系 ；

   (2)分析数据库级别没有关系的表之间是否有业务关系 ；

二、映射关系

2.1、 一对一查询（查询订单的客户信息）

查询主表：orders
关联表：user

2.1.1 resultType实现

思路：新建po类继承原有类（所用字段居多），作为返回类型
po定义

public class OrdersUser extends Orders  {	
	private String username;
	private String sex;
	private String address;
	}

.xml文件

	<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersUser">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address
		FROM
		orders,
		USER
		WHERE orders.user_id = user.id
	</select>

.java接口

public List<OrdersUser> findOrdersUser();

测试代码

public void OrdersUserTest() {
		SqlSession sqlSession=sqlSessionFactory.openSession();
		OrdersUserMapper ordersUserMapper=sqlSession.getMapper(OrdersUserMapper.class);
		List<OrdersUser> ordersUser=ordersUserMapper.findOrdersUser();
		System.out.print(ordersUser);
		sqlSession.close();
	}

2.1.2 resultMap实现

resultMap定义：

	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserMap">
		<id column="id" property="id" />
		<result column="user_id" property="user_id" />
		<result column="number" property="number" />
		<result column="createtime" property="createtime" />
		<result column="note" property="note" />
		<association property="user" javaType="cn.itcast.mybatis.po.User">
			<id column="id"  property="id" />
			<result column="username" property="username" />
			<result column="sex" property="sex" />
			<result column="address" property="address" />
		</association>
	</resultMap>

.xml文件

	<select id="findOrdersUserMap" resultMap="OrdersUserMap">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address
		FROM
		orders,
		USER
		WHERE
		orders.user_id
		= user.id
	</select>

.java接口

public List<Orders> findOrdersUserMap();

测试代码

	@Test
	public void OrdersUserMapTest() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersUserMapper ordersUserMapper = sqlSession
				.getMapper(OrdersUserMapper.class);
        List<Orders> orders=ordersUserMapper.findOrdersUserMap();
        System.out.print(orders);
        sqlSession.close();
	}

2.2、 一对多查询（查询订单及订单明细信息）

查询主表：orders
关联表：orderdatail
思路：向orders中添加List<orderdatail >属性
resultMap定义

	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersDetailMap"
		extends="OrdersUserMap">
		<collection property="orderdatail" ofType="cn.itcast.mybatis.po.OrderDetail">
			<id column="orderdetail_id" property="id" />
			<result column="orders_id" property="orders_id" />
			<result column="items_id" property="items_id" />
			<result column="items_num" property="items_num" />
		</collection>
	</resultMap>

.xml文件

<select id="findOrderDetailMap" resultMap="OrdersDetailMap">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address,
		orderdetail.id orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id
		FROM
		orders,
		USER,
		orderdetail
		WHERE orders.user_id = user.id AND
		orderdetail.orders_id=orders.id
	</select>

.java接口

public List<Orders> findOrderDetailMap();

测试代码

	@Test
	public void OrdersDetailMapTest(){
		SqlSession sqlSession =sqlSessionFactory.openSession();
		OrdersUserMapper ordersUserMapper=sqlSession.getMapper(OrdersUserMapper.class);
		List<Orders> orders=ordersUserMapper.findOrderDetailMap();
		System.out.print(orders);
		sqlSession.close();
	}

2.3、 多对多查询（查询用户购买商品）

查询主表：user
关联表：orders、orderdetail、items
思路：
向user类中添加List<Orders> orders属性；
向orders类中添加List<OrderDetail>属性；
向OrderDetaiL中添加Items属性；
resultMap定义

<resultMap type="cn.itcast.mybatis.po.User" id="UserItemsMap">
		<id column="id" property="id" />
		<result column="username" property="username" />
		<result column="sex" property="sex" />
		<result column="address" property="address" />
		<collection property="orders" ofType="cn.itcast.mybatis.po.Orders">
			<id column="id" property="id" />
			<result column="user_id" property="user_id" />
			<result column="number" property="number" />
			<result column="createtime" property="createtime" />
			<result column="note" property="note" />
			<collection property="orderdatail" ofType="cn.itcast.mybatis.po.OrderDetail">
				<id column="orderdetail_id" property="id" />
				<result column="orders_id" property="orders_id" />
				<result column="items_id" property="items_id" />
				<result column="items_num" property="items_num" />
				<association property="items" javaType="cn.itcast.mybatis.po.Items">
					<id column="id" property="id" />
					<result column="items_name" property="name" />
					<result column="items_price" property="price" />
					<result column="items_detail" property="detail" />
				</association>
			</collection>
		</collection>
	</resultMap>

.xml文件

<select id="findUserItemsMap" resultMap="UserItemsMap">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address,
		orderdetail.id orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id,
		items.name items_name,
		items.detail items_detail,
		items.price
		items_price
		FROM
		orders,
		USER,
		orderdetail,
		items
		WHERE orders.user_id =
		user.id AND orderdetail.orders_id=orders.id AND
		orderdetail.items_id =
		items.id
	</select>

.java接口

public List<User> findUserItemsMap();

测试代码

	@Test
	public void UserItemsMapTest(){
		SqlSession sqlSession=sqlSessionFactory.openSession();
		OrdersUserMapper orderUserMapper=sqlSession.getMapper(OrdersUserMapper.class);
		List<User> users=orderUserMapper.findUserItemsMap();
		System.out.print(users);
		sqlSession.close();
	}

三、总结

resultType：
将查询结果按照sql列名pojo属性名一致性映射到pojo中

resultMap：
对结果有特殊映射要求时，使用association和collection完成高级映射

标签	association	collection
映射结果类型	pojo对象	list集合
结果类型关键字	javaType	ofType
应用	一对一	一对多