首页 > 其他分享 >JDBC Batch Insert OutOfMemoryError

JDBC Batch Insert OutOfMemoryError

时间:2023-09-14 10:04:00浏览次数:30  
标签:Insert JDBC name ps executeBatch OutOfMemoryError null conn String


I have written a method insert() in which I am trying to use JDBC Batch for inserting half a million records into a MySQL database:

public void insert(int nameListId, String[] names) {
        String sql = "INSERT INTO name_list_subscribers (name_list_id, name, date_added)"+" VALUES (?, ?, NOW())";
        Connection conn = null;
        PreparedStatement ps = null;

        try{
            conn = getConnection();
            ps = conn.prepareStatement(sql);

            for(String s : names ){
                ps.setInt(1, nameListId); 
                ps.setString(2, s);
                ps.addBatch();
            }

            ps.executeBatch();

        }catch(SQLException e){
            throw new RuntimeException(e);
        }finally{
            closeDbResources(ps, null, conn);
        }
    }




But whenever I try to run this method, I get the following error:



java.lang.OutOfMemoryError: Java heap space
    com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
    com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
    org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)



If I replace ps.addBatch() with ps.executeUpdate() and remove ps.executeBatch(), it works fine, though it takes some time. Please let me know if you know if using Batch is appropriate in this situation, and if it is, then why does it give OurOfMemoryError?



[color=green]It is out of memory because it hold all the transaction in memory and only send it over to the database when you call executeBatch.



If you don't need it to be atomic and would like the get better performance, you can keep a counter and call executeBatch every n number of records.[/color]



public void insert(int nameListId, String[] names) {
        String sql = "INSERT INTO name_list_subscribers (name_list_id, name, date_added)"+" VALUES (?, ?, NOW())";
        Connection conn = null;
        PreparedStatement ps = null;

        try{
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            int icount = 0;
            for(String s : names ){
                ps.setInt(1, nameListId); 
                ps.setString(2, s);
                ps.addBatch();
                if(icount%1000 == 0){
                  ps.executeBatch();
                  ps.clearBatch();
                }

                icount++;
            }

            ps.executeBatch();

        }catch(SQLException e){
            throw new RuntimeException(e);
        }finally{
            closeDbResources(ps, null, conn);
        }
    }

标签:Insert,JDBC,name,ps,executeBatch,OutOfMemoryError,null,conn,String
From: https://blog.51cto.com/u_16261339/7467821

相关文章

  • SQL Server: How to insert million numbers to table fast?
    YesterdayIattendedatlocalcommunityeveningwhereoneofthemostfamousEstonianMVPs–HennSarv–spokeaboutSQLServerqueriesandperformance.DuringthissessionwesawverycooldemosandinthispostingIwillintroduceyo......
  • Query Guide-Query From Insert
    Query查询Query定义了Siddhi中的处理逻辑。它使用来自一个或多个流、命名窗口、表和/或命名聚合的事件,以流方式处理事件,并将输出事件生成到流、命名窗或表中。目的查询提供了一种方法,可以按照事件到达的顺序处理事件,并使用有状态和无状态的复杂事件处理和流处理操作生成输出。语法......
  • Bug库____org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorr
    Bug:使用到了spring的jdbctemplate模板使用到以下template.queryForObject(sql,requiredType)template.queryForList(sql,elementType,args)报以下错误org.springframework.jdbc.IncorrectResultSetColumnCountException:Incorrectcolumncount:expected1,actual3检查完......
  • BUG(Spring Framework JdbcTemplate) org.springframework.jdbc.IncorrectResultSetCo
    一.SpringFramework queryForObject问题1.spring4.0之前使用使用jdbctemplate的queryForObject(Stringsql,Object[]args,RowMapper<T>rowMapper)直接放入class类型会报错org.springframework.jdbc.IncorrectResultSetColumnCountException:Incorrectcolumncount:expec......
  • java中Mysql Insert 的高效应用
    在Java中,使用MySQL进行高效的插入操作可以采取以下几种方法:1.使用批量插入:通过使用批量插入语句,可以一次性插入多条数据,减少与数据库的交互次数,提高插入效率。可以使用JDBC的`addBatch()`方法将多个插入语句添加到批处理中,然后使用`executeBatch()`方法执行批处理。2.使用预编译......
  • ClickHouse使用之四 ——外部数据源导入通用方案之insert into select from
    需求:1、在工作中,我们常常需要将外部hive或者mysql、oracle等数据源导入到clickhouse中,对于多种外部数据源,是否有通用的数据导入方案?2、我们在clickhouse上维持一张查询主表,但外部数据源表是hive增量表,新增数据需要同步更新到clickhouse上,是否有不通过第三方组件的插入方式......
  • JDBC 连接 MySQL 报错 Unknown system variable ‘query_cache_size‘
    官方说法:ThequerycacheisdeprecatedasofMySQL5.7.20,andisremovedinMySQL8.0.Deprecationincludesquery_cache_size.querycache在MySQL5.7.20已经过时了,并且在MySQL8.0版本中被移除了。所以要更新mysql驱动版本<dependency><groupId>mysql</groupId......
  • Debezium系列之:获取Oracle JDBC驱动程序和XStream API文件
    Debezium系列之:获取OracleJDBC驱动程序和XStreamAPI文件一、背景二、详细步骤1.下载OracleClient包2.解压缩3.复制ojdbc8.jar和xstreams.jar文件4.创建环境变量三、XStream连接器属性一、背景DebeziumOracle连接器需要OracleJDBC驱动程序(ojdbc8.jar)才能连接到Oracl......
  • Apache IoTDB开发系统之JDBC
    DependenciesJDK>=1.8Maven>=3.1PackageonlyJDBCprojects在根目录中执行以下命令:mvncleanpackage-pljdbc-am-DskipTests如何在本地maven存储库中安装在根目录中:mvncleaninstall-pljdbc-am-Dmaven.test.skip=true将IoTDBJDBC与Maven结合使用<dependencies......
  • Java实现关系型数据库工具类JdbcUtils系列九:通用DAO
    Java实现关系型数据库工具类JdbcUtils系列九:通用DAO一、创建对应数据库表的实体类二、数据库连接池Druid工具类三、DAO类四、BaseDAO五、DatabaseInfoDao六、通用DAO测试类一、创建对应数据库表的实体类数据库表结构CREATETABLE`databaseInfo`(`id`bigint(11)NOTNULLAU......