Mybatis多表操作

一、建表

表中数据请根据下面的例子，一对一，一对多 的实际情况自行添加

• t_customer客户表

CREATE TABLE `t_customer` (
    `id` INT ( 10 ) NOT NULL AUTO_INCREMENT,
    `address` VARCHAR ( 120 ) DEFAULT NULL,
    `postcode` VARCHAR ( 6 ) DEFAULT NULL,
    `sex` VARCHAR ( 2 ) DEFAULT NULL,
    `cname` VARCHAR ( 24 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = gb2312;

image.png

• t_order订单表

CREATE TABLE `t_orders` (
    `id` INT ( 10 ) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR ( 24 ) DEFAULT NULL,
    `customer_id` INT ( 3 ) NOT NULL,
    PRIMARY KEY ( `id` ),
    KEY `orders_customer_fk` ( `customer_id` ),
CONSTRAINT `orders_customer_fk` FOREIGN KEY ( `customer_id` ) REFERENCES `t_customer` ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 3 DEFAULT CHARSET = gb2312;

image.png

二、一对一

假如一个客户只有一个订单

• private List Orders orders; // 实体类不能返回List
• <associationproperty="orders" column="id"select="com.example.demo.mapper.OrdersMapper.getOrdersById"/>

三、一对多

假如一个客户有多个订单

1、实体类

• Customer

public class Customer implements Serializable {
    private static final long serialVersionUID = 451949797460417653L;
    private int id;
    private String address;
    private String postcode;
    private String sex;
    private String cname;
    private List<Orders> orders;   // 一对多这里要写一个List

    // getter和setter省略
}

• Orders

public class Orders implements Serializable {
    private static final long serialVersionUID = 8215977396669780567L;
    private int id;
    private String code;
    private int customerId;

    // getter和setter省略

}

2、Mapper

• CustomerMapper

public interface CustomerMapper {
    Customer getCustomer(int id);
}

• OrdersMapper

public interface OrdersMapper {
    List<Orders> getOrdersById(int customerId);   
}

3、xml

• CustomerMapper.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.example.demo.mapper.CustomerMapper">

    <resultMap type="Customer" id="resultCustomerMap">
        <result property="id" column="id"/>
        <result property="address" column="address"/>
        <result property="postcode" column="postcode"/>
        <result property="sex" column="sex"/>
        <result property="cname" column="cname"/>
        <collection property="orders" column="id" select="com.example.demo.mapper.OrdersMapper.getOrdersById"/>
    </resultMap>
    <!--这里用collection，用association也返回成功了
          property为Customer实体类里定义的Order集合的属性
          column为t_customer表里要要传的字段-->
    <select id="getCustomer" resultMap="resultCustomerMap" parameterType="int">
        SELECT *
        FROM test.t_customer
        WHERE id=#{id}
    </select>

• OrderMapper.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.example.demo.mapper.OrdersMapper">

    <select id="getOrdersById" resultType="Orders" parameterType="int">
        SELECT * FROM test.t_orders
        WHERE customer_id=#{customer_id}
    </select>

</mapper>

4、测试用例

image.png