关于mybatis批量插入及返回自增主键

最近做需求，遇到了批量插入需要返回自增主键的内容，网上内容比较乱，整理测试了一下，希望能帮助到你。

1. 数据准备

1.1 用户表POJO

@Data
@Builder
public class UserEntity implements Serializable{
    private Integer id;  // 自增主键
    private String name;
    private Integer age;
    private String sex;
}

1.2 Mybatis 填充自增键值概念

useGeneratedKeys	是否需要取出内部使用的主键
keyProperty	指定POJO的列名
keyColumn	指定数据库里的列名，一般不需要指定

<insert id="insert1" useGeneratedKeys="true" keyProperty="id">
    insert into tb_user (name, age, sex)
    values (#{name}, #{age}, #{sex})
</insert>

2. 单条插入

keyProperty="entity.id" #{sex} keyProperty="id"@Mapper
public interface UserMapper {
    void insert1(UserEntity userEntity);
    void insert2(@Param("entity") UserEntity userEntity);
}


<?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.tao.mybatis.mapper.UserMapper">

    <sql id="Base_Column_List">
        id AS id,name AS name,age AS age,sex AS sex
    </sql>

    <insert id="insert1" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user (name, age, sex)
        values (#{name}, #{age}, #{sex})
    </insert>
    
    <insert id="insert2" useGeneratedKeys="true" keyProperty="entity.id">
        insert into tb_user (name, age, sex)
        values (#{entity.name}, #{entity.age}, #{entity.sex})
    </insert>

</mapper>

• 如果 mapper 接口参数中未写 @Param 注解，mapper xml 中可以直接使用属性，如 keyProperty="id" #{sex}
• 如果 mapper 接口参数中写明 @Param 注解，mapper xml 中须用(参数名.属性)，如 keyProperty="entity.id" #{entity.sex}

测试：

@Slf4j
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(DemoApplication.class, args);
        UserEntity user1 = UserEntity.builder().name("张三").age(18).sex("男").build();
        UserEntity user2 = UserEntity.builder().name("王五").age(19).sex("女").build();
        UserMapper userMapper = context.getBean(UserMapper.class);
        userMapper.insert1(user1);
        userMapper.insert2(user2);
        log.info("插入数据结束！user1.id => {}, user2.id => {}", user1.getId(), user2.getId());
    }
}

console: 插入数据结束！user1.id => 41, user2.id => 42

3. 批量插入

@Mapper
public interface UserMapper {
    void insertBatch1(@Param("list") List<UserEntity> list); // 等价于 void insertBatch1(List<UserEntity> list);
    void insertBatch2(@Param("sex") String sex, @Param("list") List<UserEntity> list);
}

<?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.tao.mybatis.mapper.UserMapper">
    <sql id="Base_Column_List">
        id AS id,name AS name,age AS age,sex AS sex
    </sql>
    <insert id="insertBatch1" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user (name, age, sex) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.name},#{item.age},#{item.sex})
        </foreach>
    </insert>
    <insert id="insertBatch2" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user (name, age, sex) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.name},#{item.age},#{sex})
        </foreach>
    </insert>
</mapper>

• 如果是批量插入数据，并要求返回自增主键，mapper 接口中 POJO列表参数名只能是（collection、list、array)
• mybatis 版本需要大于3.3.0

// 来源：org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator#getParameters

    private Collection<Object> getParameters(Object parameter) {
        Collection<Object> parameters = null;
        if (parameter instanceof Collection) {
            parameters = (Collection)parameter;
        } else if (parameter instanceof Map) {
            Map parameterMap = (Map)parameter;
            if (parameterMap.containsKey("collection")) {
                parameters = (Collection)parameterMap.get("collection");
            } else if (parameterMap.containsKey("list")) {
                parameters = (List)parameterMap.get("list");
            } else if (parameterMap.containsKey("array")) {
                parameters = Arrays.asList(parameterMap.get("array"));
            }
        }

        if (parameters == null) {
            parameters = new ArrayList();
            parameters.add(parameter);
        }

        return parameters;
    }

测试：

@Slf4j
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(DemoApplication.class, args);
        UserEntity user1 = UserEntity.builder().name("张三").age(18).sex("男").build();
        UserEntity user2 = UserEntity.builder().name("王五").age(19).sex("女").build();
        UserMapper userMapper = context.getBean(UserMapper.class);

        userMapper.insertBatch1(Arrays.asList(user1, user2));
        log.info("插入数据结束！user1.id => {}, user2.id => {}", user1.getId(), user2.getId());

    }
}

console: 插入数据结束！user1.id => 48, user2.id => 49

4. 附录

https://github.com/mybatis/mybatis-3/pull/350

https://github.com/mybatis/mybatis-3/pull/547