2-Mybatis-高级映射

订单商品数据模型

分析：

• 用户表（user）: user_id, user_name 记录了购买商品的用户信息
• 订单表（orders）: orders_id,number, user_id 记录了用户创建的订单（购买商品的订单）
• 订单明细表（orderdetail）：detail_id,orders_id, items_id 记录了订单的详细信息（购买商品的信息）
• 商品表（items）：items_id,items_name, items_price 记录了商品信息

User和orders：

User–》orders：一个用户可以创建多个订单，一对多
Orders–》user：一个订单只能由一个用户创建，一对一

Orders和orderdetail：

Orders–》orderdetail：一个订单包括多个订单明细表，一对多
Orderdetail–》orders：一个订单明细表对应一个订单，一对一

Orderdetail和items：

Orderdetail–》items：一个订单明细表对应一个商品，一对一
Items–》orderdetail：一个商品可以在多个订单明细表中存在，一对多

Orders和items：

Orders–》items：一个订单包含多个商品
Items–》orders：一个商品可以在多个订单中

一对一查询

1. 需求：

对订单表进行查询，查询订单表信息以及创建订单的用户信息

2. resultType

2.1 SQL：

SELECT orders.*,`user`.user_name,`user`.user_birth FROM orders,`user` WHERE orders.user_id = `user`.user_id

2.2 创建pojo

public class OrdersCustom extends Orders{
    private String user_name;
    private Date user_birth;
    public String getUser_name() {
        return user_name;
    }
    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }
    public Date getUser_birth() {
        return user_birth;
    }
    public void setUser_birth(Date user_birth) {
        this.user_birth = user_birth;
    }
    @Override
    public String toString() {
        return "OrdersCustom [user_name=" + user_name + ", user_birth="
                + user_birth + ", toString()=" + super.toString() + "]";
    }   
}

2.3 创建mapper.java

public interface OrdersCustomMapper {
    //查询订单关联查询用户信息
    //<select id="selectOrders" resultType="orders">
    public List<OrdersCustom> selectOrders() throws IOException;
}

2.4 创建mapper.xml

<select id="selectOrders" resultType="orderscustom">
    SELECT orders.*,`user`.user_name,`user`.user_birth 
FROM orders,`user` 
WHERE orders.user_id = `user`.user_id
</select>

3 resultMap

3.1 添加User属性

    private User user; //创建订单的用户
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }

3.2 创建mapper.java

public interface OrdersMapper {
    public List<Orders> selectOrders() throws IOException;
}

3.3 创建mapperxml

    <!-- resultMap标签：定义订单查询关联用户的resultMap
            type属性：查询结果对应的实体类
            id属性：该resultMap的id
            - 配置映射的订单信息
            id标签：Orders的主键
                column属性：数据表中名
                property属性：实体类属性名
            result标签：Orders的其他列
                column属性：数据表中名
                property属性：实体类属性名
            - 配置映射的关联的用户信息
            association标签：
                property属性：关联的实体类在Orders中属性名
                javaType属性：关联的实体类
     -->
<resultMap type="com.wzw.mybatis.model.Orders" id="OrdersUserResultMap">
    <id column="orders_id" property="orders_id"/>
    <result column="number" property="number"/>
    <association property="user" javaType="com.wzw.mybatis.model.User">
        <id column="user_id" property="user_id"/>
        <result column="user_name" property="user_name"/>
        <result column="user_birth" property="user_birth"/>
    </association>
</resultMap>
<select id="selectOrders" resultMap="OrdersUserResultMap">
    SELECT orders.*,`user`.user_name,`user`.user_birth 
    FROM orders,`user` 
    WHERE orders.user_id = `user`.user_id
</select>

一对多查询

1. 需求：

对订单表及订单明细表查询，获取订单详细信息

2. SQL

