通过动态SQL方式实现
通过动态SQL方式,Executor的类型不需要特别设置,用默认的SIMPLE就可以。
具体步骤如下:
第一步:定义Mapper映射文件和接口类
映射文件中定义动态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));
cityList.add(city);
}
System.out.println("插入行数:" + cityDao.insertByList(cityList));
//迭代Map类型参数
Map<String,Integer> map = new HashMap<>();
for(int i = 10;i < 20 ;i++) {
map.put(arr[random.nextInt(arr.length)] + "_" + (i + 1),(i + 1));
}
System.out.println("插入行数:" + cityDao.insertByMap(map));
}
}
通过将Executor类型设置为BATCH实现
此种方式需要将Executor类型设置为BATCH,而在映射文件中不需要拼接批量SQL,用普通单条插入的SQL即可。具体步骤如下:
第一步:定义映射文件和映射接口类
映射文件:
<insert id="insert" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
insert into student(sname, age, gender, nick_name) values (#{sname}, #{age}, #{gender}, #{nickName})
</insert>
映射接口类:
public interface StudentMapper {
int insert(Student student);
}
第二步:调用
/**
* 用Executor为Batch方式完成批量插入
*/
@Test
public void testBatchInsertStudentByExectorTypeBatch() {
SqlSession sqlSession = MybatisUtil.getSessionFactory().openSession(ExecutorType.BATCH,false);
try {
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
for(Student student : studentList) {
studentMapper.insert(student);
}
// 提交暂存在JDBC驱动中的批量语句
sqlSession.flushStatements();
// 提交事务
sqlSession.commit();
log.info("成功批量插入: " + studentList.size()+"条数据");
// 从本批次插入数据中随机抽查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">
<insert id="insertCity" parameterType="com.lanou3g.mybatis.bean.City">
insert into city(cname) values (#{cname});
</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 {
int insertCity(City city);
}
程序运行入口
public class App {
public static void main(String[] args) throws IOException {
//1、读入配置文件
String confPath = "mybatis_conf.xml";
InputStream is = Resources.getResourceAsStream(confPath);
//2、构建SqlSessionFactory(用于获取sqlSession)
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"citydao");
//3、获取sqlSession对象(用于具体的RUID)
SqlSession sqlSession = sessionFactory.openSession(ExecutorType.BATCH,false);
//4、具体的RUID
CityDao cityDao = sqlSession.getMapper(CityDao.class);
String[] arr = new String[]{"金","木","水","火","土"};
Random random = new Random();
List<City> cityList = new ArrayList<>();
for(int i = 0;i < 100 ;i++) {
City city = new City();
city.setCname(arr[random.nextInt(arr.length)] + "_" + (i + 1));
cityList.add(city);
}
long start = System.currentTimeMillis();
int rows = 0;
int batchSize = 10;
int count = 0;
for (City city : cityList) {
cityDao.insertCity(city);
count ++;
if ( count % batchSize == 0){
rows += flushStatement(sqlSession);
}
}
sqlSession.flushStatements();
sqlSession.commit();
sqlSession.close();
log.info("插入数据行数:" + rows+", 耗时:" + (System.currentTimeMillis() - start));
}
/**
* 用于计算插入到数据库的数据行数
* @param sqlSession
* @return
*/
public static int flushStatement(SqlSession sqlSession) {
int effectRows = 0;
List<BatchResult> batchResults = sqlSession.flushStatements();
if(batchResults == null || batchResults.size() < 1) {
return effectRows;
}
int[] effectArr = batchResults.get(0).getUpdateCounts();
for(int effectRow : effectArr) {
effectRows += effectRow;
}
return effectRows;
}
}