Mybatis的学习8 ____模糊查询,分页显示和动态sql编写
1.实验环境:
smbms_bill(账单表)
smbms_provider(供应商表)
2.pojo(实体类的编写)
pojo.Bill.java
(注意:实体类中有一个providerName属性,而在 smbms_bill 数据库中没有该字段,其对应的是smbms_provider中的proName字段)
1 public class Bill { 2 private Integer id; //id 3 private String billCode; //账单编码 4 private String productName; //商品名称 5 private String productDesc; //商品描述 6 private String productUnit; //商品单位 7 private BigDecimal productCount; //商品数量 8 private BigDecimal totalPrice; //总金额 9 private Integer isPayment; //是否支付 10 private Integer providerId; //供应商ID 11 private Integer createdBy; //创建者 12 private Date creationDate; //创建时间 13 private Integer modifyBy; //更新者 14 private Date modifyDate;//更新时间 15 16 private String providerName;//供应商名称 17 18 19 public String getProviderName() { 20 return providerName; 21 } 22 public void setProviderName(String providerName) { 23 this.providerName = providerName; 24 } 25 public Integer getId() { 26 return id; 27 } 28 public void setId(Integer id) { 29 this.id = id; 30 } 31 public String getBillCode() { 32 return billCode; 33 } 34 public void setBillCode(String billCode) { 35 this.billCode = billCode; 36 } 37 public String getProductName() { 38 return productName; 39 } 40 public void setProductName(String productName) { 41 this.productName = productName; 42 } 43 public String getProductDesc() { 44 return productDesc; 45 } 46 public void setProductDesc(String productDesc) { 47 this.productDesc = productDesc; 48 } 49 public String getProductUnit() { 50 return productUnit; 51 } 52 public void setProductUnit(String productUnit) { 53 this.productUnit = productUnit; 54 } 55 public BigDecimal getProductCount() { 56 return productCount; 57 } 58 public void setProductCount(BigDecimal productCount) { 59 this.productCount = productCount; 60 } 61 public BigDecimal getTotalPrice() { 62 return totalPrice; 63 } 64 public void setTotalPrice(BigDecimal totalPrice) { 65 this.totalPrice = totalPrice; 66 } 67 public Integer getIsPayment() { 68 return isPayment; 69 } 70 public void setIsPayment(Integer isPayment) { 71 this.isPayment = isPayment; 72 } 73 74 public Integer getProviderId() { 75 return providerId; 76 } 77 public void setProviderId(Integer providerId) { 78 this.providerId = providerId; 79 } 80 public Integer getCreatedBy() { 81 return createdBy; 82 } 83 public void setCreatedBy(Integer createdBy) { 84 this.createdBy = createdBy; 85 } 86 public Date getCreationDate() { 87 return creationDate; 88 } 89 public void setCreationDate(Date creationDate) { 90 this.creationDate = creationDate; 91 } 92 public Integer getModifyBy() { 93 return modifyBy; 94 } 95 public void setModifyBy(Integer modifyBy) { 96 this.modifyBy = modifyBy; 97 } 98 public Date getModifyDate() { 99 return modifyDate; 100 } 101 public void setModifyDate(Date modifyDate) { 102 this.modifyDate = modifyDate; 103 } 104 105 @Override 106 public String toString() { 107 return "Bill{" + 108 "id=" + id + 109 ", billCode='" + billCode + '\'' + 110 ", productName='" + productName + '\'' + 111 ", productDesc='" + productDesc + '\'' + 112 ", productUnit='" + productUnit + '\'' + 113 ", productCount=" + productCount + 114 ", totalPrice=" + totalPrice + 115 ", isPayment=" + isPayment + 116 ", providerId=" + providerId + 117 ", createdBy=" + createdBy + 118 ", creationDate=" + creationDate + 119 ", modifyBy=" + modifyBy + 120 ", modifyDate=" + modifyDate + 121 ", providerName='" + providerName + '\'' + 122 '}'; 123 } 124 }
pojo.Provider.java
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 Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate;//更新时间 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 Date getCreationDate() { return creationDate; } public void setCreationDate(Date 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; } @Override public String toString() { return "Provider{" + "id=" + id + ", proCode='" + proCode + '\'' + ", proName='" + proName + '\'' + ", proDesc='" + proDesc + '\'' + ", proContact='" + proContact + '\'' + ", proPhone='" + proPhone + '\'' + ", proAddress='" + proAddress + '\'' + ", proFax='" + proFax + '\'' + ", createdBy=" + createdBy + ", creationDate=" + creationDate + ", modifyBy=" + modifyBy + ", modifyDate=" + modifyDate + '}'; } }
需求1:根据用户给的条件查询 满足条件的 Bill 的结果列表,并进行分页显示:
分析:用户可能提供providerName参数,而smbms_bill数据库中没有该字段(实体类中添加providerName属性),存在于smbms_provider表中,所以进行连表查询。
1.BillMapper接口如下:
1 //3.通过条件查询获取Bill的列表(两张表) providerName在bill表中没有该字段 2 List<Bill> getBillList(@Param("providerName") String providerName, @Param("providerId") Integer providerId, @Param("currentPage") Integer currentPage,@Param("pageSize") Integer pageSize);
2.BillMapper.xml文件如下:
<select id="getBillList" resultMap="Res"> select b.*,p.proName from smbms_bill b,smbms_provider p where b.providerId=p.id <if test="providerId!=null"> and b.providerId=#{providerId} </if> <if test="providerName!=null and providerName!=''"> and p.proName like CONCAT ('%',#{providerName},'%') </if> order by providerId asc limit #{currentPage},#{pageSize} </select> <resultMap id="Res" type="Bill"> <result property="providerName" column="proName"/> </resultMap>
3.测试类:
1 @Test 2 public void getBillList(){ 3 SqlSession sqlSession = MybatisUtils.getSqlSession(); 4 BillMapper mapper = sqlSession.getMapper(BillMapper.class); 5 6 int currentPage=1; 7 int pageSize=2; 8 int indexStart=(currentPage-1)*pageSize; 9 10 List<Bill> bills = mapper.getBillList(null, 8, indexStart, pageSize); 11 12 for (Bill bill : bills) { 13 System.out.println(bill); 14 } 15 }
需求二:对已有订单进行修改:
分析:bill有很多字段,用户可以进行部分字段属性值的修改操作,此时使用动态sql实现:
1.BillMapper接口的编写:
1 //5.修改订单 2 int modifyBill(Bill bill);
2.BillMapper.xml配置文件的编写:
<!--最后一个没有逗号~~~~~--> <update id="modifyBill" parameterType="Bill"> update smbms_bill <trim prefix="set" prefixOverrides="," suffix="where id=#{id}"> <if test="billCode!=null">billCode=#{billCode},</if> <if test="productName!=null">productName=#{productName},</if> <if test="productDesc!=null">productDesc=#{productDesc}</if> </trim> </update>
3.测试类:
@Test public void modifyBill(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BillMapper mapper = sqlSession.getMapper(BillMapper.class); Bill bill1 = new Bill(); bill1.setId(3); bill1.setBillCode("33333333333"); bill1.setProductName("伸腿瞪眼丸"); bill1.setProductDesc("爽歪歪"); int bills = mapper.modifyBill(bill1); System.out.println(bills); }