2017.7.7 PostgreSQL performs updates when insertions cause duplicates

Reference from: https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/1109198#1109198

Functional requirements: The interface definition contains the field update, and when it is true, there is a record in the bulk insert that causes the unique key to repeat, not to report the exception, but to perform the update.

1. Interface definition

1 {2"Resources":3    [4       {5"Name": "*",6"Display_name": "*",7"description": "*",8"Service": "*"9       },Ten       .... One    ], A"Update":true -  -}

2. Code

The update is performed in the catch statement by the exception Duplicatekeyexception that was reported. But here's a question, which is said separately.

1             Try {2 Resourcedao.insert (Resource);3                 //Query the ID of the AdminGroup, add the association4 addadminpermission (Resource);5}Catch(duplicatekeyexception e) {6 //if (stringutils.equals (Update,constants.key_false)) {7 //throw new Authserviceexception (Errorcode.resource_duplicate_key, New Object[]{resource.getfdresnam E ()}, E);8 //}else if (stringutils.equals (update,constants.key_true)) {9 //resourcedao.update (Resource);Ten //                } One                 Throw NewAuthserviceexception (Errorcode.resource_duplicate_key,Newobject[]{resource.getfdresname ()}, E); A}Catch(Exception e) { -                 Throw NewAuthserviceexception (errorcode.resource_create_failed,Newobject[]{resource.getfdresname ()}, E); -}

3.SQL statements

Example statements:

1 INSERT  intothe_table (ID, column_1, column_2)2 VALUES(1,'A','X')3  onCONFLICT (unique key) doUPDATE 4 SETColumn_1= 'A',5Column_2= 'X';

Complete statement:

1     <InsertID= "Insert"ParameterType= "Resource"Usegeneratedkeys= "true"Keyproperty= "Fdresid">2 INSERT into T_resource3         <Trimprefix="("suffix=")"Suffixoverrides=",">4             <ifTest= "Fdresname! = null">5 Fd_res_name,6             </if>7             <offTest= "Fddisplayname! = null">8 Fd_display_name,9             </if>Ten             <ifTest= "Fdresdesc! = null"> One Fd_res_desc, A             </if> -             <ifTest= "Fdtenantid! = null"> - Fd_tenantid, the             </if> -             <ifTest= "Fdservice! = null"> - Fd_service, -             </if> +         </Trim> -         <Trimprefix= "Values" ("suffix=")"Suffixoverrides=","> +             <ifTest= "Fdresname! = null"> A #{fdresname,jdbctype=varchar}, at             </if> -             <ifTest= "Fddisplayname! = null"> - #{fddisplayname,jdbctype=varchar}, -             </if> -             <offTest= "Fdresdesc! = null"> - #{fdresdesc,jdbctype=varchar}, in             </if> -             <ifTest= "Fdtenantid! = null"> to #{fdtenantid,jdbctype=integer}, +             </if> -             <ifTest= "Fdservice! = null"> the #{fdservice,jdbctype=varchar}, *             </if> $         </Trim>Panax Notoginseng  < ifTest= "Fdupdate = = ' true '"> the On CONFLICT (Fd_res_name, Fd_tenantid, Fd_service) do UPDATE +             <Set> A                 <ifTest= "Fddisplayname! = null">Fd_display_name = #{fddisplayname},</if> the                 <ifTest= "Fdresdesc! = null">Fd_res_desc = #{fdresdesc},</if> +                 <ifTest= "Fdservice! = null">Fd_service = #{fdservice},</if> -                 <ifTest= "Fdmodifydate! = null"> $ fd_modify_date = #{fdmodifydate,jdbctype=varchar}, $                 </if> -                 <ifTest= "Fdmodifyperson! = null"> - Fd_modify_person = #{fdmodifyperson,jdbctype=varchar}, the                 </if> -             </Set>Wuyi         </if> the     </Insert>

4. Problems encountered

As mentioned earlier, the previous processing was done by the reported exception information. If repeated, update is performed in the catch statement.

            Try{
                Resourcedao. Insert (Resource);
// EnquiryAdminGroupof theID,Add Association
Addadminpermission (Resource);
}Catch(Duplicatekeyexception e) {
//if (Stringutils.equals (Update,constants.key_false)) {
//throw new Authserviceexception (Errorcode.resource_duplicate_key, New OBJECT[]{RESOURCE.GETFDRESN Ame ()}, E);
//}else if (Stringutils.equals (update,constants.key_true)) {
Resourcedao.update (Resource);
//                }
throw NewAuthserviceexception (ErrorCode.Resource_duplicate_key, NewObject[]{resource.getfdresname ()}, E;
}Catch(Exception e) {
                throw NewAuthserviceexception (ErrorCode.resource_create_failed, NewObject[]{resource.getfdresname ()}, E;
}

2017.7.7 PostgreSQL performs updates when insertions cause duplicates