在使用java比较和更新数据时，如何解决巨大的性能问题

Question

我想比较两个具有大约30个值的相同类型对象的列表。当存在差异时，应将新值更新到数据库中
我的代码变得非常慢的地方，比如每分钟更新一次，是数据应该在什么时候更新。

从数据库和Excel中读取所有数据集是很快的。但当涉及到下面的代码时，它已经开始在200个数据集之后几乎停止。

我无法为您提供完整的代码，因为方法getLists((调用了很多函数，在我看来，这些函数的代码太多了。但也许你仍然可以帮助我解决我的问题，因为性能变低的相关部分发生在给定的代码中。

也许有很多物体被创造出来了或者类似的东西？

编辑：我用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();
}
}

Answer 1

这样的东西会更有效率。您可以重用PreparedStatements，甚至可以批量更新，以加快事务处理速度。此外，正如其他人所说，记得关闭您的PreparedStatements。

class Test implements Closeable {
// reusable PreparedStatements
private final PreparedStatement updateIdkz;
private final PreparedStatement updatePersonelId;
private final PreparedStatement updateBirthDate;
public Test(Connection con) throws SQLException {
this.updateIdkz = con
.prepareStatement("UPDATE Pensionär SET idkz = ?");
this.updatePersonelId = con
.prepareStatement("UPDATE Pensionär SET PersNr = ? Where pknr = ?");
this.updateBirthDate = con
.prepareStatement("UPDATE Pensionär SET Geburtsdatum = ? Where pknr = ?");
}
/** Closes all the prepared statements. */
@Override
public void close() throws IOException {
try {
updateIdkz.close();
} catch (SQLException e) {
}
try {
updatePersonelId.close();
} catch (SQLException e) {
}
try {
updateBirthDate.close();
} catch (SQLException e) {
}
}
public void addBatchUpdateIdkz(Pensioner p) {
try {
updateIdkz.clearParameters();
updateIdkz.setInt(1, p.getIdkz());
updateIdkz.addBatch();
} catch (Exception e) {
e.printStackTrace();
}
}
public void addBatchUpdatePersonelId(Pensioner p) {
try {
updatePersonelId.clearParameters();
updatePersonelId.setInt(1, p.getPersonelId());
updatePersonelId.setInt(2, p.getPensionInsuranceNumber());
updatePersonelId.addBatch();
} catch (Exception e) {
e.printStackTrace();
}
}
public void addBatchUpdateBirthDate(Pensioner p) {
try {
updateBirthDate.clearParameters();
updateBirthDate.setDate(1, Date.valueOf(p.getBirthDate()));
updateBirthDate.setInt(2, p.getPensionInsuranceNumber());
updateBirthDate.addBatch();
} catch (Exception e) {
e.printStackTrace();
}
}
public void executeBatchUpdateIdkz() throws SQLException {
updateIdkz.executeBatch();
}
public void executeBatchUpdatePersonelId() throws SQLException {
updatePersonelId.executeBatch();
}
public void executeBatchUpdateBirthDate() throws SQLException {
updateBirthDate.executeBatch();
}
public static void main(String[] args) {
Pensioner p1 = null, p2 = null, p3 = null, p4 = null;
Connection con = null;
// try with resources to ensure you close the prepared statements
try (Test t = new Test(con);) {
// Update multiple Idkz
t.addBatchUpdateIdkz(p1);
t.addBatchUpdateIdkz(p2);
t.addBatchUpdateIdkz(p3);
t.addBatchUpdateIdkz(p4);
t.executeBatchUpdateIdkz();
// Update multile PersonelId
t.addBatchUpdatePersonelId(p1);
t.addBatchUpdatePersonelId(p2);
t.addBatchUpdatePersonelId(p3);
t.addBatchUpdatePersonelId(p4);
t.executeBatchUpdatePersonelId();
// etc...
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}

Answer 2

您可以为每个try... catch添加一个finally块，并关闭PreparedStatement对象。

示例：

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();
}
finally {
ps.close();
}

如果有效，请告诉我。