mybatis中的关联查询和连接查询

实体类：

public class Employee {
    private Integer id;
    private Integer employeeNumber;
    private String name;
    private String gender;
    private Date birthday;
    private String telephone;
    private String email;
    private String address;
    private String photo;
    private String education;
    private Integer departmentNumber;
    private Integer positionNumber;
    private Date inTime;
    private String password;
    private String notes;
    
    //下面俩为两个实体类对象
    private Department department; //部门
    private Position position;      //职位

关联查询

以上面的实体类为例：

当查询employee的对象时，这个对象中还有部门和职位两个对象，如果使用连接查询的话，相当于三个表连接起来，比较复杂，我们这时候就可以使用关联查询进行处理。

<resultMap id="EmployeeMsg2" type="cn.aynu.entity.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="employee_number" property="employeeNumber"/>
        <result column="nameEmp" property="name"/>
        <result column="gender" property="gender"/>
        <result column="telephone" property="telephone"/>
        <association property="department" column="department_number" javaType="cn.aynu.entity.Department"
                     select="aaa"/>
        <association property="position"  column="position_number" javaType="cn.aynu.entity.Position"
                     select="bbb">
        </association>
    </resultMap>

    <select id="aaa" resultType="cn.aynu.entity.Department">
        select name from department where department_number = #{departmentNumber}
    </select>

    <select id="bbb" resultType="cn.aynu.entity.Position">
        select name from position where position_number = #{positionNumber}
    </select>

    <!--模糊查询结果-->
    <select id="selectEmpByName" resultMap="EmployeeMsg2">
        select *
        from
        history
        where name like '%' #{nameInput} '%' and
        department_number = <include refid="use3"/> and status = '在职'
        limit #{page.pageIndexStart},#{page.pageSize}
    </select>

    <sql id="use3">
        (select department_number
            from history where
            employee_number = #{employeeNumber}
            ) and position_number != '3003' and position_number != '3004'
    </sql>

注意，aaa和bbb标签会在映射文件中报错，但是不影响使用。这里的主查询是selectEmpByName，进行主查询的时候，也会进行关联查询，相当于访问数据库三次。关联查询的好处就是，不用进行表之间复杂的连接。

连接查询

连接查询，就是通过每个表之间相同的字段进行连接。这个优点就是只用访问一次数据库。