Mybaits课堂练习02

public class Bill {
    private Integer id;
    private String billCode;
    private String productName;
    private String productDesc;
    private String productUnit;
    private Double productCount;
    private Double totalPrice;
    private Integer isPayment;
    private Integer createdBy;
    private String creationDate;
    private Integer modifyBy;
    private String modifyDate;
    private Integer providerId;

    public Integer getId() {
        return id;
    }

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

    public String getBillCode() {
        return billCode;
    }

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

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getProductDesc() {
        return productDesc;
    }

    public void setProductDesc(String productDesc) {
        this.productDesc = productDesc;
    }

    public String getProductUnit() {
        return productUnit;
    }

    public void setProductUnit(String productUnit) {
        this.productUnit = productUnit;
    }

    public Double getProductCount() {
        return productCount;
    }

    public void setProductCount(Double productCount) {
        this.productCount = productCount;
    }

    public Double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Integer getIsPayment() {
        return isPayment;
    }

    public void setIsPayment(Integer isPayment) {
        this.isPayment = isPayment;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public String getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(String creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public String getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(String modifyDate) {
        this.modifyDate = modifyDate;
    }

    public Integer getProviderId() {
        return providerId;
    }

    public void setProviderId(Integer providerId) {
        this.providerId = providerId;
    }

    public Provider getProvider() {
        return provider;
    }

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

    private Provider provider;


}

<?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="cn.kgc.dao.BillMapper">

<resultMap id="billList" type="Bill">
<result property="billCode" column="billCode"/>
<result property="productName" column="productName"/>
<result property="totalPrice" column="totalPrice"/>
<result property="isPayment" column="isPayment"/>
<result property="creationDate" column="creationDate"/>
<association property="provider" javaType="Provider">
<result property="proName" column="proName"/>
</association>
</resultMap>
<!-- 通过商品名称模糊查询订单数据-->
    <select id="getBillByName" parameterType="String" resultMap="billList">
         select billCode,productName,totalPrice,isPayment,p.creationDate as creationDate,proName
            from smbms_bill b,smbms_provider p where productName like concat('%',#{name},'%')
            and providerId = p.id
    </select>
    <!--    通过商品名称(模糊查询), 供应商id, 是否付款 查询  订单信息-->
    <select id="getBill" parameterType="Bill" resultMap="billList">
        select billCode,productName,totalPrice,isPayment,p.creationDate as creationDate,proName
            from smbms_bill b,smbms_provider p where productName like concat('%',#{productName},'%')
            and  providerId = #{providerId} and isPayment=#{isPayment} and providerId = p.id
    </select>

    <!--    供应商表的增加-->
    <insert id="addBill" parameterType="Bill">
        insert into smbms_bill(productName,productDesc,productUnit)
        values(#{productName},#{productDesc},#{productUnit});
    </insert>
    <!--    根据id修改其信息-->
    <update id="updateBill" parameterType="Bill">
        update smbms_bill set productName=#{productName} where id = #{id}
    </update>
<!--    根据id删除信息-->
    <delete id="deleteBillById" parameterType="Bill">
        delete from smbms_bill where id = #{id}
    </delete>


    <!--   根据id查询供应商及其所有的订单列表-->
    <resultMap id="providerList" type="Provider">
        <id property="id" column="id"/>
        <result property="proCode" column="proCode"/>
        <result property="proName" column="proName"/>
        <result property="proContact" column="proContact"/>
        <result property="proPhone" column="proPhone"/>
        <collection property="billList" ofType="Bill">
            <result property="billCode" column="billCode"/>
            <result property="productName" column="productName"/>
            <result property="totalPrice" column="totalPrice"/>
            <result property="isPayment" column="isPayment"/>
        </collection>
    </resultMap>

    <select id="getProvideById" parameterType="int" resultMap="providerList">
        select p.id as id,proCode,proName,proContact,proPhone,billCode,productName,
            totalPrice,isPayment from smbms_bill b,smbms_provider p
                where providerId = p.id and p.id=#{id}
    </select>



</mapper>

public interface BillMapper {
    /**
     * 根据商品名查询订单信息（模糊查询）
     * @param name
     * @return
     */
    List<Bill> getBillByName(String name);
    /**
     * 按照 商品名称(模糊查询), 供应商id, 是否付款
     * 查询订单信息
     * @param bill
     * @return
     */
    List<Bill> getBill(Bill bill);

    /**
     * 增加商品名，商品类型，商品件数单位
     * @param bill
     * @return
     */
    int addBill(Bill bill);

    /**
     * 根据id，修改商品名
     * @param bill
     * @return
     */
    int updateBill(Bill bill);

    /**
     * 根据id 删除其数据
     * @param bill
     */

    int deleteBillById(int bill);

    /**
     * 根据id获取供应商及其供应商品信息
     * @param id
     * @return
     */
    List<Provider> getProvideById(int id);

}

public class BillTest {
    SqlSession session = MybatisUtils.getSqlSession();


    /**
     * 通过商品名称模糊查询订单数据
     */
    @Test
    public void test01(){

        List<Bill> billList= session.getMapper(BillMapper.class).getBillByName("油");
        for (Bill bill:billList
             ) {
            System.out.println(bill.getBillCode()+bill.getProductName()+
                    bill.getTotalPrice()+bill.getIsPayment()+bill.getCreationDate()
            +bill.getProvider().getProName());

        }
    }
    @Test
    public void test02() {
        Bill bill1 = new Bill();
        bill1.setProductName("海");
        bill1.setIsPayment(1);
        bill1.setProviderId(11);

        List<Bill> billList = session.getMapper(BillMapper.class).getBill(bill1);

        for (Bill bill : billList) {
            System.out.println(bill.getBillCode() + "---" + bill.getProductName()
                    + "---" + bill.getTotalPrice() + "---" + bill.getIsPayment()
                    + "---" + bill.getCreationDate() + "---" + bill.getProvider().getProName());
        }
    }
    @Test
    public void addTest(){
        Bill bill1 = new Bill();
        bill1.setProductName("维他柠檬茶");
        bill1.setProductDesc("饮料--茶");
        bill1.setProductUnit("罐");
        int i = session.getMapper(BillMapper.class).addBill(bill1);
        session.commit();
    }

    @Test
    public void updateTest(){
        Bill bill1 = new Bill();
        bill1.setProductDesc("饮料--茶");

        bill1.setId(21);
        int i = session.getMapper(BillMapper.class).updateBill(bill1);
        System.out.println(i);
        session.commit();
    }

    @Test
    public void deleteBillByIdTest(){
        int i = session.getMapper(BillMapper.class).deleteBillById(19);
        session.commit();
        System.out.println(i);
    }

    @Test
    public void providerTest(){
        List<Provider> providerList = session.getMapper(BillMapper.class).getProvideById(2);

        for (Provider provider : providerList) {
            System.out.println(provider.getId()+"---"+provider.getProName()+"---"+provider.getProContact()
                    +"---"+provider.getProPhone());
            for (Bill bill : provider.getBillList()) {
                System.out.println("---"+bill.getBillCode()+"---"+bill.getProductName()+"---"+
                        bill.getTotalPrice()+"---"+bill.getIsPayment());
            }
        }
    }
}

运行结果：