使用Mybatis进行连表查询、left join

目前有个需求：
条件筛选出一个A表的结果list，然后需要增加一个字段，另一张B表的uuid，
他们之间的关联关系是 a.uuid=b.releaseUuid,
若不存在则返回null.

因此考虑用左连接,不存在字段也不影响总的A表条数

---

本来实体

---

dao:

/**
 * t_table dao
 * @author Mingchenchen
 *
 */
public interface ReleaseDao{
    /**
     * 获取条件筛选结果的总数
     * @param parameterMap
     * @return
     */
    public Integer getCountByCondition(Map<String, Object> parameterMap);

    /**
     * 列表 条件筛选
     * @param releaseEntity
     * @return
     */
    public List<ReleaseEntity> getReleaseListByCondition(Map<String, Object> parameterMap);
}

---

Entity:

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * 实体
 * @author Mingchenchen
 *
 */
@Table(name = "t_table")
public class ReleaseEntity {
    @Id
    private String uuid;

    @Column
    private String name;

    @Column(name = "app_name")
    private String appName;

    @Column(name = "app_uuid")
    private String appUuid;

    @Column(name="deploy_type")
    private String deployType;//

    @Column(name="department_code")
    private String departmentCode;//

    @Column(name="department_name")
    private String departmentName;//

    @Column(name = "status")
    private int status;//状态 0:已创建 1:已编排 2:已部署

    @Column(name = "flag")
    private int releaseType;//

    @Column(name="delete_flag")
    private int deleteFlag;//

    @Column(name = "create_time")
    private Date   createTime;

    @Column(name = "create_user")
    private String createUser;

    @Column(name = "update_time")
    private Date   updateTime;

    @Column(name = "update_user")
    private String updateUser;

    @Column(name = "remark")
    private String remark;

    /** 多余返还的字段 不要注解column 否则insert会报错 相当于让mybatis忽略此字段 */
    private String departmentCode;//所属业务线的简码
    private String departmentName;//所属业务线的名称
    private String deployType;//发布单的部署类型:QA TEST DEV PRODUCT
    private String namespace;

    /*
    * Getter and setter
    */
}

---

Mybatis xml sql:

<?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.xxx.dao.ReleaseDao">
    <!-- 结果集:1 -->
    <resultMap id="microServiceResultMap" type="MicroServiceEntity">  
        <id property="uuid" column="uuid" />  
        <result property="name" column="name" /> 
        <result property="version" column="version" />   
        <result property="ports" column="port_metadata" />  
    </resultMap>  
    <!-- 结果集:2 -->
    <resultMap id="releaseResultMap" type="ReleaseEntity">
      <id property="uuid" column="uuid" />
      <result property="name" column="name"/>
      <result property="appName" column="app_name"/>
      <result property="appUuid" column="app_uuid"/>
      <result property="deployType" column="deploy_type"/>
      <result property="departmentCode" column="department_code"/>
      <result property="departmentName" column="department_name"/>
      <result property="status" column="status"/>
      <result property="qps" column="qps"/>
      <result property="releaseType" column="dms_flag"/>
      <result property="deleteFlag" column="delete_flag"/>
      <result property="createTime" column="create_time"/>
      <result property="createUser" column="create_user"/>
      <result property="updateTime" column="update_time"/>
      <result property="updateUser" column="update_user"/>
      <result property="remark" column="remark"/>
      <result property="layoutTemplateUuid" column="layout_template_uuid"/>//第二张表的字段
    </resultMap>

    <!-- 列表总数 -->
    <select id="getCountByCondition" parameterType="java.util.Map" resultType="java.lang.Integer">  
        select count(*) 
        from dms_release 
        where 1=1 
        <if test="releaseStatu != null and releaseStatu != -1">
                and status = #{releaseStatu}
        </if>
        <if test="appName != null and appName !=''">
                and app_name like #{appName}
        </if>  
        <if test="deployType != null and deployType != ''">
                and deploy_type = #{deployType}
        </if>  
        <if test="departmentCode != null and departmentCode != ''">
                and department_code = #{departmentCode}
        </if> 
        and delete_flag = 0
    </select>  


    <!-- 发布单列表 条件查询 -->
    <select id="getReleaseListByCondition" parameterType="java.util.Map"  
                                           resultMap="releaseResultMap">  
        SELECT   
        d.department_code,d.department_name,
        d.status,d.qps,d.dms_flag,d.delete_flag,d.create_time,
        d.create_user,d.update_time,d.update_user,
        d.remark,
        l.uuid as layout_template_uuid //需要第二张表的uuid
        FROM 
        d_table as d
        LEFT JOIN l_table as l
        ON d.uuid=l.release_uuid
        AND l.delete_flag=0  //注意此处join的那张表的查询条件必须写在ON后不能写在最后面
        WHERE 1=1 
        <if test="releaseStatu != null and releaseStatu != -1">
                and d.status = #{releaseStatu}
        </if>
        <if test="appName != null and appName !=''">
                and d.app_name like #{appName}
        </if>  
        <if test="deployType != null and deployType != ''">
                and d.deploy_type = #{deployType}
        </if>  
        <if test="departmentCode != null and departmentCode != ''">
                and d.department_code = #{departmentCode}
        </if> 
        AND d.delete_flag = 0 
        ORDER BY d.create_time 
        DESC LIMIT #{pn},#{ps}
        //pn是开始下表 从0开始
        //ps是总条数 比如是10,10 即从10条开始,共需10条 ,而不是10,20
    </select>  
</mapper>

---

优化

before:

SELECT 
a.name,a.uuid,a.app_name,a.app_uuid,a.release_uuid,a.release_name,
a.layout_template_name,a.layout_template_uuid,a.layout_template_version,
a.cluster_uuid,a.status,a.remark,a.delete_flag,
a.create_time,a.create_user,a.update_time,a.update_user,
d.department_code,d.deploy_type,d.department_name,d.namespace
FROM app_instance as a
LEFT JOIN dms_release as d
ON a.release_uuid = d.uuid
AND d.delete_flag=0
WHERE a.delete_flag=0
ORDER BY a.create_time 
DESC LIMIT 1,2

after：

SELECT 
a.name,a.uuid,a.app_name,a.app_uuid,a.release_uuid,a.release_name,
a.layout_template_name,a.layout_template_uuid,a.layout_template_version,
a.cluster_uuid,a.status,a.remark,a.delete_flag,
a.create_time,a.create_user,a.update_time,a.update_user,
d.department_code,d.deploy_type,d.department_name,d.namespace
FROM (
        select * from app_instance where delete_flag=0 
        ORDER BY create_time desc LIMIT 1,2
     ) as a
LEFT JOIN dms_release as d
ON a.release_uuid = d.uuid
AND d.delete_flag=0

ALTER TABLE app_instance ADD INDEX index_release_uuid (release_uuid)

思路:
1.先筛选出符合条件的再进行join,而不是先全表join再筛选
2.加入关键选项的索引