MyBatis高级映射（多对多查询）

1、需求

查询用户及购买的商品信息

2、表结构

use用户表

orders订单表

orders订单表外键

orderdetail订单详情表（订单表和商品表的关联表）

orderdetail订单详情表外键（订单表和商品表的关联表）

items商品表

3、SQL语句

4、项目编码

4.1、Mapper.xml

定义resultMap

<!-- 查询用户及购买的商品 -->
<resultMap type="org.mybatis.demo.po.User" id="UserAndItemsResultMap">
	<!-- 用户信息 -->
	<id column="user_id" property="id"/>
	<result column="username" property="username"/>
	<result column="sex" property="sex"/>
	<result column="address" property="address"/>
		
	<!-- 订单信息
	一个用户对应多个订单，使用collection映射
	 -->
	 <collection property="ordersList" ofType="org.mybatis.demo.po.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 property="orderdetails" ofType="org.mybatis.demo.po.Orderdetail">
	  			<id column="orderdetail_id" property="id"/>
			 	<result column="items_id" property="itemsId"/>
			 	<result column="items_num" property="itemsNum"/>
			 	<result column="orders_id" property="ordersId"/>
			 	<!-- 商品信息
			  	一个订单明细对应一个商品
			  	 -->
	  	 	<association property="items" javaType="org.mybatis.demo.po.Items">
	  	 		<id column="items_id" property="id"/>
	  	 		<result column="items_name" property="name"/>
	  	 		<result column="items_detail" property="detail"/>
	  	 		<result column="items_price" property="price"/>
	  	 		<result column="createtime" property="createtime"/>
	  	 	</association>
	  	</collection>
	 </collection>
</resultMap>

定义statement

<!-- 查询用户及购买的商品信息，使用resultmap -->
<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
	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>

4.2、定义Mapper接口

package org.mybatis.demo.mapper;

import java.util.List;

import org.mybatis.demo.po.User;

public interface OrdersMapper {

	public List<User> findUserAndItemsResultMap();
}

4.3、单元测试

package org.mybatis.demo.test;

import java.io.IOException;
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 org.mybatis.demo.mapper.OrdersMapper;
import org.mybatis.demo.po.Orderdetail;
import org.mybatis.demo.po.Orders;
import org.mybatis.demo.po.User;

public class TestMyBatis {

	private SqlSessionFactory sessionFactory;

	@Before
	public void createSqlSessionFactory() throws IOException {
		//引入配置文件
		String resource = "SqlMapConfig.xml";
		InputStream ins = Resources.getResourceAsStream(resource);
		
		//创建SqlSession对象
		sessionFactory = new SqlSessionFactoryBuilder().build(ins);
	}
	
	@Test
	public void test1() {
		SqlSession session = null;
		try {
			session = sessionFactory.openSession();
			OrdersMapper mapper = session.getMapper(OrdersMapper.class);
			List<User> list = mapper.findUserAndItemsResultMap();
			for(User user : list){
				System.out.println(user.getUsername());
				System.out.println("########");
				for(Orders order:user.getOrdersList()){
					System.out.println("orderID:" + order.getId());
					System.out.println("-------");
					for(Orderdetail detial:order.getOrderdetails()){
						System.out.println("创建时间：" + detial.getItems().getCreatetime());
					}
				}
			}
		} finally {
			if(session != null){
				session.close();
			}
		}
	}

}

4.4、源代码

MyBatis高级映射（多对多查询