mybatis 批量insert , update

最新推荐文章于 2023-06-06 10:46:32 发布

原创最新推荐文章于 2023-06-06 10:46:32 发布 · 899 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mybatis

mybaits 专栏收录该内容

1 篇文章

订阅专栏

本文介绍使用MyBatis进行批量数据插入和更新的方法。通过定义映射类MealInfo及对应的数据表meal_info，利用mapper接口实现批量操作。XML映射文件中详细展示了insertList和updateList方法的具体实现。

数据表：meal_info

映射类：MealInfo

批量插入或更新的集合：List<MealInfo> mealInfos

mapper方法：

mealInfoMapper.insertList(mealInfos);

mealInfoMapper.updateList(mealInfos);

映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mapper.MealInfoMapper" >
  <resultMap id="BaseResultMap" type="com.entity.MealInfo" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="gender" property="gender" jdbcType="VARCHAR" />
    <result column="energy" property="energy" jdbcType="DOUBLE" />
    <result column="protein" property="protein" jdbcType="DOUBLE" />
    <result column="fat" property="fat" jdbcType="DOUBLE" />
    <result column="cho" property="cho" jdbcType="DOUBLE" />
    <result column="createDate" property="createdate" jdbcType="TIMESTAMP" />
    <result column="updateDate" property="updatedate" jdbcType="TIMESTAMP" />
  </resultMap>
     
  <update id="updateList" parameterType="java.util.List">
	  update meal_info set
	    energy=
	  <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
	      when #{item.id,jdbcType=DECIMAL} then #{item.energy,jdbcType=VARCHAR}
	  </foreach>
	  ,protein=
	  <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
	      when #{item.id,jdbcType=DECIMAL} then #{item.protein,jdbcType=VARCHAR}
	  </foreach>
	  ,fat=
	  <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
	      when #{item.id,jdbcType=DECIMAL} then #{item.fat,jdbcType=VARCHAR}
	  </foreach>
	  ,cho=
	  <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
	      when #{item.id,jdbcType=DECIMAL} then #{item.cho,jdbcType=VARCHAR}
	  </foreach>
	  where ID in
	  <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
	      #{item.id,jdbcType=DECIMAL}
	  </foreach>
  </update>
  
  <insert id="insertList" parameterType="java.util.List">  
    <selectKey resultType="long" keyProperty="id" order="AFTER">  
        SELECT  
        LAST_INSERT_ID()  
    </selectKey>  
    insert into meal_info
    (id, name, gender,energy,protein,fat,cho,createDate, updateDate)  
    values
    <foreach collection="list" item="item" index="index" separator="," >  
        (#{item.id},#{item.name},#{item.gender},#{item.energy},#{item.protein},
        #{item.fat},#{item.cho},now(),now())  
    </foreach>  
  </insert>