mybatis动态sql，批量插入，批量删除，模糊查询

最新推荐文章于 2024-07-24 17:29:42 发布

成年人的苦衷

最新推荐文章于 2024-07-24 17:29:42 发布

阅读量875

点赞数 3

分类专栏：框架文章标签： mybatis

本文链接：https://blog.csdn.net/weixin_43996168/article/details/108537341

版权

框架专栏收录该内容

10 篇文章 0 订阅

订阅专栏

trim可以去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。
trim的属性：

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

mybatis中foreach的属性：

属性	描述
collection	要做foreach的对象，mapper接口传过来的参数
item	集合中元素迭代时的别名，该参数为必选。
open	foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选
separator	元素之间的分隔符
close	foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。

动态插入，选择性插入

mapper接口：

int insert(User user);

xml：

 <!-- 插入用户 -->
  <insert id="insert">
       insert into SYS_USER
       <trim prefix="(" suffix=")" suffixOverrides=",">
           ID,CODE,NAME,YEAR,PWD,ENABLED,
           <if test="tel != null and tel != ''">
               TEL,
           </if>
           <if test="email != null and email != ''">
               EMAIL,
           </if>
           <if test="addr != null and addr != ''">
               ADDR,
           </if>
       </trim>
       <trim prefix="values (" suffix=")" suffixOverrides=",">
           #{id},
           #{code,jdbcType=VARCHAR},
           #{name,jdbcType=VARCHAR},
           #{year,jdbcType=DECIMAL},
           #{pwd,jdbcType=VARCHAR},
           #{enabled,jdbcType=DECIMAL},
           <if test="tel != null and tel != ''">
               #{tel,jdbcType=VARCHAR},
           </if>
           <if test="email != null and email != ''">
               #{email,jdbcType=VARCHAR},
           </if>
           <if test="addr != null and addr != ''">
               #{addr,jdbcType=VARCHAR},
           </if>
       </trim>
   </insert>

批量插入

java：

int insertBatch(List<User> list);

xml：

