mybatis逆向工程实现条件联表查询

情景

mybatis逆向工程下，利用PutawayExample实现对putaways表的条件查询，但要用的字段在goods表里putaways只有外键gid，例如根据商品类型进行筛选，并不可以直接实现条件查询而需要联表查询。

putaways表

goods表

解决方案

• Putaway实体内增加字段

// 商品详情
private Good good;

额外添加属性来保存联表查到的goods表信息

• PutawayExample实体内增添条件函数

    // 自定义
    public Criteria andTypeEqualTo(String value) {
        addCriterion("goods.type =", value, "good.type");
        return (Criteria) this;
    }
    public Criteria andNameLike(String value) {
        addCriterion("goods.name like", value, "good.name");
        return (Criteria) this;
    }
    // 自定义

根据需求来添加对应条件

例如我这里是需要实现根据商品类型筛选和对商品模糊查询后筛选，故增加了这两个字段

为什么要添加？因为type和name都是Good内的属性，PutawayExample并不会拥有

• PutawayMapper内添加自定义接口函数

    // 联合表条件分页查询
    List<Putaway> selectPutawayGoodByExample(PutawayExample putawayExample);

• PutawayMapper.xml内添加相关实现

• 添加自定义ResultMap，用于将查到的字段映射到Putaway实体

<!--    自定义字段到实体属性的映射resultMap-->
<resultMap id="PutawayResultMap" type="indi.Cverse.BuyByCar.pojo.Putaway">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sun Feb 26 19:34:40 CST 2023.
    -->
    <id column="puid" jdbcType="INTEGER" property="puid" />
    <result column="sid" jdbcType="VARCHAR" property="sid" />
    <result column="gid" jdbcType="INTEGER" property="gid" />
    <result column="time" jdbcType="TIMESTAMP" property="time" />
    <result column="quantity" jdbcType="INTEGER" property="quantity" />
    <result column="price" jdbcType="REAL" property="price" />

    <result column="name" jdbcType="VARCHAR" property="good.name" />
    <result column="type" jdbcType="VARCHAR" property="good.type" />
    <result column="description" jdbcType="VARCHAR" property="good.description" />
    <result column="labels" jdbcType="VARCHAR" property="good.labels" />
</resultMap>
<!--    自定义字段到实体属性的映射resultMap-->

• 添加自定义sql

<!--    自定义联表查询-->
<select id="selectPutawayGoodByExample" parameterType="indi.Cverse.BuyByCar.pojo.PutawayExample" resultMap="PutawayResultMap">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sun Feb 26 19:34:40 CST 2023.
    -->
    select
    <if test="distinct">
        distinct
    </if>
    *
    from putaways left join goods on putaways.gid = goods.gid
    <if test="_parameter != null">
        <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
        order by ${orderByClause}
    </if>
</select>
<!--    自定义联表查询-->

成果展示

成功实现联表条件查询