myybatis 常用开发注解（批量插入）

1. 批量插入

1.1 注解形式的批量插入（一）

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

注意：tableName、coulmNoId需要自己定义

• 快捷工具

"(#{item.ages},#{item.password})"的生成，可以使用如下的类：

/**
 * 描述：接口文档，帮助类
 * @author uusao
 * @create 2018-03-24 10:16
 **/
public class TestOnlyByJunit4 {


    /**
     * 暴力反射，获取类的私有字段
     */
    @Test
    public void getProperties() {
        System.out.println(getValues(User.class, false));
    }

    /**
     * 多条数据查询  帮助类
     *
     * @param clazz
     * @param containId
     * @return
     */
    public static String getValues(Class clazz, boolean containId) {
        StringBuilder values = new StringBuilder();
        values.append("\"(");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            //System.out.println(field.getName());
            String propertyName = field.getName();
            if (propertyName.equals("id")) {
                if (!containId) { //不包含id
                    continue;
                }
            }
            String value = "#{item." + propertyName + "}" + ",";
            if (fields[fields.length - 1].getName().equals(propertyName)) {
                value = "#{item." + propertyName + "}";
            }

            values.append(value);
        }
        values.append(")\"");
        //System.out.println(values.toString());
        return values.toString();
    }

}

1.2 使用注解方式(二)

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

/*UserDAOProvider.java*/
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("(id, name) ");  
        sb.append("VALUES ");  
        MessageFormat mf = new MessageFormat("(null, #'{'list[{0}].name})");  
        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 (null, #{list[0].name}), (null, #{list[1].name})[,(null, #{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);

3. 源码位置

github-源码下载地址