Prepared statement initialized tiwice and closed once

I want to know that if a PreparedStatement object is initialized twice the way shown in code snippet below and closed only once in finally block, will开发者_运维百科 it fail to close? I am not getting any error in this code but will it be a better idea to use 2 different preparedStatements instead of one. I think it fails to close the preparedStatement at #1.

    Connection conn = null;
 PreparedStatement ps = null;
 try {
  conn = getConnection();
  ps = conn.prepareStatement(QueryUtil.UPDATE_POLICY_DETAILS); // #1
  ps.setInt(1, iCancellationPolicyId);
  ps.executeUpdate();

  //some code here


  ps = conn.prepareStatement(QueryUtil.UPDATE_POLICY_CHARGES); // #2
  ps.setInt(1, iCancellationPolicyId);
  ps.executeUpdate();


  //some code here


 } catch (SQLException sqlExp) {
  sqlExp.printStackTrace();
  LOG.fatal(sqlExp);
 } finally {
  ps.close();
  conn.close();

 }

---

You are using two different prepared statements... it's just you're only using one variable. The result of the second call to prepareStatement is assigned to ps, after which you've got no reference to the first prepared statement any more.

Part of me thinks that you should use two separate variables and close each statement separately. The other part of me wonders if closing the connection will automatically close all the prepared statements associated with the connection anyway... I can't see any guarantee of that though.

I think the most robust approach would indeed be to use two different variables. Ideally you should also close each item in its own finally block - otherwise if the first close call throws, you'll skip the next one.

---

I see a few things here.

First, you are simply reassigning the value of ps with the same value. So the second conn.prepare is redundant and can be eliminated.

The second is that you may want to consider whether preparation is necessary. Typically you prepare a command that is used many times. I suspect that preparing for 2 uses is less efficient than simply executing the command.

I could be wrong.

edit: I am wrong. I misread the command values. So, yes, you would probably want to have discrete variables to ensure that they are properly closed.

But the observation regarding preparation is correct, i think.