MyBatis(2) 多表映射
2023-10-30 15:00:46 # Backend # MyBatis

1. 对一映射

关键词: association标签/javaType属性/property属性

设计实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
@Data
public class Customer {
    private Integer customerId;
    private String customerName;
    private List<Order> orderList;// 对多关系
}  

@Data
public class Order {
    private Integer orderId;
    private String orderName;
    private Customer customer;// 对一关系
}

1.1 通过association标签实现

OrderMapper接口

1
2
3
public interface OrderMapper {
	Order selectOrderWithCustomer(Integer orderId);
}

OrderMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<resultMap id="selectOrderWithCustomerResultMap" type="order">
    <id column="order_id" property="orderId"/>
    <result column="order_name" property="orderName"/>

    <!-- 使用association标签配置“对一”关联关系 -->
    <!-- property属性：在Order类中对一的一端进行引用时使用的属性名 -->
    <!-- javaType属性：一的一端类的全类名 -->
    <association property="customer" javaType="customer">
        <!-- 配置Customer类的属性和字段名之间的对应关系 -->
        <id column="customer_id" property="customerId"/>
        <result column="customer_name" property="customerName"/>
    </association>
</resultMap>

<!-- Order selectOrderWithCustomerById(Integer orderId);-->
<select id="selectOrderWithCustomerById" resultMap="selectOrderWithCustomerResultMap">
    select order_id, order_name, c.customer_id, customer_name
    from t_order o
    left join ssmdb.t_customer c
        on o.customer_id = c.customer_id
    where o.order_id=#{orderId}
</select>

经测试，将查询语句中的字段添加上表名不影响结果，例如将 order_id 改为 o.order_id

配置文件中的 <setting name="mapUnderscoreToCamelCase" value="true"/> 也不影响结果

测试结果

image-20231025202642558

1.2 通过级联属性赋值实现

1
2
3
4
5
6
7
<resultMap id="selectOrderWithCustomerResultMap" type="order">
    <id column="order_id" property="orderId"/>
    <result column="order_name" property="orderName"/>
    <!-- 也可以通过级联属性赋值-->
    <result column="customer_id" property="customer.customerId"/>
    <result column="customer_name" property="customer.customerName"/>
</resultMap>

1.3 通过分步查询实现

OrderMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<resultMap id="selectOrderWithCustomerByStepResultMap" type="order">
    <id column="order_id" property="orderId"/>
    <result column="order_name" property="orderName"/>
    <!--
        select:设置分步查询的sql的唯一标识（namespace.SQLId或mapper接口的全类名.方法名）
        column:设置分步查询的条件,字段名
        fetchType:当开启了全局的延迟加载之后，可通过此属性手动控制延迟加载的效果
        fetchType="lazy|eager": lazy表示延迟加载，eager表示立即加载
    -->
    <association property="customer"
                 select="top.whalefall.mapper.CustomerMapper.selectOrderWithCustomerStepTwo" 	# 步骤二写在了另一个mapper文件中
                 column="customer_id"
                 fetchType="eager"/>
</resultMap>

<!-- Order selectOrderWithCustomerStepOne(Integer orderId);-->
<select id="selectOrderWithCustomerStepOne" resultMap="selectOrderWithCustomerByStepResultMap">
    select order_id, order_name, customer_id
    from t_order
    where order_id = #{orderId}
</select>

CustomerMapper.xml

1
2
3
4
5
6
<!-- Customer selectOrderWithCustomerStepTwo(Integer customerId);-->
<select id="selectOrderWithCustomerStepTwo" resultType="customer">
    select customer_id, customer_name
    from t_customer
    where customer_id = #{customerId}
</select>

测试类

1
2
3
4
5
6
7
@Test
public void testRelationshipToOne() {
    OrderMapper orderMapper = session.getMapper(OrderMapper.class);
    // 查询Order对象，检查是否同时查询了关联的Customer对象
    Order order = orderMapper.selectOrderWithCustomerStepOne(2);
    log.info("order = " + order);
}

运行结果

image-20231027013257480

2. 对多映射

关键词: collection标签/ofType属性/property属性

2.1 通过collection标签实现

CustomerMapper接口

1
Customer selectCustomerWithOrderListById(Integer customerId);

CustomerMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<resultMap id="selectCustomerWithOrderListResultMap" type="customer">
    <id column="customer_id" property="customerId"/>
    <result column="customer_name" property="customerName"/>
    <!-- collection标签：映射“对多”的关联关系 -->
    <!-- property属性：在Customer类中，关联“多”的一端的属性名 -->
    <!-- ofType属性：集合属性中元素的类型 -->
    <collection property="orderList" ofType="order">
        <id column="order_id" property="orderId"/>
        <result column="order_name" property="orderName"/>
    </collection>
</resultMap>

