MyBatis基础05之多对多关系

多对多

一张订单里 可以包含多种产品
一种产品 可以出现在多张订单里
这就是多对多关系
为了维系多对多关系，必须要一个中间表。
在这里我们使用订单项(OrderItem)表来作为中间表

1. Order表和OrderItem表

create table order_ (
  id int(11) NOT NULL AUTO_INCREMENT,
  code varchar(32) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table order_item_(
  id int(11) NOT NULL AUTO_INCREMENT, 
  oid int ,
  pid int ,
  number int ,
  PRIMARY KEY(id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO order_ VALUES (1,'code000A');
INSERT INTO order_ VALUES (2,'code000B');

INSERT INTO order_item_ VALUES (null, 1, 1, 100);
INSERT INTO order_item_ VALUES (null, 1, 2, 100);
INSERT INTO order_item_ VALUES (null, 1, 3, 100);
INSERT INTO order_item_ VALUES (null, 2, 2, 100);
INSERT INTO order_item_ VALUES (null, 2, 3, 100);
INSERT INTO order_item_ VALUES (null, 2, 4, 100);

2.实体类

  OrderItem.java
  ...
  private int id;
  private int number;
  private Order order;
  private Product product;  


  Order.java

    ...
    private int id;
    private String code;
    private List<OrderItem> orderItems;

3 . 映射文件

Order.xml

    <?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="com.how2java.pojo">
        <resultMap type="Order" id="orderBean">
            <id column="oid" property="id" />
            <result column="code" property="code" />

            <collection property="orderItems"  
             ofType="OrderItem">
                <id column="oiid" property="id" />
                <result column="number" property="number" />
                <association property="product"  
                 javaType="Product">
                    <id column="pid" property="id"/>
                    <result column="pname" property="name"/>
                    <result column="price" property="price"/>
                </association>               
            </collection>
        </resultMap>

        <select id="listOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid',  
             p.id 'pid', oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi    on o.id =oi.oid
                left join product_ p on p.id = oi.pid
        </select>

        <select id="getOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid', p.id 'pid',  
             oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi on o.id =oi.oid
                left join product_ p on p.id = oi.pid
            where o.id = #{id}
        </select>
    </mapper>   

Product.xml

<?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="com.how2java.pojo">
        <resultMap type="Product" id="productBean">
            <id column="pid" property="id" />
            <result column="pname" property="name" />
            <result column="price" property="price" />

            <!-- 多对一的关系 -->
            <!-- property: 指的是属性名称,  
            javaType：指的是属性的类型 -->
            <association property="category"   
                javaType="Category">
                <id column="cid" property="id"/>
                <result column="cname" property="name"/>
            </association>
        </resultMap>

        <select id="listProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid',  
             c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
        </select>   
        <select id="getProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid',  
             c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
            where p.id = #{id}
        </select>   
    </mapper>

OrderItem.xml

 <?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="com.how2java.pojo">

        <insert id="addOrderItem" parameterType="OrderItem">
            insert into order_item_
                values(null,#{order.id},  
                #{product.id},#{number})
        </insert>   
        <insert id="deleteOrderItem" parameterType="OrderItem">
            delete from order_item_
                where oid = #{order.id} and pid = #{product.id}
        </insert>   

    </mapper>

MyBatis.xml

  <mappers>
        <mapper resource="com/how2java/pojo/Category.xml"/>
        <mapper resource="com/how2java/pojo/Product.xml"/>
        <mapper resource="com/how2java/pojo/Order.xml"/>
        <mapper resource="com/how2java/pojo/OrderItem.xml"/>
    </mappers>

4 .查询操作

通过Order.xml的listOrder对应的sql语句进行查询：

<select id="listOrder" resultMap="orderBean">
    select o.*,p.*,oi.*, o.id 'oid',  
     p.id 'pid', oi.id 'oiid', p.name 'pname' 
        from order_ o 
        left join order_item_ oi    on o.id =oi.oid 
        left join product_ p on p.id = oi.pid 
</select>


联合order_, order_item_, product_ 三张表进行查询



<resultMap type="Order" id="orderBean">
    <id column="oid" property="id" />
    <result column="code" property="code" />

    <collection property="orderItems" ofType="OrderItem">
        <id column="oiid" property="id" />
        <result column="number" property="number" />
        <association property="product" javaType="Product">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
            <result column="price" property="price"/>
        </association>              
    </collection>
</resultMap>


查询结果 id和code字段放在Order对象里，  
 然后通过一对多的<collection>标签  
 把oiid和number放在OrderItem对象里，  
 最后把pid,pname,price放进Product对象里。

    private static void listOrder(SqlSession session) {
        List<Order> os = session.selectList("listOrder");
        for (Order o : os) {
            System.out.println(o.getCode());
            List<OrderItem> ois= o.getOrderItems();
            for (OrderItem oi : ois) {
                System.out.format("\t%s\t%f\t%d%n", oi.getProduct().getName(),oi.getProduct().getPrice(),oi.getNumber());
            }
        }
    }

5 .建立关系

    首先通过id分别获取Ordre对象和Product对象，然后创建一个新的OrderItem对象，接着设置Order，设置Product，设置数量，最后调用"addOrderItem" 对应的sql语句插入数据。

Order o1 = session.selectOne("getOrder", 1);
Product p6 = session.selectOne("getProduct", 6);
OrderItem oi = new OrderItem();
oi.setProduct(p6);
oi.setOrder(o1);
oi.setNumber(200);
session.insert("addOrderItem", oi);



addOrderItem调用insert into 语句插入一条OrderItem记录

<insert id="addOrderItem" parameterType="OrderItem">
    insert into order_item_ 
        values(null,#{order.id},#{product.id},#{number})
</insert> 

6 .删除关系

    private static void deleteOrderItem(SqlSession session) {
        Order o1 = session.selectOne("getOrder",1);
        Product p6 = session.selectOne("getProduct",6);
        OrderItem oi = new OrderItem();
        oi.setProduct(p6);
        oi.setOrder(o1);
        session.delete("deleteOrderItem", oi);     
    }

1. 删除订单时，删除对应的订单项
   比如删除订单A，那么就应该删除订单A在订单项里所对应的数据。

提示，通过Mybatis执行多条sql语句需要增加一个参数：allowMultiQueries

在Order.xml中

    <delete id="deleteOrder" parameterType="_int">
        delete from order_item_ where oid = #{order.id};
        delete from order_ where id = #{id};
    </delete>

TestMyBatis.java

           int id = 1;
           session.delete("deleteOrder",id);
          // 直接执行即可删除orderItem和order