java SSM第三章学习内容（mybatis-resultMap映射,association一对一，collection一对多）
学习内容：
resultMap映射
association一对一
collection一对多
一.resultMap映射
用途：将结果集映射到java对象中，把需要映射的属性和类型填入其中
select:
id 里面的内容名字，必须跟接口方法名一致
resultMap 里面的可以自定义名字，到时调用resultMap必须跟自定义的名称一样
parameterType 参数类型，例如你是放int就填int，对象就填对象名字，还有一个resultType是返回类型
sql语句 根据查询需求写SQL语句，需要传入的值用#{自定义名称，最好跟要传入的属性名差不多}括起来
resultMap:
type 类型，如果没有自动匹配包要加上全称如:com.entity.Bill
id select传过来的id
result property 映射实体内属性的名字
result column 映射sql列名字
二.association表一对一连接查询
association用途：联表一对一查询
property 实体类中的别名
javaType 属性类型
id 可以给也可以不给
association里面的result property 实体类的别名 sql数据库列名
三.collection表一对多连接查询
用途:联表一对多查询
大部分都一致
Collection property 实体类属性的别名（因一对多查询的是集合结果集，需要在实体类创建一个集合）
Collection ofType 集合集的类型
思路：1.实体类添加属性
2.接口填写查询方法
3.xml添加相应映射
4.在测试类通过静态方法获得SqlSession拿到查询结果
参考代码：
com.dao
BillMapper接口
public interface BillMapper {
//多参数
List<Bill> selectmf(@Param("id2")int id2,@Param("isPayment2")int isPayment2,@Param("productName2")String productName2);
//一对一表id查询
List<Bill> select1d1mh(int id);
//一对一表模糊查询
List<Bill> select1d1mh2(String productName);
//一对一表是否付费
List<Bill> selectff(int isPayment);
}
ProviderMapper接口
public interface ProviderMapper {
//查询总提条数
int count();
//查询数据库总数
List<Provider> selectAll();
//增加
int insertProvider(Provider provider);
//修改
int updateProvider(Provider provider);
//删除
int delectProvider(Provider provider);
//一对多查询
List<Provider> selectydd(int id);
}
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.dao.BillMapper">
<resultMap type="com.entity.Bill" id="BillProviderResult">
<!-- <id property="id" column="id"/> -->
<result property="billCode" column="billCode"/>
<result property="productName" column="productName"/>
<result property="totalPrice" column="totalPrice"/>
<result property="isPayment" column="isPayment"/>
<association property="provider" javaType="com.entity.Provider">
<!-- <id property="id" column="sp.id"/> -->
<result property="proCode" column="proCode"/>
<result property="proName" column="proName"/>
<result property="proContact" column="proContact"/>
<result property="proPhone" column="proPhone"/>
</association>
</resultMap>
<!-- 普通条件+模糊查询 -->
<select id="selectmf" resultType="com.entity.Bill">
SELECT * FROM smbms_bill WHERE id=#{id2} and isPayment=#{isPayment2} and productName LIKE CONCAT(#{productName2},'%')
</select>
<!-- 一对一ID查询 -->
<select id="select1d1mh" resultMap="BillProviderResult" parameterType="int">
SELECT * FROM smbms_provider sp,smbms_bill sb WHERE sb.id=#{id} and sb.id=sp.id
</select>
<!-- 一对一模糊查询 -->
<select id="select1d1mh2" resultMap="BillProviderResult" parameterType="String">
SELECT * FROM smbms_provider sp,smbms_bill sb WHERE sb.productName Like CONCAT(#{productName},'%') and sb.id=sp.id
</select>
<!-- 一对一是否付费查询 -->
<select id="selectff" resultMap="BillProviderResult" parameterType="int">
SELECT * FROM smbms_provider sp,smbms_bill sb WHERE sb.isPayment=#{isPayment} and sb.id=sp.id
</select>
</mapper>
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.dao.ProviderMapper">
<resultMap type="com.entity.Provider" id="ProviderBillResult">
<result 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="com.entity.Bill">
<result property="billCode" column="billCode"/>
<result property="productName" column="productName"/>
<result property="totalPrice" column="totalPrice"/>
<result property="isPayment" column="isPayment"/>
</collection>
</resultMap>
<!-- id代表方法名，必须跟接口方法名一致，resultType代表返回信息 -->
<select id="count" resultType="int">
select count(1) as count from smbms_provider
</select>
<!-- com.entitiy.User 路径是从包到类，通过.形式填写，不包含后缀名 -->
<select id="selectAll" resultType="com.entity.Provider">
select * from smbms_provider
</select>
<!-- 增 -->
<insert id="insertProvider" parameterType="com.entity.Provider">
insert into smbms_provider VALUES(#{id},#{proCode},#{proName},#{proDesc},#{proContact},#{proPhone},#{proAddress},#{proFax},#{createdBy},#{creationDate},#{modifyDate},#{modifyBy})
</insert>
<!-- 修 -->
<update id="updateProvider" parameterType="com.entity.Provider">
UPDATE smbms_provider SET proContact=#{proContact} WHERE id=#{id}
</update>
<!-- 删除 -->
<delete id="delectProvider" parameterType="com.entity.Provider">
delete from smbms_provider where id=#{id}
</delete>
<!-- 一对多查询 -->
<select id="selectydd" resultMap="ProviderBillResult" parameterType="int">
SELECT sp.id,sp.proCode,sp.proName,sp.proContact,sp.proPhone,sb.billCode,sb.productName,sb.totalPrice,sb.isPayment FROM smbms_provider sp,smbms_bill sb WHERE sb.id=sp.id and sp.id=#{id}
</select>
</mapper>
实体类
Bill 实体类
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 double modifyBy;
private Date modifyDate;
private double 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 double getModifyBy() {
return modifyBy;
}
public void setModifyBy(double modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
public double getProviderId() {
return providerId;
}
public void setProviderId(double providerId) {
this.providerId = providerId;
}
}
Provider 实体类
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 List<Bill>billList;
public List<Bill> getBillList() {
return billList;
}
public void setBillList(List<Bill> billList) {
this.billList = billList;
}
public Provider(int id, String proCode, String proName, String proDesc,
String proContact, String proPhone, String proAddress,
String proFax, int createdBy, Date creationDate, Date modifyDate,
int modifyBy) {
super();
this.id = id;
this.proCode = proCode;
this.proName = proName;
this.proDesc = proDesc;
this.proContact = proContact;
this.proPhone = proPhone;
this.proAddress = proAddress;
this.proFax = proFax;
this.createdBy = createdBy;
this.creationDate = creationDate;
this.modifyDate = modifyDate;
this.modifyBy = modifyBy;
}
public Provider() {
super();
}
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 getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
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 Provider(String proContact,int id) {
super();
this.proContact = proContact;
this.id = id;
}
public Provider(int id) {
super();
this.id = id;
}
}
测试类
BillMapperTest
public class BillMapperTest {
public static void main(String[] args) {
SqlSession connent = MyBatisUtil.createsqlSqlSession();
/* <!-- 普通条件+模糊查询 -->
List<Bill> st = connent.getMapper(BillMapper.class).selectmf(1,2,"洗");
for (Bill s:st) {
System.out.println(s.getId()+s.getProductName()+s.getIsPayment()+s.getBillCode());
}*/
/*<!-- 一对一id查询 -->
List<Bill> st=connent.getMapper(BillMapper.class).select1d1mh(1);
for (Bill s:st) {
System.out.println(s.getBillCode()+"\t"+s.getProductName()+"\t"+s.getProvider().getProCode()+"\t"+
s.getProvider().getProName()+"\t"+s.getProvider().getProPhone()+"\t"+s.getTotalPrice()+"\t"+s.getIsPayment());
}*/
/* <!-- 一对一模糊查询 -->
List<Bill> st=connent.getMapper(BillMapper.class).select1d1mh2("洗");
for (Bill s:st) {
System.out.println(s.getBillCode()+"\t"+s.getProductName()+"\t"+s.getProvider().getProCode()+"\t"+
s.getProvider().getProName()+"\t"+s.getProvider().getProPhone()+"\t"+s.getTotalPrice()+"\t"+s.getIsPayment());
}*/
//<!-- 一对一已付费查询 -->
List<Bill> st =connent.getMapper(BillMapper.class).selectff(1);
for (Bill s:st) {
System.out.println(s.getBillCode()+"\t"+s.getProductName()+"\t"+s.getProvider().getProCode()+"\t"+
s.getProvider().getProName()+"\t"+s.getProvider().getProPhone()+"\t"+s.getTotalPrice()+"\t"+s.getIsPayment());
}
}
}
ProvideMapperTest
public class ProvideMapperTest {
public static void main(String[] args) {
SqlSession connent = MyBatisUtil.createsqlSqlSession();
/*//查询
List<Provider> list = connent.getMapper(ProviderMapper.class).selectAll();
int num = connent.getMapper(ProviderMapper.class).count();
System.out.println(num);
for (Provider l:list) {
System.out.println(l.getId()+"\t"+l.getProCode()+"\t"+l.getProAddress()+"\t"+l.getProDesc());
}*/
/*//增加
Date date = null;
try {
date = new SimpleDateFormat("yyyy-MM-dd").parse("2005-12-12");
} catch (Exception e) {
// TODO: handle exception
}
Provider provider = new Provider(16,"ZJ_GYS004", "洗米杰用品厂", "长期合作伙伴，主营产品：充气娃娃",
"冼伟杰", "18567674222","广东省广州市员村", "0579-34452222", 1, date,date,0);
int num=connent.getMapper(ProviderMapper.class).insertProvider(provider);
connent.commit();
System.out.println(num);*/
/*//修改
Provider p = new Provider("冼伟杰", 16);
int num = connent.getMapper(ProviderMapper.class).updateProvider(p);
connent.commit();
System.out.println(num);*/
/*删除
Provider p = new Provider(1);
int num = connent.getMapper(ProviderMapper.class).delectProvider(p);
connent.commit();
System.out.println(num);*/
//一对多
List<Provider> list = connent.getMapper(ProviderMapper.class).selectydd(1);
for (Provider l:list) {
System.out.print(l.getId()+"\t"+l.getProCode()+"\t"+l.getProName()+"\t"+l.getProContact()+"\t"+l.getProPhone());
for (Bill b:l.getBillList()) {
System.out.println(b.getId()+"\t"+b.getBillCode()+"\t"+b.getTotalPrice()+"\t"+b.getIsPayment());
}
}
}
}
Util工具类
public class MyBatisUtil {
private static SqlSessionFactory factory;
static{
try {
InputStream is = Resources
.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
} catch (Exception e) {
// TODO: handle exception
}
}
public static SqlSession createsqlSqlSession(){
return factory.openSession(false);
}
public static void closess(SqlSession ss){
if (ss!=null) {
ss.close();
}
}
}
连接数据库的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>
<!-- 引入database.properties文件 -->
<properties resource="database.properties" />
<!-- 配置mybatis运行环境 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 获取数据库信息 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${user}" />
<property name="password" value="${pwd}" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 将UserMapper.xml加载配置文件中,路径从包到类，包含文件后缀名，前面用/隔开 -->
<mapper resource="com/dao/ProviderMapper.xml" />
<mapper resource="com/dao/BillMapper.xml"/>
</mappers>
</configuration>