mybatis练习之二

1、接口类

 1  /**
 2      * 实现按条件查询订单表，查询条件如下
 3      * 商品名称（模糊查询）
 4      * 供应商（供应商id）
 5      * 是否付款
 6      * @param bill
 7      * @return
 8      */
 9     public Bill getBillByOther(Bill bill);
10 
11 
12 /**
13      * 增加供货商信息
14      * @param provider 供货商实体类
15      * @return
16      */
17     public int addProvide(Provider provider);
18 
19 
20 
21 /**
22      * 实现根据供应商id修改供应商信息
23      * @param provider
24      * @return
25      */
26     public int updateProviderByid(Provider provider);
27 
28 
29 
30 /**
31      * 实现根据供应商id删除供应商信息
32      * @param id
33      * @return
34      */
35     public int delProviderByid(@Param("id") String id);
36 
37 
38 
39 /**
40      * 根据供应商id获取供应商及其订单列表
41      * @param id
42      * @return
43      */
44     public Provider getProviderBillList(String id);
45 
46 
47 
48 /**
49      * 根据商品名称（模糊查询）、供应商id、是否付款查询订单以及供应商信息
50      * @param bill
51      * @return
52      */
53     public Bill getBillProviderByBill(Bill bill);

接口类

2、Mapper.xml的配置

 1 <!--    实现按条件查询订单表(无内部嵌套)-->
 2     <select id="getBillByOther" resultMap="BillProviderResult" parameterType="Bill">
 3         SELECT b.*,p.proName FROM smbms_bill b,smbms_provider p WHERE b.providerId = p.id and b.providerId = #{providerId} and isPayment = #{isPayment} and productName LIKE CONCAT('%',#{productName},'%')
 4 </select>
 5 <resultMap id="BillProviderResult" type="Bill">
 6         <result property="billCode" column="billCode"></result>
 7         <result property="productName" column="productName"></result>
 8         <result property="totalPrice" column="totalPrice"></result>
 9         <result property="isPayment" column="isPayment"></result>
