MyBatis联表查询——别名方式

在使用MyBatis你想工程时，单表操作其实是非常完美的，涉及到多表联合查询时，需要我们自己写联表的SQL语句。

我拿出项目中的部分代码作为示例，

EmployeeMapper.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.atguigu.crud.mapper.EmployeeMapper">
    <resultMap id="BaseResultMap"
        type="com.atguigu.crud.bean.Employee">
        <result column="emp_id" jdbcType="INTEGER" property="empId" />
        <result column="emp_name" jdbcType="VARCHAR" property="empName" />
        <result column="gender" jdbcType="CHAR" property="gender" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="d_id" jdbcType="INTEGER" property="dId" />
    </resultMap>
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria"
                separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and
                                    #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value"
                                        item="listItem" open="(" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Update_By_Example_Where_Clause">
        <where>
            <foreach collection="example.oredCriteria" item="criteria"
                separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and
                                    #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value"
                                        item="listItem" open="(" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Base_Column_List">
        emp_id, emp_name, gender, email, d_id
    </sql>

    <!-- 别名 -->
    <sql id="Base_Column_List_Alias">
        ${alias}.emp_id, ${alias}.emp_name, ${alias}.gender, ${alias}.email, d_id
    </sql>

    
    <!-- 关联对象 -->
    <resultMap type="Employee" id="BaseResultMapWithDept" extends="BaseResultMap">
        <association property="department" resultMap="com.atguigu.crud.mapper.DepartmentMapper.BaseResultMap"></association>
    </resultMap>

说明：department为Employee.java实体类中的关联对象，private Department department;

    <!-- 查询返回部门信息 -->
    <select id="selectByExampleWithDept" resultMap="BaseResultMapWithDept">
        select
        <if test="distinct">
            distinct
        </if>
        <include refid="Base_Column_List_Alias">
            <property name="alias" value="e" />
        </include>
        ,
        <include refid="com.atguigu.crud.mapper.DepartmentMapper.Base_Column_List_Alias">
            <property name="alias" value="d" />
        </include>

