SpringBoot集成Mybatis实现多表查询(XML版)

1,EnginCheckOrder(头表实体)

public class EnginCheckOrder implements Serializable {
    private String id;

    private String checkOrderCode;

    private String contractCode;

    private String jobCode;

    private String checkType;

    private String checkWay;

    private String checkResult;

    private Date planDate;

    private Date actualDate;

    private String descri;

    private String createBy;

    private Date createDate;

    private String updateBy;

    private Date updateDate;

    private String deleteFlag;
    
    //自行生成get和set方法

}

2,EnginCheckOrderItem(子表实体)

public class EnginCheckOrderItem implements Serializable {
    private String id;

    private String checkOrderId;

    private Integer seqNo;

    private String checkItemCode;

    private String checkItemName;

    private String checkRequire;

    private String checkUom;

    private String specialItem;

    private String checkResult;

    private String remark;

    private String createBy;

    private Date createDate;

    private String updateBy;

    private Date updateDate;

    private String deleteFlag;
    
    //自行生成get和set方法
    
}

3,EnginCheckOrderExt(拓展头子表实体)

public class EnginCheckOrderExt extends EnginCheckOrder {

    private List<EnginCheckOrderItem> itemList;

    //自行生成get和set方法
    
}

4,EnginCheckOrderExtMapper(xml对应的接口文件)

@Repository
public interface EnginCheckOrderExtMapper {
    List<EnginCheckOrderExt> findCheckOrderList(EnginCheckOrder enginCheckOrder);
}

5,EnginCheckOrderExtMapper.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.yun.demo.extmapper.EnginCheckOrderExtMapper">
<!--头表的映射 -->
  <resultMap id="headerExtMap" type="com.yun.demo.extmapper.extentity.EnginCheckOrderExt">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="check_order_code" jdbcType="VARCHAR" property="checkOrderCode" />
    <result column="contract_code" jdbcType="VARCHAR" property="contractCode" />
    <result column="job_code" jdbcType="VARCHAR" property="jobCode" />
    <result column="check_type" jdbcType="VARCHAR" property="checkType" />
    <result column="check_way" jdbcType="VARCHAR" property="checkWay" />
    <result column="check_result" jdbcType="VARCHAR" property="checkResult" />
    <result column="plan_date" jdbcType="DATE" property="planDate" />
    <result column="actual_date" jdbcType="DATE" property="actualDate" />
    <result column="descri" jdbcType="VARCHAR" property="descri" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_date" jdbcType="DATE" property="createDate" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_date" jdbcType="DATE" property="updateDate" />
    <result column="delete_flag" jdbcType="CHAR" property="deleteFlag" />
    <!--子表的集合 -->
    <collection property="itemList" column="id" select="selectItemByOrderId" />
  </resultMap>

<!--子表的映射 -->
  <resultMap id="itemMap" type="com.yun.demo.mapper.entity.EnginCheckOrderItem">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="check_order_id" jdbcType="VARCHAR" property="checkOrderId" />
    <result column="seq_no" jdbcType="INTEGER" property="seqNo" />
    <result column="check_item_code" jdbcType="VARCHAR" property="checkItemCode" />
    <result column="check_item_name" jdbcType="VARCHAR" property="checkItemName" />
    <result column="check_require" jdbcType="VARCHAR" property="checkRequire" />
    <result column="check_uom" jdbcType="VARCHAR" property="checkUom" />
    <result column="special_item" jdbcType="VARCHAR" property="specialItem" />
    <result column="check_result" jdbcType="VARCHAR" property="checkResult" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_date" jdbcType="DATE" property="createDate" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_date" jdbcType="DATE" property="updateDate" />
    <result column="delete_flag" jdbcType="CHAR" property="deleteFlag" />
  </resultMap>

  <select id="findCheckOrderList"
          parameterType="com.yun.demo.mapper.entity.EnginCheckOrder"
          resultMap="headerExtMap">
    select * from check_order t1
    <where>
      <if test="checkOrderCode !=null">
        and t1.check_order_code like concat(concat('%',#{checkOrderCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="contractCode !=null">
        and t1.contract_code like concat(concat('%',#{contractCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="jobCode !=null">
        and t1.job_code like concat(concat('%',#{jobCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="checkType !=null">
        and t1.check_type like concat(concat('%',#{checkType,jdbcType=VARCHAR}),'%')
      </if>
      <if test="checkWay !=null">
        and t1.check_way like concat(concat('%',#{checkWay,jdbcType=VARCHAR}),'%')
      </if>
      <if test="planDate !=null">
        and t1.plan_date =#{planDate,jdbcType=DATE}
      </if>
      <if test="actualDate !=null">
        and t1.actual_date =#{actualDate,jdbcType=DATE}
      </if>
    </where>
    ORDER BY t1.plan_date desc
  </select>

  <select id="selectItemByOrderId" parameterType="string" resultMap="itemMap">
    select * from check_order_item where check_order_id=#{id} order by seq_no
</select>

</mapper>