Mybatis关联查询

一、一对多关联查询

  建表:  country、minister

1.多表连接查询方式

1）定义实体  

public class CountryBean {    private Integer cid;    private String cname;    private Set<MinisterBean> ministerBeanSet;

public class MinisterBean {    private Integer mid;    private String mname;

2)  Mybatis配置

<resultMap id="countryResultMap" type="CountryBean">   <result property="cid" column="CID" jdbcType="INTEGER"/>   <result property="cname" column="CNAME" jdbcType="VARCHAR"/>   <collection property="ministerBeanSet" ofType="MinisterBean">       <id property="mid" column="MID" jdbcType="INTEGER"/>       <result property="mname" column="MNAME" jdbcType="VARCHAR"/>   </collection></resultMap><resultMap id="ministerResultMap" type="MinisterBean">    <result property="mid" column="MID" jdbcType="INTEGER"/>    <result property="mname" column="MNAME" jdbcType="VARCHAR"/></resultMap><select id="getCountryInfo" resultMap="countryResultMap">    select c.cid,c.cname,m.mid,m.mname      from country c,minister m     where c.cid=m.cid      and c.cid=#{cid}</select>

3)测试

@Testpublic void test3(){    CountryBean countryBean = session.selectOne("cm.getCountryInfo",1);    System.out.println(countryBean);}

2.多表单独查询方式

1)实体同上

2) Mybatis配置

关联属性<collection/> 的数据来自于id="getMinisterByCId".而该查询的动态参数来自于id="getCountryByCId"查询结果的cid字段

<resultMap id="countryResultMap" type="CountryBean">   <result property="cid" column="CID" jdbcType="INTEGER"/>   <result property="cname" column="CNAME" jdbcType="VARCHAR"/>   <collection property="ministerBeanSet" ofType="MinisterBean"               select="getMinisterByCId" column="cid"/></resultMap><resultMap id="ministerResultMap" type="MinisterBean">    <result property="mid" column="MID" jdbcType="INTEGER"/>    <result property="mname" column="MNAME" jdbcType="VARCHAR"/></resultMap><select id="getCountryByCId" resultMap="countryResultMap">    select cid,cname from country where cid=#{cid}</select><select id="getMinisterByCId" resultMap="ministerResultMap">    select mid,mname from minister where cid=#{cid}</select>

3)测试

@Test    public void test3(){//        CountryBean countryBean = session.selectOne("cm.getCountryInfo",1);        CountryBean countryBean = session.selectOne("cm.getCountryByCId",1);        System.out.println(countryBean);    }

二、多对一关联查询

1.多表连接查询方式

1.)定义实体

private Integer cid;    private String cname;//    private Set<MinisterBean> ministerBeanSet;

private Integer mid;private String mname;private CountryBean country;

2.)Mybatis配置

<resultMap id="countryResultMap" type="CountryBean">   <result property="cid" column="CID" jdbcType="INTEGER"/>   <result property="cname" column="CNAME" jdbcType="VARCHAR"/></resultMap><resultMap id="ministerResultMap" type="MinisterBean">    <result property="mid" column="MID" jdbcType="INTEGER"/>    <result property="mname" column="MNAME" jdbcType="VARCHAR"/>    <association property="country" javaType="CountryBean">        <id property="cid" column="CID" jdbcType="INTEGER"/>        <result property="cname" column="CNAME" jdbcType="VARCHAR"/>    </association></resultMap><select id="getMinisterInfo" resultMap="ministerResultMap">    select c.cid,c.cname,m.mid,m.mname      from country c,minister m     where c.cid=m.cid      and m.mid=#{mid}</select>

3)测试

@Testpublic void test5(){    MinisterBean ministerBean = session.selectOne("cm.getMinisterInfo",1);    System.out.println(ministerBean);}

2.多表单独查询方式

1)实体同上

2)Mybatis配置

<resultMap id="countryResultMap" type="CountryBean">   <result property="cid" column="CID" jdbcType="INTEGER"/>   <result property="cname" column="CNAME" jdbcType="VARCHAR"/></resultMap><resultMap id="ministerResultMap" type="MinisterBean">    <result property="mid" column="MID" jdbcType="INTEGER"/>    <result property="mname" column="MNAME" jdbcType="VARCHAR"/>    <association property="country" javaType="CountryBean"        select="getCountryByCId" column="CID" /></resultMap><select id="getMinisterByMId" resultMap="ministerResultMap">    select mid,mname,cid from minister where mid=#{mid}</select><select id="getCountryByCId" resultMap="countryResultMap">    select cid,cname from country where cid=#{cid}</select>

3)测试

    @Test    public void test5(){//        MinisterBean ministerBean = session.selectOne("cm.getMinisterInfo",1);        MinisterBean ministerBean = session.selectOne("cm.getMinisterByMId",1);        System.out.println(ministerBean);    }