Mybatis 注解方式-按条件、批量增删改查

按条件查询

@Select("<script>" +
            "SELECT role_id,role_name " +
            "FROM `edu_base_role` " +
            "<where>" +
            "<if test=\"roleType != null\">" +
            "role_type=#{roleType} " +
            "</if>" +
            "</where>" +
            "</script>")
    List<MemRoleResult> selectMemRole(@Param("roleType") Integer roleType);

按条件更新

 @Update("<script>" +
            "UPDATE edu_member_rigths_dispose" +
            "<set>" +
            "<if test=\"mrId != null\">" +
            " mr_id = #{mrId}, " +
            "</if>" +
            "<if test=\"mrName != null\">" +
            "mr_name = #{mrName}," +
            "</if>" +
            "</set>" +
            "WHERE mrd_id = #{mrdId}" +
            "</script>")
    void updMemRightsDispose(MemRigthsDispose memRigthsDispose);

批量插入

  @Insert("<script>" +
            "INSERT INTO edu_member_user_rights_detail (" +
            "mrd_unit,mrd_remarks,murd_term_end_time,murd_status,murd_use_time,create_uid,create_time,del_status) VALUES " +
            " <foreach item='c' index='index' collection='addUserRightsDetailParamsList' separator=',' >" +
            " (#{c.mrdUnit},#{c.mrdRemarks},#{c.murdTermEndTime},#{c.murdStatus},#{c.murdUseTime},#{c.createUid}," +
            "#{c.createTime},#{c.delStatus}) " +
            " </foreach>" +
            "</script>")
    void addBatchUserRightsDetail(@Param("addUserRightsDetailParamsList")List<AddUserRightsDetailParams> addUserRightsDetailParamsList);

批量查询

@Select("<script>" + "SELECT " + LIST_COLUMNS + " FROM edu_tag_library WHERE del_status=0 "
            + "<if test = \"null != tlBizIds \" > " + "and tl_biz_id IN " +
            "<foreach item='tlBizId' index='index' collection='tlBizIds' open='(' separator=',' close=')'>" +
            "#{tlBizId}" +
            "</foreach>" +
            "</if></script>")
    List<TagLibrary> selectTagLibraryByTlBizId(@Param("tlBizIds") List<String> tlBizIds);

模糊查询
@Select("<script>" +
            "SELECT fc_id,fc_number,dept_name,job_name, create_name as applicantName,fc_type,fc_cost_item,fc_cost_project,fc_fee,fc_cost_date" +
            " from edu_fee_cost WHERE del_status=0 and ki_id=#{kiId}" +
            " <if test = \"null !=  fcNumber and fcNumber != ''\"> " +
            " and fc_number like CONCAT('%',#{fcNumber},'%') " +
            " </if> " +
            " <if test = \"null !=  applicantName and applicantName != ''\"> " +
            " and create_name like CONCAT('%',#{applicantName},'%') " +
            " </if> " +
            " <if test = \"null != fcStartDate and null !=fcEndDate and fcStartDate!='' and fcEndDate !='' \"> " +
            " and  DATE_FORMAT(fc_cost_date,'%Y-%m-%d') between #{fcStartDate} and #{fcEndDate} " +
            " </if> " +
            " <if test = \"null != fcStartFee and null !=fcEndFee and fcEndFee !=-1 \"> " +
            " and  fc_fee between #{fcStartFee} and #{fcEndFee} " +
            " </if> " +
            " <if test = \"null != fcStartFee  and null !=fcEndFee and fcEndFee==-1 \"> " +
            " and  fc_fee >= #{fcStartFee}" +
            " </if> " +
            " <if test = \"null != searchName and searchName !=''  \"> " +
            " and (fc_number like CONCAT('%',#{searchName},'%') or create_name like CONCAT('%',#{searchName},'%') or fc_cost_project like CONCAT('%',#{searchName},'%'))"+
            " </if> " +
            " <if test = \"null != fcType and fcType.size > 0 \">" +
            " and fc_type in"+
            "<foreach item='item' index='index' collection='fcType' open='(' separator=',' close=')'>" +
            " #{item} " +
            "</foreach>" +
            " </if> " +
            "</script>" )
    List<FeeCostPageResult> getFeeCostPage(FeeCostPageParam param);

批量更新

@Update("<script>" + " <foreach collection='list' item='item' index='index' separator=','> "
            + " UPDATE edu_tag_user_rel  " + " SET del_status = 1  " + " WHERE "
            + " <if test = \" item.usrId != null and item.usrId != '' \"> " + " tur_obj_id = #{item.usrId} " + " </if> "
            + " <if test = \" item.kiId != null and item.kiId != '' \"> " + " tur_obj_id = #{item.kiId} " + " </if> "
            + " <if test = \" item.tlBizId != null and item.tlBizId != '' \"> "
            + " AND tl_id = ( SELECT tl_id FROM edu_tag_library WHERE tl_biz_id = #{item.tlBizId} AND tl_source_type = 0 AND del_status = 0 AND enable_status = 1 ) "
            + " </if> " + " </foreach> " + "</script>")
    int delTurByBizId(List<DelTurParams> list);