<!-- Customer selectCustomerWithOrderListById(Integer customerId);-->
<select id="selectCustomerWithOrderListById" resultMap="selectCustomerWithOrderListResultMap">
    select c.customer_id, c.customer_name, o.order_id, o.order_name
    from t_customer c
    left join ssmdb.t_order o
        on c.customer_id = o.customer_id
    where c.customer_id = #{customerId}
</select>

测试结果

image-20231025230346652

2.2 通过分步查询实现

CustomerMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<resultMap id="selectCustomerWithOrderListByStepResultMap" type="customer">
    <id column="customer_id" property="customerId"/>
    <result column="customer_name" property="customerName"/>
    <collection property="orderList"
                select="top.whalefall.mapper.OrderMapper.selectCustomerWithOrderListStepTwo"
                column="customer_id"
                fetchType="eager"/>
</resultMap>

<!-- Customer selectCustomerWithOrderListStepOne(Integer customerId);-->
<select id="selectCustomerWithOrderListStepOne" resultMap="selectCustomerWithOrderListByStepResultMap">
    select customer_id, customer_name
    from t_customer
    where customer_id = #{customerId}
</select>

OrderMapper.xml

1
2
3
4
5
6
<!-- List<Order> selectCustomerWithOrderListStepTwo(Integer customerId);-->
<select id="selectCustomerWithOrderListStepTwo" resultType="order">
    select order_id, order_name, customer_id
    from t_order
    where customer_id = #{customerId}
</select>

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
@Test
public void testRelationshipToMulti() {
    CustomerMapper customerMapper = session.getMapper(CustomerMapper.class);
    // 查询Customer对象同时将关联的Order集合查询出来
    Customer customer = customerMapper.selectCustomerWithOrderListStepOne(1);
    log.info("customer.getCustomerId() = " + customer.getCustomerId());
    log.info("customer.getCustomerName() = " + customer.getCustomerName());
    // 如果设置为懒加载, 且不执行下面代码的话只会查第一步
    List<Order> orderList = customer.getOrderList();
    for (Order order : orderList) {
        log.info("order = " + order);
    }
}

3. 多表映射优化

设置名 描述 有效值 默认值
autoMappingBehavior 指定 MyBatis 应如何自动映射列到字段或属性。NONE 表示关闭自动映射；PARTIAL 只会自动映射没有定义嵌套结果映射的字段。 FULL 会自动映射任何复杂的结果集（无论是否嵌套）。 NONE, PARTIAL, FULL PARTIAL

将 autoMappingBehavior 设置为full, 进行多表resultMap映射的时候，可以省略符合列和属性命名映射规则（列名=属性名，或者开启驼峰映射也可以自定映射）的result标签

1
2
<!--开启resultMap自动映射 -->
<setting name="autoMappingBehavior" value="FULL"/>

省略 result 标签

1
2
3
4
5
6
7
8
<resultMap id="selectCustomerWithOrderListResultMap" type="customer">
    <id column="customer_id" property="customerId"/>
	<!-- <result column="customer_name" property="customerName"/>-->
    <collection property="orderList" ofType="order">
        <id column="order_id" property="orderId"/>
		<!-- <result column="order_name" property="orderName"/>-->
    </collection>
</resultMap>

4. 延迟加载

分步查询的优点: 可以实现延迟加载，但是必须在核心配置文件中设置全局配置信息:

  • lazyLoadingEnabled: 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载
  • aggressiveLazyLoading: 当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载

此时就可以实现按需加载，获取的数据是什么，就只会执行相应的sal。此时可通过association和collection中的fetchType属性设置当前的分步查询是否使用延迟加载，fetchType=”lazy(延迟加载)eager(立即加载)’

1
<setting name="lazyLoadingEnabled" value="true"/>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<resultMap id="selectOrderWithCustomerByStepResultMap" type="order">
    <id column="order_id" property="orderId"/>
    <result column="order_name" property="orderName"/>
    <!--
        select:设置分步查询的sql的唯一标识（namespace.SQLId或mapper接口的全类名.方法名）
        column:设置分步查询的条件,字段名
        fetchType:当开启了全局的延迟加载之后，可通过此属性手动控制延迟加载的效果
        fetchType="lazy|eager":lazy表示延迟加载，eager表示立即加载
    -->
    <association property="customer"
                 select="top.whalefall.mapper.CustomerMapper.selectOrderWithCustomerStepTwo"
                 column="customer_id"
                 fetchType="lazy"/>
</resultMap>

测试类中只用到了 orderName, 不涉及 customer 信息, 因此只会执行第一步查询

1
2
3
4
5
6
7
@Test
public void testRelationshipToOne() {
    OrderMapper orderMapper = session.getMapper(OrderMapper.class);
    // 查询Order对象，检查是否同时查询了关联的Customer对象
    Order order = orderMapper.selectOrderWithCustomerStepOne(2);
    log.info("order = " + order.getOrderName());
}

image-20231027014331521

Prev
2023-10-30 15:00:46 # Backend # MyBatis
Next