SELECT orders.*,`user`.user_name,`user`.user_birth,
orderdetail.detail_id,orderdetail.num,orderdetail.items_id
FROM orders,`user`,orderdetail 
WHERE orders.user_id = `user`.user_id 
AND orderdetail.orders_id = orders.orders_id

3. 添加OrderDetail属性

private List<OrderDetail> orderDetails; //订单明细信息
public List<OrderDetail> getOrderDetails() {
    return orderDetails;
}
public void setOrderDetails(List<OrderDetail> orderDetails) {
    this.orderDetails = orderDetails;
}

4. 创建mapper.java

    //查询订单及订单明细信息
    public List<Orders> selectOrdersAndOrderDetail() throws IOException;

5. 创建mapper.xml

<!-- 查询订单及订单明细信息 -->
<resultMap type="com.wzw.mybatis.model.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
    <!-- 配置订单信息 -->
    <!-- 配置用户信息 -->
    <!-- 配置订单明细信息 -->
    <collection property="orderDetails" ofType="com.wzw.mybatis.model.OrderDetail">
        <id column="detail_id" property="detail_id"/>
        <result column="num" property="num"/>
        <result column="orders_id" property="orders_id"/>
        <result column="items_id" property="items_id"/>
    </collection>
</resultMap>
<select id="selectOrdersAndOrderDetail" resultMap="OrdersAndOrderDetailResultMap">
    SELECT orders.*,`user`.user_name,`user`.user_birth,
    orderdetail.detail_id,orderdetail.num,orderdetail.items_id
    FROM orders,`user`,orderdetail 
    WHERE orders.user_id = `user`.user_id 
AND orderdetail.orders_id = orders.orders_id
</select>

多对多查询

1. 需求：

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

2. SQL

SELECT `user`.*,orders.*,orderdetail.*,items.*
FROM orders,`user`,orderdetail,items
WHERE orders.user_id = `user`.user_id 
AND orderdetail.orders_id = orders.orders_id
AND orderdetail.items_id = items.items_id

3. 添加Orders属性

    private List<Orders> orderList;
    public List<Orders> getOrderList() {
        return orderList;
    }
    public void setOrderList(List<Orders> orderList) {
        this.orderList = orderList;
    }

4. mapper.java

//查询用户及用户购买的商品信息
public List<User> selectUserAndItems() throws IOException;

5. resultMap

<!-- 查询用户及购买商品的信息 -->
<resultMap type="com.wzw.mybatis.model.User" id="UserAndItemsResultMap">
<id column="user_id" property="user_id"/>
    <result column="user_name" property="user_name"/>
    <result column="user_birth" property="user_birth"/>
    <!-- 配置订单表信息:一个用户创建多个订单 -->
    <collection property="orderList" ofType="com.wzw.mybatis.model.Orders">
        <id column="orders_id" property="orders_id"/>
        <result column="number" property="number"/>
        <!-- 配置订单明细表信息：一个订单存在多个订单明细 -->
        <collection property="orderDetails" ofType="com.wzw.mybatis.model.OrderDetail">
            <id column="detail_id" property="detail_id"/>
            <result column="num" property="num"/>
            <result column="orders_id" property="orders_id"/>
            <result column="items_id" property="items_id"/>
            <!-- 配置商品信息：一个订单明细对应一个商品 -->
            <association property="items" javaType="com.wzw.mybatis.model.Items">
                <id column="items_id" property="items_id"/>
                <result column="items_name" property="items_name"/>
                <result column="items_price" property="items_price"/>
            </association>
        </collection>
    </collection>
</resultMap>

6. mapper.xml

<select id="selectUserAndItems" resultMap="UserAndItemsResultMap">
    SELECT `user`.*,orders.*,orderdetail.*,items.*
    FROM orders,`user`,orderdetail,items
    WHERE orders.user_id = `user`.user_id 
    AND orderdetail.orders_id = orders.orders_id
    AND orderdetail.items_id = items.items_id
</select>