MyBatis基础之一对多、多对一、多对多

一对多

1、增加一个表product_、插入两个表（category_和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;

delete from category_;
INSERT INTO category_ VALUES (1,'category1');
INSERT INTO category_ VALUES (2,'category2');
delete from product_;
INSERT INTO product_ VALUES (1,'product a', 88.88, 1);
INSERT INTO product_ VALUES (2,'product b', 88.88, 1);
INSERT INTO product_ VALUES (3,'product c', 88.88, 1);
INSERT INTO product_ VALUES (4,'product x', 88.88, 2);
INSERT INTO product_ VALUES (5,'product y', 88.88, 2);
INSERT INTO product_ VALUES (6,'product z', 88.88, 2);

2、写两个实体类(Product、Category)

public class Product {
    private int id;
    private String name;
    private float price;
    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 + "]";
    }
}

import java.util.List;
public class Category {
    private int id;
    private String name;
    List<Product> products;//一对多！

    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 List<Product> getProducts() {
        return products;
    }
    public void setProducts(List<Product> products) {
        this.products = products;
    }
    @Override
    public String toString() {
        return "Category [id=" + id + ", name=" + name + "]";
    }   
}

3、配置category.xml
collection一对多的标签

<!-- 通过resultMap把数据取出来放在对应的 对象属性里--> 
  <mapper namespace="com.how2java.pojo">
        <resultMap type="Category" id="categoryBean">
            <id column="cid" property="id" />对应Category类属性
            <result column="cname" property="name" />

        <!-- 一对多的关系 -->
      <!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
  <collection property="products" ofType="Product"> 
                <id column="pid" property="id" />对应Product类属性
                <result column="pname" property="name" />
                <result column="price" property="price" />
            </collection>
        </resultMap>

        <!-- 关联查询分类和产品表 -->
        <select id="listCategory" resultMap="categoryBean">
         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>   
    </mapper>

4、测试类TestMybatis

public class TestMybatis {
    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();

        List<Category> cs = session.selectList("listCategory");//select中的id
        for (Category c : cs) {
            System.out.println(c);
            List<Product> ps = c.getProducts();//一对多中的多
            for (Product p : ps) {
                System.out.println("\t"+p);
            }
        }
        session.commit();
        session.close();

    }
}

多对一

1、对实体类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 + "]";
    }
}

2、配置product.xml（对应着Product的属性）
使用association 进行多对一关系关联，指定表字段名称与对象属性名称的一一对应关系。
Category的id 字段 和Product的id字段同名，Mybatis不知道谁是谁的，所以需要通过**取别名**cid,pid来区分。
name字段同理。

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

        <!-- 根据id查询Product, 关联将Orders查询出来 -->
        <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>   
    </mapper>

3、配置mybatis-cinfig.xml
在mybatis-config.xml中增加对于Product.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.how2java.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/how2java/pojo/Category.xml"/>
        <mapper resource="com/how2java/pojo/Product.xml"/>
    </mappers>
</configuration>

4、配置TestMybatis类

public class TestMybatis { 
    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();

        List<Product> ps = session.selectList("listProduct");
        for (Product p : ps) {
   System.out.println(p+" 对应的分类是 \t "+ p.getCategory());
        }

        session.commit();
        session.close();
    }
}

多对多

1、添加表（order_）和（order_item_），分别添加数据

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、Order实体类

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

}

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

3、分别的映射文件Order.xml、Product.xml、OrderItem.xml

Order.xml

<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

<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

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

4、mybatis.config.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>

5、查询操作，TestMybatis

public class TestMybatis {
    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());
            }
        }
    }
}

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

public class TestMybatis {

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

        addOrderItem(session);//插入
        listOrder(session);

        session.commit();
        session.close();

    }

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

7、删除操作（其实和插入一样，取 #{order.id} 和 #{product.id}）

public class TestMybatis {

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

        deleteOrderItem(session);
        listOrder(session);

        session.commit();
        session.close();

    }

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

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