批量插入数据,常见的使用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(); }
总结:
如果数据量不大,能第一种就够了。如果数据内容多,字段又多,试试其它的方式,看下效率是否有更快。 同步数据,还是适合晚上的时候,用定时器去跑。
来源地址:https://blog.csdn.net/qq_35461948/article/details/130195282