基于 MyBatis 执行 SQL 批量操作的插件。
已上传到**仓库，可直接引用， 1.0.0 适配 MyBatis 3.0.*-3.1.*， 1.1.0 适配 3.2.*-3.4.1 。
<dependency>
<groupId>net.coderbee</groupId>
<artifactId>mybatis-batch</artifactId>
<version>1.1.0</version>
</dependency>MyBatis conf 配置：
<configuration>
<plugins>
<plugin interceptor="net.coderbee.mybatis.batch.BatchParameterHandler" />
<plugin interceptor="net.coderbee.mybatis.batch.BatchStatementHandler" />
</plugins>
</configuration>测试表的 SQL 语句：
create table simple_user (
id int not null generated by default as identity (start with 1000),
name varchar(255) not null,
email varchar(255) not null,
primary key (id)
);Mapper XML 编写说明，需要把 parameterType 指定为 net.coderbee.mybatis.batch.BatchParameter，如下：
<update id="updateBatch" parameterType="net.coderbee.mybatis.batch.BatchParameter">
update simple_user set name = name || #{} where id = #{id}
</update>
<sql id="insertSql">
insert into simple_user (
name,
email
) values (
#{name},
#{email}
)
</sql>
<insert id="batchInsertByMap" useGeneratedKeys="true"
keyProperty="id" parameterType="net.coderbee.mybatis.batch.BatchParameter">
<include refid="insertSql" />
</insert>调用示例见 TestBatch.java，截取部分如下：
@SuppressWarnings("unchecked")
@Test
public void testOnHsqldb() {
SqlSession sqlSession = hsqldbSqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = buildUsers();
BatchParameter<User> users = BatchParameter.wrap(userList);
userMapper.batchInsert2Hsqldb(users);
checkResult(userList);
testBatchDelete(userMapper);
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "abc123");
map.put("email", "[email protected]");
List<Map<String, Object>> list = Arrays.asList(map);
userMapper.batchInsertByMap(
BatchParameter.<Map<String, Object>> wrap(list));
Assert.assertNotNull(((Number) map.get("id")).intValue() == 1003);
}
public void testBatchDelete(UserMapper userMapper) {
List<User> list = userMapper.selectAll();
List<User> users = buildToUpdateUsers();
int counts = userMapper.deleteBatch(BatchParameter.wrap(users));
Assert.assertTrue(counts == 2);
List<User> afterDeleteList = userMapper.selectAll();
Assert.assertTrue(list.size() - afterDeleteList.size() == 2);
}
protected List<User> buildToUpdateUsers() {
List<User> users = new ArrayList<User>();
users.add(createUser(1, "-x"));
users.add(createUser(2, "-y"));
users.add(createUser(3, "-z"));
return users;
}
protected void checkResult(List<User> userList) {
int idStart = 1000; // 自动增长的 ID 从 1000 开始
for (User user : userList) {
Assert.assertEquals(idStart++, user.getId());
}
}
protected List<User> buildUsers() {
List<User> userList = new ArrayList<User>();
userList.add(createUser("bruce.liu", "[email protected]"));
userList.add(createUser("coderbee.net", "[email protected]"));
userList.add(createUser("world", "[email protected]"));
return userList;
}
private User createUser(int id, String name) {
User u = new User();
u.setId(id);
u.setName(name);
return u;
}
private User createUser(String name, String email) {
User u = new User();
u.setName(name);
u.setEmail(email);
return u;
}注意：
- 对于批量插入且需要获取自动生成主键的，取决于数据库的实现，比如 derby 就不支持返回批量插入时生成的主键。
- 对于批量更新，Oracle 是不支持返回每一条语句所影响的具体行数的，只是返回了成功、但记录数未知的结果。
如果依赖于批量执行的具体结果，需要结合具体的数据库测试确定是否支持。
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent