当前位置：Gxlcms > 数据库问题 > 2017.7.7 postgreSQL在插入造成重复时执行更新

2017.7.7 postgreSQL在插入造成重复时执行更新

时间：2021-07-01 10:21:17 帮助过：25人阅读

{ 2 "resources": 3 [ 4 { 5 "name":"*", 6 "display_name": "*", 7 "description": "*", 8 "service": "*" 9 }, 10 .... 11 ], 12 "update":true 13 14 }

 

2.代码

原本是通过报的异常 DuplicateKeyException，在catch语句里执行update。但是这里有一个问题，后面单独说。

  1. <span style="color: #008080;"> 1</span>             <span style="color: #0000ff;">try</span><span style="color: #000000;"> {
  2. </span><span style="color: #008080;"> 2</span> <span style="color: #000000;">                resourceDao.insert(resource);
  3. </span><span style="color: #008080;"> 3</span>                 <span style="color: #008000;">//</span><span style="color: #008000;"> 查询admingroup的id,添加关联</span>
  4. <span style="color: #008080;"> 4</span> <span style="color: #000000;">                AddAdminPermission(resource);
  5. </span><span style="color: #008080;"> 5</span>             } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (DuplicateKeyException e) {
  6. </span><span style="color: #008080;"> 6</span> <span style="color: #008000;">//</span><span style="color: #008000;">                if (StringUtils.equals(update,Constants.KEY_FALSE)) {
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #008000;">//</span><span style="color: #008000;">                    throw new AuthServiceException(ErrorCode.RESOURCE_DUPLICATE_KEY, new Object[]{resource.getFdResName()}, e);
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #008000;">//</span><span style="color: #008000;">                }else if (StringUtils.equals(update,Constants.KEY_TRUE)){
  9. </span><span style="color: #008080;"> 9</span> <span style="color: #008000;">//</span><span style="color: #008000;">                    resourceDao.update(resource);
  10. </span><span style="color: #008080;">10</span> <span style="color: #008000;">//</span><span style="color: #008000;">                }</span>
  11. <span style="color: #008080;">11</span>                 <span style="color: #0000ff;">throw</span> <span style="color: #0000ff;">new</span> AuthServiceException(ErrorCode.RESOURCE_DUPLICATE_KEY, <span style="color: #0000ff;">new</span><span style="color: #000000;"> Object[]{resource.getFdResName()}, e);
  12. </span><span style="color: #008080;">12</span>              } <span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
  13. </span><span style="color: #008080;">13</span>                 <span style="color: #0000ff;">throw</span> <span style="color: #0000ff;">new</span> AuthServiceException(ErrorCode.RESOURCE_CREATE_FAILED, <span style="color: #0000ff;">new</span><span style="color: #000000;"> Object[]{resource.getFdResName()}, e);
  14. </span><span style="color: #008080;">14</span>             }

 

3.SQL语句

示例语句：

  1. <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> the_table (id, column_1, column_2) 
  2. </span><span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">VALUES</span> (<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">A</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">X</span><span style="color: #ff0000;">‘</span>)<span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">ON</span> CONFLICT (唯一键) DO <span style="color: #0000ff;">UPDATE</span> 
  3. <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #0000ff;">SET</span> column_1 <span style="color: #808080;">=</span><span style="color: #000000;"><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">A</span><span style="color: #ff0000;">‘</span>, 
  4. </span><span style="color: #800000; font-weight: bold;">5</span>     column_2 <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">X</span><span style="color: #ff0000;">‘</span>;

 

