Mybatis批量新增数据

package jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * by imooc
 */
public class JdbcUtil {

   //处理数据库事务的 提交事务
   public static void commit(Connection conn)
   {
      if(null!=conn)
      {
         try {
            conn.commit();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }


   }

   //事务的回滚
   public static void rollback(Connection conn)
   {
      if(null!=conn)
      {
         try {
            conn.rollback();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }

   }

   //事务的开始
   public static void begin(Connection conn)
   {
      if(null!=conn)
      {
         try {
            conn.setAutoCommit(false);
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }

   }


   /**
    * 获取连接的方法
    * @return Connection
    * @throws Exception
    */
   public static Connection getConnection() throws Exception
   {
      Properties properties=new Properties();
      //速度快的方式
      //new fileinputStream();
      InputStream is=JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
      //jdbc文件 内容 以流的方法加载到properties文件中 ing     alias  alies
      properties.load(is);
      String driver=properties.getProperty("driver");
      String username=properties.getProperty("username");
      String password=properties.getProperty("password");
      String url=properties.getProperty("jdbcUrl");
      /*System.out.println(driver+"password"+password);*/
      Class.forName(driver);

      return DriverManager.getConnection(url, username, password);

   }
   /**
    * 通用的关闭资源的方法
    * @param conn
    * @param statement
    * @param resultSet
    */
   public static void closeResources(Connection conn,Statement statement,ResultSet resultSet)
   {
      if(null!=conn)
      {
         try {
            conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if(null!=statement)
      {
         try {
            statement.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }

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


   }

   public static void main(String[] args) throws Exception {
      System.out.println(JdbcUtil.getConnection());
   }

}

package jdbc;

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

/**
 * Created by imooc
 */
public class BatchTestOne {

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

        Connection conn=null;
        PreparedStatement preparedStatement=null;
        conn=JdbcUtil.getConnection();
        JdbcUtil.begin(conn);
        String sql="insert into t_user(username,password) values(?,?)";
        preparedStatement=conn.prepareStatement(sql);

        long beginTime=System.currentTimeMillis();
        for (int i = 0; i <10000 ; i++)
        {
            preparedStatement.setString(1,"hello"+(i+1));
            preparedStatement.setString(2,"world"+(i+1));
            preparedStatement.executeUpdate();
        }
        JdbcUtil.commit(conn);
        long endTime=System.currentTimeMillis();
        System.out.println("total time:"+(endTime-beginTime));//2277
    }
}

package jdbc;

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

/**
 * Created by imooc
 */
public class BatchTestTwo {

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

        Connection conn=null;
        PreparedStatement preparedStatement=null;
        conn=JdbcUtil.getConnection();
        JdbcUtil.begin(conn);//autocommit false
        String sql="insert into t_user(username,password) values(?,?)";
        preparedStatement=conn.prepareStatement(sql);


        long beginTime=System.currentTimeMillis();
        for (int i = 0; i <10000 ; i++)
        {
            preparedStatement.setString(1,"hello"+(i+1));
            preparedStatement.setString(2,"world"+(i+1));

            preparedStatement.addBatch();

            if((i+1)%500==0)
            {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        //若果不是500的倍数，执行剩余部分
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
        long endTime=System.currentTimeMillis();
        System.out.println(endTime-beginTime);

        JdbcUtil.commit(conn);
    }
}

=======方式一============

public int addPersons(@Param("persons") List<Person> persons);

<insert id="addPersons">
  insert into person(username,email,gender) VALUES
  <foreach collection="persons" item="person" separator=",">
    (#{person.username},#{person.email},#{person.gender})
  </foreach>
</insert>

public void processMybatisBatch()
{
    SqlSession sqlSession = this.getSqlSessionFactory().openSession();
    PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
    List<Person> persons=new ArrayList<Person>();
    for (int i=0;i<5;i++){
        Person person=new Person("tom"+i,"email"+i,"f");
        persons.add(person);
    }
    personMapper.addPersons(persons);
    sqlSession.commit();

====方式二=============

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

<insert id="addPersons">
   <foreach collection="persons" item="person" separator=";">
      insert into person(username,email,gender) VALUES
     (#{person.username},#{person.email},#{person.gender})
    </foreach>
 </insert>

====方式三：ExecutorType=================

public int addPerson(Person user);

<insert id="addPerson" parameterType="person">
  insert into person(username,email,gender) VALUES (#{username},#{email},#{gender})
</insert>

public void testBatchForExecutor()
{
    SqlSession sqlSession=getSqlSessionFactory().openSession(ExecutorType.BATCH);
    PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
    for (int i=0;i<10000;i++){
        personMapper.addPerson(new Person("tom","tom@imooc.com","F"));
    }
    sqlSession.commit();
    sqlSession.close();

当几十或几百条的批量添加，可以使用第一种方式。如果不知道多少条的情况，使用第二种方式。

转载于:https://my.oschina.net/popfei/blog/1837034