工具框架用spring-batch,数据库是mysql(未做特殊优化)。
比较数据框架mybatis和spring jdbc的插入效率。
Mybatis三种实现:
1、mybatis的官方写法
public void batchInsert1(List<Poi> 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<Poi> 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,定义如下:
<mapper namespace="com.emg.trans.mapper.batchMapper"> <insert id="batchInsert" parameterType="List"> insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values <foreach collection="list" item="poi" index="index" separator="," > (#{poi.tag}, GeomFromText(#{poi.point}), #{poi.poiid}, #{poi.meshid}, #{poi.owner}, #{poi.featcode}, #{poi.sortcode}, #{poi.namec}, #{poi.namee}, #{poi.namep}, #{poi.names}) </foreach> </insert> </mapper>
3、利用spring的事务,直接执行插入操作。
@Transactional("dbTransaction") public void batchInsert3(List<Poi> poilist) throws Exception { for (Poi poi : poilist) { apmapper.insertPoi(poi); } }
apmapper是PoiMapper的实例,用@Autowired配置。
Spring-JDBC的三种实现:
A、用spring事务执行插入操作
@Transactional("dbTransaction") public void batchInsertJDBC1(List<Poi> 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<Poi> poilist) throws DataAccessException { String sql = "insert into tb_poi (tag, poi, poiid, meshid, owner, featcode, sortcode, namec, namee, namep, names) values (?, GeomFromText(?), ?, ?, ?, ?, ?, ?, ?, ?, ?)"; List<Object[]> batchArgs = new ArrayList<Object[]>(); 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<Poi> 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); }
测试一:每次处理100条,共600条。
测试二:每次处理1000条,共9000条。
直接上结论:
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
相关推荐
mybatis与spring整合时所依赖的jar包,包括: 1.Mybatis所需的jar包括: ant-1.9.6.jar ant-launcher-1.9.6.jar ... mysql-connector-java-5.1.45-bin.jar commons-dbcp2-2.2.0.jar commons-pool2-2.5.0.jar
3.2.7.jar,mybatis-spring-1.2.2.jar,mysql-connector-java-5.1.7-bin.jar,slf4j-api-1.7.5.jar,slf4j-log4j12-1.7.5.jar,spring-aop-3.2.0.RELEASE.jar,spring-aspects-3.2.0.RELEASE.jar,spring-beans-...
spring-JDBC的jar包整合,自用
SpringBoot整合mybatis-plus实现多数据源的动态切换且支持分页查询,案例以postgresql和oracle数据库为数据源,分别使用mybatis-plus分页插件和pagehelper分页插件实现分页查询。
主要介绍了Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
项目使用日志框架实现sql日志完整输出,并使用springmvc和jxls分别实现excel导出
3.2.7/mybatis-spring-1.2.2/mysql-connector-java-5.1.7-bin/slf4j-api-1.7.5/slf4j-log4j12-1.7.5/spring-aop-4.1.3.RELEASE/spring-aspects-4.1.3.RELEASE/spring-beans-4.1.3.RELEASE/spring-context-4.1.3....
mysql-connector-java-5.1.27 org.springframework.aop-3.1.1.RELEASE org.springframework.asm-3.1.1.RELEASE org.springframework.aspects-3.1.1.RELEASE org.springframework.beans-3.1.1.RELEASE org.spring...
1.0.jar aspectjweaver-1.7.1.jar commons-fileupload-1.2.2.jar commons-logging-1.1.1.jar druid-0.2.9.jar jstl-1.2.jar junit-4.11.jar mybatis-3.1.1.jar mybatis-spring-1.1.1.jar mysql-connector-...
commons-collections-3.2.1 commons-dbcp-1.1 commons-logging-1.1.3 commons-pool-1.6 mybatis-3.2.0-SNAPSHOT mybatis-spring-1.1.1 mysql-connector-java-5.1.27 org.springframework.aop-3.1.1.RELEASE org....
url: jdbc:mysql://localhost:3306/movie?characterEncoding=utf8&serverTimezone=UTC username: root password: 123456 cache: redis: #使用前缀 use-key-prefix: true #redis过期时间 time-to-live: 60s ...
mysql-connector-java-5.1.10-bin.jar slf4j-api-1.6.2.jar slf4j-log4j12-1.6.2.jar spring-aop-3.2.4.RELEASE.jar spring-aspects-3.2.4.RELEASE.jar spring-beans-3.2.4.RELEASE.jar spring-build-src-3.2.4....
spring_mvc和mybatis_jar包: spring-context-4.3.2.RELEASE.jar spring-aop-4.3.2.RELEASE.jar spring-beans-4.3.2.RELEASE.jar spring-core-4.3.2.RELEASE.jar spring-expression-4.3.2.RELEASE.jar spring-web-...
mysql-connector-java-5.1.26-bin.jar slf4j-api-1.7.5.jar slf4j-log4j12-1.7.5.jar spring-aop-3.2.0.RELEASE.jar spring-aspects-3.2.0.RELEASE.jar spring-beans-3.2.0.RELEASE.jar spring-context-3.2.0....
所以,搭建ibatis的框架也会有多种方式(我这里mybatis是3.0的,ibatis是2.3的,spring是3.0的,数据库是mysql)。下面介绍3中方式 1,只是用mybatis3。 2,使用mybatis3+spring3(使用mybatis的SqlSessionFactory ...
mysql-connector-java-5.1.27 org.springframework.aop-3.1.1.RELEASE org.springframework.asm-3.1.1.RELEASE org.springframework.aspects-3.1.1.RELEASE org.springframework.beans-3.1.1.RELEASE org.spring...
/13spring4_mybatis2/lib/mysql-connector-java-5.1.20.jar /13spring4_mybatis2/lib/slf4j-api-1.7.5.jar /13spring4_mybatis2/lib/slf4j-log4j12-1.7.5.jar /13spring4_mybatis2/lib/spring-aop-4.1.6.RELEASE....
Spring3: org.springframework.aop-3.1.0.RELEASE.jar org.springframework.asm-3.1.0.RELEASE....mysql-connector-java-5.0.4-bin.jar 其他jar: commons-logging-1.0.4.jar log4j-1.2.9.jar portlet-api-2.0.jar
3.21.0.jar,log4j-1.2.17.jar,log4j-api-2.3.jar,log4j-core-2.3.jar,mybatis-3.4.4.jar,mybatis-spring-1.1.1.jar,mysql-connector-java-8.0.17.jar,mysql-jdbc.jar,ognl-3.1.14.jar,slf4j-api.jar,slf4j-log4j12-...
spring+mybatis+jdbc+mysql集成用的jar包集合