多表查询练习

环境准备

参照MyBatis框架学习Ⅷ
中的环境配置

多对多练习

sql语句

			SELECT
              orders.id,
              orders.user_id,
              orders.number,
              user.username,
              user.sex,
              orderdetail.id  orderdetailId,
              orderdetail.items_id,
              orderdetail.items_num,
              items.name,
              items.price
            FROM orders,
              USER,
              orderdetail,
              items
            WHERE orders.user_id = user.id
                AND orders.id = orderdetail.orders_id
                AND orderdetail.items_id = items.id

使用resultMap

mapper文件

<?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="dao.mapper.OrdersMapper">
    
    <resultMap id="OrdersAndItemsRstMap" type="entity.User">
        <!-- 用户信息 -->
        <!-- 左边是表的名称，记住 -->
        <!-- 虽然查询的列没有user_id，但是是唯一主键，还是需要写，
             它最后赋值给entity.User 里面的id -->
        <id column="user_id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="sex" property="sex"></result>
        <!-- 订单信息 一个用户，对应多个订单  一对多-->
        <collection property="ordersList" ofType="entity.Orders">
            <id column="id" property="id"></id>
            <result column="user_id" property="userId"></result>
            <result column="number" property="number"></result>

            <!-- 订单详情信息 一个订单里面有多个订单详情  一对多-->
            <collection property="orderdetailList" ofType="entity.Orderdetail">
                <id column="orderdetailId" property="id"></id>
                <result column="items_id" property="itemsId"></result>
                <result column="items_num" property="itemsNum"></result>
                <!-- 商品信息 每个订单详情里面有一个商品信息  一对一 -->
                <association property="items" javaType="entity.Items">
                    <!-- 虽然查询的列没有items_id，但是是唯一主键，还是需要写，
                         它最后赋值给entity.Items 里面的id -->
                    <id column="items_id" property="id"></id>
                    <result column="name" property="name"></result>
                    <result column="price" property="price"></result>
                </association>
            </collection>
        </collection>

    </resultMap>
    <select id="findOrdersAndItemsRstMap" resultMap="OrdersAndItemsRstMap">
            SELECT
              orders.id,
              orders.user_id,
              orders.number,
              user.username,
              user.sex,
              orderdetail.id  orderdetailId,
              orderdetail.items_id,
              orderdetail.items_num,
              items.name,
              items.price
            FROM orders,
              USER,
              orderdetail,
              items
            WHERE orders.user_id = user.id
                AND orders.id = orderdetail.orders_id
                AND orderdetail.items_id = items.id
    </select>
</mapper>

package dao.mapper;

import entity.OrderMapExt;
import entity.OrdersExt;
import entity.User;
import entity.UserQueryVo;

import java.util.HashMap;
import java.util.List;

public interface OrdersMapper {
    List<User> findOrdersAndItemsRstMap();
}

实体类

package entity;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String address;
    private String sex;
    private List<Orders> ordersList;

    public List<Orders> getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

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

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                ", ordersList=" + ordersList +
                '}';
    }
}

测试文件

	@Test
    public void testfindOrdersAndItemsRstMap() throws Exception {
        //读取配置文件
        //全局配置文件的路径
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<User> ordersAndItemsRstMap = mapper.findOrdersAndItemsRstMap();

        System.out.println("ordersAndItemsRstMap = " + ordersAndItemsRstMap);
        sqlSession.close();
    }

一对多效果