叩丁狼java学习第十一天-mybatis多表查询

1.多表查询SQL

核心代码

    <resultMap id="employeeMap2" type="Employee">
  map映射
        <id property="id" column="id" />
        <result property="employeeName" column="employee_name"/>
        <association property="department"
   没有colum，因为映射的是另一对象
                     javaType="Department">
            <result property="id" column="d_id"/>
            <result property="departmentName" column="d_name"/>
        </association>
    </resultMap>
    <select id="selectEmployeeAll" resultMap="employeeMap2">
        SELECT e.id, e.employee_name, d.id AS d_id, d.department_name AS d_name
        FROM employee e JOIN department d ON e.department_id = d.id
    </select>

2.一对多

<resultMap id="departmentMap" type="Department">
        <id property="id" column="id"></id>
        <result property="departmentName" column="department_name"></result>
集合是用的colllection
        <collection property="employeeList" column="id" select="mapper.EmployeeMapper.selectById" />
    </resultMap>
    <select id="selectById" resultMap="departmentMap">
        SELECT * FROM department WHERE id=#{id}
    </select>

3.多参数

mybatis文档:

property description
column 数据库的列名或者列标签别名。与传递给resultSet.getString(columnName)的参数名称相同。注意： 在处理组合键时，您可以使用column=“{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句。这就会把prop1和prop2设置到目标嵌套选择语句的参数对象中。

<resultMap type="com.zres.product.resmaster.portal.homePage.entity.ResTreeDetailEntity" id="leftTreeFirstMaps">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <id column="lngId" jdbcType="INTEGER" property="lngId"/>
        <collection property="children" ofType="com.zres.product.resmaster.portal.homePage.entity.ResTreeDetailEntity" select="queryLeftTreeById"
                    column="{id=id, lngId=lngId}">
        </collection>
    </resultMap>
    <select id="queryLeftTreeById" parameterType="map" resultMap="leftTreeFirstMaps">
        SELECT prt.id,
               prt.tree_id as treeId,
               prt.parent_id as parentId,
               prt.res_type_id as resTypeId,
               #{lngId} as lngId,
            <choose>
                <when test="lngId == -1">
                    prt.res_type as resName,
                </when>
                <otherwise>
                    FUN_GET_LNG_DESC(#{lngId},prt.id,prt.res_type,'PUB_RES_TREE_DETAIL',prt.env_domain_id) as resName,
                </otherwise>
            </choose>
               prt.icon as icon,
               prt.query_id as queryId,
               prt.role_id as roleId
        FROM pub_res_tree_detail prt
        WHERE prt.is_display = 1
          AND prt.parent_id = #{id}
          AND prt.is_resource = 2
    </select>