完整语句：

  1. <span style="color: #008080;"> 1</span>     <span style="color: #0000ff;"><</span><span style="color: #800000;">insert </span><span style="color: #ff0000;">id</span><span style="color: #0000ff;">="insert"</span><span style="color: #ff0000;"> parameterType</span><span style="color: #0000ff;">="Resource"</span><span style="color: #ff0000;"> useGeneratedKeys</span><span style="color: #0000ff;">="true"</span><span style="color: #ff0000;"> keyProperty</span><span style="color: #0000ff;">="fdResid"</span><span style="color: #0000ff;">></span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #000000;">        INSERT INTO t_resource
  3. </span><span style="color: #008080;"> 3</span>         <span style="color: #0000ff;"><</span><span style="color: #800000;">trim </span><span style="color: #ff0000;">prefix</span><span style="color: #0000ff;">="("</span><span style="color: #ff0000;"> suffix</span><span style="color: #0000ff;">=")"</span><span style="color: #ff0000;"> suffixOverrides</span><span style="color: #0000ff;">=","</span><span style="color: #0000ff;">></span>
  4. <span style="color: #008080;"> 4</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdResName != null"</span><span style="color: #0000ff;">></span>
  5. <span style="color: #008080;"> 5</span> <span style="color: #000000;">                fd_res_name,
  6. </span><span style="color: #008080;"> 6</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  7. <span style="color: #008080;"> 7</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdDisplayName != null"</span><span style="color: #0000ff;">></span>
  8. <span style="color: #008080;"> 8</span> <span style="color: #000000;">                fd_display_name,
  9. </span><span style="color: #008080;"> 9</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  10. <span style="color: #008080;">10</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdResDesc != null"</span><span style="color: #0000ff;">></span>
  11. <span style="color: #008080;">11</span> <span style="color: #000000;">                fd_res_desc,
  12. </span><span style="color: #008080;">12</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  13. <span style="color: #008080;">13</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdTenantId != null"</span><span style="color: #0000ff;">></span>
  14. <span style="color: #008080;">14</span> <span style="color: #000000;">                fd_tenantid,
  15. </span><span style="color: #008080;">15</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  16. <span style="color: #008080;">16</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdService != null"</span><span style="color: #0000ff;">></span>
  17. <span style="color: #008080;">17</span> <span style="color: #000000;">                fd_service,
  18. </span><span style="color: #008080;">18</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  19. <span style="color: #008080;">19</span>         <span style="color: #0000ff;"></</span><span style="color: #800000;">trim</span><span style="color: #0000ff;">></span>
  20. <span style="color: #008080;">20</span>         <span style="color: #0000ff;"><</span><span style="color: #800000;">trim </span><span style="color: #ff0000;">prefix</span><span style="color: #0000ff;">="values ("</span><span style="color: #ff0000;"> suffix</span><span style="color: #0000ff;">=")"</span><span style="color: #ff0000;"> suffixOverrides</span><span style="color: #0000ff;">=","</span><span style="color: #0000ff;">></span>
  21. <span style="color: #008080;">21</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdResName != null"</span><span style="color: #0000ff;">></span>
  22. <span style="color: #008080;">22</span> <span style="color: #000000;">                #{fdResName,jdbcType=VARCHAR},
  23. </span><span style="color: #008080;">23</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  24. <span style="color: #008080;">24</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdDisplayName != null"</span><span style="color: #0000ff;">></span>
  25. <span style="color: #008080;">25</span> <span style="color: #000000;">                #{fdDisplayName,jdbcType=VARCHAR},
  26. </span><span style="color: #008080;">26</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  27. <span style="color: #008080;">27</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdResDesc != null"</span><span style="color: #0000ff;">></span>
  28. <span style="color: #008080;">28</span> <span style="color: #000000;">                #{fdResDesc,jdbcType=VARCHAR},
  29. </span><span style="color: #008080;">29</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  30. <span style="color: #008080;">30</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdTenantId != null"</span><span style="color: #0000ff;">></span>
  31. <span style="color: #008080;">31</span> <span style="color: #000000;">                #{fdTenantId,jdbcType=INTEGER},
  32. </span><span style="color: #008080;">32</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  33. <span style="color: #008080;">33</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdService != null"</span><span style="color: #0000ff;">></span>
  34. <span style="color: #008080;">34</span> <span style="color: #000000;">                #{fdService,jdbcType=VARCHAR},
  35. </span><span style="color: #008080;">35</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  36. <span style="color: #008080;">36</span>         <span style="color: #0000ff;"></</span><span style="color: #800000;">trim</span><span style="color: #0000ff;">></span>
  37. <span style="color: #008080;">37</span> 
  38. <strong><span style="color: #008080;">38</span>         <span style="color: #0000ff;"><</span></strong><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdUpdate == ‘true‘"</span><span style="color: #0000ff;">></span>
  39. <span style="color: #008080;">39</span> <span style="color: #000000;">            ON CONFLICT(fd_res_name, fd_tenantid, fd_service) DO UPDATE
  40. </span><span style="color: #008080;">40</span>             <span style="color: #0000ff;"><</span><span style="color: #800000;">set</span><span style="color: #0000ff;">></span>
  41. <span style="color: #008080;">41</span>                 <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdDisplayName != null"</span><span style="color: #0000ff;">></span>fd_display_name = #{fdDisplayName},<span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  42. <span style="color: #008080;">42</span>                 <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdResDesc != null"</span><span style="color: #0000ff;">></span>fd_res_desc = #{fdResDesc},<span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  43. <span style="color: #008080;">43</span>                 <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdService != null"</span><span style="color: #0000ff;">></span>fd_service = #{fdService},<span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  44. <span style="color: #008080;">44</span>                 <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdModifyDate != null"</span><span style="color: #0000ff;">></span>
  45. <span style="color: #008080;">45</span> <span style="color: #000000;">                    fd_modify_date = #{fdModifyDate,jdbcType=VARCHAR},
  46. </span><span style="color: #008080;">46</span>                 <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  47. <span style="color: #008080;">47</span>                 <span style="color: #0000ff;"><</span><span style="color: #800000;">if </span><span style="color: #ff0000;">test</span><span style="color: #0000ff;">="fdModifyPerson != null"</span><span style="color: #0000ff;">></span>
  48. <span style="color: #008080;">48</span> <span style="color: #000000;">                    fd_modify_person = #{fdModifyPerson,jdbcType=VARCHAR},
  49. </span><span style="color: #008080;">49</span>                 <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  50. <span style="color: #008080;">50</span>             <span style="color: #0000ff;"></</span><span style="color: #800000;">set</span><span style="color: #0000ff;">></span>
  51. <span style="color: #008080;">51</span>         <span style="color: #0000ff;"></</span><span style="color: #800000;">if</span><span style="color: #0000ff;">></span>
  52. <span style="color: #008080;">52</span>     <span style="color: #0000ff;"></</span><span style="color: #800000;">insert</span><span style="color: #0000ff;">></span>

 

