準(zhǔn)備：1.表結(jié)構(gòu)CREATE TABLE `t_user` (`id` varchar(32) CHARACTER SET utf8 NOT NULL COMMENT '主鍵',`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用戶名',`del_flag` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '刪除標(biāo)示',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;2.配置 2.1 jdbc.properties配置mysql.driver=com.mysql.jdbc.Driver mysql.url=jdbc:mysql://127.0.0.1:3306/ssm mysql.username=root mysql.password=admin #定義初始連接數(shù) mysql.initialSize=1 #定義最大連接數(shù) mysql.maxActive=20 #定義最大空閑 mysql.maxIdle=20 #定義最小空閑 mysql.minIdle=1 #定義最長(zhǎng)等待時(shí)間 mysql.maxWait=60000 2.2 spring-mybatis.xml配置 <context:component-scan base-package="com.win.ssm"/> <context:property-placeholder location="classpath:jdbc.properties"/> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${mysql.driver}"/> <property name="url" value="${mysql.url}"/> <property name="username" value="${mysql.username}"/> <property name="password" value="${mysql.password}"/> <!-- 初始化鏈接大小--> <property name="initialSize" value="${mysql.initialSize}"/> <!-- 連接池最大數(shù)量--> <property name="maxActive" value="${mysql.maxActive}"/> <!-- 連接池最大空閑--> <property name="maxIdle" value="${mysql.maxIdle}"/> <!-- 連接池最小空閑 --> <property name="minIdle" value="${mysql.minIdle}"></property> <!-- 獲取連接最大等待時(shí)間--> <property name="maxWait" value="${mysql.maxWait}"/> </bean> <!-- spring與mybatis整合類 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <!-- 查找接口的別名 --> <property name="typeAliasesPackage" value="com.win"/> <!-- 自動(dòng)掃描mapping.xml文件--> <property name="mapperLocations" value="classpath:/mapping/*.xml"/> </bean> <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref="sqlSessionFactory" /> <!--<constructor-arg index="1" value="BATCH" />--> </bean> <!-- 掃描DAO接口 --> <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.win.ssm.dao"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean> <!-- 事務(wù)管理 --> <bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> 第一種：普通for循環(huán)插入 ①junit類 @Test public void testInsertBatch2() throws Exception { long start = System.currentTimeMillis(); User user; SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(false); UserDao mapper = sqlSession.getMapper(UserDao.class); for (int i = 0; i < 500; i++) { user = new User(); user.setId("test" + i); user.setName("name" + i); user.setDelFlag("0"); mapper.insert(user); } sqlSession.commit(); long end = System.currentTimeMillis(); System.out.println("---------------" + (start - end) + "---------------"); } ②xml配置 <insert id="insert"> INSERT INTO t_user (id, name, del_flag) VALUES(#{id}, #{name}, #{delFlag}) </insert> 第二種：mybatis BATCH模式插入 ①junit類 @Test public void testInsertBatch2() throws Exception { long start = System.currentTimeMillis(); User user; SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql區(qū)別 UserDao mapper = sqlSession.getMapper(UserDao.class); for (int i = 0; i < 500; i++) { user = new User(); user.setId("test" + i); user.setName("name" + i); user.setDelFlag("0"); mapper.insert(user); } sqlSession.commit(); long end = System.currentTimeMillis(); System.out.println("---------------" + (start - end) + "---------------"); } ②xml配置與第一種②中使用相同 第三種：foreach方式插入 ①junit類 @Test public void testInsertBatch() throws Exception { long start = System.currentTimeMillis(); List<User> list = new ArrayList<>(); User user; for (int i = 0; i < 10000; i++) { user = new User(); user.setId("test" + i); user.setName("name" + i); user.setDelFlag("0"); list.add(user); } userService.insertBatch(list); long end = System.currentTimeMillis(); System.out.println("---------------" + (start - end) + "---------------"); } ②xml配置 <insert id="insertBatch"> INSERT INTO t_user (id, name, del_flag) VALUES <foreach collection ="list" item="user" separator =","> (#{user.id}, #{user.name}, #{user.delFlag}) </foreach > </insert>
nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable. 結(jié)果對(duì)比： 時(shí)間有限測(cè)試數(shù)據(jù)較少，有興趣可以自己測(cè)試以下。（不清楚為什么BATCH有時(shí)候比單條循環(huán)插入還耗時(shí)間，請(qǐng)知道的大神不吝賜教，感謝?。?/span> |
|
|
