Mysql存储过程与Mybatis实现动态行转列

目录

1、概述

1.1原表格结构

1.2 需求

1.3 解决方案

2、动态行转列sql语句

2.1sql语句

2.2sql语句解析

3、mysql构建存储过程

4、mybatis调用mysql存储过程

---

1、概述

1.1原表格结构

 base表：

 plan表：

1.2 需求

根据时间进行范围查询并且将时间由行转化为列展示，如下图：

1.3 解决方案

对于动态行转列问题难点在于要转为列的行并不是固定的，所以会导致在后端做转化比较复杂繁琐，在前端做转化又会遇到组件已封装完善，拿到后端返回的数据进行行转列的处理不现实，有可能无法使用原有已封好的组件。于是，经过了几次尝试决定使用Mysql存储过程和Mybatis调用存储过程的方式来实现，这样做的好处是直接得到了我们想要得数据结构，不需要再进行处理，并且存储过程可以封装复杂的sql语句，供外部直接调用，解决了mybatis无法处理复杂sql语句的问题。

2、动态行转列sql语句

2.1sql语句

SET @EE='';
SET @str_tmp='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(plan.plan_date=\'',plan_date,'\'',',plan.num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp FROM (SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03') A ORDER BY LENGTH(aa) DESC LIMIT 1;

SET @QQ=CONCAT('SELECT base_id,base.product_code,base.product_name, ',LEFT(@str_tmp,CHAR_LENGTH(@str_tmp)-1),'  FROM plan ,base  GROUP BY base_id');
PREPARE stmt  FROM @QQ; 
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;

2.2sql语句解析

SET @EE='';
SET @str_tmp='';

set可以定义系统变量和用户变量，用户变量定义可以使用set和select两种，
用户变量以‘@’开头，任意字符串命名。
使用set定义可以使用‘=’和‘:=’，使用select只能使用‘:=’

此sql语句定义了@EE和@str_tmp

SELECT @EE:=CONCAT(@EE,'SUM(IF(plan.plan_date=\'',plan_date,'\'',',plan.num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp FROM (SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03') A ORDER BY LENGTH(aa) DESC LIMIT 1;

复杂sql语句先将主干分析出来，上面sql语句本质上还是select 字段 from 某某表

字段为@EE:=CONCAT(@EE,'SUM(IF(plan_date=\'',plan_date,'\'',',num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp

表为SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03'查询出来的临时表，

@EE变量等于由CONCAT函数拼接成的字符串并放到@str_tmp变量中，最后再进行排序并限制为一行

CONCAT函数用于字符串的拼接 

通过select @str_tmp可以看到拼接的结果为：

SET @QQ=CONCAT('SELECT base_id,base.product_code,base.product_name, ',LEFT(@str_tmp,CHAR_LENGTH(@str_tmp)-1),'  FROM plan ,base  GROUP BY base_id');

此sql语句同上，定义变量@QQ，其值是通过CONCAT函数得到字符串，通过select @QQ可以看到其结果为：

PREPARE stmt  FROM @QQ;

预备一个语句 stmt

EXECUTE stmt;

执行 stmt 语句

DEALLOCATE PREPARE stmt;

释放执行中所使用的数据库资源
 PREPARE，EXECUTE，DEALLOCATE PREPARE，是一个流程，可以减少每次执行SQL的语法分析

3、mysql构建存储过程

上面这样复杂的sql语句在mybatis中无法使用，所以要将复杂的sql语句封装到存储过程中。封装过程如下：

第一步：在navicat选择函数，然后点击新建函数

 第二步：选择过程，并对过程命名，在这里命名为getInfo,然后点击下一步

 第三步：输入参数模式、参数名称、参数类型，然后点击完成

参数模式包括三种：IN  OUT  INOUT 

IN表示输入参数   OUT表示输出参数    INOUT既表示输入参数又表示输出参数

注意：在这里由于动态行转列输出的参数并不是固定的，所以这里只需要声明输入参数即可。

第四步：将sql语句放在BEGIN 与 END之间，并且注意要将sql语句中固定的条件换成第三步输入的参数，然后点击保存，运行一下检查是否成功

 注意：sql语句中一定要注意空格，标点符号问题，仔细比对，检查

4、mybatis调用mysql存储过程

Mapper层：使用LIstanbul<Map>进行接收

 xml：

语法：call 存储过程名称（#{参数名称，mode=参数模式}，#{参数名称，mode=参数模式}）

注意，在mybatis中参数模式全部要大写，否则编译时会报错，另外，看到一些语法为 

{call 存储过程名称（#{参数名称，mode=参数模式}，#{参数名称，mode=参数模式})} 在外面加了大括号，不知道什么原因，我没有成功，若不加大括号不成功可以试试加上。

 statementType的值是CALLABLE，告诉mybatis将要执行的是存储过程

resultType表示返回类型