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

qq123 2021年02月13日编程语言 438 0

转义字符

大于号	>	>
小于号	<	<
与	&	&
单引号	"	'
双引号	'	"

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 + '\'' + 
                '}'; 
    } 
}

IT源码网 MyBatis

微信公众号号：IT虾米（左侧二维码扫一扫）欢迎添加！

Q : 823053937

热贴推荐

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

转义字符

存储过程的用法

Q : 823053937

热贴推荐

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

转义字符

存储过程的用法

java之Mybatis中的foreach是硬解析还是软解析

database之Mybatis 自定义函数

database之在 MyBatis 中，如何为 元素设置范围以供重用

database之如何使用 MyBatis Spring Integration 连接多个数据库

java之需要帮助使 MyBatis 与糟糕的数据库设计一起工作

java之如何将 myBatis(iBatis) 结果作为 Iterable 获取。 (在非常大的行的情况下)

java之MyBatis SqlSession接口(interface)的insert()、delete()、select()、update()返回什么

java之@Transactional Spring MyBatis 不工作

Java的Mybatis中，以最简单的方式实现按年月日时分秒查询。

Mybatis中文文档

认识、搭建MyBatis

Spring Boot （七）： Mybatis极简配置

database之在 MyBatis 中，如何为元素设置范围以供重用