4.遇到的问题

之前提到过，之前的处理是通过所报异常信息来做的。如果重复了，在catch语句里执行update。

 

  1.             <span style="color: #cc7832;">try </span>{<br>                <span style="color: #9876aa;">resourceDao</span>.insert(resource)<span style="color: #cc7832;">;<br></span><span style="color: #808080;">// </span><span style="color: #808080; font-family: ‘宋体‘;">查询</span><span style="color: #808080;">admingroup</span><span style="color: #808080; font-family: ‘宋体‘;">的</span><span style="color: #808080;">id,</span><span style="color: #808080; font-family: ‘宋体‘;">添加关联<br></span>AddAdminPermission(resource)<span style="color: #cc7832;">;<br></span>} <span style="color: #cc7832;">catch </span>(DuplicateKeyException e) {<br><span style="color: #808080;">//                if (StringUtils.equals(update,Constants.KEY_FALSE)) {<br></span><span style="color: #808080;">//                    throw new AuthServiceException(ErrorCode.RESOURCE_DUPLICATE_KEY, new Object[]{resource.getFdResName()}, e);<br></span><span style="color: #808080;">//                }else if (StringUtils.equals(update,Constants.KEY_TRUE)){<br></span><span style="color: #808080;">//                    resourceDao.update(resource);<br></span><span style="color: #808080;">//                }<br></span><span style="color: #cc7832;">throw new </span>AuthServiceException(ErrorCode.<span style="color: #9876aa; font-style: italic;">RESOURCE_DUPLICATE_KEY</span><span style="color: #cc7832;">, new </span>Object[]{resource.getFdResName()}<span style="color: #cc7832;">, </span>e)<span style="color: #cc7832;">;<br></span>} <span style="color: #cc7832;">catch </span>(Exception e) {<br>                <span style="color: #cc7832;">throw new </span>AuthServiceException(ErrorCode.<span style="color: #9876aa; font-style: italic;">RESOURCE_CREATE_FAILED</span><span style="color: #cc7832;">, new </span>Object[]{resource.getFdResName()}<span style="color: #cc7832;">, </span>e)<span style="color: #cc7832;">;<br></span>}

2017.7.7 postgreSQL在插入造成重复时执行更新

标签：set   href   family   ack   ==   object   com   conf   har   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！