batchUpdate

第一种方式

    
                 update aa   set                 a=#{fptm},                b=#{csoftrain}            where c in             
     
                      #{item}            
       
    

但是这种方式修改的字段值都是一样的。

第二种方式

修改数据库连接配置：&allowMultiQueries=true

比如：jdbc:mysql://192.168.1.236:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

                        
     
                        update test                   
      
                           test=${item.test}+1                  
                        where id = ${item.id}           
                   
    

这种方式，可以一次执行多条SQL语句

batchInsert

1、mybatis的官方写法

public void batchInsert1(List
    
      poilist) throws Exception {      SqlSession sqlSession = sqlSessionFactory.getObject().openSession(ExecutorType.BATCH);      PoiMapper pmapper = sqlSession.getMapper(PoiMapper.class);      try {          for (Poi poi : poilist) {              pmapper.insertPoi(poi);          }          sqlSession.commit();      } finally {          sqlSession.close();      }  }
    

 其中Poi是一个bean。PoiMapper定义：

public interface PoiMapper {        @Insert("insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values (#{tag}, GeomFromText(#{point}), #{poiid}, #{meshid}, #{owner}, #{featcode}, #{sortcode}, #{namec}, #{namee}, #{namep}, #{names}) ")      public void insertPoi(Poi poi);  }

2、利用mysql特性，拼写insert sql。

public void batchInsert2(List
    
      poilist) throws Exception {      SqlSession sqlSession = sqlSessionFactory.getObject().openSession(ExecutorType.BATCH);      try {          sqlSession.insert("com.emg.trans.mapper.batchMapper.batchInsert", poilist);          sqlSession.commit();      } finally {          sqlSession.close();      }  }
    

 “com.emg.trans.mapper.batchMapper.batchInsert”在mybatis的xml中定义的sql，定义如下：

        
     
            insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values      
      
                  (#{poi.tag}, GeomFromText(#{poi.point}), #{poi.poiid}, #{poi.meshid}, #{poi.owner}, #{poi.featcode}, #{poi.sortcode}, #{poi.namec}, #{poi.namee}, #{poi.namep}, #{poi.names})      
          
       
    

3、利用spring的事务，直接执行插入操作。

@Transactional("dbTransaction")  public void batchInsert3(List
    
      poilist) throws Exception {      for (Poi poi : poilist) {          apmapper.insertPoi(poi);      }  }
    

推荐 2 > 3 > 1

Spring-JDBC的三种实现

A、用spring事务执行插入操作

@Transactional("dbTransaction")  public void batchInsertJDBC1(List
    
      poilist) throws DataAccessException {      String sql = "insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values (?, GeomFromText(?), ?, ?, ?, ?, ?, ?, ?, ?, ?)";      for (Poi poi : poilist) {          Object[] args = {poi.getTag(), poi.getPoint(), poi.getPoiid(), poi.getMeshid(), poi.getOwner(), poi.getFeatcode(), poi.getSortcode(), poi.getNamec(),                  poi.getNamee(), poi.getNamep(), poi.getNames()};          jdbcTemplate.update(sql, args);       }  }
    

B、用spring事务和springjdbc的batchUpdate

@Transactional("dbTransaction")  public void batchInsertJDBC2(List
    
      poilist) throws DataAccessException {      String sql = "insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values (?, GeomFromText(?), ?, ?, ?, ?, ?, ?, ?, ?, ?)";      List
     
       batchArgs = new ArrayList
      
       ();      for (Poi poi : poilist) {          Object[] args = {poi.getTag(), poi.getPoint(), poi.getPoiid(), poi.getMeshid(), poi.getOwner(), poi.getFeatcode(), poi.getSortcode(), poi.getNamec(),                  poi.getNamee(), poi.getNamep(), poi.getNames()};          batchArgs.add(args);      }      jdbcTemplate.batchUpdate(sql, batchArgs);  }
      
     
    

C、用spring事务，利用mysql特性，拼写insert sql

@Transactional("dbTransaction")  public void batchInsertJDBC3(List
    
      poilist) throws DataAccessException {      StringBuffer sqlbuf = new StringBuffer()          .append("insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values ");      MessageFormat form = new MessageFormat("(''{0}'', GeomFromText(''{1}''), ''{2}'', ''{3}'', ''{4}'', ''{5}'', ''{6}'', ''{7}'', ''{8}'', ''{9}'', ''{10}''),");      for (Poi poi : poilist) {          Object[] args = {poi.getTag(), poi.getPoint(), poi.getPoiid(), poi.getMeshid(), poi.getOwner(), poi.getFeatcode(), poi.getSortcode(), poi.getNamec().replaceAll("'", "\\\\'"),                  poi.getNamee().replaceAll("'", "\\\\'"), poi.getNamep().replaceAll("'", "\\\\'"), poi.getNames().replaceAll("'", "\\\\'")};          sqlbuf.append(form.format(args));      }      String sql = sqlbuf.toString();      sql = sql.substring(0, sql.length()-1);      jdbcTemplate.update(sql);  }
    

mybatis.1最慢。而且慢很多，很多。

mybatis.2很快。是mybatis中最快的。 mybatis.3比2慢一点。 jdbc.A比B稍快，两者差不多，和mybatis.3也在伯仲之间。 jdbc.C最快，比其他5种都快。

大排行，从用时少到用时多：jdbc.C < mybatis.2 < jdbc.A < jdbc.B , mybatis.3 << mybatis.1