MyBatis 批量新增數(shù)據(jù)

3 Idiots

傳統(tǒng)JDBC批量插入方法：

1.Java代碼中使用For循環(huán)直接插入SQL數(shù)據(jù)如：execute()或executeUpdate()方法。
2.借助于Statement、Prestatement對象的批處理方法addBatch

public class jdbcUtil {
    // 處理數(shù)據(jù)庫事務(wù)，批量操作需要手動提交事務(wù)
    public static void commit(Connection connection){
        if (null!=connection){
            try {
                connection.commit();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    //事務(wù)的回滾
    public static void rollback(Connection connection){
        if (null!=connection){
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 事務(wù)的開始
    public static void begin(Connection connection) {
        if (null != connection) {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
     /**
      * 獲取連接方法
      * @Param
      * @Return
      */
     public static Connection getConnection() throws Exception {
         Properties properties = new Properties();
         InputStream is = jdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
         //jdbc文件內(nèi)容以 流 的方式加載到properties文件中
         properties.load(is);
         String driver=properties.getProperty("driver");
         String username=properties.getProperty("username");
         String password=properties.getProperty("password");
         String url=properties.getProperty("url");
         System.out.println(driver+":"+password);
         Class.forName(driver);

         return DriverManager.getConnection(url,username,password);
     }
     /**
      * 通用的關(guān)閉資源的方法
      * @Param connection
      * @Param statement
      * @Param resultSet
      */
     public static void closeResources(Connection connection, Statement statement, ResultSet resultSet){
        if (null!=connection){
            try {
                connection.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();
            }
        }
     }
}

使用for循環(huán)插入

public class BatchTestOne {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection = jdbcUtil.getConnection();
        jdbcUtil.begin(connection); //autocommit false

        String sql = "insert into t_user(username,password) values(?,?)";
        preparedStatement=connection.prepareStatement(sql);

        long beginTime = System.currentTimeMillis();
        for (int i=0;i<10000;i++){
            preparedStatement.setString(1,"user"+(i+1));
            preparedStatement.setString(2,"pwd"+(i+1));
            preparedStatement.executeUpdate();
        }

        jdbcUtil.commit(connection);
        long endTime = System.currentTimeMillis();
        System.out.println("total time:"+(endTime-beginTime));//4150
    }
}

使用addBatch批處理方式插入

public class BatchTestTwo {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection = jdbcUtil.getConnection();
        jdbcUtil.begin(connection);

        String sql = "insert into t_user(username,password) values(?,?)";
        preparedStatement=connection.prepareStatement(sql);

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

            if ((i+1)%1000==0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }

        jdbcUtil.commit(connection);
        long endTime = System.currentTimeMillis();
        System.out.println("total time:"+(endTime-beginTime));//1817
    }
}

可以看出：使用批處理的方式要比普通的for循環(huán)要快很多。mybatis封裝了jdbc，所以mybatis的批處理方式類似于addBatch

MyBatis進(jìn)行批量插入的方法

MySQL添加多條數(shù)據(jù)的方式

insert into person(username,email,gender) VALUES("zhangsan","zhangsan@163.com","F"),("lisi","lisi@163.com","F")
或者
insert into person(username,email,gender) VALUES("tom","zhangsan@163.com","F");
insert into person(username,email,gender) VALUES("jerry","lisi@163.com","F")

1.借助foreach標(biāo)簽使用 insert into table values

public class Person {
    private Integer id;

    private String username;

    private String email;

    private String gender;

    public Person(String username, String email, String gender) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.gender = gender;
    }
}

public interface PersonMapper {
    void addPersons(@Param("persons") List<Person> persons);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="mybatis.dao.PersonMapper" >
    <resultMap id="BaseResultMap" type="mybatis.bean.Person" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="gender" property="gender" jdbcType="VARCHAR" />
    </resultMap>
    
    <insert id="addPersons">
      insert into person(username,email,gender) VALUES
      <foreach collection="persons" item="person" separator=",">
        (#{person.username},#{person.email},#{person.gender})
      </foreach>
    </insert>

</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 加入DB配置文件 -->
    <properties resource="jdbc.properties"></properties>

    <!-- 配置配置項 -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <typeAliases>
        <package name="mybatis.bean"/>
    </typeAliases>
    
    <environments default="dev_mysql">
        <environment id="dev_mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="SQL Server" value="sqlserver"/>
    </databaseIdProvider>

    <mappers>
        <mapper resource="mybatis/PersonMapper.xml"/>
    </mappers>
</configuration>

測試

public class MyBatisTest {
    public static SqlSessionFactory sqlSessionFactory = null;

    public static SqlSessionFactory getSqlSessionFactory() {
        if (sqlSessionFactory == null) {
            String resource = "mybatis-config.xml";
            try {
                Reader reader = Resources.getResourceAsReader(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return sqlSessionFactory;
    }
    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 <1000 ; i++)
        {
            Person person=new Person("jerry"+i,"email@"+i,"f");
            persons.add(person);
        }
        personMapper.addPersons(persons);
        sqlSession.commit();

    }
    public static void main(String[] args) {
        new MyBatisTest().processMybatisBatch();
    }
}

2.借助MySQL數(shù)據(jù)庫連接屬性 allowMultiQueries=true

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
username=root
password=123456

然后，只需要修改上面PersonMapper.xml中的insert語句

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

3.基于SqlSession的ExecutorType進(jìn)行批量添加

public interface PersonMapper {
    void addPerson(Person person);
}

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

測試

public void testBatchForExecutor() {
        SqlSession sqlSession = this.getSqlSessionFactory().openSession(ExecutorType.BATCH);

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

        for (int i = 0; i <10 ; i++)
        {
            personMapper.addPerson(new Person("Tom","email@"+i,"F"));
        }
        sqlSession.commit();
        sqlSession.close();
    }

總結(jié)

傳統(tǒng)JDBC批量插入方法：
1.利用for循環(huán)進(jìn)行插入的方式存在嚴(yán)重效率問題，需要頻繁獲取Session，獲取連接。
2.使用批處理，代碼和SQL的耦合度高，代碼量較大。

MyBatis進(jìn)行批量插入的方法：
1.MySQL下批量保存的兩種方式，建議使用第一種
2.借助于Executor的Batch批量添加，可與Spring框架整合，數(shù)據(jù)量大的時候，我們一般采用這種方式。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1. 簡介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存儲過程以及高級映射的優(yōu)秀的...
    笨鳥慢飛閱讀 6,227評論 0贊 4
  • Spark SQL, DataFrames and Datasets Guide Overview SQL Dat...
    草里有只羊閱讀 18,548評論 0贊 85
  • 1.修行目標(biāo)：我要過內(nèi)圣外王的生活、輕松成功、健康豐盛，喜樂，擁有氧氣般的金錢，全家每月總收入達(dá)5萬，8月30日前...
    穎儀_5b92閱讀 99評論 0贊 0
  • 或許，真正在乎你的人，才會在意這個夜晚你會不會休息好。當(dāng)然，也只有深愛你的人，才知道如何讓你在深夜里不擔(dān)心。我們總...
    畫鳴閱讀 172評論 0贊 0

友情鏈接更多精彩內(nèi)容