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、源代码