10         <result property="creationDate" column="creationDate"></result>
11         <result property="providerName" column="providerName"></result>
12     </resultMap>
13 
14 
15 <!--    实现根据供应商id修改供应商信息-->
16     <update id="updateProviderByid" parameterType="Provider">
17         update smbms_provider set proContact=#{proContact},proPhone=#{proPhone} where id=#{id}
18     </update>
19 
20 
21 <!--    实现根据供应商id删除供应商信息-->
22     <delete id="delProviderByid" parameterType="String">
23         delete from smbms_provider where id=#{id}
24     </delete>
25 
26 
27 
28 <!--    根据供应商id获取供应商及其订单列表-->
29     <select id="getProviderBillList" resultMap="providerBillList" parameterType="String">
30         select p.*,b.billCode,b.productName,b.totalPrice,b.isPayment from smbms_bill b,smbms_provider p where b.providerId = p.id and p.id = #{id}
31     </select>
32 
33  <resultMap id="providerBillList" type="Provider">
34         <id property="id" column="id"></id>
35         <result property="proCode" column="proCode"></result>
36         <result property="proName" column="proName"></result>
37         <result property="proContact" column="proContact"></result>
38         <result property="proPhone" column="proPhone"></result>
39         <collection property="billList" ofType="Bill" resultMap="billList"></collection>
40     </resultMap>
41 
42 <resultMap id="billList" type="Bill">
43         <result property="billCode" column="billCode"></result>
44         <result property="productName" column="productName"></result>
45         <result property="totalPrice" column="totalPrice"></result>
46         <result property="isPayment" column="isPayment"></result>
47     </resultMap>
48 
49 
50 
51 
52 
53 <!--    根据商品名称（模糊查询）、供应商id、是否付款查询订单以及供应商信息-->
54     <select id="getBillProviderByBill" resultMap="billProviderByBill" parameterType="Bill">
55         select b.*,p.proCode,p.proName,p.proContact,p.proPhone from smbms_bill b,smbms_provider p where b.providerId = p.id and productName LIKE CONCAT('%',#{productName},'%') and providerId = #{providerId} and isPayment=#{isPayment}
56     </select>
57 
58 <resultMap id="billProviderByBill" type="Bill">
59         <result property="billCode" column="billCode"></result>
60         <result property="productName" column="productName"></result>
61         <result property="totalPrice" column="totalPrice"></result>
62         <result property="isPayment" column="isPayment"></result>
63         <association property="provider" javaType="provider" resultMap="providerList"></association>
64     </resultMap>
65 
66 <resultMap id="providerList" type="Provider">
67         <result property="proCode" column="proCode"></result>
68         <result property="proName" column="proName"></result>
69         <result property="proContact" column="proContact"></result>
70         <result property="proPhone" column="proPhone"></result>
71     </resultMap>

Mapper.xml的配置

3、测试类

  1 /**
  2      * 实现按条件查询订单表，查询条件如下
  3      * 商品名称（模糊查询）
  4      * 供应商（供应商id）
  5      * 是否付款
  6      */
  7     @Test
  8     public void test07() {
  9         SqlSession sqlSession = myBatisUntil.getSqlSession();
 10         Bill bill = new Bill();
 11         bill.setProductName("日用");
 12         bill.setProviderId(13);
 13         bill.setIsPayment(2);
 14         Bill billByOther = sqlSession.getMapper(ProviderMapper.class).getBillByOther(bill);
 15         if (billByOther == null){
 16             System.out.println("查无信息");
 17         }else {
 18             System.out.println("订单编码:"+billByOther.getBillCode()+"\t商品名称"+billByOther.getProductName()
 19                     +"\t供应商名称"+billByOther.getProviderName()+"\t账单金额"+billByOther.getTotalPrice()
 20                     +"\t是否付款"+billByOther.getIsPayment()+"\t创建时间"+billByOther.getCreationDate());
 21         }
 22        
 23     }
 24 
 25     /**
 26      * 实现根据供应商id修改供应商信息
 27      */
 28     @Test
 29     public void test08(){
 30         SqlSession sqlSession = myBatisUntil.getSqlSession();
 31 
 32         Provider provider = new Provider();
 33         provider.setId(15);
 34         provider.setProContact("冯宝宝");
 35         provider.setProPhone("18060975170");
 36         int count = sqlSession.getMapper(ProviderMapper.class).updateProviderByid(provider);
 37         sqlSession.commit();
 38         System.out.println("添加："+count);
 39     }
 40 
 41     /**
 42      * 实现根据供应商id删除供应商信息
 43      */
 44     @Test
 45     public void test09(){
 46         SqlSession sqlSession = myBatisUntil.getSqlSession();
 47        
 48         int count = sqlSession.getMapper(ProviderMapper.class).delProviderByid("16");
 49         sqlSession.commit();
 50         System.out.println("添加："+count);
 51     }
 52 
 53     /**
 54      * 根据供应商id获取供应商及其订单列表
 55      */
 56     @Test
 57     public void test10(){
 58         SqlSession sqlSession = myBatisUntil.getSqlSession();
 59 
 60         Provider providerBillList = sqlSession.getMapper(ProviderMapper.class).getProviderBillList("1");
 61         sqlSession.commit();
 62         System.out.println("供应商id:"+providerBillList.getId()+"供应商编码:"
 63                 +providerBillList.getProCode()+"供应商联系人:"
 64                 +providerBillList.getProContact()+"供应商联系电话:"
 65                 +providerBillList.getProPhone());
 66         List<Bill> billList = providerBillList.getBillList();
 67         for (Bill bill : billList) {
 68             System.out.println("订单编码:"+bill.getBillCode()+"商品名称:"+bill.getProductName()
 69                     +"订单金额:"+bill.getTotalPrice()+"是否付款(1、未付款 2、已付款):"+bill.getIsPayment());
 70         }
 71     }
 72 
 73     /**
 74      * 根据商品名称（模糊查询）、供应商id、是否付款查询订单以及供应商信息
 75      */
 76     @Test
 77     public void test11(){
 78         SqlSession sqlSession = myBatisUntil.getSqlSession();
 79         Bill bill = new Bill();
 80         bill.setProductName("皂");
 81         bill.setProviderId(13);
 82         bill.setIsPayment(2);
 83         Bill billProviderByBill = sqlSession.getMapper(ProviderMapper.class).getBillProviderByBill(bill);
 84         System.out.println("账单编码:"+billProviderByBill.getBillCode()+"商品名称:"
 85                 +billProviderByBill.getProductName()+"供应商编码:"
 86                 +billProviderByBill.getProvider().getProCode()+"供应商名称:"
 87                 +billProviderByBill.getProvider().getProName()+"供应商联系人:"
 88                 +billProviderByBill.getProvider().getProContact()+"联系电话:"+billProviderByBill.getProvider().getProPhone()
 89                 +"总金额:"+billProviderByBill.getTotalPrice()+"是否支付(1、未付款 2、已付款):"+billProviderByBill.getIsPayment());
 90     }
 91 
 92 
 93 /**
 94      * 增加供货商信息
 95      */
 96     @Test
 97     public void test04(){
 98         SqlSession sqlSession = myBatisUntil.getSqlSession();
 99         
100         Provider provider = new Provider();
101         provider.setId(16);
102         provider.setProCode("ZJ_GYS002");
103         provider.setProName("乐摆日用品厂");
104         provider.setProDesc("长期合作伙伴，主营产品：各种中、高档塑料杯，塑料乐扣水杯（密封杯）、保鲜杯（保鲜盒）、广告杯、礼品杯");
105         provider.setProContact("王佳欣");
106         provider.setProPhone("18060975170");
107         provider.setProAddress("湖里区");
108         provider.setProFax("0579-34452321");
109         provider.setCreatedBy(1);
110         provider.setCreationDate("2019-10-21 10:01:30");
111         int count = sqlSession.getMapper(ProviderMapper.class).addProvide(provider);
112         sqlSession.commit();
113         System.out.println("添加："+count);
114     }

测试类

posted on 2019-10-23 14:50 迪恩9 阅读(412) 评论(0) 编辑收藏举报