Mybatis多表查询拓展

最新推荐文章于 2023-08-22 17:17:50 发布

Spirit_NKlaus

最新推荐文章于 2023-08-22 17:17:50 发布

阅读量272

点赞数

分类专栏： Mybatis

本文链接：https://blog.csdn.net/LittleMangoYX/article/details/95941008

版权

Mybatis 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

关于mybatis的多表查询，有时候关联其它表只需要取其中部分字段，其实可以直接在xml里面写SQL，在实体中声明对应字段去实现

首先是RepairPartsChangeExt 实体类

public class RepairPartsChangeExt extends RepairPartsChange {

    //来自其它表一些字段
    private String partsApplyCode;

    private String contractCode;

    private String jobCode;

    private String repairCode;

    private String repairPerson;

    //自行生成get，set方法

来看下RepairPartsChangeExtMapper.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.extmapper.rep.RepairPartsChangeExtMapper">
  <resultMap id="headerExtMap" type="com.yun.extmapper.rep.extentity.RepairPartsChangeExt">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="repair_code_id" jdbcType="VARCHAR" property="repairCodeId" />
    <result column="apply_date" jdbcType="DATE" property="applyDate" />
    <result column="parts_desc" jdbcType="VARCHAR" property="partsDesc" />
    <result column="picture_url" jdbcType="VARCHAR" property="pictureUrl" />
    <result column="need_quantity" jdbcType="DECIMAL" property="needQuantity" />
    <result column="skill_affirm" jdbcType="VARCHAR" property="skillAffirm" />
    <result column="parts_apply_status" jdbcType="VARCHAR" property="partsApplyStatus" />
    <result column="materiel_code" jdbcType="VARCHAR" property="materielCode" />
    <result column="to_materiel_code" jdbcType="VARCHAR" property="toMaterielCode" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_date" jdbcType="TIMESTAMP" property="updateDate" />
    <result column="delete_flag" jdbcType="CHAR" property="deleteFlag" />
    <result column="enabled" jdbcType="CHAR" property="enabled" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="org_id" jdbcType="VARCHAR" property="orgId" />
    <result column="attribute1" jdbcType="VARCHAR" property="attribute1" />
    <result column="attribute2" jdbcType="VARCHAR" property="attribute2" />
    <result column="attribute3" jdbcType="VARCHAR" property="attribute3" />
    <result column="attribute4" jdbcType="VARCHAR" property="attribute4" />
    <result column="attribute5" jdbcType="VARCHAR" property="attribute5" />
    <!-- 实体中对应其它表的字段-->
    <result column="parts_apply_code" jdbcType="VARCHAR" property="partsApplyCode" />
    <result column="contract_code" jdbcType="VARCHAR" property="contractCode" />
    <result column="job_code" jdbcType="VARCHAR" property="jobCode" />
    <result column="repair_code" jdbcType="VARCHAR" property="repairCode" />
    <result column="repair_person" jdbcType="VARCHAR" property="repairPerson" />
  </resultMap>

  <select id="findRepairPartsChangeList"
          parameterType="com.yun.mapper.rep.entity.RepairPartsChange"
          resultMap="headerExtMap">
    select t1.*, t2.parts_apply_code, t2.contract_code, t2.job_code, t2.repair_code, t2.repair_person
    from repair_parts_change t1,repair_item t2
    where t1.repair_code_id = t2.id
      <if test="partsApplyCode !=null">
        and t2.parts_apply_code =#{partsApplyCode,jdbcType=VARCHAR}
      </if>
      <if test="contractCode !=null">
        and t2.contract_code like concat(concat('%',#{contractCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="jobCode !=null">
        and t2.job_code like concat(concat('%',#{jobCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="repairCode !=null">
        and t2.repair_code like concat(concat('%',#{repairCode,jdbcType=VARCHAR}),'%')
      </if>
      <if test="repairPerson !=null">
        and t2.repair_person like concat(concat('%',#{repairPerson,jdbcType=VARCHAR}),'%')
      </if>
      <if test="skillAffirm !=null">
        and t1.skill_affirm like concat(concat('%',#{skillAffirm,jdbcType=VARCHAR}),'%')
      </if>
      <if test="partsApplyStatus !=null">
        and t1.parts_apply_status = #{partsApplyStatus,jdbcType=VARCHAR}
      </if>
    ORDER BY t1.create_date desc
  </select>

</mapper>

最后在RepairPartsChangeExtMapper完成对xml中SQL的接口声明

@Repository
public interface RepairPartsChangeExtMapper {
    //对应xml中查询sql的id
    List<RepairPartsChangeExt> findRepairPartsChangeList(RepairPartsChange repairPartsChange);

}