Mybatis003

练习一：

1.在接口 BillMapper中添加方法

//根据供应商id ，获取这些对应商的订单列表信息(数组入参)
    public List<Bill> getBillByProviderArray(Integer[] ids);

    //根据供应商id ，获取这些对应商的订单列表信息(List入参)
    public List<Bill> getBillByProviderList(List<Integer> idlist);

 2.在BillMapper.xml文件中添加SQL语句

<!--     遍历数组参数-->
    <select id="getBillByProviderArray" resultMap="billlist">

        select * from smbms_bill where 1=1
        and providerId in
        <foreach collection="array" item="ids" open="(" separator="," close=")">
                #{ids}
        </foreach>


    </select>
<!--遍历list集合-->
    <select id="getBillByProviderList" resultMap="billlist">

        select * from smbms_bill where 1=1
        and providerId in
        <foreach collection="list" item="idlist" open="(" separator="," close=")">
            #{idlist}
        </foreach>


    </select>

 3.在TestBillMapper.java 中测试

    @Test
    public void test1(){
     //测试数组入参
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        Integer[] ids = new Integer[]{1,2};
        List<Bill> billByProviderArray = sqlSession.getMapper(BillMapper.class).getBillByProviderArray(ids);

        for (Bill bill : billByProviderArray){
            System.out.println(bill.getProviderId()+"\t"+bill.getProductName());
        }

        SqlSessionUtil.closeSqlsession(sqlSession);
    }

    @Test
    public void test2(){
        //测试list入参
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        List<Bill> billByProviderArray = sqlSession.getMapper(BillMapper.class).getBillByProviderList(list);

        for (Bill bill : billByProviderArray){
            System.out.println(bill.getProviderId()+"\t"+bill.getProductName());
        }

        SqlSessionUtil.closeSqlsession(sqlSession);
    }

 练习二：

1.在 接口 ProviderMapper 中添加方法

//模糊查询（供应商编码，供应商名称，供应商联系人，） 时间范围（在本年）
    //使用choose（when，otherwise） 完成
    public List<Provider> getProviderByCodeNameContactTime(@Param("proCode")String proCode, @Param("proName")String proName,@Param("proContact")String  proContact,@Param("fromDate")Date date1,@Param("comeDate")Date date2);

 2.在 ProviderMapper.xml 文件中添加

<select id="getProviderByCodeNameContactTime" resultType="com.bean.Provider">
        SELECT * FROM smbms_provider WHERE 1=1
        <choose>
            <when test="proCode!=null">
                AND proCode like concat('%',#{proCode},'%')
            </when>
            <when test="proName != null">
                AND proName like concat('%',#{proName},'%')
            </when>
            <when test="proContact != null">
                and proContact like concat('%',#{proContact},'%')
            </when>
            <when test="fromDate != null and comDate !=null  ">
                and creationDate between #{fromDate} and #{comeDate}
            </when>
            <otherwise>
                AND featured = 1
            </otherwise>
        </choose>
    </select>

 3.在TestProviderMapper中测试

    @Test
    public void test3(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();

        Date fromDate = null;
        Date comeDate = null;
        try {
            fromDate = new SimpleDateFormat("yyyy-MM-dd").parse("2020-01-01");
             comeDate = new SimpleDateFormat("yyyy-MM-dd").parse("2020-07-10");
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<Provider> list = sqlSession.getMapper(ProviderMapper.class).getProviderByCodeNameContactTime("B", "北", "张",fromDate,comeDate);

        for (Provider p : list){
            System.out.println(p.toString());
        }
        SqlSessionUtil.closeSqlsession(sqlSession);
    }