ResultType和ResultMap（day37）

学习目标

1. 重点掌握ResultMap的作用

2. 重点掌握ResultMap的使用方式

3. 重点掌握ResultMap的级联属性

2 resultType和resultMap

2.1 resultType--自动映射

情况一 : 如果查询结果是一行数据: 例如根据id查询某个职位信息

使用POJO封装一行数据

• 接口中的方法的返回值就是用于封装结果集的POJO类型

public Jobs getJobsById(String jobId);

• select标签中的resultType属性的值是封装结果集的POJO的全限定名

<select id="getJobsById" resultType="cn.tedu.pojo.Jobs">
    SELECT job_id, job_title, min_salary, max_salary FROM jobs WHERE job_id = #{jobId}
</select>

情况二 : 如果查询结果为多行数据: 例如查询所有职位信息 使用List<POJO>封装多行数据

• 接口中的方法的返回值是List<POJO>类型

public List<Jobs> getJobsAll();

• select标签的resultType属性的值是POJO的全限定名

<select id="getJobsAll" resultType="cn.tedu.pojo.Jobs">
    SELECT job_id, job_title, min_salary, max_salary FROM jobs
</select>

2.2 resultMap--手动映射

• 使用mybatis，有两个属性标签<resultType>、<resultMap>可以提供结果映射

• resultMap标签: 是用于定义javaBean和数据库表的映射规则,建议只要定义resultMap,就将整表的全部列的映射全部写上

  • id属性: 给resultMap的映射关系自定义一个名称，是唯一值
  • type属性: 用于指定需要自定义规则的Java类型,其中如果设置了包扫描,就可以直接写类名即可

• id标签: 指定主键列的封装规则,也可以使用result标签定义,但是对主键的特殊照顾,底层会有优化

  • column属性: 映射主键列
  • property属性: 映射主键属性

• result标签: 指定普通列的封装规则

  • column属性: 映射非主键列
  • property属性: 映射非主键属性

3 入门案例

3.1 前期准备

①在JSDSecondStage项目下,创建ResultMapDemo,将版本设置为2.5.4

②在pom.xml中添加相关的依赖

<!--mysql数据库驱动依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--引入相关mybatis依赖-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>

③在模块下的src/main/java/cn/tedu包中,将pojo包导入进去,该包下包含两个类

• Locations hr库中locations表的javaBean类
• Countries hr库中countries表的javaBean类

④在模块下的src/main/java/cn/tedu包中,将mapper包导入进去,该包下包含两个接口

• LocationsMapper 用于定义映射locations表的接口
• CountriesMapper 用于定义映射countries表的接口

⑤在模块下的src/main/resources目录中,将mapper文件夹导入进去,该包下包含两个SQL文件

• LocationsMapper.xml 用于写操作locations表的SQL语句
• CountriesMapper.xml 用于写操作countries表的SQL语句

⑥配置文件application.yml内容

#数据库链接
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/hr?serverTimezone=Asia/Shanghai&characterEncoding=utf8
    username: root
    password: root
