Mybatis调用Postgres自定义函数实现批量更新的解决方案

青旅半醒 2022-08-09 15:51 830阅读 0赞

前言

如题，提供批量更新的一种解决方案，不一定最优，仅仅是一种解决问题的思路，废话不多说下面具体看一下应用场景和详细的解决步骤~

应用场景

如下图所示，是一个信息编辑页面：

![20151030185451099][]

可以这是一个标准的批量修改的功能，当用户点击“修改”按钮时候页面的数据变为可编辑状态，可以修改1个或多个数据项，点击保存后数据同步更新到DB。不知道各位看到这种功能时的第一思路是什么呢？下面就从数据表设计开始谈谈我的思路和方案。

数据表设计

观察上图不难发现基本编辑项都为一些系统参数项，所以这种界面适用于不经常变化的一些参数或数据的维护，而且操作也较为简单。既然是参数类型的数据，为了通用起见，我们肯定要设计成纵表的形式，如下图所示：

![Center][]

很简单，唯一关注一下abbr\_name（拼音简写）这个字段，由于在界面上我们的数据不是以列表的形式展示的，而是固定的、提前安排好哪个位置放什么样的数据，所以我们就需要挨个去指定<input/>文本框的name属性，这里我们定义一条规则：name属性和表中的abbr\_name保持一致，这样做有两个关键的好处。

第一，我们决定用pg的自定义函数来处理这个批量更新，思路就是给函数传一个字符串数组作为参数，在函数内部进行loop，获取数组的每一个元素进行分割，获取到update语句需要的参数值。这个数组的每一个元素是“参数名+分隔符+参数值”的一个字符串，例如“nwdz\#8.8.8.8”，我就是以井号作为分隔符，前面串接参数名，后面串接参数值，这样当这个字符串传入函数的时候，我再通过pg的字符串函数进行分割，就直接得到了需要update set所需要的param\_value和where条件中的所需要的abbr\_name。  
第二，方便显示。我们的显示界面明显不是一个可用foreach标签循环的List，而且当数据量较大时候如何显示？一个一个查，很麻烦不可取，所以这时abbr\_name和<input/>的name一致的好处就体现出来了：我们依然在后台查询返回一个List，数据传到页面后转换成json数组，通过js去遍历的时候我们可以通过jquery的$(input\[name='xxx'\]).val(xxx)来实现页面数据的填充，有点js基础的话应该可以想来是如何实现的，再贴一小段参考代码：

success:function(data){
                    var ulistJosn = getStrJson(data);
                    for ( var i = 0; i < ulistJosn.length; i++) {
                       var abbrname = ulistJosn[i].abbrname;
                       var paramvalue = ulistJosn[i].paramvalue;
                       $("input[name="+abbrname+"]").val(paramvalue);
                    }
                 }

最后来看一下批量更新的自定义函数：

CREATE OR REPLACE FUNCTION func_update_basic_param(arr text[])
      RETURNS void AS
      $body$ 
    	declare 
    	    arrLength integer;
    	    rownum integer := 0; 
    	    elementStr text;
    	    elementPartOne text;
    	    elementPartTwo text;
            BEGIN  
    	   arrLength = array_length(arr,1);
    	   -- begin loop
    	   while rownum < arrLength LOOP 
                  -- rebuild string
                  elementStr:=arr[rownum];
                  elementPartOne:=split_part(elementStr,'%',1);
                  elementPartTwo:=split_part(elementStr,'%',2);
                  -- do update
                  update t_sys_param_settings set param_value = elementPartTwo where abbr_name = elementPartOne;
    	   rownum := rownum + 1; 
    	   END LOOP;
    	   RETURN;
            END;  
            $body$ LANGUAGE 'plpgsql';

用到几个简单的pg内置函数，例如：array\_length(anyarray,int) 返回数组指定维度的长度，split\_part(str,del,field)根据del分割str返回生成的第field个子字符串。写这个函数的目的也就是为了减少连接数据库的次数，通过一次调用完成批量操作。

总结

本篇仅仅记录了一种较为简单的批量更新的实现方式，顺便学习一下postgresql自定义函数的基本语法，整体和oracle的procedure还是很类似的，如果有错误的地方和更好的建议欢迎批评指正，The End。

[20151030185451099]: /images/20220731/a2bffb7c3e8d47b79d99e33e3a12d726.png
[Center]: /images/20220731/d3b12eabd43d453f97566ef87a787730.png