postgresql批量新增或更新的sql语句(Mapper实现) 

  1     @Insert({"<script>" +
  2             "<foreach collection=\"list\" item=\"item\" separator=\";\"> " +
  3             "INSERT INTO sc_water_month (\n" +
  4             "id,\n" +
  5             "section_name,\n" +
  6             "section_code,\n" +
  7             "province_name,\n" +
  8             "province_code,\n" +
  9             "city_name,\n" +
 10             "city_code,\n" +
 11             "basin_name,\n" +
 12             "river_name,\n" +
 13             "result,\n" +
 14             "analyse_item,\n" +
 15             "water_quality,\n" +
 16             "water_temperature,\n" +
 17             "ph,\n" +
 18             "dissolved_oxygen,\n" +
 19             "conductivity,\n" +
 20             "turbidity,\n" +
 21             "permanganate_index,\n" +
 22             "cod,\n" +
 23             "five_day_bod,\n" +
 24             "ammonia,\n" +
 25             "total_phosphorus,\n" +
 26             "total_nitrogen,\n" +
 27             "cu,\n" +
 28             "zn,\n" +
 29             "fluoride,\n" +
 30             "selenium,\n" +
 31             "arsenic,\n" +
 32             "hg,\n" +
 33             "cadmium,\n" +
 34             "hexavalent_chrome,\n" +
 35             "pb,\n" +
 36             "cyn,\n" +
 37             "volatile_phenol,\n" +
 38             "petroleum,\n" +
 39             "anion_active_agent,\n" +
 40             "sulphide,\n" +
 41             "data_time,\n" +
 42             "create_time,\n" +
 43             "update_time\n" +
 44             ")\n" +
 45             "VALUES\n" +
 46             "(#{item.id},\n" +
 47             "#{item.sectionName},\n" +
 48             "#{item.sectionCode},\n" +
 49             "#{item.provinceName},\n" +
 50             "#{item.provinceCode},\n" +
 51             "#{item.cityName},\n" +
 52             "#{item.cityCode},\n" +
 53             "#{item.basinName},\n" +
 54             "#{item.riverName},\n" +
 55             "#{item.result},\n" +
 56             "#{item.analyseItem},\n" +
 57             "#{item.waterQuality},\n" +
 58             "#{item.waterTemperature},\n" +
 59             "#{item.ph},\n" +
 60             "#{item.dissolvedOxygen},\n" +
 61             "#{item.conductivity},\n" +
 62             "#{item.turbidity},\n" +
 63             "#{item.permanganateIndex},\n" +
 64             "#{item.cod},\n" +
 65             "#{item.fiveDayBod},\n" +
 66             "#{item.ammonia},\n" +
 67             "#{item.totalPhosphorus},\n" +
 68             "#{item.totalNitrogen},\n" +
 69             "#{item.cu},\n" +
 70             "#{item.zn},\n" +
 71             "#{item.fluoride},\n" +
 72             "#{item.selenium},\n" +
 73             "#{item.arsenic},\n" +
 74             "#{item.hg},\n" +
 75             "#{item.cadmium},\n" +
 76             "#{item.hexavalentChrome},\n" +
 77             "#{item.pb},\n" +
 78             "#{item.cyn},\n" +
 79             "#{item.volatilePhenol},\n" +
 80             "#{item.petroleum},\n" +
 81             "#{item.anionActiveAgent},\n" +
 82             "#{item.sulphide},\n" +
 83             "#{item.dataTime},\n" +
 84             "#{item.createTime},\n" +
 85             "#{item.updateTime}\n" +
 86             " ) ON conflict (section_name, city_name, data_time) DO\n" +
 87             "UPDATE \n" +
 88             "SET \n" +
 89             "water_quality=#{item.waterQuality},\n" +
 90             "water_temperature=#{item.waterTemperature},\n" +
 91             "ph=#{item.ph},\n" +
 92             "dissolved_oxygen=#{item.dissolvedOxygen},\n" +
 93             "conductivity=#{item.conductivity},\n" +
 94             "turbidity=#{item.turbidity},\n" +
 95             "permanganate_index=#{item.permanganateIndex},\n" +
 96             "cod=#{item.cod},\n" +
 97             "five_day_bod=#{item.fiveDayBod},\n"+
 98             "ammonia=#{item.ammonia},\n"+
 99             "total_phosphorus=#{item.totalPhosphorus},\n" +
100             "total_nitrogen=#{item.totalNitrogen},\n"+
101             "cu=#{item.cu},\n"+
102             "zn=#{item.zn},\n"+
103             "fluoride=#{item.fluoride},\n"+
104             "selenium=#{item.selenium},\n"+
105             "arsenic=#{item.arsenic},\n"+
106             "hg=#{item.hg},\n"+
107             "cadmium=#{item.cadmium},\n"+
108             "hexavalent_chrome=#{item.hexavalentChrome},\n"+
109             "pb=#{item.pb},\n"+
110             "cyn=#{item.cyn},\n"+
111             "volatile_phenol=#{item.volatilePhenol},\n"+
112             "petroleum=#{item.petroleum},\n"+
113             "anion_active_agent=#{item.anionActiveAgent},\n"+
114             "sulphide=#{item.sulphide},\n"+
115             "data_time=#{item.dataTime},\n"+
116             "create_time=#{item.createTime},\n"+
117             "update_time=#{item.updateTime}\n"+
118             "</foreach>" +
119             "</script>"})
120     void insertOrUpdateBatch(@Param("list") List<ScWaterDO> list);

 

posted @ 2022-12-27 17:25  云翊宸  阅读(1478)  评论(0)    收藏  举报