MyBatis - 连接查询避免N+1问题

最新推荐文章于 2024-08-06 09:40:35 发布

Prometheus_xiao

最新推荐文章于 2024-08-06 09:40:35 发布

阅读量471

点赞数

本文链接：https://blog.csdn.net/Prometheus_xiao/article/details/105580687

版权

数据库 - MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1. 一对一关联的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="com.base.dao.TestMapper">
    <resultMap id="MaterialBaseResultMap" type="com.base.bean.MaterialBaseInfo">
        <id column="a_id" jdbcType="VARCHAR" property="id" />
        <result column="a_order_number" jdbcType="INTEGER" property="orderNumber" />
        <result column="a_category_ids" jdbcType="VARCHAR" property="categoryIds" />
        <result column="a_material_name" jdbcType="VARCHAR" property="materialName" />
        <result column="a_material_specifications" jdbcType="VARCHAR" property="materialSpecifications" />
        <result column="a_material_unit" jdbcType="VARCHAR" property="materialUnit" />
        <result column="a_material_best_before" jdbcType="INTEGER" property="materialBestBefore" />
        <result column="a_material_describe" jdbcType="VARCHAR" property="materialDescribe" />
        <result column="a_material_main_img" jdbcType="VARCHAR" property="materialMainImg" />
        <result column="a_material_imgs" jdbcType="VARCHAR" property="materialImgs" />
        <result column="a_create_user_id" jdbcType="INTEGER" property="createUserId" />
        <result column="a_create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="a_update_user_id" jdbcType="INTEGER" property="updateUserId" />
        <result column="a_update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="a_status" jdbcType="CHAR" property="status" />
    </resultMap>
    <resultMap id="InventoryDetailedResultMap" type="com.base.bean.InventoryDetailed">
        <id column="b_id" jdbcType="VARCHAR" property="id" />
        <result column="b_base_id" jdbcType="VARCHAR" property="baseId" />
        <result column="b_inventory_id" jdbcType="VARCHAR" property="inventoryId" />
        <result column="b_material_count" jdbcType="INTEGER" property="materialCount" />
        <result column="b_material_price" jdbcType="DECIMAL" property="materialPrice" />
        <result column="b_material_remarks" jdbcType="VARCHAR" property="materialRemarks" />
        <result column="b_create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="b_create_user_id" jdbcType="INTEGER" property="createUserId" />
        <result column="b_update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="b_update_user_id" jdbcType="INTEGER" property="updateUserId" />
        <result column="b_status" jdbcType="CHAR" property="status" />
        <association property="materialBaseInfo" javaType="com.base.bean.MaterialBaseInfo" resultMap="MaterialBaseResultMap"></association>
    </resultMap>

    <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="InventoryDetailedResultMap">
        select
        a.id as  `a_id`,
        a.order_number as  `a_order_number`,
        a.category_ids as  `a_category_ids`,
        a.material_name as  `a_material_name`,
        a.material_specifications as  `a_material_specifications`,
        a.material_unit as  `a_material_unit`,
        a.material_best_before as  `a_material_best_before`,
        a.material_describe as  `a_material_describe`,
        a.material_main_img as  `a_material_main_img`,
        a.material_imgs as  `a_material_imgs`,
        a.create_user_id as  `a_create_user_id`,
        a.create_time as  `a_create_time`,
        a.update_user_id as  `a_update_user_id`,
        a.update_time as  `a_update_time`,
        a.status as  `a_status`,
        b.id as `b_id`,
        b.base_id as `b_base_id`,
        b.inventory_id as `b_inventory_id`,
        b.material_count as `b_material_count`,
        b.material_price as `b_material_price`,
        b.material_remarks as `b_material_remarks`,
        b.create_time as `b_create_time`,
        b.create_user_id as `b_create_user_id`,
        b.update_time as `b_update_time`,
        b.update_user_id as `b_update_user_id`,
        b.status as `b_status`
        from inventory_detailed b left join material_base_info a on b.base_id = a.id
        where b.id = #{id}
    </select>
</mapper>