阅读前,请先阅读MyBatis批量操作_MySql
jdbc.properties
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@//127.0.0.1:1521/orcl
jdbc.username=mybatis
jdbc.password=mybatis
在这里说说与mysql不同的,相同的就不再叙述了
Test方法
//通过list进行批量插入
@Test
public void batchInsertStudentWithListOracle(){
List<Student> list= new ArrayList<Student>();
for(int i = 2;i < 10;i++){
Student student = new Student();
student.setName("test" + i);
student.setId(i);
list.add(student);
}
int n=studentMapper.batchInsertStudentWithListOracle(list);
System.out.println("成功插入"+n+"条记录");
sqlSession.commit();
}
//分页批量插入
@Test
public void batchInsertStudentPageOracle(){
List<Student> list= new ArrayList<Student>();
for(int i = 0;i < 2000;i++){
Student student = new Student();
student.setName("test" + i);
student.setId(i);
list.add(student);
}
try {
saveOracle(list);
} catch (Exception e) {
e.printStackTrace();
}
}
private void saveOracle(List<Student> uidCodeList) throws Exception {
SqlSession batchSqlSession = null;
try {
batchSqlSession =SqlSessionFactoryUtil.openSession();//获取批量方式的sqlsession
int batchCount = 1000;//每批commit的个数
int batchLastIndex = batchCount - 1;//每批最后一个的下标
for(int index = 0; index < uidCodeList.size()-1;){
if(batchLastIndex > uidCodeList.size()-1){
batchLastIndex = uidCodeList.size() - 1;
batchSqlSession.insert("com.mybatis.mappers.StudentMapper.batchInsertStudentWithListOracle", uidCodeList.subList(index, batchLastIndex+1));
batchSqlSession.commit();
System.out.println("index:"+index+" batchLastIndex:"+batchLastIndex);
break;//数据插入完毕,退出循环
}else{
batchSqlSession.insert("com.mybatis.mappers.StudentMapper.batchInsertStudentWithListOracle", uidCodeList.subList(index, batchLastIndex+1)); batchSqlSession.commit();
System.out.println("index:"+index+" batchLastIndex:"+batchLastIndex);
index = batchLastIndex + 1;//设置下一批下标
batchLastIndex = index + (batchCount - 1);
}
}
}finally{
batchSqlSession.close();
}
}
@Test
public void batchUpdateStudentWithListOracle() {
logger.info("更新学生(带条件)");
List<Student> list = new ArrayList<Student>();
list.add(new Student(5, "张三aa"));
list.add(new Student(6, "李四aa"));
int n = studentMapper.batchUpdateStudentWithListOracle(list);
System.out.println("成功更新" + n + "条记录");
sqlSession.commit();
}
@Test
public void batchDeleteStudentWithListOracle() {
logger.info("通过IdList批量更新");
List<Student> list = new ArrayList<Student>();
list.add(new Student(12, null));
list.add(new Student(13, null));
int n = studentMapper.batchDeleteStudentWithListOracle(list);
System.out.println("成功删除" + n + "条记录");
sqlSession.commit();
}
对应的Mapper
<insert id="batchInsertStudentWithListOracle" parameterType="List">
insert into /*+append_values */ t_student(id,name)
<foreach collection="list" item="item" index="index"
separator="union all">
select #{item.id}, #{item.name} from dual
</foreach>
</insert>
<!-- open="begin" close=";end;" 要有 -->
<delete id="batchDeleteStudentWithListOracle" parameterType="java.util.List">
<span style="white-space:pre"> </span><foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
<span style="white-space:pre"> </span>DELETE FROM t_student where id=#{item.id}
</foreach>
</delete>
<!-- open="begin" close=";end;" 要有 -->
<update id="batchUpdateStudentWithListOracle" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";" >
UPDATE t_student
<set>
name=#{item.name}
</set>
where id=#{item.id}
</foreach>
</update>