使用mybatis 批量插入

                                                         

                                                       使用mybatis 批量插入

(1.1) 使用Statement 批量插入
public void batchInsertJdbc1()throws Exception{
  DataSource ds = (DataSource)SpringContextHolder.getBean("dataSource");
  Connection conn = ds.getConnection();
  Statement st =conn.createStatement();
  String sql = "insert into user(userid,username,birthday,salary) values('3000','武二郞',now(),8000);";
  for(int i=0;i<5000;i++){
   st.addBatch(sql);
  }
  st.executeBatch();
}

(1.2) 使用PreparedStatement 批量插入，速度比1.1快
private void jdbc2()throws Exception{
  Connection conn = DBUtil.getConnection();
  String sql = "insert into user values (?,?,?,?)";
  PreparedStatement st =conn.prepareStatement(sql);
  for(int i=0;i<5000;i++){
   st.setString(1, "3001");
   st.setString(2, "武二郞");
   st.setDate(3, null);
   st.setDouble(4, 9000);
   st.addBatch();
  }
  st.executeBatch();
  System.out.println(new Date());
  st.close();
  conn.close();
 }

(1.3) 使用mybatis 批量插入 ， 速度超快
分批插入，每批1000行

public void batchInsert(List<UserPO> list){
  int len = list.size();
  int mod =1000;
  int n = len%mod==0? len/mod:len/mod + 1;
  int start = 0;
  int pos = 0;
  int k =mod;
  List subList = null;
  for(int i=0;i<n;i++){
   if(len/(i+1) < mod){
    k=len%mod;
   }
   pos +=k;
   subList = list.subList(start, pos);
   userDao.batchInsert(subList);
   start+=mod;
  }
 }

public void batchInsert(List<UserPO> list){
  this.insert("cn.demo.dao.IUserDao.batchInsert", list);
 }
 
<insert id="batchInsert" parameterType="java.util.List" >
  insert into user(userid,username)
  values
  <foreach item="o" collection="list" index="ind" separator=",">
   (#{o.userId},#{o.userName})
  </foreach>
 </insert>
 
(1.4) 通过SqlSession批量插入
public void batchInsertJdbc3(List<UserPO> list)throws Exception{
  log.info(this.getClass()+" batchInsertJdbc3");
  //新获取一个模式为BATCH，自动提交为false的session
  //如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出
  SqlSession session = this.getSqlSessionTemplate().getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
  UserPO user = null;
  try{
  for(int i=0;i<list.size();i++){
   user = list.get(i);
   session.insert("cn.demo.dao.IUserDao.insertUser", user);
   if ((i+1) % 1000 == 0 || i == list.size() - 1) {
    //log.info(this.getClass()+" batchInsertJdbc3 commit " + i);
                //手动每1000个一提交，提交后无法回滚
    session.commit();
                //清理缓存，防止溢出
                session.clearCache();
            }
  }
  }catch(Exception e){
   session.rollback();
   e.printStackTrace();
  }
  finally{
   session.close();
  }
 }