java批量插入數(shù)據(jù)的幾種方法

更新時(shí)間：2023年06月30日 14:24:26 作者：天狼1222

這篇文章主要給大家介紹了關(guān)于java批量插入數(shù)據(jù)的幾種方法,大家在Java項(xiàng)目中經(jīng)常會(huì)出現(xiàn)大量向數(shù)據(jù)庫中插入的情況,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

1，常見的mybatis foreach xml

<insert id="insertBatch"  parameterType="java.util.List">
		insert into  CODEINFO (CODE_TYPE, CODE, MEAN, STATE, SORT_ID)
		values
		<foreach collection ="records" item="item" separator =",">
			(#{item.codeType}, #{item.code},
			 #{item.remark},  #{item.state}, #{item.sortId})
		</foreach >
	</insert>

mapper:

int insertBatch(@Param("records") List<CodeInfo> records);

對(duì)于數(shù)據(jù)量不是很大的，基本夠用。如果同步數(shù)據(jù)特別慢，再考慮其它的方式?；蛘咄砩狭璩吭偻綌?shù)據(jù)。

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.執(zhí)行batch
                    ps.executeBatch();
                    //3.清空batch
                    ps.clearBatch();
                }
            }
            //提交數(shù)據(jù)
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("批量插入花費(fèi)的時(shí)間為："+(end-start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCUtils.close(conn, ps);
        }
    }

數(shù)據(jù)庫連接：

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;
    //    靜態(tài)代碼塊
    static{
        /* 將外部properties文件放在src文件夾中，用類的加載器讀文件，格式：
         * 當(dāng)前類名.class.getClassLoader().getResourceAsStream("外部文件名");*/
        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();
        }
    }
    //    構(gòu)造獲得數(shù)據(jù)庫鏈接方法
    public static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //    構(gòu)造關(guān)閉流的方法
    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();
            }
        }
    }
    //    重載關(guān)閉流的方法
    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;
@Component
public class MybatisGeneralBatchUtils {
    private static final Logger logger = LoggerFactory.getLogger(MybatisGeneralBatchUtils.class);
    /**
     * 每次處理1000條
     */
    private static final int BATCH_SIZE = 1000;
    /**
     * 批量處理修改或者插入
     *  變成一條一條的數(shù)據(jù)，然后最后一起執(zhí)行。并不是 insertBatch那種方式
     * @param data        需要被處理的數(shù)據(jù)
     * @param mapperClass Mybatis的Mapper類
     * @param function    自定義處理邏輯
     * @return int 影響的總行數(shù)
     */
    public <T, U, R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T, U, R> 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++;
            }
            // 非事務(wù)環(huán)境下強(qiáng)制commit，事務(wù)情況下該commit相當(dāng)于無效
            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;
@Component
public 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> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }
}

調(diào)用：

mapper:

int insertSelective(CodeInfo codeInfo);

xml:

<insert id="insertSelective" parameterType="com.web.dict.entity.CodeInfo">
		insert into CODEINFO
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="codeType != null">
				CODE_TYPE,
			</if>
			<if test="code != null">
				CODE,
			</if>
			<if test="mean != null">
				MEAN,
			</if>
			<if test="state != null">
				STATE,
			</if>
			<if test="sortId != null">
				SORT_ID,
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="codeType != null">
				#{codeType,jdbcType=VARCHAR},
			</if>
			<if test="code != null">
				#{code,jdbcType=VARCHAR},
			</if>
			<if test="mean != null">
				#{mean,jdbcType=VARCHAR},
			</if>
			<if test="state != null">
				#{state,jdbcType=VARCHAR},
			</if>
			<if test="sortId != null">
				#{sortId,jdbcType=VARCHAR},
			</if>
		</trim>
	</insert>

service:

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

這個(gè)方法看起來比較通用，但是我自己測(cè)的話，速度反而比較慢?？赡苁且?yàn)槟M的字段和數(shù)據(jù)都比較少；后面有遇到數(shù)據(jù)量大的，再進(jìn)行一個(gè)比對(duì)。

官網(wǎng)推薦的方法：

MyBatis文檔中寫批量插入的時(shí)候，是推薦使用另外一種方法中 Batch Insert Support 標(biāo)題里的內(nèi)容

try(SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
        SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
        List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
        BatchInsert<SimpleTableRecord> 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();
    }