Mybatis积累(5)：注解实现批量插入

目录

1. 批量插入

1.1 @Insert批量插入

1.2 @InsertProvider批量插入

---

1. 批量插入

1.1 @Insert批量插入

@Repository
public interface UserMapper {
     public String tableName = "user";
     public String column = "username, password";
      
  @Insert("<script> " +
            "insert into " + tableName +
            "(" + column + ") " +
            "values " +
            "<foreach collection=\"items\" index=\"index\" item=\"item\" separator=\",\"> "
            +
            "(#{item.username},#{item.password})"
            +
            "</foreach> " +
            "</script>")
    int batchSave(@Param("items") List<User> items);
        
}

1.2 @InsertProvider批量插入

public interface UserDAO {  
    @InsertProvider(type = UserDAOProvider.class, method = "insertAll")  
    void insertAll(@Param("list") List<User> list);  
}  

public class UserDAOProvider {  
    public String insertAll(Map map) {  
        List<User> users = (List<User>) map.get("list");  
        StringBuilder sb = new StringBuilder();  
        sb.append("INSERT INTO User ");  
        sb.append("(name, age) ");  
        sb.append("VALUES ");  
    MessageFormat mf = new MessageFormat(#'{'list[{0}].name},#'{'list[{0}].age})");  
        for (int i = 0; i < users.size(); i++) {  
            sb.append(mf.format(new Object[]{i}));  
            if (i < users.size() - 1) {  
                sb.append(",");  
            }  
        }  
        return sb.toString();  
    }  
} 

MyBatis会把UserDAO的insertAll方法中的List类型的参数存入一个Map中, 默认的key是”list”, 可以用@Param注解自定义名称, MyBatis在调用@InsertProvide指定的方法时将此map作为参数传入, 所有代码中使用List users = (List) map.get(“list”);获取list参数. 可以从代码中看出生成的SQL语句大致为:

INSERT INTO User (id, name) VALUES (#{list[0].age, #{list[0].name}), (#{list[1].age, #{list[1].name})[,(#{list[i].age #{list[i].name})]  

1.3 使用xml配置

<?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="mapper.AttachmentTableMapper">
    <insert id="insertByBatch" parameterType="java.util.List">
        insert into attachment_table (name, logID,url)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name,jdbcType=VARCHAR}, #{item.logid,jdbcType=INTEGER},#{item.url,jdbcType=LONGVARCHAR})
        </foreach>
    </insert>
</mapper>

2. 批量更新Mapper编写（注解形式）

 @Insert("<script> " +
            "insert into " + tableName +
            "(" + column+ ") " +
            "values " +
            "<foreach collection=\"items\" index=\"index\" item=\"item\" separator=\",\"> " +
            "(#{item.id},#{item.watchCount},#{item.collectCount},#{item.activityId})" +
            "</foreach> " +
            "ON DUPLICATE KEY UPDATE " +
            "id = VALUES(id)," +
            "watch_count = VALUES(watch_count), " +
            "collect_count = VALUES(collect_count), " +
            "activity_id = VALUES(activity_id) " +
            "</script>")
    Integer batchUpdate(@Param("items") List<ActivityStatistics> items);