【JDBC】使用Spring提供的JDBCTemplate通过Statement向MySql数据库插入千万条数据，耗时4m49s，使用insert语句批量插入方式

事实证明，使用

insert into emp(name,age,cdate) 

values

('A' , 20, '2019-10-13 00:00:00'),

('B' , 21, '2019-10-13 01:00:00'),

('C' , 22, '2019-10-13 05:00:00')

的方式是最快的，和单条插入比速度提升了十倍，简直是数量级的差距。估计这就是MyBatis五分钟方案所采取的内部实现方式。

代码如下：

package com.hy.action.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class BatchJDBCStmtInsert2 {
    private static Logger logger = Logger.getLogger(BatchJDBCStmtInsert2.class);
    
    public static void main(String[] args) {
        long startTime = System.currentTimeMillis();
        
        //把beans.xml的类加载到容器
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jt=(JdbcTemplate)applicationContext.getBean("jdbcTemplate");
        
        // Initialize conn&stmt
        Connection conn=null;
        Statement stmt=null;
        
        try {
            conn =jt.getDataSource().getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            
            String ctime="2017-11-01 00:00:01";
            int index=0;
            
            for(int i=0;i<10000;i++) {
                String insertSql="insert into emp(name,age,cdate) values ";
                List<String> list=new ArrayList<String>();
                
                for(int j=0;j<1000;j++) {
                    index++;
                    
                    Object arr[]={"'E:"+index+"'",index % 100,"'"+ctime+"'"};
                    String valueSql=MessageFormat.format("({0},{1},{2})", arr);
                    list.add(valueSql);
                    
                    ctime=timePastOneSecond(ctime);
                }
                
                String sql=insertSql+String.join(",", list);
                stmt.addBatch(sql);
                stmt.executeBatch();
                // stmt.clearBatch();
                conn.commit();
                logger.info("#"+i+" 1000 records have been inserted to table:'emp'.");
            }
        } catch (SQLException e) {
            logger.error("Error happened:"+e);
            try {
                conn.rollback();
            } catch (SQLException e1) {
                logger.error("Can not rollback because of the error:'"+e+"'.");
            }
        }finally {
            try {
                stmt.close();
                conn.close();
                
                long endTime = System.currentTimeMillis();
                logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
            } catch (SQLException e1) {
                logger.error("Can not close connection because of the error:'"+e1+"'.");
            }
        }
    }
    
    public static String timePastOneSecond(String otime) {
        try {
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date dt=sdf.parse(otime);
            
            Calendar newTime = Calendar.getInstance();
            newTime.setTime(dt);
            newTime.add(Calendar.SECOND,1);
            
            Date dt1=newTime.getTime();
            String retval = sdf.format(dt1);
            
            return retval;
        }
        catch(Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
     // format seconds to day hour minute seconds style
    // Example 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

控制台输出：

 INFO [main] - #9990 1000 records have been inserted to table:'emp'.
 INFO [main] - #9991 1000 records have been inserted to table:'emp'.
 INFO [main] - #9992 1000 records have been inserted to table:'emp'.
 INFO [main] - #9993 1000 records have been inserted to table:'emp'.
 INFO [main] - #9994 1000 records have been inserted to table:'emp'.
 INFO [main] - #9995 1000 records have been inserted to table:'emp'.
 INFO [main] - #9996 1000 records have been inserted to table:'emp'.
 INFO [main] - #9997 1000 records have been inserted to table:'emp'.
 INFO [main] - #9998 1000 records have been inserted to table:'emp'.
 INFO [main] - #9999 1000 records have been inserted to table:'emp'.
 INFO [main] - Time elapsed:4m49s.

数据库状态：

--END-- 2019年10月13日15:03:55