Java实用笔记——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();
	}
}
复制代码

喜欢的朋友可以点点关注点点赞，创作不易，支持下。