MyBaits基础：多对多

                         一个订单有多个产品，一个产品对应多个订单

参考：http://how2j.cn/k/mybatis/mybatis-many-to-many/1091.html

表结构：

• Product

create table product_(
id int NOT NULL AUTO_INCREMENT,
name varchar(30)  DEFAULT NULL,
price float  DEFAULT 0,
cid int ,
PRIMARY KEY (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• Orders

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;

• OrderItem

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;

实体类：

• Product

public class Product {
    private int id;
    private String name;
    private float price;
    private Category category;  //新增类别属性
     
    public Category getCategory() {
        return category;
    }
    public void setCategory(Category category) {
        this.category = category;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
    }
 
}

• Orders

import java.util.List;
 
public class Order {
    private int id;
    private String code;
     
    List<OrderItem> orderItems;
     
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public List<OrderItem> getOrderItems() {
        return orderItems;
    }
    public void setOrderItems(List<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }
 
}

• OrderItem

public class OrderItem {
    private int id;
    private int number;
    private Order order;
    private Product product;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getNumber() {
        return number;
    }
    public void setNumber(int number) {
        this.number = number;
    }
    public Order getOrder() {
        return order;
    }
    public void setOrder(Order order) {
        this.order = order;
    }
    public Product getProduct() {
        return product;
    }
    public void setProduct(Product product) {
        this.product = product;
    }
     
}

映射文件：

• 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.java.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>

• Orders.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.java.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>

• 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.java.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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
      <package name="com.java.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/how2java?characterEncoding=UTF-8"/>
            <property name="username" value="root"/>
            <property name="password" value="admin"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/java/pojo/Category.xml"/>
        <mapper resource="com/java/pojo/Product.xml"/>
        <mapper resource="com/java/pojo/Order.xml"/>
        <mapper resource="com/java/pojo/OrderItem.xml"/>
    </mappers>
</configuration>

运行：（查询订单）

public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
  
        listOrder(session);
 
        session.commit();
        session.close();
  
    }
 
    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());
            }
        }
    }

输出： 

运行：（订单添加产品）

private static void addOrderItem(SqlSession session) {
        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);
    }

输出：

运行：（删除产品）

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

输出：

PS：多对多不存在修改关系的做法，就是删除旧的，然后新增一条即达到修改的效果。