Since the PreparedStatement is within the Connection context, you can only reuse it with the same connection. Next time you check out from the pool you may get a different connection, hence you won't be able to reuse your PreparedStatement. However, ...
If you associate a map of PreparedStatements with each connection, you may have your Prepare dStatement cached across usages although for possibly different threads (the users of the connections). You would typically build the cache on demand - if there is no prepared statement for your SQL, create a new one and store it in the cache. The problem with this approach is how you attach the prepared statements to the connections. If you are using a 3rd-party (e.g. J2EE) connection pool, it's pretty tough - you would probably have to identify connections somehow and keep a separate catalog of PStmts hashtables. If you have your own connection pool, you can always implement Connection by wrapping the actual Connection object and delegating all of the Connection.method() to it. If this seems to end up in too much development, I would rather profile carefully the PreparedStatement creation and see if it's worth it. The today's databases and Intranets are so fast that this effort might not be worth it. Don't fall into the stereotype that any database-related operation is slow - measure it first to see if you really have the problem. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 1:24 PM To: JDJList Subject: [jdjlist] PreparedStatement hi all! is there anyone who can quickly answer the following question for me (otherwise I will have to dig into JDBC implementation details...) in one of my servlets I would like to keep PreparedStatement objects over numerous calls to the same pages and just update the parameters every time. e.g. Connection con = ... //obtain connection, perhaps from connection pool PreparedStatement pstmt = con.prepareStatement("SELECT * FROM any WHERE some=?"); I keep the reference pstmt (e.g. inside the session object) for later use. the connection, on the other hand, is released again (because it came from a connection pool or the like, not closed!) now my question is: when I call something like pstmt.setInt(1,10); ResultSet rs = pstmt.executeQuery(); later, is it of any relevance if the connection-object that was used for the creation of the PreparedStatement is back in the pool, or even used by someone else in the meantime? thanx & greetings to all -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net To change your membership options, refer to: http://www.sys-con.com/java/list.cfm To change your membership options, refer to: http://www.sys-con.com/java/list.cfm
