mybatis 转义字符和存储过程的用法

转义字符

大于号 > >
小于号 < &lt; 
与 & &amp;
单引号 " &apos;
双引号 ' &quot;

 

sql

 

 <select id="getLivingVideoProjInfo2New" resultType="com.pdzx.dto.LivingVideoInfo">
    SELECT
    t1.id AS projId,
    t2.id AS sectionId ,
    t1.land_agent AS companyId,
    t3.name AS companyName,
    t1.assess_date AS assessDate,
    t2.video_cover AS videoCover,
    CONCAT(t1.proj_name,'-',t2.section_name) AS  projName,
    MIN(t4.video_addr) AS videoAddr
    FROM b_proj_base_info AS t1
    JOIN b_proj_section_info AS t2 ON t1.id=t2.proj_id
    JOIN u_base_company_info AS t3 ON t1.land_agent=t3.id
    JOIN b_video_info AS t4 ON t2.id=t4.proj_section_id
    JOIN  b_proj_device AS  t5 ON  t5.is_live=1 AND  t4.device_id=t5.device_id
    WHERE 1=1
    <if test="companyId!=null and companyId!='' ">
      AND t1.land_agent=#{companyId}
    </if>
    <if test="projName!=null and projName!='' ">
      AND  (t1.proj_name LIKE CONCAT('%',#{projName},'%') OR t2.section_name LIKE CONCAT('%',#{projName},'%') )
    </if>
    AND t4.upload_time &gt;= #{startDate}
    AND t4.upload_time &lt;= #{endDate}
    AND  #{endDate}+0  &gt;= t4.upload_time+0+7*60
    GROUP BY t1.id, t2.id
    ORDER BY t1.assess_date DESC
  </select>

 

 存储过程的用法

存储过程pr_soft_usage_single

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_soft_usage_single`(
p_SectionID INT -- 标段ID
)
BEGIN

/* *********************************** *
存储过程版：
软件使用情况查询-单标段版本
modify:
20191129 添加日期判定，20191001前后执行不同的判定规则
20191204 添加对通用模版公司进行特殊处理完全使用判定：
                通用模版公司只判定以下这些规则：a、项目信息的完整采集
                                                b、上传简报
                                                c、录入并上传测区
                                                d、上传标准动作
20200421 2020Q2季度开始执行新规则，添加重点关注项(即项目总结 b_proj_sum_up)填写
20200422 关联pdsc工作量填写的软件使用情况，只有工作量软件使用情况和软件自动判定的都为完全使用才为完全使用，否则NVL(工作量软件使用情况, 软件自动判定使用情况)
20200604 之前工作量关联项目的时候一个标段只能使用一次，现在改成了一个软件标段可以被多个工作量关联，需要取最大值
20200616 软件使用情况查询新需求改动：工作量软件使用情况如果有值，则直接取工作量软件使用情况（下发审核时批量更新，且可以后台修改），反之则取实际计算出来的值
* ************************************ */

DECLARE vn_cnt INT;
DECLARE vn_min INT;
DECLARE vd_assess_date VARCHAR(10);
DECLARE wl_soft_usage VARCHAR(150) DEFAULT '';

SET @p_date_1 = '2019-10-01';
SET @p_date_2 = '2020-04-01';

SELECT COUNT(1), MAX(t1.assess_date)
INTO vn_cnt, vd_assess_date
FROM b_proj_base_info t1
JOIN b_proj_section_info t2 ON t1.id = t2.proj_id
WHERE t2.id = p_SectionID;

IF vn_cnt > 0 THEN -- 1

IF vd_assess_date >= @p_date_2 THEN -- 2

-- 工作量软件使用情况优先判定 20200616
DROP TEMPORARY TABLE IF EXISTS tmp_wl_usage;
CREATE TEMPORARY TABLE tmp_wl_usage AS
SELECT CASE WHEN t.soft_usage = '完全使用' THEN 2 WHEN t.soft_usage = '通用模版完全使用' THEN 1 ELSE 0 END is_full_use, t.soft_usage
  FROM pdsc.m_workload_list t
 WHERE t.pdzx_section_id = p_SectionID
   AND IFNULL(t.soft_usage, '') <> ''
;

SELECT MIN(is_full_use) INTO vn_min FROM tmp_wl_usage;

IF vn_min = 2 THEN

    SET wl_soft_usage = '完全使用';

ELSEIF vn_min = 1 THEN

    SET wl_soft_usage = '通用模版完全使用';

ELSEIF vn_min = 0 THEN

    SELECT MAX(t.soft_usage)
      INTO wl_soft_usage
      FROM pdsc.m_workload_list t
     WHERE t.pdzx_section_id = p_SectionID
       AND IFNULL(t.soft_usage, '') <> ''
       AND t.soft_usage NOT IN ('完全使用', '通用模版完全使用');

END IF;

DROP TEMPORARY TABLE IF EXISTS tmp_wl_usage;

END IF; -- 2

IF IFNULL(wl_soft_usage, '') <> '' THEN -- 3

SELECT CASE WHEN wl_soft_usage IN ('完全使用', '通用模版完全使用') THEN 1 ELSE 0 END is_full_use, wl_soft_usage soft_usage FROM    DUAL;

ELSE -- 3

-- 该部分为20191001前后共有的规则
DROP TEMPORARY TABLE IF EXISTS tmp_mrq_info;
CREATE TEMPORARY TABLE tmp_mrq_info AS
SELECT p_SectionID proj_section_id, COUNT(1) mrq_num
FROM b_mrq_info t
WHERE t.proj_section_id = p_SectionID;


-- 执行20191001之后的规则
IF vd_assess_date >= @p_date_1 THEN -- 4

DROP TEMPORARY TABLE IF EXISTS tmp_img_info;
CREATE TEMPORARY TABLE tmp_img_info AS
SELECT p_SectionID proj_section_id,
       COUNT(1) img_num,
       CAST(IFNULL(SUM(CASE WHEN t.category_id >= '1' THEN 1 ELSE 0 END), 0) AS SIGNED) img_class,
       CAST(IFNULL(SUM(CASE WHEN IFNULL(t.problem_desc, '') <> '' THEN 1 ELSE 0 END), 0) AS SIGNED) img_prob
FROM b_img_addr t
WHERE t.proj_section_id = p_SectionID
AND t.is_valid = 1
AND t.st IN ('01', '02')
;


DROP TEMPORARY TABLE IF EXISTS tmp_sampling_partition;
CREATE TEMPORARY TABLE tmp_sampling_partition AS
SELECT p_SectionID proj_section_id, COUNT(1) sampl_part_num
FROM b_sampling_partition t
WHERE t.proj_section_id = p_SectionID;


DROP TEMPORARY TABLE IF EXISTS tmp_proj_perfect;
CREATE TEMPORARY TABLE tmp_proj_perfect AS
SELECT p_SectionID proj_section_id, COUNT(1) perfect_num
FROM b_proj_base_info t1
JOIN b_proj_section_info t2 ON t1.id = t2.proj_id
JOIN b_proj_unit_user t3 ON t1.id = t3.proj_id AND t2.id = t3.proj_section_id
JOIN u_unit_user t4 ON t3.uu_user_id = t4.id
WHERE t2.id = p_SectionID
AND t1.proj_addr <> '' -- 项目地址
AND t1.city_code <> '' -- 城市
AND IFNULL(t2.build_area, 0) + IFNULL(t2.underground_area, 0) > 0 -- 地上面积+地下面积之和大于0
AND IFNULL(t2.format_type, '') <> '' -- 层数类型
AND IFNULL(t2.business_type, '') <> '' -- 业态类型
AND IFNULL(t2.construction_stage, '') <> '' -- 施工阶段
AND IFNULL(t4.unit_property, '') IN ('01', '02', '03', '20') -- 建设单位、总包单位、监理单位、物业单位
;

DROP TEMPORARY TABLE IF EXISTS tmp_standard_action;
CREATE TEMPORARY TABLE tmp_standard_action AS
SELECT p_SectionID proj_section_id, COUNT(1) stac_num
FROM b_standard_action t
WHERE t.proj_section_id = p_SectionID
;

-- 执行20191001至20200331的规则
IF vd_assess_date < @p_date_2 THEN -- 5

SELECT CASE WHEN c.soft_usage = '完全使用' THEN 1 ELSE 0 END is_full_use,
-- c.soft_usage
CASE WHEN c.land_agent = 1 AND c.soft_usage = '完全使用' THEN '通用模版完全使用' ELSE c.soft_usage END soft_usage
FROM (
SELECT b.proj_id, b.proj_section_id, b.land_agent,
NVL(CONCAT_WS(';', b.is_mrq, b.is_img, b.is_excel, b.is_word, b.is_perfect, b.is_stac, b.is_samp_part, b.is_img_class, is_img_prob), '完全使用') soft_usage
FROM (
SELECT a.proj_id, a.proj_section_id, a.land_agent,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_use_mrq = 0 AND INSTR(a.flag, ',01') > 0 AND a.mrq_num < 11 THEN '实测未达标' ELSE NULL END ELSE NULL END is_mrq,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN REPLACE(a.flag, ',01', '') LIKE '%,%' AND a.img_num < 11 THEN '拍照未达标' ELSE NULL END ELSE NULL END is_img,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_excel = 0 THEN '打分表未上传' ELSE NULL END ELSE NULL END is_excel,
CASE WHEN a.is_word = 0 THEN '简报未上传' ELSE NULL END is_word,
CASE WHEN a.perfect_num = 0 THEN '项目信息未完善' ELSE NULL END is_perfect,
CASE WHEN a.stac_num < 5 THEN '标准动作未达标' ELSE NULL END is_stac,
CASE WHEN a.sampl_part_num = 0 THEN '测区未上传' ELSE NULL END is_samp_part,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.img_class < 10 THEN '照片分类未达标' ELSE NULL END ELSE NULL END is_img_class,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.img_prob < 10 THEN '照片描述未达标' ELSE NULL END ELSE NULL END is_img_prob
FROM (
SELECT t1.id proj_id, t2.id proj_section_id, CAST(t1.land_agent AS SIGNED) land_agent,
t4.mrq_num, t5.img_num, t5.img_class, t5.img_prob, t6.sampl_part_num, t7.perfect_num, t8.stac_num,
CASE WHEN IFNULL(t2.excel_addr, '') <> '' THEN 1 ELSE 0 END is_excel,
CASE WHEN IFNULL(t2.word_addr, '') <> '' THEN 1 ELSE 0 END is_word,
REPLACE(CONCAT(',', REPLACE(REPLACE(t3.flag, ',null', ''), '00,', '')), ',,', ',') flag,
t2.is_use_mrq
FROM b_proj_base_info t1
JOIN b_proj_section_info t2 ON t1.id = t2.proj_id
JOIN t_excel_file_info t3
  ON t1.template_id = t3.template_id
 AND t1.assess_type = t3.assess_type
 AND t1.building_type = t3.building_type
 AND IFNULL(t1.special_type, '') = IFNULL(t3.special_type, '')
LEFT JOIN tmp_mrq_info t4 ON t2.id = t4.proj_section_id
LEFT JOIN tmp_img_info t5 ON t2.id = t5.proj_section_id
LEFT JOIN tmp_sampling_partition t6 ON t2.id = t6.proj_section_id
LEFT JOIN tmp_proj_perfect t7 ON t2.id = t7.proj_section_id
LEFT JOIN tmp_standard_action t8 ON t2.id = t8.proj_section_id
WHERE t2.id = p_SectionID
) a
) b
) c
;

-- 执行20200401之后的规则
ELSEIF vd_assess_date >= @p_date_2 THEN -- 5

-- 重点关注项(即项目总结)填写是否达标
-- 暂定至少填写一条非空的flag总结
DROP TEMPORARY TABLE IF EXISTS tmp_proj_sumup;
CREATE TEMPORARY TABLE tmp_proj_sumup AS
SELECT p_SectionID proj_section_id, COUNT(1) sumup_num
  FROM b_proj_sum_up t
 WHERE t.proj_section_id = p_SectionID
   AND IFNULL(t.sum_contents, '') <> ''
;

SELECT
CASE WHEN c.soft_usage = '完全使用' THEN 1 ELSE 0 END is_full_use,
CASE WHEN c.land_agent = 1 AND c.soft_usage = '完全使用' THEN '通用模版完全使用' ELSE c.soft_usage END soft_usage
FROM (
SELECT b.proj_id, b.proj_section_id, b.land_agent,
NVL(CONCAT_WS(';', b.is_mrq, b.is_img, b.is_excel, b.is_word, b.is_perfect, b.is_stac, b.is_samp_part, b.is_img_class, b.is_img_prob, b.is_sumup), '完全使用') soft_usage
FROM (
SELECT a.proj_id, a.proj_section_id, a.land_agent,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_use_mrq = 0 AND INSTR(a.flag, ',01') > 0 AND a.mrq_num < 11 THEN '实测未达标' ELSE NULL END ELSE NULL END is_mrq,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN REPLACE(a.flag, ',01', '') LIKE '%,%' AND a.img_num < 11 THEN '拍照未达标' ELSE NULL END ELSE NULL END is_img,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_excel = 0 THEN '打分表未上传' ELSE NULL END ELSE NULL END is_excel,
CASE WHEN a.is_word = 0 THEN '简报未上传' ELSE NULL END is_word,
CASE WHEN a.perfect_num = 0 THEN '项目信息未完善' ELSE NULL END is_perfect,
CASE WHEN a.stac_num < 5 THEN '标准动作未达标' ELSE NULL END is_stac,
CASE WHEN a.sampl_part_num = 0 THEN '测区未上传' ELSE NULL END is_samp_part,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.img_class < 10 THEN '照片分类未达标' ELSE NULL END ELSE NULL END is_img_class,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.img_prob < 10 THEN '照片描述未达标' ELSE NULL END ELSE NULL END is_img_prob,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.sumup_num = 0 THEN '项目总结填写未达标' ELSE NULL END ELSE NULL END is_sumup
FROM (
SELECT t1.id proj_id, t2.id proj_section_id, CAST(t1.land_agent AS SIGNED) land_agent,
t4.mrq_num, t5.img_num, t5.img_class, t5.img_prob, t6.sampl_part_num, t7.perfect_num, t8.stac_num,
CASE WHEN IFNULL(t2.excel_addr, '') <> '' THEN 1 ELSE 0 END is_excel,
CASE WHEN IFNULL(t2.word_addr, '') <> '' THEN 1 ELSE 0 END is_word,
REPLACE(CONCAT(',', REPLACE(REPLACE(t3.flag, ',null', ''), '00,', '')), ',,', ',') flag,
t2.is_use_mrq,
t9.sumup_num
FROM b_proj_base_info t1
JOIN b_proj_section_info t2 ON t1.id = t2.proj_id
JOIN t_excel_file_info t3
  ON t1.template_id = t3.template_id
 AND t1.assess_type = t3.assess_type
 AND t1.building_type = t3.building_type
 AND IFNULL(t1.special_type, '') = IFNULL(t3.special_type, '')
LEFT JOIN tmp_mrq_info t4 ON t2.id = t4.proj_section_id
LEFT JOIN tmp_img_info t5 ON t2.id = t5.proj_section_id
LEFT JOIN tmp_sampling_partition t6 ON t2.id = t6.proj_section_id
LEFT JOIN tmp_proj_perfect t7 ON t2.id = t7.proj_section_id
LEFT JOIN tmp_standard_action t8 ON t2.id = t8.proj_section_id
LEFT JOIN tmp_proj_sumup t9 ON t2.id = t9.proj_section_id
WHERE t2.id = p_SectionID
) a
) b
) c
;

DROP TEMPORARY TABLE IF EXISTS tmp_proj_sumup;

END IF; -- 5

DROP TEMPORARY TABLE IF EXISTS tmp_sampling_partition;
DROP TEMPORARY TABLE IF EXISTS tmp_proj_perfect;
DROP TEMPORARY TABLE IF EXISTS tmp_standard_action;


-- 执行20191001之前的规则
ELSEIF vd_assess_date < @p_date_1 THEN -- 4

DROP TEMPORARY TABLE IF EXISTS tmp_img_info;
CREATE TEMPORARY TABLE tmp_img_info AS
SELECT p_SectionID proj_section_id,
       COUNT(1) img_num
FROM b_img_addr t
WHERE t.proj_section_id = p_SectionID
AND t.is_valid = 1
AND t.st IN ('01', '02')
;


SELECT CASE WHEN c.soft_usage = '完全使用' THEN 1 ELSE 0 END is_full_use,
-- c.soft_usage
CASE WHEN c.land_agent = 1 AND c.soft_usage = '完全使用' THEN '通用模版完全使用' ELSE c.soft_usage END soft_usage
FROM (
SELECT b.proj_id, b.proj_section_id, b.land_agent,
NVL(CONCAT_WS(';', b.is_mrq, b.is_img, b.is_excel), '完全使用') soft_usage
FROM (
SELECT a.proj_id, a.proj_section_id, a.land_agent,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_use_mrq = 0 AND INSTR(a.flag, ',01') > 0 AND a.mrq_num < 11 THEN '实测未达标' ELSE NULL END ELSE NULL END is_mrq,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN REPLACE(a.flag, ',01', '') LIKE '%,%' AND a.img_num < 11 THEN '拍照未达标' ELSE NULL END ELSE NULL END is_img,
CASE WHEN a.land_agent <> 1 THEN CASE WHEN a.is_excel = 0 THEN '打分表未上传' ELSE NULL END ELSE NULL END is_excel
FROM (
SELECT t1.id proj_id, t2.id proj_section_id, CAST(t1.land_agent AS SIGNED) land_agent,
t4.mrq_num, t5.img_num,
CASE WHEN IFNULL(t2.excel_addr, '') <> '' THEN 1 ELSE 0 END is_excel,
REPLACE(CONCAT(',', REPLACE(REPLACE(t3.flag, ',null', ''), '00,', '')), ',,', ',') flag,
t2.is_use_mrq
FROM b_proj_base_info t1
JOIN b_proj_section_info t2 ON t1.id = t2.proj_id
JOIN t_excel_file_info t3
  ON t1.template_id = t3.template_id
 AND t1.assess_type = t3.assess_type
 AND t1.building_type = t3.building_type
 AND IFNULL(t1.special_type, '') = IFNULL(t3.special_type, '')
LEFT JOIN tmp_mrq_info t4 ON t2.id = t4.proj_section_id
LEFT JOIN tmp_img_info t5 ON t2.id = t5.proj_section_id
WHERE t2.id = p_SectionID
) a
) b
) c
;

END IF; -- 4

DROP TEMPORARY TABLE IF EXISTS tmp_mrq_info;
DROP TEMPORARY TABLE IF EXISTS tmp_img_info;

END IF; -- 3


ELSE -- 2

SELECT NULL is_full_use, NULL soft_usage FROM DUAL;

END IF; -- 2

END

调用方式

 

 mybatis写法

<select id="getUseSoftDetail" resultType="com.pdzx.dto.UseSoftDetail" statementType="CALLABLE">
    <![CDATA[
          CALL pdzx.pr_soft_usage_single(#{sectionId})
        ]]>
  </select>

<![CDATA[   ]]> 是什么

这是XML语法。在CDATA内部的所有内容都会被解析器忽略。

如果文本包含了很多的"<"字符 <=和"&"字符——就象程序代码一样，那么最好把他们都放到CDATA部件中。

但是有个问题那就是 <if test="">   </if>   <where>   </where> 等这些标签都不会被解析，所以我们只把有特殊字符的语句放在 <![CDATA[   ]]>  尽量缩小 <![CDATA[  ]]> 的范围

 

dao层

UseSoftDetail getUseSoftDetail(@Param("sectionId") String sectionId);

实体类

package com.pdzx.dto;

/**
 * Created by admin on 2019/11/29.
 */
public class UseSoftDetail {

    private Integer is_full_use;

    private String soft_usage;

    public Integer getIs_full_use() {
        return is_full_use;
    }

    public void setIs_full_use(Integer is_full_use) {
        this.is_full_use = is_full_use;
    }

    public String getSoft_usage() {
        return soft_usage;
    }

    public void setSoft_usage(String soft_usage) {
        this.soft_usage = soft_usage;
    }

    @Override
    public String toString() {
        return "UseSoftDetail{" +
                "is_full_use=" + is_full_use +
                ", soft_usage='" + soft_usage + '\'' +
                '}';
    }
}

 

posted on 2020-07-13 21:08  路仁甲  阅读(413)  评论(0)  编辑  收藏  举报
Powered by:
博客园
Copyright © 2024 路仁甲
Powered by .NET 8.0 on Kubernetes