MyBatis Call Postgres Custom function to implement batch update solution __ function

Objective

The title, to provide a batch update solution, not necessarily optimal, is only a solution to the problem of ideas, nonsense not to say the following specific look at the application scenarios and detailed solution steps ~

Application Scenarios

As the following illustration shows, it is an information editing page:

This is a standard batch modification function, when the user clicks the "Modify" button when the page's data becomes editable, you can modify 1 or more data items, click Save Data Synchronization Update to DB. I don't know what the first idea is when you see this kind of function. The following is from the datasheet design to start talking about my ideas and plans.

Data table Design

It is easy to observe that the basic editing items are all system parameter items, so this interface is suitable for the maintenance of some parameters or data that are not changed frequently, and the operation is simpler. Since it is a parameter type of data, for general purposes, we must design the form of a vertical table, as shown in the following figure:

Very simple, the only focus on the abbr_name (Pinyin) This field, because the interface of our data is not in the form of a list, but fixed, in advance to arrange what position to put what kind of data, so we need to specify the <input/> The Name property of the text box, where we define a rule: The Name property is consistent with the abbr_name in the table, which has two key benefits.

First, we decided to use PG's custom function to deal with this batch update, the idea is to pass a string array of functions as parameters, loop inside the function, get each element of the array to split, get to the UPDATE statement required parameter values. Each element of this array is a string of "parameter name + separator + parameter value". For example, "nwdz#8.8.8.8", I just use the well number as the separator, the front string parameter name, and then the string parameter value, so when this string passed into the function, I again through the PG string function to split, The required param_value and where conditions needed for the update set are directly obtained abbr_name.
Second, convenient display. Our display interface is obviously not a list that can be used with a foreach tag loop, and how to display it when the data volume is larger. One check, very troublesome not advisable, so at this time Abbr_name and <input/> 's name consistent benefits are reflected: we still in the background query returned a list, the data uploaded to the page and converted to a JSON array, Through JS to traverse the time we can through jquery $ (input[name= ' xxx ']). val (XXX) to achieve the fill of the page data, a bit JS basic words should be able to think how to achieve, and then paste a small section of reference code:

             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);
                }
             

Finally, take a look at the custom functions for batch updates:

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;  
        

With a few simple PG built-in functions, such as: Array_length (anyarray,int) returns the length of the specified dimension of the array, Split_part (Str,del,field) returns the generated first field substring based on Del split str. The purpose of writing this function is to reduce the number of connections to the database, and to complete the bulk operation with one call.

Summarize

This article only records a relatively simple implementation of the batch update, by the way learn the basic syntax of PostgreSQL custom function, the overall and Oracle procedure is very similar, if there is wrong place and better suggestions welcome criticism, the end.