项目----mapper层多表数据访问

1.实体类entities关联id

package com.cc.springboot.entities;

import java.util.Date;

/**
 * 帐单实体类
 * @Title: Provider
 * @Description: com.mengxuegu.springboot.entities
 * @Auther: www.mengxuegu.com
 * @Version: 1.0
 */
public class Bill {

    private Integer bid;
    // 账单编码
    private String billCode;
    // 商品名称
    private String billName;
    // 商品单位
    private String billCom;
    // 商品数量
    private Integer billNum;
    // 总金额
    private Double money;
    // 供应商
    private Provider provider;

    //供应商id
    private Integer pid;

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    // 是否付款 0 未付款， 1已付款
    private Integer pay;
    // 创建时间
    private Date createDate;
    public Bill() {}

    public Bill(Integer bid, String billCode, String billName, String billCom, Integer billNum, Double money, Provider provider, Integer pay) {
        this.bid = bid;
        this.billCode = billCode;
        this.billName = billName;
        this.billCom = billCom;
        this.billNum = billNum;
        this.money = money;
        this.provider = provider;
        this.pay = pay;
        this.createDate = new Date();
    }


    @Override
    public String toString() {
        return "Bill{" +
                "bid=" + bid +
                ", billCode='" + billCode + '\'' +
                ", billName='" + billName + '\'' +
                ", billCom='" + billCom + '\'' +
                ", billNum=" + billNum +
                ", money=" + money +
                ", provider=" + provider +
                ", pay=" + pay +
                ", createDate=" + createDate +
                '}';
    }

    public Integer getBid() {
        return bid;
    }

    public void setBid(Integer bid) {
        this.bid = bid;
    }

    public String getBillCode() {
        return billCode;
    }

    public void setBillCode(String billCode) {
        this.billCode = billCode;
    }

    public String getBillName() {
        return billName;
    }

    public void setBillName(String billName) {
        this.billName = billName;
    }

    public String getBillCom() {
        return billCom;
    }

    public void setBillCom(String billCom) {
        this.billCom = billCom;
    }

    public Integer getBillNum() {
        return billNum;
    }

    public void setBillNum(Integer billNum) {
        this.billNum = billNum;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public Provider getProvider() {
        return provider;
    }

    public void setProvider(Provider provider) {
        this.provider = provider;
    }

    public Integer getPay() {
        return pay;
    }

    public void setPay(Integer pay) {
        this.pay = pay;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
}

关键-》添加了pid以及setter和getter

2.新建一个实体类entities/BillProvider 继承bill

package com.cc.springboot.entities;
//封装新的实体，因为账单列表页和详情页都有供应商的名称
public class BillProvider extends Bill {
    private  String providerName;

    public String getProviderName() {
        return providerName;
    }

    public void setProviderName(String providerName) {
        this.providerName = providerName;
    }
}

 3.创建mapper接口 mapper/BillMapper

package com.cc.springboot.mapper;

import com.cc.springboot.entities.Bill;
import com.cc.springboot.entities.BillProvider;

import java.util.List;

public interface BillMapper {
    //    获取账单列表
    List<BillProvider> getBills(Bill bill);

    //    通过id获取账单列表
    BillProvider getBillByBid(Integer bid);

//    添加
    int addBill(Bill bill);

//    更新
    int updateBill(Bill bill);

//    通过id删除
    int deleteBillByBid(Integer bid);
}

4.创建静态资源resources/mabaties/mapper/BillMapper.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.cc.springboot.mapper.BillMapper">

    <!--查询列表-->
   <select id="getBills" resultType="com.cc.springboot.entities.BillProvider">
       select  * from bill b left join provider p on b.pid=p.pid
       where 1=1
       <if test='billName != "null" and billName != ""'>
           and b.bill_name like '%${billName}%'
       </if>
       <if test='pid !=null'>
           and b.pid=#{pid}
       </if>
       <if test='pay != null'>
           and b.pay =#{pay}
       </if>
   </select>

    <!--通过id查询-->
    <select id="getBillByBid" resultType="com.cc.springboot.entities.BillProvider">
           select * from bill b left join provider p on b.pid=p.pid
       where b.bid=#{bid}
    </select>

    <!--添加-->
    <insert id="addBill">
           INSERT INTO `bill` ( `bill_code`, `bill_name`, `bill_com`, `bill_num`, `money`, `pay`, `pid`, `create_date`)
        VALUES ( #{billCode}, #{billName}, #{billCom}, #{billNum}, #{money}, #{pay}, #{pid}, now());

    </insert>

    <!--修改-->
    <update id="updateBill">
   UPDATE `bill`
        SET `bill_code`=#{billCode}, `bill_name`= #{billName}, `bill_com`=#{billCom}, `bill_num`=#{billNum}, `money`=#{money}, `pay`=#{pay}, `pid`=#{pid}, `create_date`='2018-11-17 15:22:03'
        WHERE `bid`=#{bid}
    </update>

    <!--删除-->
    <delete id="deleteBillByBid">
        delete from bill where bid=#{bid}
    </delete>
</mapper>

5. 测试

package com.cc.springboot;

import com.cc.springboot.entities.Bill;
import com.cc.springboot.entities.BillProvider;
import com.cc.springboot.entities.Provider;
import com.cc.springboot.mapper.BillMapper;
import com.cc.springboot.mapper.ProviderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


import java.util.List;


@SpringBootTest(classes = {SpringBootBillApplication.class})
public class SpringBootBillApplicationTests {
   @Autowired
    ProviderMapper providerMapper;

   @Autowired
    BillMapper billMapper;
    @Test
    void contextLoads() {
        Bill b=new Bill();
        b.setBillName("com");
        List<BillProvider> bills = billMapper.getBills(b);
        System.out.println(bills.get(0));

        BillProvider billProvider = billMapper.getBillByBid(5);
        System.out.println(billProvider);

        Bill bill=(Bill) billProvider;
        bill.setBillName("hhhhhh");
        billMapper.updateBill(bill);


        billMapper.addBill( new Bill(null, "Bi-AA11", "粮油aaa", "斤", 80,480.8, new Provider(2002, "PR-BB", "梦学谷供应商222", "小李", "18888666982", "深圳软件园", "0911-0123453", "品质B"), 1));

        billMapper.deleteBillByBid(5);
 
    }

}