常用Mybatis-SQL语句写法

Mapper.java

查询

批量查询

    List<PreferProbeItem> queryProbe(String userId);

    List<RiskVO> queryRiskVO(String riskId);

    List<RiskVO> queryCloseDProject();

    List<PreferProbeItem> queryProbeList(@Param("condition") Condition condition, @Param("topic") String topic);

    List<PreferProbeItem> queryUserProbes(@Param("userId") String userId, @Param("topicValue") String topicValue,
        @Param("probeType") String probeType);

    List<ProbeInfo> getProbeInfo(ApiProbeParam apiParam);

    List<Trending> queryTrending(@Param("probeId") String probeId, @Param("startTime") Date startTime,
        @Param("endTime") Date endTime, @Param("regionOrg") String regionOrg, @Param("repOffice") String repOffice);

    List<WarningDetails> queryTaskLink(@Param("list") List<WarningDetailsParam> params);

    List<RiskVO> queryRiskVOs(@Param("list") List<String> commonNo);

    List<String> queryFilterRiskIds(List<String> commonNoList);

    List<WarningDetailsParam> queryPlinkTask(@Param("repofficeCode") String repofficeCode);

其他查询

    Integer queryRiskIdS(String commonNo);

    int queryCloseTaskCount(@Param("dateMonth") String dateMonth);

    String queryThreshold(@Param("threshold") String threshold);

    ProbeCriterion queryProbeCriterion(@Param("regionOrg") String regionOrg, @Param("repOffice") String repOffice,
        @Param("probeId") String probeId);

    String queryTopic(@Param("probeId") String probeId);

增加

批量插入

    void insertStateDeploy(List<StateDeploy> stateDeploys);

    void insertRiskIds(List<String> RiskIds);

    void insertPerformanceDeploy(List<KpiDeploy> kpiDeploys);

其他插入

    void insertRiskVO(RiskVO riskVO);

    void insert(@Param("warningDetails") WarningDetails warningDetails);

    void insertPlinkTask(@Param("warningDetailsParam") WarningDetailsParam warningDetailsParam);

修改

批量更新

    void updateStateDeploys(List<StateDeploy> stateDeploys);

其他更新

    void updateCloseTime(String commonNo);

    void updateRiskVO(RiskVO riskVO);

删除

批量删除

void patchDelete(List<Long> ids);

其他删除

    void deletePerformanceDeploy(KpiDeploy KpiDeploy);

Mapper.xml

查询

