尝试读取700k +的数据,并且发生错误“超出了GC开销限制”

阿玛寇:

好的,我需要帮助检查代码,因为我还是编程新手(目前是我在计算机科学专业的第二年)。GC Overhead Limit Exceeded当我尝试在下面运行代码时,出现标题中的错误

对此代码的简要说明,我正在尝试从CSV文件读取数据,然后将其传输到数据库。仅供参考,实际上我需要读取10个表/ CSV文件,但是在此我将显示该表,Tickets因为该错误仅在我尝试读取该表/文件时发生。其他表仅具有数百行/数据,而表Tickets却具有735,504行/数据。此外,在运行代码6个小时之后,在发生错误之前,我已成功读取450,028数据。

我该如何解决此错误?可以进行哪些修改以改进我的代码?如果你们能帮助我,我非常感谢:)

public class Demo2 {
    public static void main(String[] args) {    

        String url = "jdbc:mysql://localhost:3306/database";
        String username = "root";
        String password = "password";
        
        try {
            //Connect to the database
            Connection connection = DriverManager.getConnection(url, username, password);
            
            //Test on one table only
            String tableName = "Tickets";
            
            System.out.println("Connecting to TABLE " +tableName +"...");
            readCSVFile(tableName, connection);
            
            System.out.println();
            System.out.println("THE END");

            connection.close();//close connection to the database
        }
        catch (SQLException e) {
            System.out.println("ERROR at main(): SQLException!!");
            e.printStackTrace();
        }
    }
    
    static int countNewRow = 0;
    static int countUpdatedRow = 0;
    
    //Method to read the CSV File
    static void readCSVFile(String tableName, Connection conn) {
        
        //Read CSV File
        try {
            String path = tableName +".csv";
            
            BufferedReader br = new BufferedReader(new FileReader(path));
            br.readLine();//skip the first line
            String inData;
            
            //Read The Remaining Line
            while((inData=br.readLine()) != null)
            {
                String[] rowData = inData.split(",");
                ArrayList <String> rowDataList = new ArrayList<String>();
                
                for (int i=0; i<rowData.length; i++)
                    rowDataList.add(rowData[i]);
                
                
                //To combine String that starts and ends with "
                for(int i=0; i<rowDataList.size(); i++) {
                    if (rowDataList.get(i).charAt(0) == '"') {
                        String string1 = rowDataList.get(i).substring(1, rowDataList.get(i).length());
                        String string2 = rowDataList.get(i+1).substring(0, rowDataList.get(i+1).length()-1);
                        String combined = string1 +"," +string2;
                        
                        rowDataList.set(i, combined); 
                        rowDataList.remove(i+1);
                        
                        break;
                    }
                }
                
                
                //Remove the RM
                for(int i=0; i<rowDataList.size(); i++) {
                    if (rowDataList.get(i).startsWith("RM")) {
                        String string = rowDataList.get(i).substring(2);
                        rowDataList.set(i, string);
                    }
                }
                
                //This is just to keep track of the data that has been read
                System.out.println("[" +rowDataList.get(0) +"]");
                
                //Transfer the data to the database
                insertToDatabase(conn, tableName, rowDataList);
            }
            
            System.out.println("New Row Added : " +countNewRow);
            System.out.println("Updated Row   : " +countUpdatedRow);
            System.out.println("== Process Completed ==");
            br.close();
        }
        catch (FileNotFoundException e) {
            System.out.println("ERROR at readCSVFile(): FileNotFoundException!!");
            e.printStackTrace();
        }
        catch (IOException e) {
            System.out.println("ERROR at readCSVFile(): IOException!!");
            e.printStackTrace();
        }
        catch (SQLException e) {
            System.out.println("ERROR at readCSVFile(): SQLException!!");
            e.printStackTrace();
        }
        catch (ParseException e) {
            System.out.println("ERROR at readCSVFile(): ParseException!!");
            e.printStackTrace();
        }
    }
    
    
    static void insertToDatabase(Connection connection, String tableName, ArrayList <String> rowDataList) throws SQLException, ParseException {
        
        String tableIdName = tableName;
        if (tableIdName.charAt(tableIdName.length()-1) == 's')
            tableIdName = tableIdName.substring(0, tableIdName.length()-1);
        
        //To read row
        String rowID = rowDataList.get(0);
        String selectSQL = "SELECT * FROM " +tableName +" "
                          +"WHERE " +tableIdName +"_ID = " +rowID;
        
        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(selectSQL);
        
        boolean value = result.next();
        
        //INSERT @ UPDATE row
        if (value == true) { //Update Row if the data is already existed
            updateStatementt(tableName, connection, rowDataList);
            countUpdatedRow++;
        }
        else { //Insert New Row
            insertStatementt(tableName, connection, rowDataList);
            countNewRow++;
        }
    }
    
