Mybaits课堂练习02
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class Bill { private Integer id; private String billCode; private String productName; private String productDesc; private String productUnit; private Double productCount; private Double totalPrice; private Integer isPayment; private Integer createdBy; private String creationDate; private Integer modifyBy; private String modifyDate; private Integer providerId; public Integer getId() { return id; } public void setId(Integer 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 Integer getIsPayment() { return isPayment; } public void setIsPayment(Integer isPayment) { this.isPayment = isPayment; } public Integer getCreatedBy() { return createdBy; } public void setCreatedBy(Integer createdBy) { this.createdBy = createdBy; } public String getCreationDate() { return creationDate; } public void setCreationDate(String creationDate) { this.creationDate = creationDate; } public Integer getModifyBy() { return modifyBy; } public void setModifyBy(Integer modifyBy) { this.modifyBy = modifyBy; } public String getModifyDate() { return modifyDate; } public void setModifyDate(String modifyDate) { this.modifyDate = modifyDate; } public Integer getProviderId() { return providerId; } public void setProviderId(Integer providerId) { this.providerId = providerId; } public Provider getProvider() { return provider; } public void setProvider(Provider provider) { this.provider = provider; } private Provider provider; }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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="cn.kgc.dao.BillMapper"> <resultMap id="billList" type="Bill"> <result property="billCode" column="billCode"/> <result property="productName" column="productName"/> <result property="totalPrice" column="totalPrice"/> <result property="isPayment" column="isPayment"/> <result property="creationDate" column="creationDate"/> <association property="provider" javaType="Provider"> <result property="proName" column="proName"/> </association> </resultMap> <!-- 通过商品名称模糊查询订单数据--> <select id="getBillByName" parameterType="String" resultMap="billList"> select billCode,productName,totalPrice,isPayment,p.creationDate as creationDate,proName from smbms_bill b,smbms_provider p where productName like concat('%',#{name},'%') and providerId = p.id </select> <!-- 通过商品名称(模糊查询), 供应商id, 是否付款 查询 订单信息--> <select id="getBill" parameterType="Bill" resultMap="billList"> select billCode,productName,totalPrice,isPayment,p.creationDate as creationDate,proName from smbms_bill b,smbms_provider p where productName like concat('%',#{productName},'%') and providerId = #{providerId} and isPayment=#{isPayment} and providerId = p.id </select> <!-- 供应商表的增加--> <insert id="addBill" parameterType="Bill"> insert into smbms_bill(productName,productDesc,productUnit) values(#{productName},#{productDesc},#{productUnit}); </insert> <!-- 根据id修改其信息--> <update id="updateBill" parameterType="Bill"> update smbms_bill set productName=#{productName} where id = #{id} </update> <!-- 根据id删除信息--> <delete id="deleteBillById" parameterType="Bill"> delete from smbms_bill where id = #{id} </delete> <!-- 根据id查询供应商及其所有的订单列表--> <resultMap id="providerList" type="Provider"> <id property="id" column="id"/> <result property="proCode" column="proCode"/> <result property="proName" column="proName"/> <result property="proContact" column="proContact"/> <result property="proPhone" column="proPhone"/> <collection property="billList" ofType="Bill"> <result property="billCode" column="billCode"/> <result property="productName" column="productName"/> <result property="totalPrice" column="totalPrice"/> <result property="isPayment" column="isPayment"/> </collection> </resultMap> <select id="getProvideById" parameterType="int" resultMap="providerList"> select p.id as id,proCode,proName,proContact,proPhone,billCode,productName, totalPrice,isPayment from smbms_bill b,smbms_provider p where providerId = p.id and p.id=#{id} </select> </mapper>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface BillMapper { /** * 根据商品名查询订单信息(模糊查询) * @param name * @return */ List<Bill> getBillByName(String name); /** * 按照 商品名称(模糊查询), 供应商id, 是否付款 * 查询订单信息 * @param bill * @return */ List<Bill> getBill(Bill bill); /** * 增加商品名,商品类型,商品件数单位 * @param bill * @return */ int addBill(Bill bill); /** * 根据id,修改商品名 * @param bill * @return */ int updateBill(Bill bill); /** * 根据id 删除其数据 * @param bill */ int deleteBillById(int bill); /** * 根据id获取供应商及其供应商品信息 * @param id * @return */ List<Provider> getProvideById(int id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class BillTest { SqlSession session = MybatisUtils.getSqlSession(); /** * 通过商品名称模糊查询订单数据 */ @Test public void test01(){ List<Bill> billList= session.getMapper(BillMapper.class).getBillByName("油"); for (Bill bill:billList ) { System.out.println(bill.getBillCode()+bill.getProductName()+ bill.getTotalPrice()+bill.getIsPayment()+bill.getCreationDate() +bill.getProvider().getProName()); } } @Test public void test02() { Bill bill1 = new Bill(); bill1.setProductName("海"); bill1.setIsPayment(1); bill1.setProviderId(11); List<Bill> billList = session.getMapper(BillMapper.class).getBill(bill1); for (Bill bill : billList) { System.out.println(bill.getBillCode() + "---" + bill.getProductName() + "---" + bill.getTotalPrice() + "---" + bill.getIsPayment() + "---" + bill.getCreationDate() + "---" + bill.getProvider().getProName()); } } @Test public void addTest(){ Bill bill1 = new Bill(); bill1.setProductName("维他柠檬茶"); bill1.setProductDesc("饮料--茶"); bill1.setProductUnit("罐"); int i = session.getMapper(BillMapper.class).addBill(bill1); session.commit(); } @Test public void updateTest(){ Bill bill1 = new Bill(); bill1.setProductDesc("饮料--茶"); bill1.setId(21); int i = session.getMapper(BillMapper.class).updateBill(bill1); System.out.println(i); session.commit(); } @Test public void deleteBillByIdTest(){ int i = session.getMapper(BillMapper.class).deleteBillById(19); session.commit(); System.out.println(i); } @Test public void providerTest(){ List<Provider> providerList = session.getMapper(BillMapper.class).getProvideById(2); for (Provider provider : providerList) { System.out.println(provider.getId()+"---"+provider.getProName()+"---"+provider.getProContact() +"---"+provider.getProPhone()); for (Bill bill : provider.getBillList()) { System.out.println("---"+bill.getBillCode()+"---"+bill.getProductName()+"---"+ bill.getTotalPrice()+"---"+bill.getIsPayment()); } } } }
运行结果: