随笔记录。亲测可用

1.批量插入

mapper

int insertBrandBusinessByExcel(List<Label> list);

.xml

  <!--批量插入-->
  <insert id="insertBrandBusinessByExcel" parameterType="com.mj.linen.pojo.label.Label">
      insert into tb_label (ecp,pid,ehco)<!--对应字段-->
      values 
      <foreach collection="list" separator="," item="label"><!--循环插入，item:别名-->
         (#{label.ecp,jdbcType=VARCHAR},#{label.pid,jdbcType=INTEGER},#{label.ehco,jdbcType=VARCHAR})
      </foreach>
  </insert>

controller：

/**
	 * 批量入库
	 * @param list
	 * @param pid
	 * @return
	 */
	@PostMapping("/insertAll")//@RequestParam 不写会报List错误
	public Object insertLabel(@RequestParam("list")List<Label> list,String ehco,int pid,String user){

postman：

相应事物层控制层就不写了都是相应的调用（我是测试），或处理下逻辑。

2.批量更新1

mapper

int batchUpdateLabelDTO(List<Label> list);

.xml

<!--批量更新(回收)-->
  <update id="batchUpdateLabelDTO" parameterType="java.util.List">
       <foreach collection="list" separator=";" item="label">
         update tb_label
         <set> 
            state = #{label.state,jdbcType=INTEGER},
            hid = #{label.hid,jdbcType=INTEGER},
            frequency = #{label.frequency,jdbcType=INTEGER}
         </set>
         <where> 
            id = #{label.id,jdbcType=INTEGER}
         </where> 
      </foreach>
  </update>

controller：

/**
	 * 批量更新
	 * @param list
	 * @param
	 * @return
	 */
	@PostMapping("/CascadeUpdate")
	public Object batchUpdateLabelDTO(@RequestBody List<Label> list){

postman:

批量更新2

mapper

int  batchUpdateResponsiblePersonnel(String responsiblepersonnelid);

.xml

 <update id="batchUpdateResponsiblePersonnel" parameterType="java.lang.String">
        update responsiblepersonnel set DataState='5' where ResponsiblePersonnelId IN
        <foreach collection="responsiblepersonnelid.split(',')" item="item" open="(" separator="," close=")">
           '${item}'
        </foreach>
    </update>

controller：

@PutMapping("/ResponsiblePerson/BatchDeleteResponsible")
    @ApiOperation(value = "批量更新", notes="")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "responsiblepersonnelid", value = "String数组逗号隔开 ", required = true, paramType = "query", dataType = "String"),
    })
    public Map updateDelResponsiblePersonnelById(String responsiblepersonnelid){

postman:

批量更新3

mapper

 int updateUserPerformance(List<TUserPerformance> list);

xml

<update id="updateUserPerformance" parameterType="com.hy.modules.hy.entity.TUserPerformance">
		update t_user_performance
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="percentage=case" suffix="end,">
				<foreach collection="list" item="item" index="index">
					<if test="item.percentage!=null">
						when user_id=#{item.userId} then #{item.percentage}
					</if>
				</foreach>
			</trim>
			<trim prefix="money =case" suffix="end,">
				<foreach collection="list" item="item" index="index">
					<if test="item.money!=null">
						when user_id=#{item.userId} then #{item.money}
					</if>
				</foreach>
			</trim>
		</trim>
		where
		performance_details_id=#{list[0].performanceDetailsId} and user_id in (
			<foreach collection="list" separator="," item="item" index="index">
				#{item.userId}
			</foreach>
		)
	</update>

解释：如果 user_id= 1 then 1  user_id= 2 then 2 （执行了三条数据更改 所以有三个when）自行理会。

上述执行SQL为 (1,2,3 为了演示而标注 )：

update t_user_performance 
set 
  percentage=case when user_id=? then 1 
				  when user_id=? then 2
				  when user_id=? then 3 end,
 money =case when user_id=? then ?
             when user_id=? then ?
             when user_id=? then ? end
 where 
     performance_details_id=? and user_id in ( ? , ? , ? ) 

3.批量查询

mapper

List<LabelDTO> selectLabelDTO(List<String> list);

.xml

  <select id="selectLabelDTO"  parameterType="java.util.List" resultMap="lable_variety_BaseResultMap">
     SELECT l.id,l.ecp,l.pid,l.frequency,l.ehco,l.state,l.hid,l.gross,l.employ,l.unused,l.account_balance,
      l.total_cost,l.created,l.updated,l.delivery_time,
      v.id v_id,v.variety_name,v.category_id,v.grade_id,v.image,v.specification,v.rental_price,v.occupancy_expenses,
     v.frequency v_frequency,v.echo,v.created v_created,v.updated v_updated
     FROM tb_label l LEFT JOIN tb_variety v ON v.id = l.pid
      WHERE l.ecp in
      <foreach collection="list" item="label" open="(" close=")" separator=",">
          #{label}<!--数组类型为对象时可调用多个字段，我的类型只是是字符串，所以没调用，像：#{label.id}-->
      </foreach>
  </select>

查询和同样上两个同样。

 

记录，方便以后查阅。