在mapper.xml中编写sql规则和常见函数写法

在 mapper.xml 中编写规则和常见函数写法

目录

• 在 mapper.xml 中编写规则和常见函数写法
  • service 传到 mapper.xml
  • 常见查询语句的写法
    • group_concat
    • case when else end
    • COALESCE
    • DUAL
  • 模糊查询写法
  • 关于 where 1 = 1
  • xml 中不能存在的特殊字符 —— 特殊转义或 <![CDATA[]]>
  • sql 编写的一些顺序回顾

service 传到 mapper.xml

• 情况一：传来的是一些基本的数据类型

  • 一开始有 #{param1}、#{arg0} 的那种按顺序填入的就不说了，不推荐使用

  • 在接口方法的参数上使用 @param(value) 注解，就可改变封装的map对象的key值，就可以实现接口方法里的参数名和xml文件内接收参数名对应一致

    • 原来在service传参到mapper时，只要参数大于等于两个，自定义参数就需要用到 @Param，但是在后来在什么情况下可以不用加 @param 注解，便能自定义传参名来着

  • 在 MyBatis 底层会把参数封装成一个 Map 对象，这里我们就可以传入一个 map 对象，例如：

    Map<String, Object> map = new HashMap<>();
    map.put("id", 3);
    map.put("state", 1);
    List<User> userlist = userMapper.selectUser(map);
    

• 情况二：传来的是集合、数组

  • 不管是数组还是集合 #{collection[0]} 这种写法也是可以正常识别的
  • 当然最常见的还是写 in 语句的之后所用

• ${} 与 #{}

  • 在 sql 中 #{} 相当于一个参数占位符 " ? " 了，用来补全预编译语句
    • 就是最开始学习时 初始化 用 PreparedStatementd 在 service 层上现拼 sql 语句时用的占位符 " ? "
    • 可以理解为占位过的部分在预编译时会被引起来
    • 还可以设置 javaType，jdbcType，mode 等
    • jdbcType 是用于指定传递给 SQL 语句的参数类型，必要时做转换或格式设置
    • javaType 同理，就是改成了指定传过来的参数所属的 java 类型（就像是 resultType 一样）
  • ${} 本质就是字符串拼接，无法防止被注入，但如果是其内容为 sql 语句的一部分的话，就只能用这个了，如表名称，order by 排序字段，分组 group by 等
  • 平常情况下，能用 #{} 的话就不要用 ${}，占位符更安全

常见查询语句的写法

SELECT
-- 基本的查询获取name值
	name,
	a.name,

-- 不管是否能查到、是否有值，都把salary这一列置为空字符串
'' salary,

-- 判断car是否为null空的，如果是就是空字符串(也可以写成别的样式)
IFNULL( car, '' ) car,

-- 判断work是否是yes，是则显示有工作，不是则显示没工作
IF ( work = 'yes', '有工作', '没工作' ) work,

-- 判断，如果是null或者空字符串就都显示null，否则就显示为拼接结果：[其值]
CASE WHEN fa.fkd IS NULL OR fa.fkd = '' THEN NULL
            ELSE concat('[', fa.fkd, ']') END fkd,

-- 多种写法混合：
CASE a.BJKZTJ
        WHEN '压力控制' THEN 'P'
        WHEN '流量控制' THEN 'Q'
        ELSE '' END bjlx,
        CASE WHEN JDSZSJ IS NULL THEN '[0]'
            ELSE CAST(JDSZSJ AS VARCHAR) END sj,
        CASE WHEN JDSZYL IS NULL THEN ''
            ELSE concat('[', JDSZYL, ']') END yl,

-- 拼接操作，给people加上中括号：[people]的显示样式，单纯的拼接
concat( '[', people, ']' ) people

-- 判断XZB是否是null，若是null就取WY的值，然后截取只留一位小数，将多个满足条件的值连接起来，并且是按照px列的值进行升序排序拼接的，结果列命名为XZB
GROUP_CONCAT( truncate ( IFNULL( XZB, WY ), 1 ) ORDER BY px ASC ) XZB,

-- COALESCE(value [, ...])获取的是括号内左边第一个不为空的值，无值就为0，全都无就是空字符串
ifnull( CONCAT('[{','"x":',COALESCE(dx.XZB, '0'),',','"y":',COALESCE(dx.ZZB, '0'),'},{','"x":',COALESCE(dx.XZB+ex.xZB, '0'),',','"y":',COALESCE(dx.yZB+ex.yZB, '0'),'}]'),'' ) lj,	

-- 如果想多重拼接、拼接 + 拼接，类似这种写法：
CONCAT( '[', GROUP_CONCAT( CONCAT( '"', IF ( HJJZ = '', NULL, HJJZ ), '"' ) ORDER BY CD ASC ), ']' ) HJJZ,

-- 多重判断 + 拼接：
IF(concat('[',GROUP_CONCAT(CASE WHEN te.GBMC IS NULL THEN '' ELSE concat('"', te.GBMC, '"') END),']') = '[]',NULL,
   concat('[',GROUP_CONCAT( CASE WHEN te.GBMC IS NULL THEN '' ELSE concat('"', te.GBMC, '"') END ),']')) AS GBMC

group_concat

• group_concat 拼接，默认都是用逗号隔开
• 但也可以更改分隔符，如：group_concat(字段 separator '--') 就改成了 ' -- '

case when else end

• 上述语句可见 case 有两种写法：

