0709作业
1.创建工程导入mybatis依赖jar包
2.创建mybatis-config.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"></properties>
<typeAliases>
<package name="com.openlab.pojo"></package>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"></property>
<property name="url" value="${url}"></property>
<property name="username" value="${username}"></property>
<property name="password" value="${password}"></property>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com//openlab//mapper//BillMapper.xml"></mapper>
<mapper resource="com//openlab//mapper//ProviderMapper.xml"></mapper>
</mappers>
</configuration>
3.创建db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf-8
username=root
password=123456
4.创建bill的实体类
package com.openlab.pojo;
import java.math.BigDecimal;
import java.util.Date;
public class Bill {
private int id;
private String billCode;
private String productName;
private String productDesc;
private String productUnit;
private double productCount;
private double totalPrice;
private int isPayment;
private int createdBy;
private Date creationDate;
private int modifyBy;
private Date modifyDate;
private int providerId;
private Provider provider;
public Provider getProvider() {
return provider;
}
public void setProvider(Provider provider) {
this.provider = provider;
}
public int getId() {
return id;
}
public void setId(int 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 int getIsPayment() {
return isPayment;
}
public void setIsPayment(int isPayment) {
this.isPayment = isPayment;
}
public int getCreatedBy() {
return createdBy;
}
public void setCreatedBy(int createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public int getModifyBy() {
return modifyBy;
}
public void setModifyBy(int modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
public int getProviderId() {
return providerId;
}
public void setProviderId(int providerId) {
this.providerId = providerId;
}
}
5.创建Provider的实体类
package com.openlab.pojo;
import java.util.Date;
import java.util.List;
public class Provider {
private int id;
private String proCode;
private String proName;
private String proDesc;
private String proContact;
private String proPhone;
private String proAddress;
private String proFax;
private int createdBy;
private Date creationDate;
private Date modifyDate;
private int modifyBy;
private String companyLicPicPath;
private String orgCodePicPath;
private List<Bill> bills;
public List<Bill> getBills() {
return bills;
}
public void setBills(List<Bill> bills) {
this.bills = bills;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public int getId() {
return id;
}
public void setId(int 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 int getCreatedBy() {
return createdBy;
}
public void setCreatedBy(int createdBy) {
this.createdBy = createdBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
public int getModifyBy() {
return modifyBy;
}
public void setModifyBy(int modifyBy) {
this.modifyBy = modifyBy;
}
public String getCompanyLicPicPath() {
return companyLicPicPath;
}
public void setCompanyLicPicPath(String companyLicPicPath) {
this.companyLicPicPath = companyLicPicPath;
}
public String getOrgCodePicPath() {
return orgCodePicPath;
}
public void setOrgCodePicPath(String orgCodePicPath) {
this.orgCodePicPath = orgCodePicPath;
}
}
5.创建BillMapper接口
package com.openlab.mapper;
import com.openlab.pojo.Bill;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface BillMapper {
List<Bill> selectByProductName(@Param("name") String productName);
List<Bill> selectByProviderId(@Param("id") Integer id);
List<Bill> selectByIsPayment(@Param("num") Integer num);
}
6.创建ProviderMapper接口
package com.openlab.mapper;
import com.openlab.pojo.Provider;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface ProviderMapper {
List<Provider> selectBillByProviderId(@Param("id") Integer providerId);
}
7.创建BillMapper接口的xml映射文件
<?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="com.openlab.mapper.BillMapper">
<resultMap id="BillResultMap" type="Bill">
<id property="id" column="id"></id>
<result property="billCode" column="billCode"></result>
<result property="productName" column="productName"></result>
<result property="totalPrice" column="totalPrice"></result>
<result property="isPayment" column="isPayment"></result>
<result property="creationDate" column="creationDate"></result>
<association property="provider" javaType="Provider">
<id property="id" column="pid"></id>
<result property="proName" column="proName"></result>
</association>
</resultMap>
<select id="selectByProductName" resultMap="BillResultMap">
select b.*,p.id as pid,p.proName
from smbms_bill b
left join smbms_provider p
on b.providerId = p.id
where b.productName like concat('%',#{name},'%')
</select>
<select id="selectByProviderId" resultMap="BillResultMap">
select b.*,p.id as pid,p.proName
from smbms_bill b
left join smbms_provider p
on b.providerId = p.id
where b.providerId = #{id}
</select>
<select id="selectByIsPayment" resultMap="BillResultMap">
select b.*,p.id as pid,p.proName
from smbms_bill b
left join smbms_provider p
on b.providerId = p.id
where b.isPayment = #{num}
</select>
</mapper>
8.创建ProviderMapper接口的xml配置文件
<?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="com.openlab.mapper.ProviderMapper">
<select id="selectBillByProviderId" resultMap="providerResultMap">
select b.*,p.id as pid,p.proName
from smbms_bill b
left join smbms_provider p
on b.providerId = p.id
where b.providerId = #{id}
</select>
<resultMap id="providerResultMap" type="Provider">
<id property="id" column="pid"></id>
<result property="proName" column="proName"></result>
<collection property="bills" ofType="Bill">
<id property="id" column="id"></id>
<result property="billCode" column="billCode"></result>
<result property="productName" column="productName"></result>
<result property="totalPrice" column="totalPrice"></result>
<result property="isPayment" column="isPayment"></result>
<result property="creationDate" column="creationDate"></result>
</collection>
</resultMap>
</mapper>
9.测试
package com.openlab;
import com.openlab.mapper.BillMapper;
import com.openlab.mapper.ProviderMapper;
import com.openlab.pojo.Bill;
import com.openlab.pojo.Provider;
import com.openlab.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class TestBillMapper {
@Test
public void test1(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByProductName("米");
for (Bill bill : bills){
int num = bill.getIsPayment();
String isPay = null;
if(num==1){
isPay = "未付款";
}else if(num==2){
isPay = "已付款";
}
System.out.println(bill.getBillCode() + "\t"
+ bill.getProductName() + "\t"
+ bill.getProvider().getProName() + "\t"
+ bill.getTotalPrice() + "\t"
+ isPay + "\t"
+ bill.getCreationDate());
}
SqlSessionFactoryUtil.closeSqlSession(sqlSession);
}
@Test
public void test2(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByProviderId(2);
for (Bill bill : bills){
int num = bill.getIsPayment();
String isPay = null;
if(num==1){
isPay = "未付款";
}else if(num==2){
isPay = "已付款";
}
System.out.println(bill.getBillCode() + "\t"
+ bill.getProductName() + "\t"
+ bill.getProvider().getProName() + "\t"
+ bill.getTotalPrice() + "\t"
+ isPay + "\t"
+ bill.getCreationDate());
}
SqlSessionFactoryUtil.closeSqlSession(sqlSession);
}
@Test
public void test3(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderId(2);
for(Provider provider:providers){
for (Bill bill : provider.getBills()){
int num = bill.getIsPayment();
String isPay = null;
if(num==1){
isPay = "未付款";
}else if(num==2){
isPay = "已付款";
}
System.out.println(bill.getBillCode() + "\t"
+ bill.getProductName() + "\t"
+ provider.getProName() + "\t"
+ bill.getTotalPrice() + "\t"
+ isPay + "\t"
+ bill.getCreationDate());
}
}
SqlSessionFactoryUtil.closeSqlSession(sqlSession);
}
@Test
public void test4(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Bill> bills = sqlSession.getMapper(BillMapper.class).selectByIsPayment(2);
for (Bill bill : bills){
int num = bill.getIsPayment();
String isPay = null;
if(num==1){
isPay = "未付款";
}else if(num==2){
isPay = "已付款";
}
System.out.println(bill.getBillCode() + "\t"
+ bill.getProductName() + "\t"
+ bill.getProvider().getProName() + "\t"
+ bill.getTotalPrice() + "\t"
+ isPay + "\t"
+ bill.getCreationDate());
}
SqlSessionFactoryUtil.closeSqlSession(sqlSession);
}
}