批量查询

    <select id="queryProbe" resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
        select rel.probe_id    probeId
             , all_probe.topic topic
             , all_probe.stage stage
             , all_probe.name  apiName
             , rel.rank        rank
             , all_probe.type  probeType
        from dm_scm_rel_ioc_user_probe rel
                 inner join
             dm_scm_dim_ioc_all_probe all_probe on rel.probe_id = all_probe.probe_id
        where rel.user_id = #{userId}
        and rank != o
        order by stage, rank asc
    </select>

    <select id="queryRiskVO" parameterType="String" resultType="com.-.it.regioc.bean.result.RiskVO">
        select risk_id       riskId,
               plink_task_id plinkTaskId,
               close_time    closeTime
        from dm_scm_rel_ioc_risk_task
        where close_time >= #{riskId}
    </select>

    <select id="queryCloseDProject" resultType="com.-.it.regioc.bean.result.RiskVO">
        select risk_id as riskId, plink_task_id as plinkTaskId, close_time as closeTime
        from dm_scm_rel_ioc_risk_task
        where close_time is not null
    </select>

    <select id="queryProbeList" parameterType="com.-.it.regioc.bean.Condition"
            resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
        SELECT rel.probe_id    probeId,
               all_probe.topic topic,
               all_probe.stage stage,
               all_probe.NAME  probeName,
               rel.RANK        rank
        FROM dm_scm_rel_ioc_user_probe rel,
             dm_scm_dim_ioc_all_probe all_probe
        WHERE all_probe.probe_id = rel.probe_id
          and rel.user_id = #{condition.userId}
          AND all_probe.topic = #{topic}
        UNION ALL
        SELECT all_probe.probe_id probeId,
               all_probe.topic    topic,
               all_probe.stage    stage,
               all_probe.NAME     probeName,
               0                  rank
        FROM dm_scm_dim_ioc_all_probe all_probe
        WHERE not exists (SELECT probe_id
                               FROM dm_scm_rel_ioc_user_probe rel
                               WHERE rel.user_id = #{condition.userId}
                                 AND all_probe.topic = #{topic})
        ORDER BY stage, RANK ASC
    </select>

    <select id="queryUserProbes" resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
        SELECT rel.user_id     userId,
               rel.probe_id    probeId,
               all_probe.topic topic,
               all_probe.stage stage,
               all_probe.NAME  probeName,
               rel.RANK        RANK
        FROM dm_scm_rel_ioc_user_probe rel
                 JOIN dm_scm_dim_ioc_all_probe all_probe ON rel.probe_id = all_probe.probe_id
        WHERE rel.user_id = #{userId}
          AND all_probe.topic = #{topicValue}
          AND all_probe.type = #{probeType}
    </select>

    <select id="getProbeInfo" parameterType="com.-.it.regioc.bean.apiparam.ApiProbeParam"
            resultType="com.-.it.regioc.bean.result.ProbeInfo">
        select base_val baseVal, target_val targetVal, threshold_val thresholdVal,probe_id probeId
        from dm_scm_rel_ioc_country_probe
        where 1=1
        <if test="regionCnName != null and regionCnName != ''">
            and region_org_id = #{regionCnName}
        </if>
        <if test="repoffceCnName != null and repoffceCnName != ''">
            and office_id = #{repoffceCnName}
        </if>
        <if test="probeId != null and probeId != ''">
            and probe_id = #{probeId}
        </if>
    </select>

    <select id="queryTrending" resultType="com.-.it.regioc.bean.model.Trending">
        (
        select base_val baseVal,update_time updateTime
        from dm_scm_rel_ioc_country_probe dsricp
        where update_time > #{startTime} and #{endTime} > update_time
        and probe_id = #{probeId}
        <if test="regionOrg == null or regionOrg ==''">
            and region_org_id = 'ALL'
        </if>
        <if test="regionOrg !=null and regionOrg !=''">
            and region_org_id = #{regionOrg}

            <if test="repOffice == null or repOffice == ''">
                and office_id = 'ALL'
            </if>
            <if test="repOffice !=null and repOffice != ''">
                and office_id = #{repOffice}
            </if>
        </if>
        order by update_time desc
        )
        union all
        (
        select base_val baseVal,update_time updateTime
        from dm_scm_rel_ioc_country_probe dsricp
        where #{startTime} > update_time
        and probe_id = #{probeId}
        <if test="regionOrg == null or regionOrg ==''">
            and region_org_id = 'ALL'
        </if>
        <if test="regionOrg !=null and regionOrg !=''">
            and region_org_id = #{regionOrg}

            <if test="repOffice == null or repOffice == ''">
                and office_id = 'ALL'
            </if>
            <if test="repOffice !=null and repOffice != ''">
                and office_id = #{repOffice}
            </if>
        </if>
        order by update_time desc limit 1
        )
    </select>

    <select id="queryTaskLink" parameterType="java.util.List"
            resultType="com.-.it.regioc.bean.result.WarningDetails">
        select probe_id probeId,project_id projectId,plink_task_id pLinkTaskId from dm_scm_rel_ioc_task where 1 = 1
        and probe_id in
        <foreach collection="list" item="param" open="(" separator="," close=")">
            #{param.probeId}
        </foreach>
        and project_id in
        <foreach collection="list" item="param" open="(" separator="," close=")">
            #{param.projectId}
        </foreach>
    </select>

    <select id="queryRiskVOs" parameterType="java.util.List" resultType="com.-.it.regioc.bean.result.RiskVO">
        select risk_id riskId,
        plink_task_id plinkTaskId,
        close_time closeTime
        from dm_scm_rel_ioc_risk_task
        where
        risk_id in
        <foreach collection="list" item="commonNo" separator="," open="(" close=")">
            #{commonNo}
        </foreach>
    </select>

    <select id="queryFilterRiskIds" parameterType="java.util.List" resultType="String">
        select risk_id
        from dm_scm_rel_ioc_risk_task
        where
        risk_id in
        <foreach collection="list" item="commonNo" separator="," open="(" close=")">
            #{commonNo}
        </foreach>
        AND close_time is not null
        and NOW()- close_time <![CDATA[ < ]]> interval '7 day'
    </select>

    <select id="queryPlinkTask" resultType="com.-.it.regioc.bean.model.WarningDetailsParam">
        select a.repoffice_code   as repofficeCode,
               a.pLink_project_id as pLinkProjectId,
               a.repoffice_name   as repofficeName,
               a.plink_group_id   as pLinkGroupId,
               a.topic
        from dm_scm_rel_ioc_plink_task a
        where a.repoffice_code = #{repofficeCode}
    </select>

其他查询

    <select id="queryRiskIdS" parameterType="String" resultType="Integer">
        select count(1)
        from dm_scm_rel_ioc_risk_task
        where risk_id = #{commonNo}
    </select>

    <select id="queryCloseTaskCount" resultType="java.lang.Integer">
        select count(1) as total
        from dm_scm_rel_ioc_risk_task
        where close_time like concat('', #{dateMonth}, '%')
    </select>

    <select id="queryThreshold" resultType="string">
        select param_value
        from dm_scm_common_config
        where param_type = #{threshold}
    </select>

    <select id="queryProbeCriterion" resultType="com.-.it.regioc.bean.config.porbecriterion.ProbeCriterion">
        select probe_id probeId,
        threshold_val thresholdVal,
        base_val baseVal,
        target_val targetVal
        from dm_scm_rel_ioc_country_probe rel
        where probe_id = #{probeId}
        <if test="regionOrg != null and regionOrg != ''">
            and region_org_id = #{regionOrg}
        </if>
        <if test="regionOrg == null and regionOrg == ''">
            and region_org_id = 'total'
        </if>

        <if test="repOffice != null and repOffice != ''">
            and office_id = #{repOffice}
        </if>
        <if test="repOffice == null or repOffice == ''">
            and office_id = 'total'
        </if>
        order by update_time desc limit 1
    </select>

    <select id="queryTopic" resultType="string">
        select topic
        from dm_scm_dim_ioc_all_probe a
        where a.probe_id = #{probeId}
    </select>

增加

批量插入

    <insert id="insertStateDeploy">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            insert into DM_SCM_REL_IOC_USER_PROBE(USER_ID ,PROBE_ID ,RANK)
            values(#{item.userId}, #{item.probeId},#{item.rank})
        </foreach>
    </insert>

    <insert id="insertRiskIds">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            insert into dm_scm_rel_ioc_risk_task(risk_id)
            values(#{item})
        </foreach>
    </insert>

    <insert id="insertPerformanceDeploy">
        insert into DM_SCM_REL_IOC_USER_KPI(USER_ID ,KPI_ID ,RANK) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.userId}, #{item.kpiId},(#{index}+1))
        </foreach>
    </insert>

其他插入

    <insert id="insertRiskVO">
        insert into dm_scm_rel_ioc_risk_task(risk_id, plink_task_id,close_time)
        values (#{riskId}, #{plinkTaskId},now())
    </insert>

    <insert id="insert" parameterType="com.-.it.regioc.bean.result.WarningDetails">
        insert into dm_scm_rel_ioc_task
        (probe_id,
         project_id,
         plink_task_id)
        values (#{warningDetails.probeId,jdbcType=VARCHAR},
                #{warningDetails.projectId,jdbcType=VARCHAR},
                #{warningDetails.pLinkTaskId,jdbcType=VARCHAR})
    </insert>

    <insert id="insertPlinkTask" parameterType="com.-.it.regioc.bean.model.WarningDetailsParam">
        insert into dm_scm_rel_ioc_plink_task
        (repoffice_code,
         repoffice_name,
         plink_project_id,
         plink_group_id,
         topic,
         update_time)
        values (#{warningDetailsParam.repofficeCode,jdbcType=VARCHAR},
                #{warningDetailsParam.repofficeName,jdbcType=VARCHAR},
                #{warningDetailsParam.plinkProjectId,jdbcType=VARCHAR},
                #{warningDetailsParam.plinkGroupId,jdbcType=VARCHAR},
                #{warningDetailsParam.topic,jdbcType=VARCHAR},
                #{warningDetailsParam.updateTime,jdbcType=TIMESTAMP})
    </insert>

修改

批量更新

    <update id="updateStateDeploys" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update dm_scm_rel_ioc_user_probe
            set rank = #{item.rank}
            where
            user_id = #{item.userId}
            and
            probe_id = #{item.probeId}
        </foreach>
    </update>

其他更新

    <update id="updateCloseTime" parameterType="string">
        UPDATE dm_scm_rel_ioc_risk_task
        SET close_time = NOW()
        WHERE risk_id = #{commonNo}
    </update>

    <update id="updateRiskVO" parameterType="com.-.it.regioc.bean.result.RiskVO">
        UPDATE dm_scm_rel_ioc_risk_task
        SET plink_task_id = #{plinkTaskId}
        WHERE risk_id = #{riskId}
    </update>

删除

批量删除

    <!--void patchDelete(List<Long> ids);
       delete from t_product where id in (1,2,3)
    -->
    <delete id="patchDelete" >
        delete from t_product where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
          #{id}
        </foreach>
    </delete>

其他删除

    <delete id="deletePerformanceDeploy" parameterType="com.-.it.regioc.bean.vueparam.KpiDeploy">
        delete
        from DM_SCM_REL_IOC_USER_KPI
        where USER_ID = #{userId}
    </delete>