1.使用传统jdbc对数据进行插入

传统的jdbc插入包括2种方式,使用for循环和使用jdbc的批处理

(一)for循环

package com.imooc.mybatis.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    /**
     * 事物的提交
     * @param connection
     */
    public static void commit(Connection connection){

        if(null!=connection){

            try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 事物的回滚
     */
    public static void rollback(Connection connection){

        if(null!=connection){
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 事物的开始,JDBC的批量添加必须要手动提交
     */
    public static void begin(Connection connection){

        if(null!=connection){

            try {
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库的连接
     */
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {

        InputStream inputStream=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

        Properties prop=new Properties();

        prop.load(inputStream);

        String driverClass=prop.getProperty("jdbc.driver");
        String jdbcUrl=prop.getProperty("jdbc.url");
        String user=prop.getProperty("jdbc.username");
        String password=prop.getProperty("jdbc.password");

        /**
         * 加载驱动
         */
        Class clazz=Class.forName(driverClass);

        /**
         * 获取数据库连接
         */
        Connection connection= DriverManager.getConnection(jdbcUrl,user,password);


        return  connection;
    }

    /**
     * 关闭资源
     */
    public static void closeResources(Connection connection, Statement statement, ResultSet resultSet){

        if(null!=resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null!=statement){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null!=connection){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

package com.imooc.mybatis.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchTestOne {

    public static void main(String[] args) throws Exception {

        Connection connection=null;
        PreparedStatement preparedStatement=null;

        connection=JdbcUtils.getConnection();

        /**
         * 设置jdbc为自动提交
         */
        JdbcUtils.begin(connection);

        String sql="insert into t_user(username,address) values(?,?)";

        Long startTime=System.currentTimeMillis();
        preparedStatement=connection.prepareStatement(sql);
        for(int i=0;i<1000;i++){

            preparedStatement.setString(1,"罗宾"+i);
            preparedStatement.setString(2,"海贼");

            preparedStatement.executeUpdate();

        }

        JdbcUtils.commit(connection);
        Long endTime=System.currentTimeMillis();
        System.out.println("total:"+(endTime-startTime));

        JdbcUtils.closeResources(connection,preparedStatement,null);

    }
}

(二)使用Jdbc批处理

package com.imooc.mybatis.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class BatchTestTwo {

    public static void main(String[] args) throws Exception{

        Connection connection=null;
        PreparedStatement preparedStatement=null;

        connection=JdbcUtils.getConnection();
        JdbcUtils.begin(connection);

        String sql="insert into t_user(username,address) values(?,?)";

        preparedStatement=connection.prepareStatement(sql);

        Long startTime=System.currentTimeMillis();

        for(int i=0;i<10000;i++){

            preparedStatement.setString(1,"炮姐"+i);
            preparedStatement.setString(2,"某科学的超电磁炮");

            //将数据添加到批处理当中
            preparedStatement.addBatch();
            if(((i+1)%1000)==0){
                //执行批处理
                preparedStatement.executeBatch();
                //清除批处理缓存
                preparedStatement.clearBatch();
            }

        }
       JdbcUtils.commit(connection);
        Long endTime=System.currentTimeMillis();

        System.out.println("total:"+(endTime-startTime));

        JdbcUtils.closeResources(connection,preparedStatement,null);
    }
}

2.Mybatis批处理

(一)mybatis通过foreach标签实现批量插入

package com.imooc.mybatis.dao;
import com.imooc.mybatis.entity.Person;
import java.util.List;

public interface PersonMapper {
    public  void  deletePerson(Integer id);
    public int addPerson(List<Person> lists);
}
**********************************************************
    <insert id="addPerson" >
        insert into person(username,email,gender) values
        <foreach collection="collection" item="person" separator="," >
            (#{person.username},#{person.email},#{person.gender})
        </foreach>
    </insert>
**********************************************************
public void addPerson(){

        SqlSession sqlSession=getSqlSessionFactory().openSession();

        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);

        List<Person> list=new ArrayList<Person>();
        for(int i=0;i<5;i++){

            list.add(new Person("桂言叶"+i,"yanye@163.com","女"));
        }

        int count=personMapper.addPerson(list);

        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();

    }

(二)mybatis通过mysql数据库连接属性allowMultiQueries=true批量插入

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
jdbc.username=root
jdbc.password=bzq18107155240
*******************************************************************************
<insert id="addPerson" >
      <foreach collection="list" item="person" separator=";">

          insert into person(username,email,gender) values
          (#{person.username},#{person.email},#{person.gender})

      </foreach>
    </insert>

如果不在jdbc的url后面加？allowMultiQueries=true，采用这种批量更新会报错的。SQL syntax

(三)使用ExecutorType.BATCH,让sqlSqssion有批处理的能力

package com.imooc.mybatis.dao;
import com.imooc.mybatis.entity.Person;
public interface PersonMapper {
    public int batchPerson(Person person);
}
*************************************************************
 <insert id="batchPerson">
         insert into person(username,email,gender) values (#{username},#{email},#{gender})
</insert>
***********************************************************
public void BatchPerson(){

        /**
         * 当添加了这个参数的时候，可以让SqlSession有批处理的能力
         */
        SqlSession sqlSession=getSqlSessionFactory().openSession(ExecutorType.BATCH);
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        for (int i = 0; i < 10000; i++) {

            personMapper.batchPerson(new Person("庞斑"+i,"pban@163.com","男"));

        }
        sqlSession.commit();
        sqlSession.close();
    }