本文共 2572 字，大约阅读时间需要 8 分钟。

1、需求

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

2、表结构

use用户表

orders订单表

orders订单表外键

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

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

items商品表

3、SQL语句

4.1、Mapper.xml

定义resultMap

   	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

package org.mybatis.demo.mapper;import java.util.List;import org.mybatis.demo.po.User;public interface OrdersMapper {	public List
   
     findUserAndItemsResultMap();}
   

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