修改一对多的bug

客户和订单就是典型的一对多关联关系。

一个客户名下可以有多个订单

USE ssm;
CREATE TABLE customer(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32),
age INT
);
INSERT INTO customer VALUES(1,'张三',22);
INSERT INTO customer VALUES(2,'李四',23);
INSERT INTO customer VALUES(3,'王五',24);
CREATE TABLE `orders`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`orderNumber` VARCHAR(16),`orderPrice` DOUBLE,
`customer_id` INT,
FOREIGN KEY (`customer_id`) REFERENCES customer(id)#用的是外键(FOREIGN KEY)来引用进行关联
);
INSERT INTO `orders` VALUES(11,20,22.22,1);
INSERT INTO `orders` VALUES(12,60,16.66,1);
INSERT INTO `orders` VALUES(13,90,19.99,2);
SELECT * FROM `customer`;
SELECT * FROM `orders`;

使用一对多的关联关系可以满足查询客户的同时查询该客户名下的所有订单

select c.id cid,name,age, o.id oid,orderNumber,orderPrice,customer_id
from customer c join orders o on c.id = o.customer_id where c.id=1;#查询了张三的信息和张三名下的所有信息

如果这俩条数据是一模一样的，那**mybatis**会给你**去重**的;所以我们可以做一个客户的实体类把**cid,name,age**这一段封装进去，那我同时想要把后面的**oid**后面的数据也封装进来怎么办?

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//3.0 configuration//3.0"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"></properties>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="com.chentianyu.pojo"/>
    </typeAliases>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driveClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.chentianyu.mapper"/>
    </mappers>

</configuration>

实体类

package com.chentianyu.pojo;

import java.util.List;

public class Customer {
    private Integer id;
    private String name;
    private Integer age;

    private List<Orders> orders;

    public Customer() {
    }

    public Customer(Integer id, String name, Integer age, List<Orders> orders) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.orders = orders;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", orders=" + orders +
                '}';
    }
}

package com.chentianyu.pojo;

public class Orders {
    private Integer id;
    private String orderNumber;
    private Double orderPrice;
    private Integer customer_id;

    public Orders() {
    }

    public Orders(Integer id, String orderNumber, Double orderPrice, Integer customer_id) {
        this.id = id;
        this.orderNumber = orderNumber;
        this.orderPrice = orderPrice;
        this.customer_id = customer_id;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }

    public Double getOrderPrice() {
        return orderPrice;
    }

    public void setOrderPrice(Double orderPrice) {
        this.orderPrice = orderPrice;
    }

    public Integer getCustomer_id() {
        return customer_id;
    }

    public void setCustomer_id(Integer customer_id) {
        this.customer_id = customer_id;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                ", orderPrice=" + orderPrice +
                ", customer_id=" + customer_id +
                '}';
    }
}

Mapper

package com.chentianyu.mapper;

import com.chentianyu.pojo.Customer;

import java.util.List;

public interface CustomerMapper {
    //查询所有的客户
    List<Customer> getAllCustomers();
}

CustomerMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chentianyu.mapper.CustomerMapper">
    <!--
    //查询所有的客户
    List<Customer> getAllCustomers();
    -->
    <resultMap id="customermap" type="customer">
        <!--主键绑定-->
        <id property="id" column="id"></id>
        <!--非主键绑定-->
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <!--多出来的一咕噜绑定orders
        实体类:
        private Integer id;
        private String orderNumber;
        private Double orderPrice;
        -->
        <collection property="orders" ofType="orders">
            <!--主键绑定-->
            <id property="id" column="id"></id>
            <!--非主键绑定-->
            <result property="orderNumber" column="orderNumber"></result>
            <result property="orderNumber" column="orderNumber"></result>
        </collection>
    </resultMap>
    <select id="getAllCustomers" resultMap="customermap">
        select c.id cid,c.name cname,c.age cage,o.id oid,o.orderNumber orderNumber,o.orderPrice orderPrice,o.customer_id customer_id from customer c join orders o on c.id = o.customer_id
    </select>
</mapper>

没改之前的输出

Opening JDBC Connection
Created connection 57241990.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3697186]
==>  Preparing: select c.id cid,c.name cname,c.age cage,o.id oid,o.orderNumber orderNumber,o.orderPrice orderPrice,o.customer_id customer_id from customer c join orders o on c.id = o.customer_id
==> Parameters: 
<==    Columns: cid, cname, cage, oid, orderNumber, orderPrice, customer_id
<==        Row: 1, 张三, 22, 11, 20, 22.22, 1
<==        Row: 1, 张三, 22, 12, 60, 16.66, 1
<==        Row: 2, 李四, 23, 13, 90, 19.99, 2
<==      Total: 3
Customer{id=null, name='null', age=null, orders=[Orders{id=null, orderNumber='20', orderPrice=null, customer_id=null}]}
Customer{id=null, name='null', age=null, orders=[Orders{id=null, orderNumber='60', orderPrice=null, customer_id=null}]}
Customer{id=null, name='null', age=null, orders=[Orders{id=null, orderNumber='90', orderPrice=null, customer_id=null}]}

