prepared statements and connection pooling

Discussions

General J2EE: prepared statements and connection pooling

  1.  hi

           The prepared statement once prepared always exists in the cache of the connection, but with connection pooling once I close the connection, the connection is returned to the pool. If I want to make use of the same prepared statement once again i.e., from the cache, how do I get the same prepared statement again, because I am not sure wheather I will get the same connection object again.
          What I thought is whenever I make a request, a free connection object is returned from the connection pool.

       can somebody answer this
  2. You will have to hold on to the connection using which you create PreparedStatements you wish to reuse. You cannot release this connection to the pool, unfortunately. Besides, as of now, Connections do not perform Statement caching, this is part of proposed new version of the JDBC standard. You will have to do it yourself, most probably. And this is tricky (imagine if your connection becomes "bad": you will have to detect this, get rid of that connection and to purge statement cache).

    Denis
  3. Have a look at the following :-

    http://theserverside.com/resources/article.jsp?l=Prepared-Statments

    Cheers,
    Rick
  4. Not really... This is too simple when you create statement in the same context in which you use it. What I was trying to apply this to, was e.g. BMP Entity loading using cached PreparedStatements. Speed improvement was significant, if not staggering ;)

    Denis.
  5. The preparedstatement optimize is processed( pre-compiled ) on the JDBC level or on the actual database level?
  6. Should be both ;) correct me if I'm wrong...