0710作业
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//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.封装SqlSessionFactoryUtil工具类
package com.openlab.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class SqlSessionFactoryUtil {
private static SqlSessionFactory factory;
static {
String str = "mybatis-config.xml";
try {
InputStream is = Resources.getResourceAsStream(str);
factory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
SqlSession sqlSession = factory.openSession();
return sqlSession;
}
public static void closeSqlSession(SqlSession sqlSession){
if(sqlSession!=null){
sqlSession.close();
}
}
}
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;
}
}
6.编写Bill实体类
package com.openlab.pojo;
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;
}
}
7.编写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> selectBillByProviderIdArray(Integer[] ids);
List<Provider> selectBillByProviderIdList(List<Integer> ids);
List<Provider> selectUnclear(@Param("code") String code ,@Param("name") String name,
@Param("people") String people,@Param("year") String year);
}
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">
<resultMap id="providerResultMap" type="Provider">
<id property="id" column="id"></id>
<result property="proCode" column="proCode"></result>
<result property="proName" column="proName"></result>
<result property="proContact" column="proContact"></result>
<result property="proAddress" column="proAddress"></result>
<result property="creationDate" column="creationDate"></result>
<collection property="bills" ofType="Bill">
<id property="id" column="bid"></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="bDate"></result>
</collection>
</resultMap>
<select id="selectBillByProviderIdArray" resultMap="providerResultMap">
select p.*,b.id as bid,b.billCode,b.productName,b.totalPrice,b.isPayment,b.creationDate bDate
from smbms_provider p
left join smbms_bill b
on b.providerId = p.id
where b.providerId in
<foreach collection="array" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</select>
<select id="selectBillByProviderIdList" resultMap="providerResultMap">
select p.*,b.id as bid,b.billCode,b.productName,b.totalPrice,b.isPayment,b.creationDate bDate
from smbms_provider p
left join smbms_bill b
on b.providerId = p.id
where b.providerId in
<foreach collection="list" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</select>
<select id="selectUnclear" resultType="Provider">
select * from smbms_provider where 1 = 1
<choose>
<when test="code!=null and code!=''">
and proCode like concat('%',#{code},'%')
</when>
<when test="name!=null and name!=''">
and proName like concat('%',#{name},'%')
</when>
<when test="people!=null and people!=''">
and proContact like concat('%',#{people},'%')
</when>
<when test="year!=null and year!=''">
and year(creationDate) = #{year}
</when>
</choose>
</select>
</mapper>
9.测试
package com.openlab.test;
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.ArrayList;
import java.util.List;
public class TestExer {
@Test
public void test1(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
Integer[] array = new Integer[]{4,8,9};
List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderIdArray(array);
for(Provider provider:providers){
System.out.println("供应商:");
System.out.println(provider.getId()+"\t"+
provider.getProCode()+"\t"+
provider.getProName()+"\t"+
provider.getProContact()+"\t"+
provider.getProAddress()+"\t"+
provider.getCreationDate());
System.out.println("该供应商的账单:");
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 test2(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Integer> list = new ArrayList<>();
list.add(4);
list.add(8);
list.add(9);
List<Provider> providers = sqlSession.getMapper(ProviderMapper.class).selectBillByProviderIdList(list);
for(Provider provider:providers){
System.out.println("供应商:");
System.out.println(provider.getId()+"\t"+
provider.getProCode()+"\t"+
provider.getProName()+"\t"+
provider.getProContact()+"\t"+
provider.getProAddress()+"\t"+
provider.getCreationDate());
System.out.println("该供应商的账单:");
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 test3(){
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSession();
List<Provider> providers =
sqlSession.getMapper(ProviderMapper.class).selectUnclear("1", "", "", "");
for(Provider provider : providers){
System.out.println(provider.getId()+"\t"+
provider.getProCode()+"\t"+
provider.getProName()+"\t"+
provider.getProContact()+"\t"+
provider.getProAddress()+"\t"+
provider.getCreationDate());
}
SqlSessionFactoryUtil.closeSqlSession(sqlSession);
}
}