Mybatis中表的关联关系

文章目录

一、表之间的关联关系
二、一对多关联关系
- 2.1 代码实现
三、多对一关联关系
- 3.1 代码实现
总结

一、表之间的关联关系

关联关系是有方向的。

一对多关联关系
多对一关联关系
一对一关联关系
多对多关联关系

二、一对多关联关系

客户和订单就是典型的一对多关系。（一个客户可以对应多个订单）
客户表(customer 主键表)：

订单表(orders 外键表)：

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

2.1 代码实现

编写实体类(customer)：

package com.bjpowernode.pojo;

import java.util.List;

public class Customer {
    //customer表中的三个列
    private int id;
    private String name;
    private int age;

    //该客户名下的所有订单的集合
    private List ordersList;

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

    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 int getAge() {
        return age;
    }

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

    public List getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List ordersList) {
        this.ordersList = ordersList;
    }

    public Customer(int id, String name, int age, List ordersList) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.ordersList = ordersList;
    }

    public Customer() {
    }
}

编写实体类(orders)：

package com.bjpowernode.pojo;

public class Orders {
    private int id;
    private String orderNumber;
    private Double orderPrice;

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

    public Orders() {
    }

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }

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

编写接口 CustomerMapper

public interface CustomerMapper {
    //根据客户的id查询客户信息，并同时查询该客户名下的所有订单
    List getById(int id);
}

编写xml文件，CustomerMapper.xml：
注意 collection标签中是oftype
注意起别名问题

    
        
        
        
        
            
            
            
        
    
    
        select c.id cid,name,age,o.id oid,orderNumber,orderPrice,customer_id
        from customer c inner join orders o on c.id = o.customer_id
        where c.id = #{cid}

测试：

    @Test
    public void test1() throws IOException {
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        CustomerMapper cMapper = session.getMapper(CustomerMapper.class);
        List list = cMapper.getById(1);
        list.forEach(customer-> System.out.println(customer));
    }

运行结果：

存在bug：
当测试去查找id为3的客户信息以及其名下的订单，由于没有订单，所以导致id为3的客户信息也查不出来：

改进：
修改xml文件中的sql语句，等值匹配改为左连接：

 
        select o.id oid,orderNumber,orderPrice,c.id cid,name,age
        from orders o inner join customer c on o.customer_id = c.id
        where o.id = #{oid}

测试：

    @Test
    public void test2() throws IOException {
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
        Orders orders = ordersMapper.getByOrder(11);
        System.out.println(orders);
    }

运行结果：

总结

一对多：one2many (one中间有o—>collection—>oftype)
多对一：many2one(many中间有a—>assocation—>javatype)

Mybatis中表的关联关系

Java相关栏目本月热门文章