英文:
Trying to read 700k+ of data and the Error "GC Overhead Limit Exceeded" occurred
问题
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) + ","
<details>
<summary>英文:</summary>
Alright so I need help in reviewing my codes because I'm kinda still new in programming (currently in my second year of Diploma in Computer Science). I got this error as in the title ```GC Overhead Limit Exceeded``` when I tried running my code below.
A brief explanation of this code, I'm trying to read data from a CSV File and then transfer it to a database. FYI, there are actually 10 tables/CSV files that I need to read, but on this I'll show this one table ```Tickets``` because the error only occurred when I tried to read that table/file. The other tables have hundreds of rows/data only while the table ```Tickets``` have 735,504 of rows/data. Furthermore, I've succeeded in reading 450,028 of data after 6 hours of running the code before the error occurred.
What can I do to fix this error? What can be modified to improve my code? I really appreciate it if you guys can help me :)
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);
}
}
</details>
# 答案1
**得分**: 1
对于简短的情况,只需读取一行并按照您的意愿处理。您的内存不足以容纳所有的 70 万行。
<details>
<summary>英文:</summary>
For short, read a single line and do whatever you want to do with it. You don't have enough memory for all 700k lines.
</details>
# 答案2
**得分**: 0
如果您真的想将所有这些数据读入Java堆中,可以通过例如使用`-Xmx`命令行开关来增加堆大小。由于文本数据在JVM中的编码方式,您可能需要比总数据大小更多的堆空间。
此外,您的代码中可能有一些地方可以减轻JVM内存管理系统的负担。例如,使用“+”连接字符串可能会生成大量临时数据,这将增加垃圾收集器的负担。使用`StringBuilder`组装字符串可能是一个简单、对资源要求较少的替代方法。
<details>
<summary>英文:</summary>
If you really want to read all this data into the Java heap, increase the heap size using, for example, the `-Xmx` command-line switch. Because of the way textual data is encoded in the JVM, you'll probably need much more heap that the total data size would suggest.
In addition, there might be some places in your code where you can take the strain off the JVM's memory management system. For example, concatenating strings using "+" can generate a lot of temporary data, which will increase the load on the garbage collector. Assembling strings using a `StringBuilder` might be a simple, less resource-hungry, alternative.
</details>
# 答案3
**得分**: 0
你应该为更新语句添加```statement.close()```。
<details>
<summary>英文:</summary>
You should add ```statement.close()``` for the update Statement.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论