框架课程0709作业

0709作业

1.创建工程导入mybatis依赖jar包
2.创建mybatis-config.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <properties resource="db.properties"></properties>

    <typeAliases>
        <package name="com.openlab.pojo"></package>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"></property>
                <property name="url" value="${url}"></property>
                <property name="username" value="${username}"></property>
                <property name="password" value="${password}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com//openlab//mapper//BillMapper.xml"></mapper>
        <mapper resource="com//openlab//mapper//ProviderMapper.xml"></mapper>

    </mappers>

</configuration>

3.创建db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8
username=root
password=123456

4.创建bill的实体类

package com.openlab.pojo;

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

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

    private Provider provider;

    public Provider getProvider() {
        return provider;
    }

    public void setProvider(Provider provider) {
        this.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 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 int getIsPayment() {
        return isPayment;
    }

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

    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 int getProviderId() {
        return providerId;
    }

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

5.创建Provider的实体类

package com.openlab.pojo;

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

public class Provider {
    private int id;
    private String proCode;
    private String proName;
    private String proDesc;
    private String proContact;
    private String proPhone;
    private String proAddress;
    private String proFax;
    private int createdBy;
    private Date creationDate;
    private Date modifyDate;
    private int modifyBy;
    private String companyLicPicPath;
    private String orgCodePicPath;

    private List<Bill> bills;

    public List<Bill> getBills() {
        return bills;
    }

    public void setBills(List<Bill> bills) {
        this.bills = bills;
    }

    public Date getCreationDate() {
        return creationDate;
    }

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

    public int getId() {
        return id;
    }

    public void setId(int 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 int getCreatedBy() {
        return createdBy;
    }

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

    public Date getModifyDate() {
        return modifyDate;
    }

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

    public int getModifyBy() {
        return modifyBy;
    }

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

    public String getCompanyLicPicPath() {
        return companyLicPicPath;
    }

    public void setCompanyLicPicPath(String companyLicPicPath) {
        this.companyLicPicPath = companyLicPicPath;
    }

    public String getOrgCodePicPath() {
        return orgCodePicPath;
    }

    public void setOrgCodePicPath(String orgCodePicPath) {
        this.orgCodePicPath = orgCodePicPath;
    }
}

5.创建BillMapper接口

package com.openlab.mapper;

import com.openlab.pojo.Bill;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface BillMapper {

    List<Bill> selectByProductName(@Param("name") String productName);

    List<Bill> selectByProviderId(@Param("id") Integer id);

    List<Bill> selectByIsPayment(@Param("num") Integer num);
}

6.创建ProviderMapper接口

package com.openlab.mapper;

import com.openlab.pojo.Provider;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ProviderMapper {

    List<Provider> selectBillByProviderId(@Param("id") Integer providerId);
}

7.创建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.openlab.mapper.BillMapper">

    <resultMap id="BillResultMap" type="Bill">
        <id property="id" column="id"></id>
        <result property="billCode" column="billCode"></result>
        <result property="productName" column="productName"></result>
        <result property="totalPrice" column="totalPrice"></result>
        <result property="isPayment" column="isPayment"></result>
        <result property="creationDate" column="creationDate"></result>

        <association property="provider" javaType="Provider">
            <id property="id" column="pid"></id>
            <result property="proName" column="proName"></result>
        </association>
    </resultMap>


    <select id="selectByProductName" resultMap="BillResultMap">
        select b.*,p.id as pid,p.proName
         from smbms_bill b
         left join smbms_provider p
         on b.providerId = p.id
         where b.productName like concat('%',#{name},'%')
    </select>

    <select id="selectByProviderId" resultMap="BillResultMap">
        select b.*,p.id as pid,p.proName
         from smbms_bill b
         left join smbms_provider p
         on b.providerId = p.id
         where b.providerId = #{id}
    </select>

    <select id="selectByIsPayment" resultMap="BillResultMap">
        select b.*,p.id as pid,p.proName
         from smbms_bill b
         left join smbms_provider p
         on b.providerId = p.id
         where b.isPayment = #{num}
    </select>

</mapper>

8.创建ProviderMapper接口的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.openlab.mapper.ProviderMapper">

    <select id="selectBillByProviderId" resultMap="providerResultMap">
        select b.*,p.id as pid,p.proName
         from smbms_bill b
         left join smbms_provider p
         on b.providerId = p.id
         where b.providerId = #{id}
    </select>

    <resultMap id="providerResultMap" type="Provider">
        <id property="id" column="pid"></id>
        <result property="proName" column="proName"></result>
        <collection property="bills" ofType="Bill">
            <id property="id" column="id"></id>
            <result property="billCode" column="billCode"></result>
            <result property="productName" column="productName"></result>
            <result property="totalPrice" column="totalPrice"></result>
            <result property="isPayment" column="isPayment"></result>
            <result property="creationDate" column="creationDate"></result>
        </collection>
    </resultMap>
</mapper>

9.测试

package com.openlab;

import com.openlab.mapper.BillMapper;
import com.openlab.mapper.ProviderMapper;
import com.openlab.pojo.Bill;
import com.openlab.pojo.Provider;
import com.openlab.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class TestBillMapper {

    @Test
    public void test1(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByProductName("米");
        for (Bill bill : bills){
            int num = bill.getIsPayment();
            String isPay = null;
            if(num==1){
                isPay = "未付款";
            }else if(num==2){
                isPay = "已付款";
            }
            System.out.println(bill.getBillCode() + "\t"
            + bill.getProductName() + "\t"
            + bill.getProvider().getProName() + "\t"
            + bill.getTotalPrice() + "\t"
            + isPay + "\t"
            + bill.getCreationDate());
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void test2(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByProviderId(2);
        for (Bill bill : bills){
            int num = bill.getIsPayment();
            String isPay = null;
            if(num==1){
                isPay = "未付款";
            }else if(num==2){
                isPay = "已付款";
            }
            System.out.println(bill.getBillCode() + "\t"
                    + bill.getProductName() + "\t"
                    + bill.getProvider().getProName() + "\t"
                    + bill.getTotalPrice() + "\t"
                    + isPay + "\t"
                    + bill.getCreationDate());
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void test3(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderId(2);
        for(Provider provider:providers){
            for (Bill bill : provider.getBills()){
                int num = bill.getIsPayment();
                String isPay = null;
                if(num==1){
                    isPay = "未付款";
                }else if(num==2){
                    isPay = "已付款";
                }
                System.out.println(bill.getBillCode() + "\t"
                        + bill.getProductName() + "\t"
                        + provider.getProName() + "\t"
                        + bill.getTotalPrice() + "\t"
                        + isPay + "\t"
                        + bill.getCreationDate());
            }
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void test4(){
        SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
        List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByIsPayment(2);
        for (Bill bill : bills){
            int num = bill.getIsPayment();
            String isPay = null;
            if(num==1){
                isPay = "未付款";
            }else if(num==2){
                isPay = "已付款";
            }
            System.out.println(bill.getBillCode() + "\t"
                    + bill.getProductName() + "\t"
                    + bill.getProvider().getProName() + "\t"
                    + bill.getTotalPrice() + "\t"
                    + isPay + "\t"
                    + bill.getCreationDate());
        }
        SqlSessionFactoryUtil.closeSqlSession(sqlSession);
    }
}