练习一:
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);
}