使用Java的常见jdbc更新查询
我正在使用jdbc编写更新查询。但基于条件,我必须为列设置不同的值。此代码是否可以修改为更简单的代码?请让我知道你的想法。使用Java的常见jdbc更新查询
if(allDealsCreated) {
System.out.println("Updating the status of the deals as CLOSED");
if(deals != null && !deals.isEmpty()) {
for (String dealId : deals) {
PreparedStatement closedPreparedStatement = null;
try (Connection con = DriverManager.getConnection(
"jdbc:as400://localhost/BB",
"<username>",
"<password>")) {
String sql = "<Update query to set status as closed>";
closedPreparedStatement = con.prepareStatement(sql);
closedPreparedStatement.executeUpdate();
} catch(Exception e) {
e.printStackTrace();
}
}
}
} else {
System.out.println("Updating the status of the deals as NEW");
if(deals != null && !deals.isEmpty()) {
for (String dealId : deals) {
PreparedStatement newPreparedStatement = null;
try (Connection con = DriverManager.getConnection(
"jdbc:as400://localhost/BB",
"<username>",
"<password>")) {
String sql = "<Update query to set status as new>";
newPreparedStatement = con.prepareStatement(sql);
newPreparedStatement.executeUpdate();
} catch(Exception e) {
e.printStackTrace();
}
}
}
}
您的代码有问题,您是在for循环中创建Connection和PreparedStatements。这不是最佳做法。请按照下面的代码。
if (deals != null && !deals.isEmpty()) {
try {
Connection con = DriverManager.getConnection(
"jdbc:as400://localhost/BB",
"<username>",
"<password>");
PreparedStatement preparedStatement = null;
String sql;
if (allDealsCreated) {
System.out.println("Updating the status of the deals as CLOSED");
sql = "UPDATE DEALS SET STATUS = 'CLOSED' WHERE DEALNO= ?";
} else {
System.out.println("Updating the status of the deals as NEW");
sql = "UPDATE DEALS SET STATUS = 'NEW' WHERE DEALNO= ?";
}
preparedStatement = con.prepareStatement(sql);
for (String dealId : deals) {
preparedStatement.setString(1, dealId);
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}
}
我没有得到这一点paramIndex = 0;
比我想要回答的要好,因为它会将“交易”检查移到外面,所以你不会建立一个你不会使用的连接。 –
你有参数传递查询吗? –
如果你的代码的两半之间的唯一区别是SQL查询本身,那么应该在开始时根据'allDealsCreated'设置那个(并且只有这个)。我也只能得到一次连接,而不是在循环的每次迭代中。这应该简化一些事情。 – dave
@dave,你可以请示例 – Yakhoob
解释我没有给你代码,因为我认为这将是一个很好的学习经验,让你自己尝试一下,而不是我手动喂食。但是,它看起来像别人已经做到了:) – dave