Mybatis映射文件（2）

野性酷女 2022-05-24 03:26 216阅读 0赞

##### Select元素 #####

<select id="selectById" parameterType="Integer" resultType="employee">
        SELECT * FROM t_employee WHERE id = #{id}
    </select>

id：唯一标识符

parameterType：参数类型

resultType：返回值类型

<select id="getEmpsByLastNameLike" resultType="employee">
        SELECT * FROM t_employee WHERE last_name LIKE #{lastName}
    </select>

返回一条记录的map：key就是列名，值就是对应的值

<select id="getEmpByIDReturnMap" resultType="Map">
        SELECT * FROM t_employee WHERE id=#{id}
    </select>

多条记录封装一个map：Map<Integer,Employee>:键是这条记录的主键，值是封装后的javaBean

//多条记录封装一个map：Map<Integer,Employee>:键是这条记录的主键，值是封装后的javaBean
    //告诉mybatis封装这个map的时候使用哪个属性作为map的key
    @MapKey("id")
    Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);

<select id="getEmpByLastNameLikeReturnMap" resultType="employee">
        SELECT * FROM t_employee WHERE last_name LIKE #{lastName}
    </select>

查出数据封装成什么类型？——列名和属性名不一样

1、别名

2、复合驼峰命名法，则在全局配置文件中开启驼峰命名法

3、resultMap自定义对应

自动封装（映射）：

※在全局设置中加上autoMappingBehavior——》开启自动映射

※开启驼峰命名规则映射功能 ：mapUnderscoreToCamelCase=true

更多更复杂的则需要自定义resultMap实现高级结果集映射功能。

##### resultMap    #####

1、resultMap单表

<mapper namespace="dao.EmployeeMapperPlus">
    
        
        <resultMap id="emp" type="bean.Employee">
            
            <id column="id" property="id"/>
            
            <result column="last_name" property="lastName"/>
            
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </resultMap>
    
        
        <select id="getEmpById" resultMap="emp">
            SELECT * FROM t_employee WHERE id=#{id}
        </select>
    </mapper>

2、resultMap多表级联

建立了Department实体类

<resultMap id="empAnddept1" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <result column="d_id" property="department.id"/>
        <result column="d_name" property="department.name"/>
    </resultMap>
    
    
    <resultMap id="empAnddept2" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        
        <association property="department" javaType="bean.Department">
            <id column="id" property="id"/>
            <result column="d_name" property="name"/>
        </association>
    </resultMap>
    <select id="getEmployeeAndDepartment" resultMap="empAnddept2">
        SELECT e.id id,e.last_name last_name,e.email email,e.gender gender,
              e.d_id d_id,d.id did,d.d_name d_name
        FROM t_employee e,t_department d
        WHERE e.d_id=d.id AND e.id=#{id}
    </select>

还有一种方法就是分开查询，分别建立员工和部门的借口和映射文件，最后将分别查出的结果合在一起。

<resultMap id="empAnddept3" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        
        <association property="department" select="dao.DepartmentMapper.getDepartmentById" column="d_id"></association>
    </resultMap>
    <select id="getEmployeeByIdStep" resultMap="empAnddept3">
        SELECT * FROM t_employee WHERE id=#{id}
    </select>

3、resultMap处理集合

<resultMap id="dept1" type="bean.Department">
        <id column="id" property="id"/>
        <result column="d_name" property="name"/>
        
        <collection property="employee" ofType="bean.Employee">
            
            <id column="id" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <select id="getDepartmentAndEmpsById" resultMap="dept1">
        SELECT d.id id,d.d_name d_name,e.id eid,e.last_name last_name,e.email email,e.gender gender
        FROM t_department d LEFT JOIN t_employee e ON d.id=e.d_id
        WHERE d.id=#{id}
    </select>

在集合类型中使用分步和延迟加载

<resultMap id="dept2" type="bean.Department">
        <id column="id" property="id"/>
        <result column="d_name" property="name"/>
        
        <collection property="employee"
                    select="dao.EmployeeMapperPlus.getEmpsByDeptId" column="id"></collection>
    </resultMap>
    <select id="getDepartmentAndEmpByIdSteps" resultMap="dept2">
        SELECT * FROM t_department WHERE id=#{id}
    </select>

懒加载直接在全局设置中开启或者使用fetchType

<resultMap id="dept2" type="bean.Department">
        <id column="id" property="id"/>
        <result column="d_name" property="name"/>
        
        <collection property="employee"
                    select="dao.EmployeeMapperPlus.getEmpsByDeptId" column="id" fetchType="eager"></collection>
        
        
    </resultMap>
    <select id="getDepartmentAndEmpByIdSteps" resultMap="dept2">
        SELECT * FROM t_department WHERE id=#{id}
    </select>

鉴别器：

<resultMap id="empAnddept4" type="bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        
        <discriminator javaType="String" column="gender">
            
            <case value="0" resultType="bean.Employee">
                <association property="department"  select="dao.DepartmentMapper.getDepartmentById" column="d_id"></association>
            </case>
            
            <case value="1" resultType="bean.Employee">
                <id column="id" property="id"/>
                <result column="last_name" property="lastName"/>
                <result column="last_name" property="email"/>
                <result column="gender" property="gender"/>
            </case>
        </discriminator>
    </resultMap>