使用Java的常见jdbc更新查询

Question

我正在使用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();
        }
    }
}
}

Answer 1

代码的问题是在for循环中创建连接和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();
        }
    }