java 批量插入数据

2023-09-03 12:36

短信预约 -IT技能 免费直播动态提醒

批量插入数据，常见的使用mybatis foreach 插入的方式，原始的方式和批处理

1，常见的mybatis foreach

xml

insert into  CODEINFO (CODE_TYPE, CODE, MEAN, STATE, SORT_ID)values(#{item.codeType}, #{item.code}, #{item.remark},  #{item.state}, #{item.sortId})

mapper:

int insertBatch(@Param("records") List records);

对于数据量不是很大的，基本够用。如果同步数据特别慢，再考虑其它的方式。或者晚上凌晨再同步数据。

2，原始的方式

批量插入

public void insertBatach(){        Connection conn=null;        PreparedStatement ps=null;        try {            long start = System.currentTimeMillis();            conn = JDBCUtils.getConnection();            conn.setAutoCommit(false);            String sql="INSERT INTO CODEINFO (CODE_TYPE, CODE, MEAN,STATE, SORT_ID) VALUES (?, ?, ?, ?, ?)";            ps = conn.prepareStatement(sql);            for(int i=1;i<=20000;i++){                ps.setObject(1, "TEST_INSERT_BATCH");                ps.setObject(2, "0"+i);                ps.setObject(3, "name_"+i);                ps.setObject(4, "0SA");                ps.setObject(5, i);                //1.sql                ps.addBatch();                if(i%500==0){                    //2.执行batch                    ps.executeBatch();                    //3.清空batch                    ps.clearBatch();                }            }            //提交数据            conn.commit();            long end = System.currentTimeMillis();            System.out.println("批量插入花费的时间为："+(end-start));        } catch (Exception e) {            e.printStackTrace();        } finally{            JDBCUtils.close(conn, ps);        }    }

数据库连接：

import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JDBCUtils {    private static String url;    private static String user;    private static String password;    private static  Connection conn = null;    //    静态代码块    static{                InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("sql.properties");        Properties p=new Properties();        try {            p.load(in);        } catch (IOException e) {            e.printStackTrace();        }//        读文件给变量赋值        String driver = p.getProperty("driver");        url = p.getProperty("url");        user = p.getProperty("user");        password = p.getProperty("password");        try {            Class.forName(driver);        } catch (ClassNotFoundException e) {            e.printStackTrace();        }    }    //    构造获得数据库链接方法    public static Connection getConnection() {        try {            conn = DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    //    构造关闭流的方法    public static void close(Connection conn,Statement stat) {        if (stat != null) {            try {                stat.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    //    重载关闭流的方法    public static void close(Connection conn,Statement stat, ResultSet rs) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (stat != null) {            try {                stat.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

原始的方法写起来麻烦些。

3，批处理

MybatisGeneralBatchUtils

import org.apache.ibatis.session.ExecutorType;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.stereotype.Component;import org.springframework.transaction.support.TransactionSynchronizationManager;import java.util.List;import java.util.function.BiFunction;@Componentpublic class MybatisGeneralBatchUtils {    private static final Logger logger = LoggerFactory.getLogger(MybatisGeneralBatchUtils.class);        private static final int BATCH_SIZE = 1000;        public  int batchUpdateOrInsert(List data, Class mapperClass, BiFunction function)  {        int i = 1;        SqlSessionFactory sqlSessionFactory = (SqlSessionFactory) SpringUtil.getBean("sqlSessionFactory");        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);        try {            U mapper = batchSqlSession.getMapper(mapperClass);            int size = data.size();            for (T element : data) {                function.apply(element, mapper);                if ((i % BATCH_SIZE == 0) || i == size) {                    batchSqlSession.flushStatements();                }                i++;            }            // 非事务环境下强制commit，事务情况下该commit相当于无效            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());        } catch (Exception e) {            batchSqlSession.rollback();            logger.error("batchUpdateOrInsert", e);        } finally {            batchSqlSession.close();        }        return i - 1;    }}

SpringUtil

import org.springframework.beans.BeansException;import org.springframework.context.ApplicationContext;import org.springframework.context.ApplicationContextAware;import org.springframework.stereotype.Component;@Componentpublic class SpringUtil implements ApplicationContextAware {    private static ApplicationContext applicationContext;    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {         SpringUtil.applicationContext = applicationContext;    }    public static Object getBean(String name) {        return applicationContext.getBean(name);    }    public static  T getBean(Class clazz) {        return applicationContext.getBean(clazz);    }}

调用：

mapper:

int insertSelective(CodeInfo codeInfo);

xml:

insert into CODEINFOCODE_TYPE,CODE,MEAN,STATE,SORT_ID,#{codeType,jdbcType=VARCHAR},#{code,jdbcType=VARCHAR},#{mean,jdbcType=VARCHAR},#{state,jdbcType=VARCHAR},#{sortId,jdbcType=VARCHAR},

service:

    @Resource    private MybatisGeneralBatchUtils mybatisGeneralBatchUtils;    public int batchInsertData(List codeInfos){        return mybatisGeneralBatchUtils.batchUpdateOrInsert(codeInfos, CodeInfoMapper.class,                (item, codeInfoMapper) -> codeInfoMapper.insertSelective(item));    }

这个方法看起来比较通用，但是我自己测的话，速度反而比较慢。可能是因为模拟的字段和数据都比较少；后面有遇到数据量大的，再进行一个比对。

官网推荐的方法：

MyBatis文档中写批量插入的时候，是推荐使用另外一种方法中 Batch Insert Support 标题里的内容

try(SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {        SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);        List records = getRecordsToInsert(); // not shown        BatchInsert batchInsert = insert(records)                .into(simpleTable)                .map(id).toProperty("id")                .map(firstName).toProperty("firstName")                .map(lastName).toProperty("lastName")                .map(birthDate).toProperty("birthDate")                .map(employed).toProperty("employed")                .map(occupation).toProperty("occupation")                .build()                .render(RenderingStrategies.MYBATIS3);        batchInsert.insertStatements().forEach(mapper::insert);        session.commit();    }