当前位置 博文首页 > 逐墨飞扬的博客：mybatis之批量插入
通过动态SQL方式，Executor的类型不需要特别设置，用默认的SIMPLE就可以。
具体步骤如下：
映射文件中定义动态SQL语句
<insert id="insertBatch" parameterType="list" useGeneratedKeys="true" keyProperty="id">
insert into student( sname, age, gender, nick_name ) values
<foreach collection="list" item="stu" index="index" separator=",">
(#{stu.sname}, #{stu.age}, #{stu.gender}, #{stu.nickName})
</foreach>
</insert>
Mapper接口
public interface StudentMapper {
int insertBatch(List<Student> studentList);
}
/**
* 使用默认的Executor类型SIMPLE，通过在Mapper文件中的<forEach>标签，拼接动态SQL，完成批量操作
*/
@Test
public void testBatchInsertStudentByForEachSqlMapper() {
SqlSession sqlSession = MybatisUtil.getSessionFactory().openSession(false);
try {
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println("影响行数： " + studentMapper.insertBatch(studentList));
// 提交事务
sqlSession.commit();
// 从本批次插入数据中随机抽查10条数据的自增id
Random random = new Random();
for(int i = 0; i < 10; i++) {
int idx = random.nextInt(studentList.size());
log.info("第"+(idx+1)+"条数据自增id: " + studentList.get(idx).getId());
}
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
jdbc.properties数据源
jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8
CityMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.CityDao">
<!-- 迭代List类型参数 -->
<insert id="insertByMap" parameterType="hashmap">
insert into city (cname,pid) values
<!-- 迭代list类型参数时，collection的值写list， 这时index就是迭代次数，item是迭代的元素 -->
<foreach collection="map" index="cname" item="pid" separator="," close=";">
(#{cname},#{pid})
</foreach>
</insert>
<!-- 迭代Map类型参数 -->
<insert id="insertByList">
insert into city (cname,pid) values
<!-- 迭代map类型参数时，collection写接口中通过@Param注解指定的map参数名称，这时index就是map的key，item就是map的value -->
<foreach collection="list" item="city" separator="," close=";">
(#{city.cname},#{city.pid})
</foreach>
</insert>
</mapper>
mybatis.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties" />
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/CityMapper.xml" />
</mappers>
</configuration>
对应数据库表的java类
@Getter
@Setter
public class City {
private Integer id;
private String cname;
private Integer pid;
@Override
public String toString() {
return "City{" +
"id=" + id +
", cname='" + cname + '\'' +
", pid=" + pid +
"}\n";
}
}
dao层接口（操作数据库）
public interface CityDao {
//迭代List类型参数
int insertByList(List<City> cityList);
//迭代Map类型参数
int insertByMap(@Param("map") Map<String,Integer> map);
}
程序运行入口
public class App {
public static void main(String[] args) throws IOException {
//1、读入配置文件
String confPath = "mybatis.xml";
InputStream is = Resources.getResourceAsStream(confPath);
//2、构建SqlSessionFactory(用于获取sqlSession)
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"development");
//3、获取sqlSession对象（用于具体的RUID)
SqlSession sqlSession = sessionFactory.openSession(true);
//4、具体的RUID
CityDao cityDao = sqlSession.getMapper(CityDao.class);
String[] arr = new String[]{"金","木","水","火","土"};
Random random = new Random();
//迭代List类型参数
List<City> cityList = new ArrayList<>();
for(int i = 0;i < 10 ;i++) {
City city = new City();
city.setCname(arr[random.nextInt(arr.length)] + "_" + (i + 1));
