【1023练习】MyBatis练习二

package cn.kgc.pojo;

import java.math.BigDecimal;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: L
 * Date: 2019/10/23
 * Time: 16:33
 * Description: No Description
 */
public class Bill {
    private int id;   //id
    private String billCode; //账单编码
    private String productName; //商品名称
    private String productDesc; //商品描述
    private String productUnit; //商品单位
    private String productCount; //商品数量
    private String totalPrice; //总金额
    private int isPayment; //是否支付
    private int providerId; //供应商ID
    private int createdBy; //创建者
    private Date creationDate; //创建时间
    private int modifyBy; //更新者
    private Date modifyDate;//更新时间
    private Provider provider;//供应商

    public int getId() {
        return id;
    }

    public void setId(int 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 String getProductCount() {
        return productCount;
    }

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

    public String getTotalPrice() {
        return totalPrice;
    }

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

    public int getIsPayment() {
        return isPayment;
    }

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

    public int getProviderId() {
        return providerId;
    }

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

    public int getCreatedBy() {
        return createdBy;
    }

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

    public Date getCreationDate() {
        return creationDate;
    }

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

    public int getModifyBy() {
        return modifyBy;
    }

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

    public Date getModifyDate() {
        return modifyDate;
    }

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

    public Provider getProvider() {
        return provider;
    }

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

package cn.kgc.pojo;

import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: L
 * Date: 2019/10/21
 * Time: 15:38
 * Description: No Description
 */
public class Provider {
    private Integer id; //id
    private String proCode;     //供应商编码
    private String proName; //供应商名称
    private String proDesc; //描述广告
    private String proContact;  //联系人
    private String proPhone;   //电话
    private String proAddress; //地址
    private String proFax;   //传真
    private Integer createdBy;   //创建者
    private String creationDate;  //创建时间
    private Integer modifyBy;     //更新者
    private Date modifyDate;   //更新时间
    private List<Bill> billList;

    public Integer getId() {
        return id;
    }

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

    public String getProCode() {
        return proCode;
    }

    public void setProCode(String proCode) {
        this.proCode = proCode;
    }

    public String getProName() {
        return proName;
    }

    public void setProName(String proName) {
        this.proName = proName;
    }

    public String getProDesc() {
        return proDesc;
    }

    public void setProDesc(String proDesc) {
        this.proDesc = proDesc;
    }

    public String getProContact() {
        return proContact;
    }

    public void setProContact(String proContact) {
        this.proContact = proContact;
    }

    public String getProPhone() {
        return proPhone;
    }

    public void setProPhone(String proPhone) {
        this.proPhone = proPhone;
    }

    public String getProAddress() {
        return proAddress;
    }

    public void setProAddress(String proAddress) {
        this.proAddress = proAddress;
    }

    public String getProFax() {
        return proFax;
    }

    public void setProFax(String proFax) {
        this.proFax = proFax;
    }

    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 Date getModifyDate() {
        return modifyDate;
    }

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

    public List<Bill> getBillList() {
        return billList;
    }

    public void setBillList(List<Bill> billList) {
        this.billList = billList;
    }
}

<?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.smbms.dao.user.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>

    <!--    1.按照商品名称模糊查询  订单信息-->
    <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>
    <!--    1.按照 商品名称(模糊查询), 供应商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>

    <!--    2.实现供应商表的增加-->
    <insert id="addBill" parameterType="Bill">
        insert into smbms_bill(productName,productDesc,productUnit)
        values(#{productName},#{productDesc},#{productUnit});
    </insert>
    <!--    3.根据id修改其信息-->
    <update id="updateBill" parameterType="Bill">
        update smbms_bill set productName=#{productName} where id = #{id}
    </update>
    <!--    4.根据id删除其信息,直接用@Delete写了，详情看BillMapper.java-->

    <!--    5.根据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>

<?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.ProviderMapper">

    <!-- 查询供应商表记录数 -->
    <select id="count" resultType="int">
        select count(1) as count from smbms_provider
    </select>

    <!-- 查询供应商列表 -->
    <select id="getProviderList" resultType="cn.kgc.pojo.Provider">
        select * from smbms_provider
    </select>

    <!-- 根据供应商名称查询供应商列表(模糊查询) -->
    <select id="getProviderListByProName" resultType="cn.kgc.pojo.Provider" parameterType="String">
        select * from smbms_provider where proName like CONCAT ('%',#{proName},'%')
    </select>

    <!-- 增加供应商 -->
    <insert id="add" parameterType="Provider">
        insert into smbms_provider (proCode,proName,proDesc,proContact,proPhone,
                                proAddress,proFax,createdBy,creationDate)
                values (#{proCode},#{proName},#{proDesc},#{proContact},#{proPhone},#{proAddress},
                #{proFax},#{createdBy},#{creationDate})
    </insert>

    <!-- 修改供应商信息 -->
    <update id="modify" parameterType="Provider">
        update smbms_provider set proCode=#{proCode},proName=#{proName},proDesc=#{proDesc},
                    proContact=#{proContact},proPhone=#{proPhone},proAddress=#{proAddress},
                    proFax=#{proFax},modifyBy=#{modifyBy},modifyDate=#{modifyDate}
                 where id = #{id}
    </update>

    <!-- 根据供应商ID删除供应商信息 -->
    <delete id="deleteProviderById" parameterType="Integer">
        delete from smbms_provider where id = #{id}
    </delete>


</mapper>

    @Test
    public void test15(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        List<Bill> billList = sqlSession.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 test16(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        Bill bill1 = new Bill();
        bill1.setProductName("不");
        bill1.setIsPayment(2);
        bill1.setProviderId(14);

        List<Bill> billList = sqlSession.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 test17(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        Bill bill1 = new Bill();
        bill1.setProductName("一加5T");
        bill1.setProductDesc("电子-手机");
        bill1.setProductUnit("部");
        int i = sqlSession.getMapper(BillMapper.class).addBill(bill1);
        sqlSession.commit();
    }

    @Test
    public void test18(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        Bill bill1 = new Bill();
        bill1.setProductName("小米5sP");
        bill1.setId(21);
        int i = sqlSession.getMapper(BillMapper.class).updateBill(bill1);
        System.out.println(i);
        sqlSession.commit();
    }

    @Test
    public void test19(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        int i = sqlSession.getMapper(BillMapper.class).deleteBillById(21);
        sqlSession.commit();
        System.out.println(i);
    }

    @Test
    public void test20(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        List<Provider> providerList = sqlSession.getMapper(BillMapper.class).getProvideById(1);

        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());
            }
        }
    }