学习一对多关系，在“SSM框架学习（二）”的基础上，从订单和客户的多对一关系掌握知识

一、创建订单表

在mybatis_test数据库下创建orders表

二、创建实体类（称为javabean/entity/domain）

1、建Order实体类

2、编写实体类的代码

package com.ssm.mybatis.entity;

/**
 * 订单实体类
 */
public class Order {
    private Integer id;
    private String orderno;
    private float price;
    private int cid;

    public Integer getId() {
        return id;
    }

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

    public String getOrderno() {
        return orderno;
    }

    public void setOrderno(String orderno) {
        this.orderno = orderno;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderno='" + orderno + '\'' +
                ", price=" + price +
                ", cid=" + cid +
                '}';
    }
}

三、创建Mapper文件

1、在resources下新建OrderMapper.xml文件


2、编写映射文件OrderMapper.xml

四、在mybatis-config.xml添加OrderMapper映射

五、测试

1、在OrderMapper.xml中编写SQL插入语句

    
        insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{cid})
    

2、在测试类中编写插入方法insertOrder()

//插入订单
    @Test
    public void insertOrder () throws IOException{
        String resource="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);

        SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s=sf.openSession();

        Order order=new Order();
        order.setId(1);
        order.setOrderno("001");
        order.setPrice(5.2f);
        order.setCid(2);

        s.insert("OrderMapper.insert",order);

        s.commit();
        s.close();
    }

查看数据库，插入成功

订单表中的cid为此订单的对应客户id，此时的值为手动输入，考虑订单与客户的多对一关系，应该改为面向对象编程

六、面向对象编程

插入订单

1、修改订单属性

将Order类中的cid更换为Customer的对象

package com.ssm.mybatis.entity;

/**
 * 订单实体类
 */
public class Order {
    private Integer id;
    private String orderno;
    private float price;
    //private int cid;
    private Customer customer;

    public Integer getId() {
        return id;
    }

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

    public String getOrderno() {
        return orderno;
    }

    public void setOrderno(String orderno) {
        this.orderno = orderno;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", orderno='" + orderno + '\'' +
                ", price=" + price +
                ", customer=" + customer +
                '}';
    }
}

2、在OrderMapper.xml修改SQL插入语句

    
        insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{customer.id})
    

原插入语句为：

        insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{cid})

将cid改为customer.id即表示为Customer实体类中的id属性

3、测试

创建order测试类


编写测试方法

//插入订单
    @Test
    public void insert() throws Exception{
        String resource="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);

        SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s=sf.openSession();

        Order order=new Order();

        order.setId(4);
        order.setOrderno("002");
        order.setPrice(10f);

        Customer customer=s.selectOne("CustomerMapper.findbyid",1);
        order.setCustomer(customer);
        s.insert("OrderMapper.insert",order);

        s.commit();
        s.close();
    }

查看数据库，可以发现2号订单关联上1号客户。

若在新增订单的同时新增客户，需要在插入完客户之后马上获得客户的id。

在CustomerMapper.xml修改SQL插入语句

    
    
    
      insert into customers(id,name,age) values (#{id}, #{name},#{age})
    

编写insert()插入方法

//插入订单
    @Test
    public void insert() throws Exception{
        String resource="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);

        SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s=sf.openSession();

        Customer customer=new Customer();
        customer.setId(4);
        customer.setName("jim");
        customer.setAge(15);
        s.insert("CustomerMapper.insert",customer);

        Order order=new Order();
        order.setId(3);
        order.setOrderno("003");
        order.setPrice(10f);
        order.setCustomer(customer);
        s.insert("OrderMapper.insert",order);

        s.commit();
        s.close();
    }

查看数据可以发现，3号订单和4号客户关联关系上。

查找订单

1、在OrderMapper.xml添加SQL的select查询语句

    
        select * from orders where id=#{id}
    

2、TestOrderCRUD类中编写findbyid()

//查询订单
    @Test
    public void findbyid() throws Exception{
        String resource="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);

        SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s=sf.openSession();

        Order order=s.selectOne("OrderMapper.findbyid",1);

        System.out.println(order.toString()+","+order.getCustomer().toString());

        s.commit();
        s.close();
    }

观察控制台发现，查找出来的订单对应客户id为空。

知识点

1. jdbc的连接方式有内连接、外连接和交叉连接。
2. 外连接：左外连接右外连接
3. 外连接和内连接的区别：外连接可以补全，没有的值可以用空值补全；内连接不能补全

原因：

1. 此处查询为关联查询。
2. 在查询订单的时候一同将客户查出，应该用左外连接

查询方法：

1. 左外连接sql语句：

        select
        o.id as oid,
        o.orderno as oorderno,
        o.price as oprice,

        c.id as cid,
        c.name as cname,
        c.age as cage

        from orders o
           left outer join customers c on o.cid=c.id
        where o.id=#{id}

2. 同时需要对查询语句的结果映射，映射出的结果为订单，订单的属性里有客户的属性

    
    
    
        select
        o.id as oid,
        o.orderno as oorderno,
        o.price as oprice,

        c.id as cid,
        c.name as cname,
        c.age as cage

        from orders o
           left outer join customers c on o.cid=c.id
        where o.id=#{id}
    
    
    
    
        
        
        
        
        
        
        
            
            
            
        
    

3、测试

若一个客户有多个订单，用collection集合表示

查询所有订单以及对应客户

1、在OrderMapper.xml添加SQL的select查询语句

    
        select
        o.id as oid,
        o.orderno as oorderno,
        o.price as oprice,

        c.id as cid,
        c.name as cname,
        c.age as cage

        from orders o
           left outer join customers c on o.cid=c.id
    

2、TestOrderCRUD类中编写findall()

//查询所有订单
    @Test
    public void findall() throws Exception{
        String resource="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);

        SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s=sf.openSession();

        List orderList=s.selectList("OrderMapper.findall");
        for(Order o:orderList){
            System.out.println(o.toString()+o.getCustomer().toString());
        }

        s.commit();
        s.close();
    }

3、查看控制台对比数据库

七、多对一关系图解（结果映射关系）