Mysql 存储过程\Mybatis框架call调用实现动态行转列

╰+攻爆jí腚メ 2024-03-31 11:58 42阅读 0赞

> 业务场景：我们在前端展示表格数据一般有两种，一种是业务的统计问题表单，还有一种就是通过问题表单，转换汇总成结果汇总表单，比如一张发货表单，有发货量，产品，时间，现在需要转换成：产品每个月获取的产品数量。也就是 时间字段 从 ‘行’提到了‘列’，行转列了，那么就涉及到，根据时间的筛选，来动态得到某个产品几个月的发货量，这里的数据处理就需要用存储过程来实现动态的行转列了。

## 一、表结构数据与需求 ##

首先先看下业务涉及的表格都有哪些，首先有一个产品表，记录产品、项目、编码信息，其次就是一个发货明细表，记录着编码的发货量、发货时间等信息，表结构如下：

<table style="width:500px;">  产品信息表  
 <tbody> 
  <tr> 
   <td>PRODUCT</td> 
   <td>PROJECT</td> 
   <td>CODE</td> 
  </tr> 
  <tr> 
   <td>A产品</td> 
   <td>A项目</td> 
   <td>A001</td> 
  </tr> 
  <tr> 
   <td>A产品</td> 
   <td>B项目</td> 
   <td>B001</td> 
  </tr> 
  <tr> 
   <td>B产品</td> 
   <td>B项目</td> 
   <td>B002</td> 
  </tr> 
 </tbody> 
</table>

这里简要概述下业务逻辑，一个产品会有多个项目，项目会有多个产品，而编码是唯一的，这些逻辑不影响我们做关联，仅做解释。

<table style="width:500px;">  发货明细表  
 <tbody> 
  <tr> 
   <td>ITEM_CODE</td> 
   <td>ACTUAL_SHIPMENT_DATE</td> 
   <td>QUANTITY</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>A001</td> 
   <td>2022-11-01</td> 
   <td>100</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>A001</td> 
   <td>2022-11-05</td> 
   <td>200</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>B001</td> 
   <td>2022-10-01</td> 
   <td>150</td> 
   <td>..</td> 
  </tr> 
 </tbody> 
</table>

产品的发货一般都有涉及产品信息，发货地，发货量，发货时间等，我们的需求是求每个月发货量，所以将编码、发货时间和发货量简要展示。

这两个表，最终合并后形式如下：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>PRODUCT</td> 
   <td>PROJECT</td> 
   <td>CODE</td> 
   <td>QUANTITY</td> 
   <td>2022-11</td> 
   <td>2022-10</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>A产品</td> 
   <td>A项目</td> 
   <td>A001</td> 
   <td>500</td> 
   <td>100</td> 
   <td>50</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>A产品</td> 
   <td>B项目</td> 
   <td>B001</td> 
   <td>600</td> 
   <td>50</td> 
   <td>200</td> 
   <td>..</td> 
  </tr> 
  <tr> 
   <td>B产品</td> 
   <td>B项目</td> 
   <td>B002</td> 
   <td>700</td> 
   <td>100</td> 
   <td>200</td> 
   <td>..</td> 
  </tr> 
 </tbody> 
</table>

可以看到这个就是数据行转列，把时间行数据转换成列头；发货量其实是针对某个编码的发货，SUM字段表示，业务查询的时间范围的某个编码的发货总量，往后是每个动态月份的按月发货量，比如 查询A001编码，22年11月-8月的发货量，那么SUM列就是将4个月发货量求和得到结果值，往后分别是2022-11、2022-10、2022-09、2022-08四列月份，值则为每个月的发货量求和（这里是月内的发货求和）。

## 二、分析处理思路 ##

这里如果只是静态的明确知道年月有多少的话，直接可以使用case when转行即可，样例如下SQL,对于动态行转列问题难点在于不知道有多少列，也就是年月列，前端的样式组件，应该是没办法处理这种动态列头，组件封装好了下次变化年份就匹配不上了，所以最好从后端数据库入手，数据库有个强大的功能，或许就能满足需求了，存储过程，然后在Mybatis的dao层映射文件调用存储过程的方式来实现，这样做的好处是直接得到了我们想要得数据结构，不需要再进行处理，并且存储过程可以封装复杂的sql语句，供外部直接调用，解决了mybatis无法处理复杂sql语句的问题。[存储过程][Link 1]的认识，我这边之前也写了一篇，大家可以了解下，**当业务需求在转换数据处理很复杂或者需要动态变化列头的时候，不妨考虑下存储过程**

固定的表头时间列的SQL处理如下：

