Prepared statement initialized tiwice and closed once

Question

Hi,

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();

 }

Answer 1

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.