mybatis 常用 xml

批量添加

    <insert id="addUserBatch">
        INSERT INTO fx_user_base(
        id,
        identitykey,
        password,
        status,
        phone,
        name
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id,jdbcType=VARCHAR},
            #{item.identitykey,jdbcType=VARCHAR},
            #{item.password,jdbcType=VARCHAR},
            #{item.status,jdbcType=INTEGER},
            #{item.phone,jdbcType=VARCHAR},
            #{item.name,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>

根据list查询

    <select id="getLv2List" resultType="com.ifeixiu.media.pojo.Department">
    SELECT
        d.id,
        d.name,
        d.parent_id,
        d.level,
        d.create_time,
        d.update_time,
        d.source
    FROM
        fx_department d
        left join network_user_role ur on ur.department_id=d.id
        left join network_role r on ur.role_id=r.id
    WHERE
        1=1
        <if test="keyword!= null and keyword!=''">
            and d.name like CONCAT('%',#{keyword},'%')
        </if>
        <if test="depIdList!= null">
            and d.id in
            <foreach collection="depIdList" item="item" index="index" open="(" separator="," close=")">
                #{item, jdbcType=VARCHAR}
            </foreach>
        </if>
        <if test="type==1">
            and r.role_type=5
        </if>
        group by d.id
    </select>

抽离公共 sql

    <sql id="Base_Column_List">
        id,
         `name`,
        parent_id,
        `level`,
        create_time,
        update_time
    </sql>

    <select id="getDepartmentById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM fx_department
        WHERE id = #{id}
    </select>

复杂 resultMap

    <resultMap id="BaseResultMap" type="com.ifeixiu.media.packaged.PkgMediaCheck">
        <id column="id" property="id"/>
        <result column="type" property="type"/>
        <result column="create_time" property="createTime"/>
        <result column="operator_time" property="operatorTime"/>
        <association property="department" resultMap="DepartmentMap"/>
    </resultMap>


    <resultMap id="DepartmentMap" type="com.ifeixiu.media.packaged.PkgDepartment">
        <result column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <result column="department_parent_id" property="parentId"/>
        <association property="parent" resultMap="parentDepartmentMap"/>
    </resultMap>

    <resultMap id="parentDepartmentMap" type="com.ifeixiu.media.packaged.PkgDepartment">
        <result column="parent_department_id" property="id"/>
        <result column="parent_department_name" property="name"/>
    </resultMap>

条件查询

SELECT
        mn.id,
        mn.title,
        mn.content,
        mn.type,
        mn.notice_type,
        mn.issuing_time,
        mn.create_time,
        mn.create_user,
        mn.update_time,
        mn.update_user,
        mn.is_top,
        mn.sort,
        mn.is_delete
    FROM
        media_notice mn
    WHERE
        1=1
        <if test="view.type!=null">
            and mn.type = #{view.type}
        </if>
        <if test="view.isDelete!=null">
            and mn.is_delete = #{view.isDelete}
        </if>
        <if test="view.noticeType!=null">
            and mn.notice_type = #{view.noticeType}
        </if>
        <if test="view.startTime != null">
            and mn.issuing_time between #{view.startTime} and #{view.endTime}
        </if>
        <if test="view.title!= null and view.title!=''">
            and mn.title like CONCAT('%',#{view.title},'%')
        </if>

case when...then...else...end...用法

<select id="getGroupList" resultType="com.dobell.research.mybatis.entities.vo.GroupInfoVO">
        SELECT
            cu.unit_name groupName,
            cu.unit_code groupCode,
            cu.third_party_id,
            ut.role_id,
            ut.head_image,
            role.`COMMENT` roleName,
            ut.job_type,
            job.`COMMENT` jobName,
            cu.create_time,
            cuc.`level` `level`,
        CASE
            WHEN ut.role_id = 8 THEN
            1 ELSE ut.role_id
            END sort
        FROM
            common_user_to_unit ut
            LEFT JOIN common_user u ON ut.user_code = u.user_code
            LEFT JOIN common_unit cu ON ut.group_code = cu.unit_code
            LEFT JOIN common_unit_control cuc on cuc.group_code = cu.unit_code
            LEFT JOIN ( SELECT type, VALUE, COMMENT FROM sys_dictionary WHERE type = 13 )         
            role ON role.VALUE= ut.role_id
            LEFT JOIN ( SELECT type, VALUE, COMMENT FROM sys_dictionary WHERE type = 5 ) 
            job ON job.`value` = ut.job_type
        WHERE
            cu.`status` = 1
            AND (ut.is_delete = 1 OR ut.role_id = 8)
            AND ut.role_id IN ( 4, 5, 6, 7, 8 )
            AND (ut.student_type != 0 OR ut.role_id = 8)
            AND u.user_code = #{userCode}
        GROUP BY
            ut.group_code
        ORDER BY sort
    </select>