<insert id="insertBatch">
    INSERT INTO t_user(id, name, del_flag)
    VALUES
    <foreach collection ="list" item="user" separator =",">
         (#{user.id}, #{user.name}, #{user.delFlag})
    </foreach >
</insert>

批量删除，（假删）

mapper接口：

 /**
     * 通过id批量删除用户(逻辑删除)
     * @param ids  用户id集合
     * @param year 年份
     * @return 影响行数
     * @date 2020/8/11 21:29
     */
    int booleanDeleteByIds(List<String> ids, int year);

xml：

<delete id="booleanDeleteByIds">
     update sys_user
     set bdel = 1
     where year = #{year}
     and id in
     <foreach collection="ids" item="id" open="(" separator="," close=")">
         #{id}
     </foreach>
 </delete>

选择性修改

java：

 int baseInfoUpdate(ProjectPreBill projectPreBill);

xml：

 <update id="baseInfoUpdate" parameterType="cn.wwkj.pms.repo.entity.po.ProjectRepoBill">
        update proj_pre_bill
        <trim prefix="set" suffixOverrides=",">
            <if test="name!=null">name=#{name},</if>
            <if test="bsId!=null">bs_id=#{bsId},</if>
            <if test="enId!=null">EN_ID=#{enId},</if>
            <if test="enPid!=null">EN_PID=#{enPid},</if>
            <if test="bsgId!=null">bsg_id=#{bsgId},</if>
            <if test="bsiId!=null">bsi_id=#{bsiId},</if>
            <if test="pctId!=null">pct_id=#{pctId},</if>
            <if test="pttId!=null">ptt_id=#{pttId},</if>
            <if test="pgtId!=null">pgt_id=#{pgtId},</if>
            <if test="muId!=null">mu_id=#{muId},</if>
            <if test="bDate!=null">b_date=#{bDate},</if>
            <if test="eDate!=null">e_date=#{eDate},</if>
            <if test="manager!=null">manager=#{manager},</if>
            <if test="contact!=null">contact=#{contact},</if>
            <if test="tel!=null">tel=#{tel},</if>
            <if test="addr!=null">addr=#{addr},</if>
            <if test="allBudget!=null" >all_budget=#{allBudget},</if>
            <if test="curBudget!=null">cur_budget=#{curBudget},</if>
            <if test="purBudget!=null">pur_budget=#{purBudget},</if>
            <if test="content!=null">content=#{content},</if>
            <if test="basis!=null">basis=#{basis},</if>
            <if test="necessity!=null">necessity=#{necessity},</if>
            <if test="calcMethod!=null">calc_method=#{calcMethod},</if>
            <if test="allGoal!=null">all_goal=#{allGoal},</if>
            <if test="curGoal!=null">cur_goal=#{curGoal},</if>
            <if test="plan!=null">plan=#{plan},</if>
        </trim>
        WHERE id=#{id} and year = #{year}
    </update>

模糊查询

mapper接口：

 /**
     * 根据当前用户ID,year查询所有 未删除项目
     * @param name    项目名称
     * @param bsgId   政府经济分类id数组
     * @param bsId    功能分类id数组
     * @param cdate   填报年月
     * @param muId    资金用途id数组
     * @param pctId   项目类别-内容分类 id数组
     * @param pgtId   项目类别-目标分类 id数组
     * @param pttId   项目类别-时间分类 id数组
     * @param year    当前年
     * @param mark    项目来源 3=GOAL：目标 2=IMP：导入  1=ADD：手工录入
     * @param enabled 项目状态 0:无效（被部门预算砍掉） 1:有效 -1:初始状态
     * @return 绩效项目集合
     * @author GaoDongYang
     * @date 2020/8/24 14:54
     **/
    List<ProjectRepoBill> getProjectRepoBillByParam(String name,
                                                    List<String> bsId,
                                                    List<String> bsgId,
                                                    List<String> pctId,
                                                    List<String> pttId,
                                                    List<String> pgtId,
                                                    List<String> muId,
                                                    String cdate,
                                                    Integer enabled,
                                                    int year
            										, String mark);

xml：

<select id="getProjectRepoBillByParam" resultType="cn.wwkj.pms.repo.entity.po.ProjectRepoBill">
        select p.id,p.en_id,p.code,p.name,s.name as mu_id_name,p.bgov,p.bcity,p.enabled,
        p.pur_budget,p.b_date,p.e_date,p.bsg_id,p.pct_id,p.pgt_id,p.bs_id,p.ptt_id,p.mu_id,p.bsi_id,p.source,p.year,p.all_budget,p.CUR_BUDGET,p.cu_id,p.manager,p.tel,p.contact,p.addr,
        (select name from sys_element_detail where id=p.bsg_id) as bsg_id_name,
        (select name from sys_element_detail where id=p.pct_id) as pct_id_name,
        (select name from sys_element_detail where id=p.bs_id) as bs_id_name,
        (select name from sys_element_detail where id=p.ptt_id) as ptt_id_name,
        (select name from sys_element_detail where id=p.pgt_id) as pgt_id_name,
        (select name from sys_element_detail where id=p.bsi_id) as bsi_id_name,
        to_char(p.cdate,'yyyy-mm-dd') cdate
        from proj_repo_bill p
        inner join sys_element_detail s on p.mu_id=s.id and p.year=#{year} and p.bdel=0
        <where>
            <if test="name!=null and name!=''">
                and p.name like concat('%',concat(#{name},'%'))
            </if>
            <if test="muId!=null and muId.size() > 0 ">
                and p.mu_id in
                <foreach collection="muId" item="muId" open="(" separator="," close=")">
                    #{muId}
                </foreach>
            </if>
            <if test="bsId!=null and bsId.size() > 0">
                and p.bs_id in
                <foreach collection="bsId" item="bsId" open="(" separator="," close=")">
                    #{bsId}
                </foreach>
            </if>
            <if test="bsgId!=null and bsgId.size() > 0">
                and p.bsg_id in
                <foreach collection="bsgId" item="bsgId" open="(" separator="," close=")">
                    #{bsgId}
                </foreach>
            </if>
            <if test="pctId!=null and pctId.size() > 0">
                and p.pct_id in
                <foreach collection="pctId" item="pctId" open="(" separator="," close=")">
                    #{pctId}
                </foreach>
            </if>
            <if test="pttId!=null and pttId.size() > 0">
                and p.ptt_id in
                <foreach collection="pttId" item="pttId" open="(" separator="," close=")">
                    #{pttId}
                </foreach>
            </if>
            <if test="pgtId!=null and pgtId.size() > 0">
                and p.pgt_id in
                <foreach collection="pgtId" item="pgtId" open="(" separator="," close=")">
                    #{pgtId}
                </foreach>
            </if>
            <if test="cdate!=null and cdate!=''">
                and to_char(p.cdate,'yyyy-mm')=#{cdate}
            </if>
            <if test="mark==1">
                and p.source='ADD'
            </if>
            <if test="mark==2">
                and p.source='IMP'
            </if>
            <if test="mark==3">
                and p.source='GOAL'
            </if>
            <if test="enabled==-1">
                and p.enabled=-1
            </if>
            <if test="enabled==0">
                and p.enabled=0
            </if>
            <if test="enabled==1">
                and p.enabled=1
            </if>
        </where>
        order by p.code desc
    </select>