when and what to close java.sql.PreparedStatement

From: Jeges Endre (jeges_at_vekoll.vein.hu)
Date: 11/01/04

  • Next message: Joe Weinstein: "Re: when and what to close java.sql.PreparedStatement" 
    Date: 1 Nov 2004 06:26:50 -0800

    Hi!

    Question one:

    I have bunches of the following code (there is Oracle 9i behind it):

    public static Vector getSomeVector(Connection conn) throws
    SQLException {
      java.util.Vector result = new Vector();
      java.sql.PreparedStatement pstmt = conn.prepareStatement(SOME_SQL);
      try {

        //Should I put this out of the try finally block. Start of block
        pstmt.setInt(1, 123456);
        pstmt.setString(2, "dummy");
        java.sql.ResultSet rset = pstmt.executeQuery();
        //End of block

        while (rset.next()) {
          result.add(rset.getString("SOME_FIELD"));
        }
      } finally {
        pstmt.close();
      }
    }

    I don't want to catch any exception, just want to close the
    PreparedStatement the right way. The question is: Do I have to close
    the _prepared_ statement or whether I have to close the _executed_
    statement? I know it would probably work the both way, I just want to
    clearify my idea of PreparedStatements.

    Question Two:

    I wonder if it is a good idea not to close after every execution, but
    just once at the end of the loop? I have heard of caching at the
    server side, and that the code below should have better performance if
    the PreparedStatement is not closed after every execution. Is this
    true?

    public static Vector getSomeVector(Connection conn) throws
    SQLException {
      java.util.Vector result = new Vector();
      java.sql.PreparedStatement pstmt = conn.prepareStatement(SOME_SQL);
      try {

        //Is it a good idea not to close after every execution, but just
    once?
        for(int i = 0; i < 10; i++) {
          pstmt.setInt(1, 123456);
          pstmt.setString(2, "dummy");
          java.sql.ResultSet rset = pstmt.executeQuery();
          while (rset.next()) {
            result.add(rset.getString("SOME_FIELD"));
          }
        }

      } finally {
        pstmt.close();
      }
    }

    Thanks in advance,

    jeges

  • Next message: Joe Weinstein: "Re: when and what to close java.sql.PreparedStatement"