第四个MyBatis程序——一对多查询 参考动力节点

类定义

首相类

package rodisland;

import java.util.Set;

public class Country 
{
	private int cid;
	private String cname;
	private Set<Minister> ministers;
	public int getCid() {
		return cid;
	}
	public void setCid(int cid) {
		this.cid = cid;
	}
	public String getCname() {
		return cname;
	}
	public void setCname(String cname) {
		this.cname = cname;
	}
	public Set<Minister> getMinister() {
		return ministers;
	}
	public void setMinister(Set<Minister> ministers) {
		this.ministers = ministers;
	}
	@Override
	public String toString() {
		return "Country [cid=" + cid + ", cname=" + cname + ", minister=" + ministers + "]";
	}
	

}

内阁成员类

package rodisland;

public class Minister 
{
	private int mid;
	private String mname;
	public int getMid() {
		return mid;
	}
	public void setMid(int mid) {
		this.mid = mid;
	}
	public String getMname() {
		return mname;
	}
	public void setMname(String mname) {
		this.mname = mname;
	}
	@Override
	public String toString() {
		return "Minister [mid=" + mid + ", mname=" + mname + "]";
	}

}

由以上类的定义可知，一个首相可以对应多个内阁成员，通过查询首相id，获得该首相的所有内阁成员集合即为一对多查询

表定义

country

minister

Dao接口

package rodisland;
public interface Dao 
{	
	Country selectCountryById(int cid);
	//Country selectMinisterByCountry(int cid);
}

测试方法

public class Mytest 
{
	private Dao dao;
	private SqlSession session;
	@Before
	public void before()
	{
		session=MyBatisUtil.getSqlSession();
		dao=session.getMapper(Dao.class);
	}
	@After
	public void after()
	{
		if(session !=null)
			session.close();
	}
	
	@Test
	public void Test01()
	{
		Country country =dao.selectCountryById(1);
		System.out.println(country);
	}
	
	
}

映射文件

（1）多表连接查询

<?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="rodisland.Dao">
<resultMap type="Country" id="countryMapper">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
<collection property="ministers" ofType="Minister">
<id column="mid" property="mid"/>
<result column="mname" property="mname"/>
</collection>
</resultMap>

<!--多表连接查询-->
<select id="selectCountryById" resultMap="countryMapper">
select cid,cname,mid,mname
from country,minister
where cid=#{xxx} and cid=countryid
</select>
</mapper>

解析：此方法相当于SQL中的内连接

 执行的语句为：select cid,cname,mid,mname from country,minister where cid=? and cid=countryid 

即使字段名和属性名相同，此处也要写好resultMap的映射关系，因为要根据resultMap进行对象的封装

property：指定Country的关联属性，也就是Country类里的集合名

ofType：集合属性的泛型类型

 

 

（2）多表单独查询

<select id="selectMinisterByCountry" resultType="Minister">
	select mid,mname from minister where countryId=#{xxx}
</select>

<resultMap type="Country" id="countryMapper">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
<collection property="ministers" ofType="Minister"
			select="selectMinisterByCountry"
			column="cid"/>
</resultMap>
<select id="selectCountryById" resultMap="countryMapper">
select cid,cname from country where cid=#{cid}
</select>

解析：

执行的语句为：

select cid,cname from country where cid=?

 

select mid,mname from minister where countryId=?

 

执行步骤：

先执行select标签中设定的，id为selectCountryById的方法

设该方法参数为1，则查询结果为：

cid=1,cname=USA

<collection property="ministers" ofType="Minister"
			select="selectMinisterByCountry"
			column="cid"/>

在该设定中，Country类的ministers集合，要通过select中设定的selectMinisterByCountry方法获得，其中selectMinisterByCountry的参数由column指定。

则ministers=select mid,mname from minister where countryId=1

获取对应的ministers集合。