在使用java比较和更新数据时,如何解决巨大的性能问题?
我想比较两个具有相同类型的对象的列表,它们有大约30个值。当存在差异时,应将新值更新到数据库中
我的代码变得非常慢的部分,比如每分钟更新一次,是数据应该更新的时间
从数据库和Excel中读取所有数据集的速度很快。但是当涉及到下面的代码时,它已经在200个数据集之后几乎停止了
我无法为您提供完整的代码,因为getList()方法调用了很多函数,在我看来,这些函数的代码太多了。但是也许你仍然可以帮我解决我的问题,因为性能变差的相关部分发生在给定的代码中
也许有很多东西被创造出来了
编辑:我用IntelliJ运行程序。当我关闭正在运行的应用程序并再次启动它时,它会从上一次加载已经缓慢加载的数据集,加载速度非常快,但随后再次减慢。也许缓存出了问题
这是我的代码:
public class MainWindow_Controller {
List<Pensioner> pensionerDB = new ArrayList();
List<Pensioner> pensionerExcel = new ArrayList();
Boolean checkStatusChange = false;
String pathDB = "";
@FXML
private TextField tfDBPath;
/**
* This method compares the pensioners from database with the ones from the Excel head file.
* Based on matching pensionInsuranceNumbers the method checks if the status is different.
* If it is different, the status gets updated.
*
* @param event
*/
@FXML
void getStatusChange(ActionEvent event) throws FileNotFoundException, IllegalAccessException, SQLException {
String type = "Statuswechsel";
if (pensionerDB.isEmpty() || pensionerExcel.isEmpty()) {
getLists();
}
pathDB = tfDBPath.getText();
//load pensioners from database in first list
pensionerDB = array[0];
//load pensioners from excel head file in second list
pensionerExcel = array[1];
//compare pensionInsuranceNumber from first list with pensionInsuranceNumber from second list and move missing datasets to third list
List<Pensioner> result = new ArrayList();
Database.connect(pathDB);
//iterates over the two lists with pensioners in database and pensioners in Excel files and adds the dataset with matching pensionInsuranceNumbers into the result list.
for (int i = 0; i < pensionerExcel.size(); i++) {
System.out.println("Processing Excelrow Number: " + i);
for (int j = 0; j < pensionerDB.size(); j++) {
updatePensionerData(pensionerExcel, pensionerDB, pathDB, i, j);
if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber() && pensionerExcel.get(i).getStatusOld() != pensionerDB.get(j).getStatusOld()) {
checkStatusChange = true;
pensionerDB.get(j).setStatusNew(pensionerDB.get(j).getStatusOld());
pensionerDB.get(j).setStatusOld(pensionerExcel.get(i).getStatusOld());
result.add(pensionerDB.get(j));
break;
} else if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber() && pensionerExcel.get(i).getStatusOld() == pensionerDB.get(j).getStatusOld()) {
break;
}
}
}
Database.close();
}
public void updatePensionerData(List<Pensioner> pensionerExcel, List<Pensioner> pensionerDB, String pathDB, int i, int j) {
if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber()) {
if (pensionerExcel.get(i).getIdkz() != pensionerDB.get(j).getIdkz()) {
Database.updateIdkz(pensionerExcel.get(i), pathDB);
}
if (!pensionerExcel.get(i).getCompany().equals(pensionerDB.get(j).getCompany())) {
Database.updateCompany(pensionerExcel.get(i), pathDB);
}
if (pensionerExcel.get(i).getPersonelId() != pensionerDB.get(j).getPersonelId()) {
Database.updatePersonelId(pensionerExcel.get(i), pathDB);
}
if (!pensionerExcel.get(i).getBirthDate().isEqual(pensionerDB.get(j).getBirthDate())) {
Database.updateBirthDate(pensionerExcel.get(i), pathDB);
}
//...
//26 more if statements for the other values
//...
}
}
}
我的数据库方法:
public static void updateIdkz(Pensioner p, String pathDB) {
String update = String.format("UPDATE Pensionär SET idkz = ?");
try {
ps = connection.prepareStatement(update);
ps.setInt(1, p.getIdkz());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void updatePersonelId(Pensioner p, String pathDB) {
String update = String.format("UPDATE Pensionär SET PersNr = ? Where pknr = ?");
try {
ps = connection.prepareStatement(update);
ps.setInt(1, p.getPersonelId());
ps.setInt(2, p.getPensionInsuranceNumber());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void updateBirthDate(Pensioner p, String pathDB) {
String update = String.format("UPDATE Pensionär SET Geburtsdatum = ? Where pknr = ?");
try {
ps = connection.prepareStatement(update);
ps.setDate(1, Date.valueOf(p.getBirthDate()));
ps.setInt(2, p.getPensionInsuranceNumber());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
# 1 楼答案
这样会更有效率。您可以重复使用PreparedStatements,甚至批量更新,以加快事务处理。另外,正如其他人所说,记住结束你准备好的陈述