mybatis 增删查改

一、定義出需要傳回實體類的resultMap

<resultMap type="TeamInfoShow" id="TeamInfoShowResult">
        <id property="id"    column="id"    />
        <result property="teamName"    column="team_name"    />
        <result property="teamLevel"    column="team_level"    />
        <result property="teamType"    column="team_type"    />
        <result property="status"    column="status"    />
        <result property="leaderId"    column="leader_id"    />
        <result property="leaderName"    column="person_name"    />
        <result property="isOpen" column="is_open"    />
        <result property="teamRemark" column="team_remark"    />
        <result property="teamTarget" column="team_target"    />
        <result property="bannerId" column="banner_id"    />
        <result property="bannerName" column="file_name"    />
        <result property="bannerUrl" column="file_url"    />
        <result property="createTime" column="create_time"    />
    </resultMap>
           

二、查詢方法

<select id="selectTeamInfoAdminList" parameterType="TeamInfoListQuery" resultMap="TeamInfoShowResult">
        select distinct(tti.id) id, tti.team_name, tti.team_level, tti.team_type, tti.status, tti.leader_id, tti.is_open, tti.team_remark, tti.team_target, tti.banner_id,
        tbp.person_name, tbf.file_name, tbf.file_url
        from t_team_info tti
        left join t_base_person tbp on tti.leader_id = tbp.id
        left join t_base_files tbf on tti.banner_id = tbf.id
        left join t_team_admin tta on tti.id = tta.team_id
        left join t_team_person ttp on ttp.team_id = tti.id
        where tti.status in (0, 1)
        <trim>
            <if test="personId != null"> and (tti.leader_id = #{personId} or tta.person_id = #{personId}) </if>
            <if test="teamName != null  and teamName != ''"> and tti.team_name like concat('%', trim(#{teamName}), '%')</if>
            <if test="teamLevel != null "> and tti.team_level = #{teamLevel}</if>
            <if test="teamType != null  and teamType != ''"> and tti.team_type = #{teamType}</if>
        </trim>
        order by tti.create_time desc
    </select>

    //也可寫寫為
    <select id="selectTeamInfoAdminList" parameterType="TeamInfoListQuery" resultMap="TeamInfoShowResult">
        select distinct(tti.id) id, tti.team_name, tti.team_level, tti.team_type, tti.status, tti.leader_id, tti.is_open, tti.team_remark, tti.team_target, tti.banner_id,
        tbp.person_name, tbf.file_name, tbf.file_url
        from t_team_info tti
        left join t_base_person tbp on tti.leader_id = tbp.id
        left join t_base_files tbf on tti.banner_id = tbf.id
        left join t_team_admin tta on tti.id = tta.team_id
        left join t_team_person ttp on ttp.team_id = tti.id
        <where>
            and tti.status in (0, 1)
            <if test="personId != null"> and (tti.leader_id = #{personId} or tta.person_id = #{personId}) </if>
            <if test="teamName != null  and teamName != ''"> and tti.team_name like concat('%', trim(#{teamName}), '%')</if>
            <if test="teamLevel != null "> and tti.team_level = #{teamLevel}</if>
            <if test="teamType != null  and teamType != ''"> and tti.team_type = #{teamType}</if>
        </where>
        order by tti.create_time desc
    </select>
           

三、新增方法

//useGeneratedKeys="true"設定為true 意思是傳回增加的字段 keyProperty="id" 設定該字段為id
    <insert id="insertTeamInfo" parameterType="TeamInfo" useGeneratedKeys="true" keyProperty="id">
        insert into t_team_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="teamName != null and teamName != ''">team_name,</if>
            <if test="teamLevel != null">team_level,</if>
            <if test="teamType != null">team_type,</if>
            <if test="teamOrder != null">team_order,</if>
            <if test="teamRemark != null">team_remark,</if>
            <if test="teamTarget != null">team_target,</if>
            <if test="leaderId != null">leader_id,</if>
            <if test="bannerId != null">banner_id,</if>
            <if test="status != null">status,</if>
            <if test="workStartDate != null">work_start_date,</if>
            <if test="workEndDate != null">work_end_date,</if>
            <if test="currentOrgCode != null">current_org_code,</if>
            <if test="currentUnitCode != null">current_unit_code,</if>
            <if test="isOpen != null">is_open,</if>
            <if test="createrId != null">creater_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updaterId != null">updater_id,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="teamName != null and teamName != ''">#{teamName},</if>
            <if test="teamLevel != null">#{teamLevel},</if>
            <if test="teamType != null">#{teamType},</if>
            <if test="teamOrder != null">#{teamOrder},</if>
            <if test="teamRemark != null">#{teamRemark},</if>
            <if test="teamTarget != null">#{teamTarget},</if>
            <if test="leaderId != null">#{leaderId},</if>
            <if test="bannerId != null">#{bannerId},</if>
            <if test="status != null">#{status},</if>
            <if test="workStartDate != null">#{workStartDate},</if>
            <if test="workEndDate != null">#{workEndDate},</if>
            <if test="currentOrgCode != null">#{currentOrgCode},</if>
            <if test="currentUnitCode != null">#{currentUnitCode},</if>
            <if test="isOpen != null">#{isOpen},</if>
            <if test="createrId != null">#{createrId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updaterId != null">#{updaterId},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>
           

四、修改方法

<update id="updateTeamInfo" parameterType="TeamInfo">
        update t_team_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="teamName != null and teamName != ''">team_name = #{teamName},</if>
            <if test="teamLevel != null">team_level = #{teamLevel},</if>
            <if test="teamType != null">team_type = #{teamType},</if>
            <if test="teamOrder != null">team_order = #{teamOrder},</if>
            <if test="teamRemark != null">team_remark = #{teamRemark},</if>
            <if test="teamTarget != null">team_target = #{teamTarget},</if>
            <if test="leaderId != null">leader_id = #{leaderId},</if>
            <if test="bannerId != null">banner_id = #{bannerId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="workStartDate != null">work_start_date = #{workStartDate},</if>
            <if test="workEndDate != null">work_end_date = #{workEndDate},</if>
            <if test="currentOrgCode != null">current_org_code = #{currentOrgCode},</if>
            <if test="currentUnitCode != null">current_unit_code = #{currentUnitCode},</if>
            <if test="isOpen != null">is_open = #{isOpen},</if>
            <if test="createrId != null">creater_id = #{createrId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updaterId != null">updater_id = #{updaterId},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>
           

五、删除方法

//單個删除
    <delete id="deleteTeamInfoById" parameterType="Long">
        delete from t_team_info where id = #{id}
    </delete>
     //批量删除 mapper 傳入的參數是數組
     // public int deleteTeamInfoByIds(Long[] ids);
    <delete id="deleteTeamInfoByIds" parameterType="String">
        delete from t_team_info where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>