说明：com.atguigu.crud.mapper.DepartmentMapper.Base_Column_List_Alias为DepartmentMapper.xml中的字段别名形式。

        FROM tbl_emp e
        left join tbl_dept d on e.`d_id`=d.`dept_id`
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
    </select>

    <select id="selectByExample"
        parameterType="com.atguigu.crud.bean.EmployeeExample"
        resultMap="BaseResultMap">
        select
        <if test="distinct">
            distinct
        </if>
        <include refid="Base_Column_List" />
        from tbl_emp
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
    </select>
    <delete id="deleteByExample"
        parameterType="com.atguigu.crud.bean.EmployeeExample">
        delete from tbl_emp
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
    </delete>
    <insert id="insert"
        parameterType="com.atguigu.crud.bean.Employee">
        insert into tbl_emp (emp_id, emp_name, gender,
        email, d_id)
        values (#{empId,jdbcType=INTEGER}, #{empName,jdbcType=VARCHAR},
        #{gender,jdbcType=CHAR},
        #{email,jdbcType=VARCHAR}, #{dId,jdbcType=INTEGER})
    </insert>
    <insert id="insertSelective"
        parameterType="com.atguigu.crud.bean.Employee">
        insert into tbl_emp
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="empId != null">
                emp_id,
            </if>
            <if test="empName != null">
                emp_name,
            </if>
            <if test="gender != null">
                gender,
            </if>
            <if test="email != null">
                email,
            </if>
            <if test="dId != null">
                d_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="empId != null">
                #{empId,jdbcType=INTEGER},
            </if>
            <if test="empName != null">
                #{empName,jdbcType=VARCHAR},
            </if>
            <if test="gender != null">
                #{gender,jdbcType=CHAR},
            </if>
            <if test="email != null">
                #{email,jdbcType=VARCHAR},
            </if>
            <if test="dId != null">
                #{dId,jdbcType=INTEGER},
            </if>
        </trim>
    </insert>
    <select id="countByExample"
        parameterType="com.atguigu.crud.bean.EmployeeExample"
        resultType="java.lang.Long">
        select count(*) from tbl_emp
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
    </select>
    <update id="updateByExampleSelective" parameterType="map">
        update tbl_emp
        <set>
            <if test="record.empId != null">
                emp_id = #{record.empId,jdbcType=INTEGER},
            </if>
            <if test="record.empName != null">
                emp_name = #{record.empName,jdbcType=VARCHAR},
            </if>
            <if test="record.gender != null">
                gender = #{record.gender,jdbcType=CHAR},
            </if>
            <if test="record.email != null">
                email = #{record.email,jdbcType=VARCHAR},
            </if>
            <if test="record.dId != null">
                d_id = #{record.dId,jdbcType=INTEGER},
            </if>
        </set>
        <if test="_parameter != null">
            <include refid="Update_By_Example_Where_Clause" />
        </if>
    </update>
    <update id="updateByExample" parameterType="map">
        update tbl_emp
        set emp_id = #{record.empId,jdbcType=INTEGER},
        emp_name = #{record.empName,jdbcType=VARCHAR},
        gender = #{record.gender,jdbcType=CHAR},
        email = #{record.email,jdbcType=VARCHAR},
        d_id = #{record.dId,jdbcType=INTEGER}
        <if test="_parameter != null">
            <include refid="Update_By_Example_Where_Clause" />
        </if>
    </update>
</mapper>

DepartmentMapper.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.atguigu.crud.mapper.DepartmentMapper">
  <resultMap id="BaseResultMap" type="com.atguigu.crud.bean.Department">
    <result column="dept_id" jdbcType="INTEGER" property="deptId" />
    <result column="dept_name" jdbcType="VARCHAR" property="deptName" />
  </resultMap>
  <sql id="Example_Where_Clause">
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
    dept_id, dept_name
  </sql>
  
  <!-- 别名 -->
  <sql id="Base_Column_List_Alias">
    ${alias}.dept_id, ${alias}.dept_name
  </sql>
  
  <select id="selectByExample" parameterType="com.atguigu.crud.bean.DepartmentExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from tbl_dept
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <delete id="deleteByExample" parameterType="com.atguigu.crud.bean.DepartmentExample">
    delete from tbl_dept
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="com.atguigu.crud.bean.Department">
    insert into tbl_dept (dept_id, dept_name)
    values (#{deptId,jdbcType=INTEGER}, #{deptName,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.atguigu.crud.bean.Department">
    insert into tbl_dept
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="deptId != null">
        dept_id,
      </if>
      <if test="deptName != null">
        dept_name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="deptId != null">
        #{deptId,jdbcType=INTEGER},
      </if>
      <if test="deptName != null">
        #{deptName,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.atguigu.crud.bean.DepartmentExample" resultType="java.lang.Long">
    select count(*) from tbl_dept
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map">
    update tbl_dept
    <set>
      <if test="record.deptId != null">
        dept_id = #{record.deptId,jdbcType=INTEGER},
      </if>
      <if test="record.deptName != null">
        dept_name = #{record.deptName,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
    update tbl_dept
    set dept_id = #{record.deptId,jdbcType=INTEGER},
      dept_name = #{record.deptName,jdbcType=VARCHAR}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
</mapper>

该别名的定义，是为了在EmployeeMapper.xml以别名方式进行查询。

实体类：

Department.java：

package com.atguigu.crud.bean;

public class Employee {
    private Integer empId;

    private String empName;

    private String gender;

    private String email;

    private Integer dId;

    // 希望查询员工的同时部门信息也是查询好的
    private Department department;

public Employee() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Employee(Integer empId, String empName, String gender, String email, Integer dId) {
        super();
        this.empId = empId;
        this.empName = empName;
        this.gender = gender;
        this.email = email;
        this.dId = dId;
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName == null ? null : empName.trim();
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender == null ? null : gender.trim();
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    public Integer getdId() {
        return dId;
    }

    public void setdId(Integer dId) {
        this.dId = dId;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

}

Department.java：

package com.atguigu.crud.bean;

public class Department {
    private Integer deptId;

    private String deptName;

    // 一旦生成有参的构造器，那么就一定要生成有参的构造器
    public Department() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Department(Integer deptId, String deptName) {
        super();
        this.deptId = deptId;
        this.deptName = deptName;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName == null ? null : deptName.trim();
    }
}