但是这里有一个坑，你select中取了别名cid和oid所以你的主键绑定中的id是不能用的，只能改成oid和cid，如下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chentianyu.mapper.CustomerMapper">
    <!--
    //查询所有的客户
    List<Customer> getAllCustomers();
    -->
    <resultMap id="customermap" type="customer">
        <!--主键绑定-->
        <id property="id" column="cid"></id>
        <!--非主键绑定-->
        <result property="name" column="cname"></result>
        <result property="age" column="cage"></result>
        <!--多出来的一咕噜绑定orders
        实体类:
        private Integer id;
        private String orderNumber;
        private Double orderPrice;
        -->
        <collection property="orders" ofType="orders">
            <!--主键绑定-->
            <id property="id" column="oid"></id>
            <!--非主键绑定-->
            <result property="orderNumber" column="orderNumber"></result>
            <result property="orderPrice" column="orderPrice"></result>
            <result property="customer_id" column="customer_id"></result>
        </collection>
    </resultMap>
    <select id="getAllCustomers" resultMap="customermap">
        select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id
    </select>
</mapper>

得到的结果是:

Opening JDBC Connection
Created connection 26757919.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1984b1f]
==>  Preparing: select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id
==> Parameters: 
<==    Columns: cid, cname, cage, oid, orderNumber, orderPrice, customer_id
<==        Row: 1, 张三, 22, 11, 20, 22.22, 1
<==        Row: 1, 张三, 22, 12, 60, 16.66, 1
<==        Row: 2, 李四, 23, 13, 90, 19.99, 2
<==      Total: 3
Customer{id=1, name='张三', age=22, orders=[Orders{id=11, orderNumber='20', orderPrice=22.22, customer_id=1}, Orders{id=12, orderNumber='60', orderPrice=16.66, customer_id=1}]}
Customer{id=2, name='李四', age=23, orders=[Orders{id=13, orderNumber='90', orderPrice=19.99, customer_id=2}]}

获取到了数据

测试类

package com.chentianyu;

import com.chentianyu.mapper.CustomerMapper;
import com.chentianyu.pojo.Customer;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class testApp {
    SqlSession sqlSession;
    CustomerMapper customerMapper;
    @Before
    public void test01() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        customerMapper = sqlSession.getMapper(CustomerMapper.class);
    }
    @Test
    public void test02(){
        List<Customer> allCustomers = customerMapper.getAllCustomers();
        allCustomers.forEach(customer -> System.out.println(customer));
    }
    @After
    public void test03(){
        sqlSession.close();
    }
}

修改一对多的bug

从中发现王五并没有订单，但是我们要找王五与订单的时候为null

按照customer主键查询数据

package com.chentianyu.mapper;

import com.chentianyu.pojo.Customer;

import java.util.List;

public interface CustomerMapper {
    //查询所有的客户
    List<Customer> getAllCustomers();

    //按主键查询信息
    Customer getByIdCustomer(Integer id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chentianyu.mapper.CustomerMapper">
    <!--
    //查询所有的客户
    List<Customer> getAllCustomers();
    -->
    <resultMap id="customermap" type="customer">
        <!--主键绑定-->
        <id property="id" column="cid"></id>
        <!--非主键绑定-->
        <result property="name" column="cname"></result>
        <result property="age" column="cage"></result>
        <!--多出来的一咕噜绑定orders
        实体类:
        private Integer id;
        private String orderNumber;
        private Double orderPrice;
        -->
        <collection property="orders" ofType="orders">
            <!--主键绑定-->
            <id property="id" column="oid"></id>
            <!--非主键绑定-->
            <result property="orderNumber" column="orderNumber"></result>
            <result property="orderPrice" column="orderPrice"></result>
            <result property="customer_id" column="customer_id"></result>
        </collection>
    </resultMap>
    <select id="getAllCustomers" resultMap="customermap">
        select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id
    </select>
    <!--
    //按主键查询信息
    Customer getByIdCustomer(Integer id);
    -->
    <select id="getByIdCustomer" resultMap="customermap" parameterType="int">
        select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id where c.id=#{id}
    </select>
</mapper>

package com.chentianyu;

import com.chentianyu.mapper.CustomerMapper;
import com.chentianyu.pojo.Customer;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class testApp {
    SqlSession sqlSession;
    CustomerMapper customerMapper;
    @Before
    public void test01() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        customerMapper = sqlSession.getMapper(CustomerMapper.class);
    }
    @Test
    public void test02(){
        List<Customer> allCustomers = customerMapper.getAllCustomers();
        allCustomers.forEach(customer -> System.out.println(customer));
    }
    @After
    public void test03(){
        sqlSession.close();
    }

    @Test
    public void test04(){
        Customer byIdCustomer = customerMapper.getByIdCustomer(2);
        System.out.println(byIdCustomer);
    }
}

运行test04

Opening JDBC Connection
Created connection 1046545660.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3e6104fc]
==>  Preparing: select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id where c.id=?
==> Parameters: 2(Integer)
<==    Columns: cid, cname, cage, oid, orderNumber, orderPrice, customer_id
<==        Row: 2, 李四, 23, 13, 90, 19.99, 2
<==      Total: 1
Customer{id=2, name='李四', age=23, orders=[Orders{id=13, orderNumber='90', orderPrice=19.99, customer_id=2}]}

但是如果把2改成王五的id=3

@Test
public void test04(){
    Customer byIdCustomer = customerMapper.getByIdCustomer(3);
    System.out.println(byIdCustomer);
}

Opening JDBC Connection
Created connection 1046545660.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3e6104fc]
==>  Preparing: select c.id cid,c.name cname,c.age cage,o.id oid,orderNumber,orderPrice,customer_id from customer c join orders o on c.id = o.customer_id where c.id=?
==> Parameters: 3(Integer)
<==      Total: 0
null

关联关系是有方向的