• 写法一：搜索 case 函数

  • 判断语句全写在 when 后，更灵活

    case when id=1 then salary*2
    			when id=2 then salary*3
    			else salary
    			end
    

• 写法二：简单 case 函数

  • when 后面只写被判断值

    case sex when '1' then '男'
    			when '2' then '女'
    			else '其他' 
    			end
    

    • 这样的写法简单，但可能会限制功能，例如一些判断式

  • 还有就是 case when 只会返回第一个符合条件的值，剩下的就不会在执行了，从而 “ 忽略掉 ”

• 除了作用在等值、范围情况下，还可以作用在列转行的操作里，例如：

  • 表：

    转为表：

  • sql 语句：

    select class_id,
    max(case when grade = 'primary' then rate else 0 end) as 'primary',
    max(case when grade = 'middle' then rate else 0 end) as 'middle',
    max(case when grade = 'high' then rate else 0 end) as 'high'
    from mst_class
    group by class_id;
    

  • 因为 case 执行一次就会结束，所以这里要分组加上 max，否则一个 id 就有三行值，每一行只有一列存数据其他都是 0

  • 可参考文章：https://blog.csdn.net/jiayi_yao/article/details/124529659

• 若是在 mysql 中根据 group by 的不同获取到两种查询结果样式，在 kingbase 可能就只有一条，想要两种的话可以考虑 select 和 group by 对应着都使用 <choose> <when></when> <otherwise></otherwise> </choose>

COALESCE

• 返回第一个非空参数的值，例如：

  SELECT product_id, list_price, min_price, COALESCE(0.9*list_price, min_price, 5) "Sale" FROM product_information WHERE supplier_id = 102050 ORDER BY product_id;
  

  • 它为所有标价的产品提供 10% 的折扣 0.9*list_price
  • 如果没有标价，则销售价格为最低价格 min_price
  • 如果没有最低价格，则销售价格为 “ 5 ”

DUAL

• DUAL 就是一张虚表，作用就是输出一条记录

• 对于 ORACLE

  • SELECT 必须有表名，所以不能省略 FROM xxx

• 对于 MySQL

  • 可以省略 FROM DUAL（不省略也可以），即：SELECT 1 也可以进行查询

模糊查询写法

• 有最开始用到的 like '%${name}%'
  • 这里不能用 #{}（单引号内不会被识别），只能选择上述拼接的方式
• 还有 like CONCAT('%', #{name}, '%')
  • 或不推荐的 like CONCAT('%', '${name}', '%')

关于 where 1 = 1

• 其实现在也还是会有好多这样写的情况，一是这样写的话在删改测试 sql 语句时会比较方便，因为不用在删除 where 后第一个条件时记着把下一个的 and 给删掉，这就像是在编写 sql 语句时把逗号写在下一个语句的开头一样的作用，看下述举例就很清楚了：

  select * 
  from xxx 
  where 1=1
  -- and x=1
  -- and y=2 
  	 and z=3
  	 
  select 
  	Column1
  -- ,Column2
  	,Column3
  

• 这样子在写、测 sql 时注释掉很方便

  • 但在 mapper.xml 中你也可以直接用 <where> 标签，正常第一个条件不加 and 的样式，因为就算第一个条件没符合条件，第二个位于新语句开头的 and 也会被识别到自行去除，就如：

    select count(*) from t_book t
    <where>
      <if test="title !=null and title !='' ">
        title = #{title} 
      </if>
      <if test="author !=null and author !='' "> 
        AND author = #{author}
      </if>
    </where> 
    

    • 若是没有 #{title} 值，也会识别到去除 author 前面的 AND

xml 中不能存在的特殊字符 —— 特殊转义或 <![CDATA[]]>

• 在 xml 中，不能存在如下特殊字符：

  • 大于等于 >=
  • <=
  • 不等于 <>
  • >
  • <
  • 且 &
  • 英文双引号 "
  • 英文单引号 '

• 在 xml 中编写 sql 语句时，小于号 < 往往不会被解析，此时就需要用特定的方法解决

• 一是使用特殊字符转义

  • 比如：

    >  >  大于号 
    &lt;  <  小于号
    

  • 使用时：

    <if test="endTime!= null and endTime!= ''">
      AND date_format(CREATETIME,'%Y-%m-%d %H:%i:%s') &lt;= #{endTime,jdbcType=VARCHAR}
    </if>
    

• 二是使用 <![CDATA[]]> 符合，其内容不会被解析

  • 例如：

    <if test="endTime != null and endTime !=''">
      <![CDATA[ AND date_format(CREATETIME,'%Y-%m-%d %H:%i:%s') <= #{endTime,jdbcType=VARCHAR} ]]>
    </if>
    
    <!--  或者：  -->
    
    <if test="endTime != null and endTime !=''">
      AND date_format(CREATETIME,'%Y-%m-%d %H:%i:%s')  <![CDATA[ <= ]]> #{endTime,jdbcType=VARCHAR}
    </if>
    

• 对于短的转义来说，<![CDATA[]]> 显长，但是含义表示 xml 解析器忽略解析，所以 <![CDATA[]]> 更快

sql 编写的一些顺序回顾

• 执行顺序为：

  • FROM

    WHERE

    GROUP BY，HAVING

    SELECT

    ORDER BY

    LIMIT

• 语句编写顺序

  • SELECT 字段列表 FROM 表名列表

    WHERE 条件列表

    GROUP BY 分组字段列表 HAVING 分组后条件列表

    ORDER BY 排序字段列表

    LIMIT 分页参数