    //Method to insert data to the database
    static void insertStatementt(String tableType, Connection conn, ArrayList <String> rowDataList) throws SQLException, ParseException {
        
        //Generate Question Mark
        String generateQuestionMark = null;
        
        if(rowDataList.size() == 1)
            generateQuestionMark = "?";
        else
            generateQuestionMark = "?, ";
        
        for(int i=1; i<rowDataList.size(); i++) {
            if(i!=rowDataList.size()-1)
                generateQuestionMark += "?, ";
            else
                generateQuestionMark += "?";
        }
        
        //Insert sql
        String sql = "INSERT INTO " +tableType +" VALUES (" +generateQuestionMark +")";
        
        PreparedStatement insertStatement = conn.prepareStatement(sql);
        
        //Insert data

        //There are other 'if' and 'else if' statements here for other tables

        else if (tableType.equals("Tickets")) {
            int ticketID = Integer.parseInt(rowDataList.get(0));
            int movieId = Integer.parseInt(rowDataList.get(1));
            int theaterId = Integer.parseInt(rowDataList.get(2));
            String[] date = rowDataList.get(3).split("/");
            String dateString = date[2] +"-" +date[1] +"-" +date[0];
            Date showDate = Date.valueOf(dateString);
            int showTimeId = Integer.parseInt(rowDataList.get(4));
            int cptId = Integer.parseInt(rowDataList.get(5));
            int pcId = Integer.parseInt(rowDataList.get(6));
            float amountPaid = Float.parseFloat(rowDataList.get(7));
            int year = Integer.parseInt(rowDataList.get(8));
            String month = rowDataList.get(9);
            
            insertStatement.setInt(1, ticketID);
            insertStatement.setInt(2, movieId);
            insertStatement.setInt(3, theaterId);
            insertStatement.setDate(4, showDate);
            insertStatement.setInt(5, showTimeId);
            insertStatement.setInt(6, cptId);
            insertStatement.setInt(7, pcId);
            insertStatement.setFloat(8, amountPaid);
            insertStatement.setInt(9, year);
            insertStatement.setString(10, month);
        }
        
        insertStatement.executeUpdate();        
        insertStatement.close();
    }
    
    
    //Method to update the data from the database
    static void updateStatementt(String tableType, Connection conn, ArrayList <String> rowDataList) throws SQLException {
        Statement  statement = conn.createStatement();
        String sql = "UPDATE " +tableType;
                
        //There are other 'if' and 'else if' statements here for other tables
        
        else if (tableType.equals("Tickets")) {
            String[] date = rowDataList.get(3).split("/");
            String dateString = date[2] +"-" +date[1] +"-" +date[0];
            
            sql += " SET movie_id = " +rowDataList.get(1) +","
                +  " theater_id = " +rowDataList.get(2) +","
                +  " showdate = \"" +dateString +"\","
                +  " showtime_id = " +rowDataList.get(4) +","
                +  " costperticket_id = " +rowDataList.get(5) +","
                +  " personcategory_id = " +rowDataList.get(6) +","
                +  " amount_paid = " +rowDataList.get(7) +","
                +  " year = " +rowDataList.get(8) +","
                +  " month = \"" +rowDataList.get(9) +"\""
                +  " WHERE ticket_id = " +rowDataList.get(0);
        }
        
        statement.executeUpdate(sql);
    }
}
捐赠:

您应该为更新语句添加'''statement.close()'''。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

读取Java中的Big文件,速度太慢,超出了gc开销限制

Clojure错误-超出了GC开销限制

错误java.lang.OutOfMemoryError:超出了GC开销限制

将大量数据写入excel:超出了GC开销限制

尝试读取pcr值时发生TPM错误(7)

Android Studio:超出了GC开销限制

PySpark超出了GC开销限制

crashlyticsGenerateSymbolsRelease-超出了GC开销限制

尝试读取数据库 C# 错误

kapt发生异常:java.lang.OutOfMemoryError:超出了GC开销限制

错误:尝试读取结果集时遇到致命错误

尝试读取Rapid XML中的节点会导致错误

尝试读取布尔输入时出现Scanner InputMismatchException错误

Eclipse milo:尝试读取数据时会话已关闭

Apache NiFi-OutOfMemory错误:SplitText处理器超出了GC开销限制

在Scala项目上-运行sbt test命令时超出了错误GC开销限制

PySpark错误java.lang.OutOfMemoryError:超出了GC开销限制

获取错误:java.lang.OutOfMemoryError:超出了GC开销限制

在Java Spark中收集数据集时超出了OutOfMemoryError GC开销限制

Java Spark-java.lang.OutOfMemoryError:超出了GC开销限制-大数据集

Tomcat java.lang.OutOfMemoryError:超出了GC开销限制

java.lang.OutOfMemoryError:超出了GC开销限制

Pyspark:java.lang.OutOfMemoryError:超出了GC开销限制

如何重现Java OutOfMemoryError-超出了GC开销限制

超出了GC开销限制,但有足够的内存

Android Studio中超出了GC开销限制

无法执行dex:超出了GC开销限制->库

构建Android源时超出了GC开销限制

Jmeter java.lang.OutOfMemoryError:超出了GC开销限制