SELECT
    	product,project,
    	ITEM_CODE,sum(total) total,
    	SUM( CASE shipmentDate WHEN '2022-05' THEN total ELSE 0 END ) '2022-05' ,
    	SUM( CASE shipmentDate WHEN '2022-06' THEN total ELSE 0 END ) '2022-06' ,
    	SUM( CASE shipmentDate WHEN '2022-07' THEN total ELSE 0 END ) '2022-07' 
    FROM
    	(
    	SELECT
    		t2.product as product,
    		t2.project as project,
    		ITEM_CODE,
    		sum( QUANTITY ) AS total,
    		DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) AS shipmentDate 
    	FROM
    		发货表 t1 INNER JOIN 编码表 t2 on  t1.ITEM_CODE = t2.code 
    		where DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) BETWEEN '2022-05' AND '2022-07'
    	GROUP BY
    		ITEM_CODE,
    		DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) 
    	ORDER BY
    		shipmentDate DESC 
    	) temp 
    GROUP BY
    	ITEM_CODE

## 三、解决方案 ##

### 1.创建存储过程，代码如下： ###

DELIMITER $$  --重定义分隔符，避开， ；这些在sql中常用的符号，但navicat中创建的存储过程不需要再重定义了，这个客户端已经做了分隔符重定义。
    //创建存储过程 
    
    CREATE DEFINER=`dbuser`@`%` PROCEDURE `RowToColumnPro`(
        IN productName VARCHAR(100),
        IN projectName VARCHAR(100),
        IN codeName VARCHAR(20),
        IN startTime VARCHAR(20),
        IN endtTime VARCHAR(20)
    )
    BEGIN
    
        --1.创建临时表，将编码表与发货表关联，先得到一个按编码，年月分组后的编码发货数据集，性能有一定提升
        DROP TEMPORARY TABLE IF EXISTS tempCode;  
        CREATE TEMPORARY TABLE tempCode(
            PRODUCT VARCHAR(100),
        	PROJECT VARCHAR(100),
        	`CODE` VARCHAR(100),
    	    NUM INT,
    	    ACTUAL_SHIPMENT_DATE VARCHAR(100) 
        )
        --------------------------
    
        --2.设置5个用户变量存储查询的产品信息动态入参条件，注意要判断下非空的情况，根据前端用户的实际传参值
        SET @strWhere=' AND 1=1 ';  -- 联表的where条件设置一个用户变量
    
    	IF  productName<>""   THEN 
        --find_in_set函数，productName 参数可以是一个列表变量如('A产品','B产品')，用来精确匹配每个值，字段值以英文”,”分隔，不能用in,in是匹配常量值
    	SET @strWhere1=CONCAT(' AND find_in_set(c.product,"',productName ,'")'); 
    	SET @strWhere=CONCAT(@strWhere,@strWhere1); -- 拼接条件
    	END IF;
    
    	IF projectName<>"" THEN 
    	SET @strWhere2=CONCAT(' AND find_in_set(c.project,"',projectName ,'")');
    	SET @strWhere=CONCAT(@strWhere,@strWhere2);
    	END IF;
    
    	IF codeName<>"" THEN 
    	SET @strWhere3=CONCAT(' AND find_in_set(c.`code`,"',codeName,'")');
    	SET @strWhere=CONCAT(@strWhere,@strWhere3);
    	END IF;
    
    	IF startTime<>""  THEN 
    	SET @strWhere4=CONCAT(' AND DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m-%d")>="',startTime,'"');
    	SET @strWhere=CONCAT(@strWhere,@strWhere4);
    	END IF;  
    
        IF endTime != "" THEN
    	SET @strWhere5=CONCAT('AND DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m-%d")<="',endTime,'"');
        SET @strWhere=CONCAT(@strWhere,@strWhere5);
    	END IF; 
    	-- SELECT @strWhere; 这个是打印出用户变量的拼接值
    	------------------------
    
        --3.定义查询sql赋值@strSelect：两表联表+产品信息动态条件：@strWhere+分组排序：发货时间年月、产品、项目、编码， 最后插入到临时表tempCode
    	SET @strSelect=CONCAT(' INSERT INTO tempCode(PRODUCT,
    	PROJECT,
    	`CODE`,
    	NUM,
    	ACTUAL_SHIPMENT_DATE) SELECT 
    	c.product AS PRODUCT,
    	c.project AS PROJECT,
    	c.`code` AS `CODE`,
    	SUM(s.QUANTITY) AS NUM,
    	DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m") AS ACTUAL_SHIPMENT_DATE
    	FROM 发货表 s 
    	INNER JOIN  编码表 c ON s.ITEM_CODE=c.`code` 
    	WHERE 1=1 ',@strWhere,
    	'GROUP BY DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m"),c.product,c.project,c.`code` 
    	ORDER BY DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m"),c.product,c.project,c.`code`');
    
    -- 	SELECT @strSelect;
    -------------------
       --4.这里先处理执行释放这个插入语句@strSelect，固定流程，可以减少每次执行SQL的语法分析
    	PREPARE stmt FROM @strSelect; -- 处理动态sql语句
    	EXECUTE stmt ;   -- 执行sql语句
    	DEALLOCATE PREPARE stmt;  -- 释放PREPARE
    	-- SELECT * FROM tempItem;
    
    ------------------------
        --5.根据临时表的转换好的产品发货数据，将时间内容动态行转列后存储在@EE用户变量当中
        --  \'  斜杆是转义，因外层套了一组单引号了，里面还要用单引号 ，即转义单引号 \' => ' 或者 ''=>'
    	SET @EE='';
    	SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(ACTUAL_SHIPMENT_DATE= \'',ACTUAL_SHIPMENT_DATE,'\',NUM,NULL)) AS "',ACTUAL_SHIPMENT_DATE,'"')) INTO @EE
    	FROM (SELECT * FROM tempCode) a;
    
        --6.这个做空判断，主要是方便前端，如果查询的编码和时间段内没有数据，则返回"无数据"字段便于前端判空，就不用加载表格组件
    	IF @EE IS NULL THEN
    	SET @EE='SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据",
    	SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据",
    	SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据"';
    	END IF;
    	
        --7.最终查询sql定义变量，查询临时表，将前面第5点得到的动态时间拼接到查询字段中，然后再同样还得按时间和产品、项目、编码分组，分组后的num字段求和发货量赋值给每个年月，
    	SET @QQ=CONCAT('SELECT 
    	product,
    	project,
    	`code`,
    	SUM(NUM) AS num,',@EE,
    	' FROM tempCode 
    	GROUP BY DATE_FORMAT(ACTUAL_SHIPMENT_DATE,"%Y-%m"), PRODUCT,PROJECT,`CODE`');
        
        --8.执行@QQ查询语句变量
    	PREPARE stmt1 FROM @QQ; -- 处理动态sql语句
    	EXECUTE stmt1 ;          -- 执行sql语句
    	DEALLOCATE PREPARE stmt1; -- 释放PREPARE
    
    END $$
    DELIMITER ;

这过程有些复杂，注释中我拆分成8个步骤分析就很明朗了。

*  DELIMITER 定义分隔符， 头设置表示以这个符号表示介绍，最后在END后还需加上，尾部设置; 还原sql原生的分隔符
 *  DEFINER=\`dbuser\`@\`%\` 用户名@主机 ， %表示localhost ，设置指定用户访问存储过程权限
 *  **参数模式包括三种：IN OUT INOUT** 
    
    **IN表示输入参数 OUT表示输出参数 INOUT表示输入参数又表示输出参数**
 *  **BEGIN ...END : 主体内容**
 *  SET 定义系统变量和用户变量，用户变量定义可以使用set和select两种，  
    用户变量以‘@’开头，任意字符串命名。
 *  CONCAT 该函数是数据库内置函数，该需求中用来拼接入参条件、时间内容行转列、拼接多个sql
 *  TEMPORARY 临时表，能一定程度提升SQL的执行效率

### 2.Mybatis映射文件调用存储过程 ###

mapper.java如下：

@Repository
    Public interface proDao{
        List<Map> getPro(String productName,String String projectName,String codeName,String startTime,String endTime);
    }

dao.xml如下：

<select id='getPro' resultType="java.util.Map" statementType="CALLABLE" >
        CALL RowToColumnPro(#{productName mode=IN},#{projectName mode=IN},#{codeName mode=IN},#{startTime mode=IN},#{endTime mode=IN})
    </select>

> 语法：call 存储过程名称（\#\{参数名称，mode=参数模式\}，\#\{参数名称，mode=参数模式\}）
> 
> **这里如果参数多，也可以定义一个<parameterMap>标签存储多个参数，然后传递在select 标签中，语句中用？传参 ：\{call saveuser(?, ?, ?)\} ，这里注意最外层有个中括号\{\}**
> 
> **注意，在mybatis中参数模式全部要大写，否则编译时会报错**
> 
> statementType的值是CALLABLE，告诉mybatis将要执行的是存储过程

接着就是给服务层调用这个dao接口了~，重点核心就在存储过程脚本编写与dao层调用存储过程。掌握起来

[Link 1]: https://blog.csdn.net/studyday1/article/details/128034349?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22128034349%22%2C%22source%22%3A%22studyday1%22%7D