MySQL多表联查和动态SQL

查询

多对多 IF标签（ 例:admin操作员表  admin_role中间表  role角色表 ）

 

 需要再admin对象中添加Role

 

    <resultMap id="AdminWithRoles" type="com.xxxx.server.pojo.Admin" extends="BaseResultMap">
        <collection property="roles" ofType="com.xxxx.server.pojo.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>
    
    <!--获取所有操作员-->
    <select id="getAllAdmins" resultMap="AdminWithRoles">
        SELECT
            a.*,
            r.id AS rid,
            r.`name` AS rname,
            r.nameZh AS rnameZh
        FROM
            t_admin a
            LEFT JOIN t_admin_role ar ON a.id = ar.adminId
            LEFT JOIN t_role r ON r.id = ar.rid
        WHERE
            a.id != #{id}
            <if test="null!=keyWords and ''!=keyWords">
                AND a.`name` LIKE CONCAT( '%', #{keyWords}, '%' )
            </if>
           
        ORDER BY
            a.id
    </select>

 查询结果：

在单表中根据 ParentId 查询出多级嵌套关系

方法一：递归

查询结果

主表中有多个外键字段指向其他表，查询结果（对象中包含对象）

表结构

修改Bean实体

 查询语句

<!-- 员工对象里面包含多个对象-->
    <resultMap id="EmployeeInfo" type="com.xxxx.server.pojo.Employee" extends="BaseResultMap">
        <!--1. 民族对象-->
        <association property="nation" javaType="com.xxxx.server.pojo.Nation">
            <id column="nid" property="id"/>
            <result column="nname" property="name"/>
        </association>
        <!--2. 政治面貌对象-->
        <association property="politicsStatus" javaType="com.xxxx.server.pojo.PoliticsStatus">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
        <!--3. 部门对象-->
        <association property="department" javaType="com.xxxx.server.pojo.Department">
            <id column="did" property="id"/>
            <result column="dname" property="name"/>
        </association>
        <!--4. 职称对象-->
        <association property="joblevel" javaType="com.xxxx.server.pojo.Joblevel">
            <id column="jid" property="id"/>
            <result column="jname" property="name"/>
        </association>
        <!--5. 政治面貌对象-->
        <association property="position" javaType="com.xxxx.server.pojo.Position">
            <id column="posid" property="id"/>
            <result column="posname" property="name"/>
        </association>
    
    </resultMap>
    
    
    <!--获取所有员工(分页)-->
    <select id="getEmployeeByPage" resultMap="EmployeeInfo">
        
        SELECT
        e.*,
        n.id AS nid,
        n.`name` AS nname,
        p.id AS pid,
        p.`name` AS pname,
        d.id AS did,
        d.`name` AS dname,
        j.id AS jid,
        j.`name` AS jname,
        pos.id AS posid,
        pos.`name` AS posname
        FROM
        t_employee e,
        t_nation n,
        t_politics_status p,
        t_department d,
        t_joblevel j,
        t_position pos
        WHERE
        e.nationId = n.id
        AND e.politicId = p.id
        AND e.departmentId = d.id
        AND e.jobLevelId = j.id
        AND e.posId = pos.id
        <if test="null!=employee.name and ''!=employee.name">
            AND e.`name` LIKE CONCAT( '%', #{employee.name}, '%' )
        </if>
        <if test="null!=employee.politicId">
            AND e.politicId = #{employee.politicId}
        </if>
        <if test="null!=employee.nationId">
            AND e.nationId = #{employee.nationId}
        </if>
        <if test="null!=employee.jobLevelId">
            AND e.jobLevelId = #{employee.jobLevelId}
        </if>
        <if test="null!=employee.posId">
            AND e.posId = #{employee.posId}
        </if>
        <if test="null!=employee.engageForm and ''!=employee.engageForm">
            AND e.engageForm = #{employee.engageForm}
        </if>
        <if test="null!=employee.departmentId">
            AND e.departmentId = #{employee.departmentId}
        </if>
        <if test="null!=beginDateScope and 2==beginDateScope.length">
            AND e.beginDate BETWEEN #{beginDateScope[0]} AND #{beginDateScope[1]}
        </if>
        ORDER BY e.id
    
    </select>

插入

遍历数组插入

    <!--  insert into table ([列名],[列名]) VALUES( ([列值],[列值]) ,([列值],[列值]) ) ;   -->
    <insert id="insertRecord">
        insert into t_menu_role(mid,rid) values
        <foreach collection="mids" item="mid" separator=",">
            (#{mid},#{rid})
        </foreach>
    </insert>

知识点

Mybatis动态Sql语句中特殊字符使用