数据库批量更新，批量插入

 

java代码

/**
	 * 修改冻结数量
	 * 
	 * @param applyTbl
	 * @return
	 */
	int updateApplyNumber(List<Object> applyTbl);

	/**
	 * 添加到库存表
	 * 
	 * @param applyTbl
	 * @return
	 */
	int saveAdvStock(List<Object> applyTbl);

List<Object> updateList = new ArrayList<>();
		for (ApplyConfirmationTbl tempObj : applyNumber) {
			// 地区剩余播放量
			int as = tempObj.getAreaSurplus();
			// 套餐所需每天播放量
			int code = tempObj.getCode();
			// 投放地区id
			int areaid = tempObj.getAreaId();
			// 地区的视频播放总量
			int advn = tempObj.getAdvnumber();
			// 冻结量
			int fzb = tempObj.getFreezenumber();
			Long showdate = tempObj.getShowDate();
			// 播放占比
			double weight = new BigDecimal((float) code / advn).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue();
			if (as < code)
				return -1;
			else
				// 添加冻结量
				tempObj.setFreezenumber(code + fzb);
			// 地址id
			tempObj.setAreaId(areaid);
			// 添加开始时间
			tempObj.setShowDate(showdate);
			// 占比
			tempObj.setWeight(weight);
			tempObj.setStatus(1);
			tempObj.setApplynumber(code);
			tempObj.setRestnumber(code);
			updateList.add(tempObj);

		}
		// 修改冻结量
		applyConfirmationMapper.updateApplyNumber(updateList);
		// 添加到库存表
		applyConfirmationMapper.saveAdvStock(updateList);

sql语句

<update id="updateApplyNumber" parameterType="java.util.List">
		<foreach collection="list" separator=";" item="applyTbl">
			UPDATE
			adv_source_tbl
			SET
			freezenumber =#{applyTbl.freezenumber},
			updated_time
			=#{applyTbl.updatedTime}
			WHERE
			area_id = #{applyTbl.areaId}
			and
			show_date=#{applyTbl.showDate}
		</foreach>
	</update>

<insert id="saveAdvStock" parameterType="java.util.List">
		<foreach collection="list" separator=";" item="applyTbl">
			INSERT
			INTO
			adv_stock_tbl(
			<if
				test="applyTbl.updatedTime != -1 and applyTbl.updatedTime != null ">
				updated_time,
			</if>
			<if test="applyTbl.deleted != -1 and applyTbl.deleted != null ">
				deleted,
			</if>
			<if
				test="applyTbl.deletedTime != -1 and applyTbl.deletedTime != null ">
				deleted_time,
			</if>
			applynumber,
			restnumber,
			source_id,
			status,
			video_id,
			show_date,
			weight,
			created_time
			)
			VALUES(
			<if
				test="applyTbl.updatedTime != -1 and applyTbl.updatedTime != null ">
				#{applyTbl.updatedTime},
			</if>
			<if test="applyTbl.deleted != -1 and applyTbl.deleted != null ">
				#{applyTbl.deleted},
			</if>
			<if
				test="applyTbl.deletedTime != -1 and applyTbl.deletedTime != null ">
				#{applyTbl.deletedTime},
			</if>
			#{applyTbl.applynumber},
			#{applyTbl.restnumber},
			#{applyTbl.id},
			#{applyTbl.status},
			#{applyTbl.videoId},
			#{applyTbl.showDate},
			#{applyTbl.weight},
			#{applyTbl.createdTime}

			)
		</foreach>

	</insert>