#MyBatis开启驼峰映射,并且扫描xml文件
mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:/mapper/*.xml

#开启日志设置
logging:
  level:
    cn:
      tedu: debug

3.2 查询locations表中的记录

①**LocationsMapper**

package cn.tedu.mapper;

import cn.tedu.pojo.Locations;
import org.apache.ibatis.annotations.Mapper;

//指定这是一个操作数据库的mapper
@Mapper
public interface LocationsMapper {
    public Locations getLocationById(Integer locationId);
}

②**LocationsMapper.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="cn.tedu.mapper.LocationsMapper">
    <select id="getLocationById" resultType="cn.tedu.pojo.Locations">
        SELECT * 
        FROM locations 
        WHERE location_id = #{locationId}
    </select>
</mapper>

③**TestResultMap**

package cn.tedu;

import cn.tedu.mapper.LocationsMapper;
import cn.tedu.pojo.Locations;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class TestResultMap {
    @Autowired
    private LocationsMapper locationsMapper;
    
    @Test
    public void testGetLocationById() {
        Locations location = locationsMapper.getLocationById(1000);
        System.out.println(location);
    }
}

3.3 使用resultMap

• resultType配置之后,开启自动映射,我们需要在application.yml中开启驼峰映射,实现自动匹配

mybatis:
  configuration:
    map-underscore-to-camel-case: true

• 而resultMap可以手动的将属性和表字段匹配

①**LocationsMapper.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="cn.tedu.mapper.LocationsMapper">
    <select id="getLocationById" resultType="cn.tedu.pojo.Locations">
        SELECT * 
        FROM locations 
        WHERE location_id = #{locationId}
    </select>
    <!--resultMap自定义javaBean映射规则
        type: 自定义规则的Java类型
        id:唯一id,方便引用
        建议只要定义resultMap,就将整表的全部列的映射全部写上
    -->
    <resultMap id="location" type="cn.tedu.pojo.Locations">
        <!--指定主键列的封装规则
            id定义主键,底层会有优化
            column:指定数据库表的哪一列
            property:指定javaBean的哪一属性
        -->
        <id column="location_id" property="locationId"/>
        <!--指定普通列的封装规则-->
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <result column="country_id" property="countryId"/>
    </resultMap>
</mapper>

②**application.yml**

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/hr?serverTimezone=Asia/Shanghai&characterEncoding=utf8
    username: root
    password: root
mybatis:
#  configuration:
#    map-underscore-to-camel-case: true
  mapper-locations: classpath:/mapper/*.xml
  
logging:
  level:
    cn:
      tedu: debug

3.4 优化

3.4.1 开启包扫描

• resultType在定义时,每次总要写全对应的javaBean的全路径,很麻烦,所以可以在配置文件中添加如下的配置,开启包扫描

mybatis:
  #指定entity扫描包类让mybatis自定扫描到自定义的包路径,这样在mapper.xml中就直接写类名即可
  type-aliases-package: cn.tedu.pojo

• 那么MyBatis会自动扫描该包下的javaBean,这样我们就直接写类名即可

<select id="getLocationById" resultType="Locations">
    SELECT * FROM locations WHERE location_id = #{locationId}
</select>

3.4.2 resultMap开启自动映射

• resultMap是可以自动映射和手动映射兼容的
• 在resultMap标签中使用autoMapping属性,如果为true就表示开启自动映射
• 但是要是开启自动映射,就需要添加驼峰规则配置

①**LocationsMapper.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="cn.tedu.mapper.LocationsMapper">
    <select id="getLocationById" resultMap="location">
        SELECT * FROM locations WHERE location_id = #{locationId}
    </select>
    <!--resultMap自定义javaBean映射规则
        type: 自定义规则的Java类型
        id:唯一id,方便引用
        建议只要定义resultMap,就将整表的全部列的映射全部写上
    -->
    <resultMap id="location" type="Locations" autoMapping="true">

    </resultMap>
</mapper>

②**application.yml**

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/hr?serverTimezone=Asia/Shanghai&characterEncoding=utf8
    username: root
    password: root
mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: cn.tedu.pojo
logging:
  level:
    cn:
      tedu: debug

4 resultMap的级联用法

4.1 国家表和地点表的关系

• countries表就是国家表,用于收集国家和区域的信息
• locations表就是地点表,用于收集地点相关信息
• 这两张表具有以下关系:
  • 在国家表的角度: 一个国家对应多个地点,也就是一对多的关系
  • 在地点表的角度: 一个地点对应一个国家,也就是一对一的关系

4.1 一对一查询

• 查询locations表记录的同时,将对应的countries表中的内容查询出来,SQL如下:

SELECT l.location_id,
       l.street_address,
       l.postal_code,
       l.city,
       l.state_province,
       l.country_id lcid,
       c.country_id cid,
       c.country_name,
       c.region_id
FROM locations l,
     countries c
WHERE l.country_id = c.country_id
  AND l.location_id = 2000

• 在这条SQL中,使用了多表关联查询,并且表字段也起了别名

①Locations

package cn.tedu.pojo;

public class Locations {
    private Integer locationId;
    private String streetAddress;
    private String postalCode;
    private String city;
    private String stateProvince;
    private String countryId;

    @Override
    public String toString() {
        return "Locations{" +
                "locationId=" + locationId +
                ", streetAddress='" + streetAddress + '\'' +
                ", postalCode='" + postalCode + '\'' +
                ", city='" + city + '\'' +
                ", stateProvince='" + stateProvince + '\'' +
                ", countryId='" + countryId + '\'' +
                ", countries=" + countries +
                '}';
    }

    public Countries getCountries() {
        return countries;
    }

    public void setCountries(Countries countries) {
        this.countries = countries;
    }

    private Countries countries;

    public Integer getLocationId() {
        return locationId;
    }

    public void setLocationId(Integer locationId) {
        this.locationId = locationId;
    }

    public String getStreetAddress() {
        return streetAddress;
    }

    public void setStreetAddress(String streetAddress) {
        this.streetAddress = streetAddress;
    }

    public String getPostalCode() {
        return postalCode;
    }

    public void setPostalCode(String postalCode) {
        this.postalCode = postalCode;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getStateProvince() {
        return stateProvince;
    }

    public void setStateProvince(String stateProvince) {
        this.stateProvince = stateProvince;
    }

    public String getCountryId() {
        return countryId;
    }

    public void setCountryId(String countryId) {
        this.countryId = countryId;
    }

}

②**LocationsMapper接口**

package cn.tedu.mapper;

import cn.tedu.pojo.Locations;
import org.apache.ibatis.annotations.Mapper;

//指定这是一个操作数据库的mapper
@Mapper
public interface LocationsMapper {
    public Locations getLocationById(Integer locationId);
}

③**LocationsMapper.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="cn.tedu.mapper.LocationsMapper">
    <select id="getLocationById" resultMap="location">
        SELECT * FROM locations WHERE location_id = #{locationId}
    </select>
    <select id="getLocationCountryById" resultMap="location2">
        SELECT l.location_id,
               l.street_address,
               l.postal_code,
               l.city,
               l.state_province,
               l.country_id lcid,
               c.country_id cid,
               c.country_name,
               c.region_id
        FROM locations l,
             countries c
        WHERE l.country_id = c.country_id
          AND l.location_id = #{locationId}
    </select>
    <!--resultMap自定义javaBean映射规则
        type: 自定义规则的Java类型
        id:唯一id,方便引用
        建议只要定义resultMap,就将整表的全部列的映射全部写上
    -->
    <resultMap id="location" type="Locations" autoMapping="true">
        <!--指定主键列的封装规则
            id定义主键,底层会有优化
            column:指定数据库表的哪一列
            property:指定javaBean的哪一属性
        -->
        <id column="location_id" property="locationId"/>
        <!--指定普通列的封装规则-->
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <result column="country_id" property="countryId"/>
    </resultMap>
    <!--
    联合查询: 使用级联属性封装结果集
    -->
    <resultMap id="location2" type="Locations">
        <id column="location_id" property="locationId"/>
        <!--指定普通列的封装规则-->
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <!--此处对应的是locations表中的country_id,但是由于SQL中使用了别名,所以使用别名作为column的值-->
        <result column="lcid" property="countryId"/>
        <!--此处对应的是countries表中的country_id,但是由于SQL中使用了别名,所以使用别名作为column的值,封装到countries属性中的countryId中-->
        <result column="cid" property="countries.countryId"/>
        <result column="country_name" property="countries.countryName"/>
        <result column="region_id" property="countries.regionId"/>
    </resultMap>
</mapper>

④**TestResultMap**

@SpringBootTest
public class TestResultMap {
    @Autowired
    private LocationsMapper locationsMapper;
    @Autowired
    private CountriesMapper countriesMapper;

    @Test
    public void testGetLocationById() {
        Locations location = locationsMapper.getLocationById(1000);
        System.out.println(location);
    }

    @Test
    public void testGetLocationCountryById() {
        Locations location = locationsMapper.getLocationCountryById(2000);
        System.out.println(location);
        System.out.println(location.getCountries());
    }
}

4.2 association定义一对一关系映射

• 上述的案例中,如果像类似于<result column="cid" property="countries.countryId"/>,这样的字段比较多的情况,每次关联属性都要写countries的级联属性的方式封装结果集,会比较繁琐,重复字段较多,其次,看不出关联关系,所以可以使用association标签进行封装

• association标签: 可以指定联合的javaBean对象

  • property属性: 指定哪个属性是联合的对象
  • javaType属性: 指定这个属性对象的类型[不能省略]

①**LocationsMapper.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="cn.tedu.mapper.LocationsMapper">
    <select id="getLocationById" resultMap="location">
        SELECT * FROM locations WHERE location_id = #{locationId}
    </select>
    <select id="getLocationCountryById" resultMap="location3">
        SELECT l.location_id,
               l.street_address,
               l.postal_code,
               l.city,
               l.state_province,
               l.country_id lcid,
               c.country_id cid,
               c.country_name,
               c.region_id
        FROM locations l,
             countries c
        WHERE l.country_id = c.country_id
          AND l.location_id = #{locationId}
    </select>
    <!--resultMap自定义javaBean映射规则
        type: 自定义规则的Java类型
        id:唯一id,方便引用
        建议只要定义resultMap,就将整表的全部列的映射全部写上
    -->
    <resultMap id="location" type="Locations" autoMapping="true">
        <!--指定主键列的封装规则
            id定义主键,底层会有优化
            column:指定数据库表的哪一列
            property:指定javaBean的哪一属性
        -->
        <id column="location_id" property="locationId"/>
        <!--指定普通列的封装规则-->
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <result column="country_id" property="countryId"/>
    </resultMap>
    <!--
    联合查询: 使用级联属性封装结果集
    -->
    <resultMap id="location2" type="Locations" autoMapping="true">
        <id column="location_id" property="locationId"/>
        <!--指定普通列的封装规则-->
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <!--此处对应的是locations表中的country_id,但是由于SQL中使用了别名,所以使用别名作为column的值-->
        <result column="lcid" property="countryId"/>
        <!--此处对应的是countries表中的country_id,但是由于SQL中使用了别名,所以使用别名作为column的值,封装到countries属性中的countryId中-->
        <result column="cid" property="countries.countryId"/>
        <result column="country_name" property="countries.countryName"/>
        <result column="region_id" property="countries.regionId"/>
    </resultMap>
    <!--association属性定义单个对象封装规则-->
    <resultMap id="location3" type="Locations">
        <id column="location_id" property="locationId"/>
        <result column="street_address" property="streetAddress"/>
        <result column="postal_code" property="postalCode"/>
        <result column="city" property="city"/>
        <result column="state_province" property="stateProvince"/>
        <result column="lcid" property="countryId"/>
        <!--association可以指定联合的javaBean对象
        property="countries" 指定哪个属性是联合的对象
        javaType="Countries" 指定这个属性对象的类型[不能省略]-->
        <association property="countries" javaType="Countries" autoMapping="true">
            <id column="cid" property="countryId"/>
            <result column="country_name" property="countryName"/>
            <result column="region_id" property="regionId"/>
        </association>
    </resultMap>
</mapper>

4.3 collection定义一对多关系映射

• 查询指定的国家中包含的地点信息,SQL如下:

SELECT c.country_id,
       c.country_name,
       l.location_id,
       l.street_address,
       l.city
FROM countries c LEFT JOIN locations l
                   ON c.country_id = l.country_id
WHERE c.country_id = 'US';

• collection标签: 可以指定联合的集合类型的属性
  • property属性: 指定哪个属性是联合的对象
  • ofType属性: 指定集合的元素类型[不能省略]

①**Countries**

package cn.tedu.pojo;

import java.util.List;

public class Countries {
    private String countryId;
    private String countryName;
    private Double regionId;
    private List<Locations> locations;

    public List<Locations> getLocations() {
        return locations;
    }

    public void setLocations(List<Locations> locations) {
        this.locations = locations;
    }

    public String getCountryId() {
        return countryId;
    }

    public void setCountryId(String countryId) {
        this.countryId = countryId;
    }

    public String getCountryName() {
        return countryName;
    }

    public void setCountryName(String countryName) {
        this.countryName = countryName;
    }

    public Double getRegionId() {
        return regionId;
    }

    public void setRegionId(Double regionId) {
        this.regionId = regionId;
    }

    @Override
    public String toString() {
        return "Countries{" +
                "countryId='" + countryId + '\'' +
                ", countryName='" + countryName + '\'' +
                ", regionId=" + regionId +
                ", locations=" + locations +
                '}';
    }
}

②**CountriesMapper接口**

package cn.tedu.mapper;

import cn.tedu.pojo.Countries;
import org.apache.ibatis.annotations.Mapper;

//指定这是一个操作数据库的mapper
@Mapper
public interface CountriesMapper {
    public Countries getCountryByIdPlus(String countryId);
}

③**CountriesMapper.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="cn.tedu.mapper.CountriesMapper">
    <select id="getCountryByIdPlus" resultMap="country">
        SELECT c.country_id,
               c.country_name,
               l.location_id,
               l.street_address,
               l.city
        FROM countries c
                 LEFT JOIN locations l
                           ON c.country_id = l.country_id
        WHERE c.country_id = #{countryId}
    </select>
    <resultMap id="country" type="Countries">
        <id column="country_id" property="countryId"/>
        <result column="country_name" property="countryName"/>
        <!--定义关联集合类型的属性的封装规则
            ofType 指定集合的元素类型
        -->
        <collection property="locations" ofType="Locations">
            <!--定义集合中的元素的封装规则-->
            <id column="location_id" property="locationId"/>
            <result column="street_address" property="streetAddress"/>
            <result column="city" property="city"/>
        </collection>
    </resultMap>
</mapper>

④**TestResultMap**

@SpringBootTest
public class TestResultMap {
    @Autowired
    private LocationsMapper locationsMapper;
    @Autowired
    private CountriesMapper countriesMapper;

    @Test
    public void testGetLocationById() {
        Locations location = locationsMapper.getLocationById(1000);
        System.out.println(location);
    }

    @Test
    public void testGetLocationCountryById() {
        Locations location = locationsMapper.getLocationCountryById(2000);
        System.out.println(location);
        System.out.println(location.getCountries());
    }

    @Test
    public void testGetCountryByIdPlus() {
        Countries country = countriesMapper.getCountryByIdPlus("US");
        System.out.println(country);
        List<Locations> locations = country.getLocations();
        for (Locations location : locations) {
            System.out.println(location);
        }
    }
}

上一篇文章：MyBatis的占位符（day36）-CSDN博客https://blog.csdn.net/Z0412_J0103/article/details/142969916下一篇文章：