mybatis批量操作Oracle数据

最新推荐文章于 2021-12-31 18:00:29 发布

人在^O^旅途

最新推荐文章于 2021-12-31 18:00:29 发布

阅读量966

点赞数

分类专栏： mybatis 文章标签： mybatis java

本文链接：https://blog.csdn.net/xp_lx1/article/details/72843382

版权

mybatis 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一、批量插入数据bean对象数据

实体类如下：

/**
	 * 标签ID（主键）
	 */
	private String tag_Id;
	/**
	 * 进出库时间
	 */
	private Date io_Time;
	/**
	 * 进出库状态(0:未授权  1：授权)
	 */
	private String status;

接口如下：

public int insertAllTreasuryInfo(List<IoTreasury> list);

mybatis.xml文件

<insert id="insertAllTreasuryInfo" parameterType="java.util.List">
		insert into t_io_treasury
		(tag_Id, io_Time, status)
		<foreach close=")" collection="list" item="item" index="index"
			open="(" separator="union">
			select
			#{item.tag_Id},#{item.io_Time},#{item.status}
			from dual
		</foreach>
	</insert>

二、批量插入String字符类型

<insert id="insertAllTreasuryInfo" parameterType="java.util.List">
		insert into t_io_treasury
		(tag_Id)
		<foreach close=")" collection="list" item="item" index="index"
			open="(" separator="union">
			select
			#{item}
			from dual
		</foreach>
	</insert>

其中#{item}使用的是item而不是#{tag_Id}
如果是bean对象就得使用item.tag_Id。

<insert id="insert" parameterType="com.b2b.cash.os.modules.entity.TbShopUpdatepriceErrorTemp">
  insert into tb_shop_updatePrice_error_temp (shop_commondity_id, name, unit, shop_id, shopSell_price)
  values
  <foreach collection="list" item="item" separator="," >
    (#{item.shopCommondityId,jdbcType=BIGINT}, #{item.name,jdbcType=VARCHAR},
    #{item.unit,jdbcType=VARCHAR}, #{item.shopId,jdbcType=BIGINT},
    #{item.shopsellPrice,jdbcType=DECIMAL})
  </foreach>
</insert>

接口：int insert(List<TbShopUpdatepriceErrorTemp> list);

注意：不能使用close=“)”和open“)”。

三、批量更新

批量更新（不含动态语言）

<update id="UpdateBatchTreasuryInfo" parameterType="java.util.List">
UPDATE t_io_treasury SET io_Time = sysdate WHERE tag_id IN
<foreach collection="list" index="index" item="idItem" open="("
separator="," close=")">
#{idItem.tag_Id}
</foreach>

</update>

批量更新动态

<foreach collection="list" item="item" index="index" separator=";">
  update tb_commodity_price
  <set>
    <if test="item.commodityId != null">
      commodity_id = #{item.commodityId,jdbcType=INTEGER},
    </if>
    <if test="item.receivableStyle != null">
      receivable_style = #{item.receivableStyle,jdbcType=INTEGER},
    </if>
    <if test="item.receivablePrice != null">
      receivable_price = #{item.receivablePrice,jdbcType=DECIMAL},
    </if>
    <if test="item.isDelete != null">
      is_delete = #{item.isDelete,jdbcType=CHAR},
    </if>
  </set>
  <where>
    <if test="item.commodityId !=null">
      AND commodity_id = #{item.commodityId,jdbcType=INTEGER}
    </if>
    <if test="item.receivableStyle !=null">
      AND receivable_style = #{item.receivableStyle,jdbcType=INTEGER}
    </if>
  </where>
</foreach>

四、批量删除

<delete id="deleteBatchTreasuryInfo" parameterType="java.util.List">
DELETE FROM t_io_treasury where tag_id in
<foreach collection="list" index="index" item="item" open="("
separator="," close=")">
#{item}
</foreach>
</delete>

五、批量查询

<select id="selectBatchTreasuryInfo" parameterType="java.util.List"
resultType="IoTreasury">
select tag_id, io_time, status, authorized, auth_time, username from
t_io_treasury where tag_id in
<foreach collection="list" index="index" item="item" open="("
separator="," close=")">
#{item}
</foreach>
</select>

查询详细介绍：http://fireinjava.iteye.com/blog/1779420

批量操作详细介绍：http://blog.csdn.net/hardworking0323/article/details/51105218