项目结构
使用传统的JDBC进行批量数据的插入
关键核心类
JdbcUtil.java
package com.imooc.mybatis.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
public JdbcUtil() {
}
public static void commonUpdate(String sql, Object... param) {
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = getConnection();
preparedStatement = conn.prepareStatement(sql);
for(int i = 0; i < param.length; ++i) {
preparedStatement.setObject(i + 1, param[i]);
}
preparedStatement.executeUpdate();
} catch (Exception var8) {
var8.printStackTrace();
} finally {
closeResources(conn, preparedStatement, (ResultSet)null);
}
}
//提交事务
public static void commit(Connection conn){
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void begin(Connection conn){
if(conn != null){
try {
conn.setAutoCommit( false );
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static Connection getConnection(){
Properties properties = new Properties();
InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream( "mysql2.properties" );
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
String driver = properties.getProperty("jdbc.driver");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
String url = properties.getProperty("jdbc.url");
try {
//注册驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void closeResources(Connection conn, Statement statement, ResultSet resultSet) {
if (null != conn) {
try {
conn.close();
} catch (SQLException var6) {
var6.printStackTrace();
}
}
if (null != statement) {
try {
statement.close();
} catch (SQLException var5) {
var5.printStackTrace();
}
}
if (null != resultSet) {
try {
resultSet.close();
} catch (SQLException var4) {
var4.printStackTrace();
}
}
}
public static void main(String[] args) throws Exception {
System.out.println(getConnection());
}
}
mysql2.peoperties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456
进行测试:JdbcTest.java
import com.imooc.mybatis.util.JdbcUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcTest {
private Connection conn = JdbcUtil.getConnection();
private PreparedStatement preparedStatement = null;
private String sql = "insert into person(username,email) values(?,?)";
private long startTime,endTime;
/**
* 方式一,使用for循环,来一条一条的插入数据,用时两千多毫秒
*/
@Test
public void demo1(){
JdbcUtil.begin( conn );//set AutoCommit = False
try {
preparedStatement = conn.prepareStatement( sql );
startTime = System.currentTimeMillis();
for(int i=0;i<10000;i++){
preparedStatement.setString( 1,"user" + (i + 1) );
preparedStatement.setString( 2,"adog@" + (i+1) + ".com" );
//这一步不能省略
preparedStatement.executeUpdate();//执行
}
JdbcUtil.commit( conn );//提交事务
//关闭连接
JdbcUtil.closeResources( conn,preparedStatement,null );
endTime = System.currentTimeMillis();
System.out.println( endTime - startTime );
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 方式二,使用BatchUpdate批量插入,用时两百多毫秒
*/
@Test
public void testBatchUpdate(){
JdbcUtil.begin( conn );//set AutoCommit = False
try {
preparedStatement = conn.prepareStatement( sql );
startTime = System.currentTimeMillis();
for(int i=0;i<10000;i++){
preparedStatement.setString( 1,"user" + (i + 1) );
preparedStatement.setString( 2,"adog@" + (i+1) + ".com" );
preparedStatement.addBatch();
//这一步不能省略(进行批量更新)
if((i+1)%1000 == 0){//批量处理,1000条数据作为一批,时间254毫秒
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
JdbcUtil.commit( conn );
//关闭连接
JdbcUtil.closeResources( conn,preparedStatement,null );
endTime = System.currentTimeMillis();
System.out.println( endTime - startTime );
} catch (SQLException e) {
e.printStackTrace();
}
}
}
使用Mybatis进行批量数据的插入
1)借助foreach标签
1.准备Dao接口
void insertPersons(List<Person> list);
2.准备mapper文件
<!--因为参数是List或Collection类型的,所以collocton="这里使用list或者collection都可以"-->
<insert id="insertPersons">
insert into person(username,email) values
<foreach collection="collection" separator="," index="i" item="person">
(#{person.username},#{person.email})
</foreach>
</insert>
3.进行测试
/**
* 测试Mybatis使用foreach,插入多条数据
*/
@Test
public void testInsertPersons(){
List<Person> list = new ArrayList<Person>( );
for(int i=0;i<5;i++){
Person person = new Person();
person.setUsername( "mick" + i);
person.setEmail( "mick@" + i + ".com" );
list.add( person );
}
//注:这里同时插入多条记录,需要最终提交事务,这里Mybatis使得默认事务不是自动提交的。
personMapper.insertPersons( list );
sqlSession.commit();
}
2)借助MySQL数据库连接属性allowMultiQueries=true
1.准备DAO接口
void insertPersons2(List<Person> list);
2.准备mappe文件
<!--Mybatis 根据allowMultiQueries进行批量插入数据-->
<insert id="insertPersons2">
<foreach collection="list" item="person" separator=";">
insert into person(username,email) values (#{person.username},#{person.email})
</foreach>
</insert>
3.准备测试
/**
* 测试Mybatis基于allowMultiQueries,插入多条数据
*/
@Test
public void testInsertPersons2(){
List<Person> list = new ArrayList<Person>( );
for(int i=0;i<5;i++){
Person person = new Person();
person.setUsername( "adog" + i);
person.setEmail( "adog@" + i + ".com" );
list.add( person );
}
//注:这里同时插入多条记录,需要最终提交事务,这里Mybatis使得默认事务不是自动提交的。
personMapper.insertPersons2( list );
sqlSession.commit();
}
3)基于SqlSession的ExecutorType进行批量添加(这种方式适合处理大批量数据,是Mybatis向数据库中插入大量数据的最终解决方案)
1.准备DAO接口
void insertByExecutorBatch(Person person);
2.准备mapper文件
<!--使用executor进行批量数据插入-->
<insert id="insertByExecutorBatch" parameterType="Person">
insert into person(username,email) values(#{username},#{email})
</insert>
3.准备测试
@Test
public void testBatchForExecutor(){
long beginTime,endTime;
beginTime = System.currentTimeMillis();
this.sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession( ExecutorType.BATCH);//开启executor
this.personMapper = sqlSession.getMapper( PersonMapper.class );
for(int i=0;i<10000;i++){
Person person = new Person();
person.setUsername( "horse" + i );
person.setEmail( "horse@" + i + ".com" );
this.personMapper.insertByExecutorBatch(person);
}
this.sqlSession.commit();
this.sqlSession.close();
endTime = System.currentTimeMillis();
System.out.println( endTime - beginTime );
}