【Mybatis】向MySql数据库插入千万记录 单条插入方式，用时 1h16m30s

本例代码下载：https://files.cnblogs.com/files/xiandedanteng/InsertMillionComparison20191012.rar

相对于批量插入，这种方式可谓虚耗时光。

代码：

package com.hy.action;

import java.io.Reader;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

import com.hy.entity.Employee;
import com.hy.mapper.EmpMapper;

public class SingleInsert1003 {
private static Logger logger = Logger.getLogger(SelectById.class);
    
    public static void main(String[] args) throws Exception{
        long startTime = System.currentTimeMillis();
        
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        try {
            EmpMapper mapper=session.getMapper(EmpMapper.class);
            String ctime="2017-11-01 00:00:01";
            int index=0;
            int changedTotal=0;
            
            for(int i=0;i<10000;i++) {

                for(int j=0;j<1000;j++) {
                    index++;
                    
                    Employee emp=new Employee("E"+String.valueOf(index),index % 100,ctime);
                    int changed=mapper.singleInsert(emp.getName(), emp.getAge(), emp.getCtime());
                    changedTotal+=changed;
                    
                    ctime=timePastOneSecond(ctime);
                }
                
                session.commit();
                System.out.println("#"+i+" changed="+changedTotal);
                
            }
        }catch(Exception ex) {
            session.rollback();
            logger.error(ex);
        }finally {
            session.close();
            
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }
    }
    
    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;
    }
}

Mapper：

<?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="com.hy.mapper.EmpMapper">
    <select id="selectById" resultType="com.hy.entity.Employee">
        select id,name,age,cdate as ctime  from emp where id=#{id}
    </select>
    
    <insert id="batchInsert">
        insert into emp(name,age,cdate)
        values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.name},#{emp.age},#{emp.ctime,jdbcType=TIMESTAMP})
        </foreach>
    </insert>
    
    <insert id="singleInsert">
        insert into emp(name,age,cdate)
        values (#{name},#{age},#{ctime,jdbcType=TIMESTAMP})
    </insert>
</mapper>

与Mapper对应的接口：

package com.hy.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.hy.entity.Employee;


public interface EmpMapper {
    Employee selectById(long id);
    int batchInsert(List<Employee> emps);
    // 用@Param标签指明和SQL的参数对应能避免出现org.apache.ibatis.binding.BindingException异常
    int singleInsert(@Param("name")String name,@Param("age")int age,@Param("ctime")String ctime);
}

部分输出如下：

#9991 changed=9992000
#9992 changed=9993000
#9993 changed=9994000
#9994 changed=9995000
#9995 changed=9996000
#9996 changed=9997000
#9997 changed=9998000
#9998 changed=9999000
#9999 changed=10000000
 INFO [main] - Time elapsed:1h16m30s.

数据库截图如下：

--END-- 2019年10月12日20:03:26