MyBatis关联查询,一对一关联查询

数据库E-R关系

实体类

public class City {
    Long id;
    String name;
    Long countryId;
    Date lastUpdate;
}
public class Country {
    Long id;
    String name;
    Date lastUpdate;
}
public class CityPlus {
    Long id;
    String name;
    Long countryId;
    Date lastUpdate;
    Country country;
}
public class CountryPlus {
    Long id;
    String name;
    Date lastUpdate;
    List<City> cityList;
}

一对一关联查询

  一对一关联查询可采用的方式有:

  1. 单步查询,通过级联属性赋值
    • result标签级联属性赋值
    • association标签级联属性赋值
  2. 分步查询

单步查询

  • 数据模型:一个实体Bean中包含另外一个实体Bean
  • SQL查询:关联SQL 查询语句,如inner join、left join、right join
  • 具体实现方式:
    • 为级联属性赋值
    • association标签

采用相同的select标签

    <select id="selectCityPlusById" resultMap="cityPlusResultMap">
        select city_id,city,city.country_id as country_id,city.last_update as last_update,
                country.country_id as country_country_id,country,country.last_update as country_last_update
        from city,country
        where city.country_id = country.country_id and  city_id=#{id}
    </select>

result标签级联属性赋值

        <id column="city_id" property="id"/>
        <result column="city" property="name"/>
        <result column="country_id" property="countryId"/>
        <result column="last_update" property="lastUpdate"/>
        <result column="country_country_id" property="country.id"/>
        <result column="country" property="country.name"/>
        <result column="country_last_update" property="country.lastUpdate"/>
    </resultMap>

 association标签级联属性赋值

  •  需要指定级联实体Bean在上级Bean中的属性名称,即association标签的property属性
  • 需要指定级联实体Bean的类型,即association标签的javaType属性
  • association标签的内部和resultMap标签内部具有相同的结构;
  • association标签也可以嵌套association标签;
    <resultMap id="cityPlusResultMap" type="canger.study.chapter04.bean.CityPlus">
        <id column="city_id" property="id"/>
        <result column="city" property="name"/>
        <result column="country_id" property="countryId"/>
        <result column="last_update" property="lastUpdate"/>
        <association property="country" javaType="canger.study.chapter04.bean.Country">
            <id column="country_country_id" property="id"/>
            <result column="country" property="name"/>
            <result column="country_last_update" property="lastUpdate"/>
        </association>
    </resultMap>

分步查询

  分步查询是指通过两次(或更多次)的查询,来为一个一对一关系的实体Bean赋值。

  • 数据模型:一个实体Bean中包含另外一个实体Bean
  • SQL查询:简单SQL语句,不存在关联查询
  • 只能通过association标签实现;

第一步查询

    <select id="selectCityPlusByIdUnderStep" resultMap="cityPlusResultMapStep">
        select city_id,city,country_id
        from city
        where city_id=#{id}
    </select>

 association标签:

  • 需要指定级联实体Bean在上级Bean中的属性名称,即association标签的property属性;
  • 需要指定下一步查询需要使用的select语句,即association标签的select属性,该属性值为Mapper接口查询方法的全限定名
  • 需要使用column属性,用于指定第二步查询的输入参数,第二步查询只有一个输入参数时,使用第一步查询结果的column名称即可;
    <resultMap id="cityPlusResultMapStep" type="canger.study.chapter04.bean.CityPlus">
        <id column="city_id" property="id"/>
        <result column="city" property="name"/>
        <result column="country_id" property="countryId"/>
        <result column="last_update" property="lastUpdate"/>
        <association property="country"
                     select="canger.study.chapter04.mapper.CountryMapper.selectCountryById"
                     column="country_id">
        </association>
    </resultMap>

第二步查询

    <select id="selectCountryById" resultMap="countryResultMap">
        select *
        from country
        where country_id=#{id}
    </select>

 

    <resultMap id="countryResultMap" type="canger.study.chapter04.bean.Country">
        <id column="country_id" property="id"/>
        <result column="country" property="name"/>
        <result column="last_update" property="lastUpdate"/>
    </resultMap>

分步查询中,第二步查询需要多个参数

当第二步查询需要多个参数时,column属性的设置方式为  {param1=column1,param2=column2},其中param1,param2...为第二步查询的mapper映射文件的SQL语句中所使用的引用参数名称,column1,column2...为第一步查询返回的列名称,示例如下:

第一步

 

    <select id="selectCityPlusByIdUnderStep" resultMap="cityPlusResultMapStep">
        select city_id,city,country_id, "Spain" as countryName
        from city
        where city_id=#{id}
    </select>

 

    <resultMap id="cityPlusResultMapStep" type="canger.study.chapter04.bean.CityPlus">
        <id column="city_id" property="id"/>
        <result column="city" property="name"/>
        <result column="country_id" property="countryId"/>
        <result column="last_update" property="lastUpdate"/>
        <association property="country"
                     select="canger.study.chapter04.mapper.CountryMapper.selectCountryByIdAndName"
                     column="{id=country_id,name=countryName}">
        </association>
    </resultMap>

第二步

    <select id="selectCountryByIdAndName" resultMap="countryResultMap">
        select *
        from country
        where country_id=#{id} and country=#{name}
    </select>

 

    <resultMap id="countryResultMap" type="canger.study.chapter04.bean.Country">
        <id column="country_id" property="id"/>
        <result column="country" property="name"/>
        <result column="last_update" property="lastUpdate"/>
    </resultMap>

 

posted @ 2018-11-19 21:48  canger  阅读(12507)  评论(1编辑  收藏  举报