The idea of prepared statements is to be able to use the same prepared statement over and over again many times. However, the convention in bean-managed entity beans is to get a connection (presumeably from the connection pool), use it, and then close it in each method, which means the prepared statements, if any, are used only once, I think.
Would it be reasonable in setEntityContext to get the connection, prepare a set of frequently used SQL statements (containing "?" parameters), and then in the other methods use the connection obtained in setEntityContext, but not close it? Would this allow the prepared statements to remain open? If so, it would be a lot more efficient than dynamically constructing the SQL every time. If getConnection is called in each method without closing the connection, does one get the same connection each time getConnection is called? Would unsetEntityContext be the correct place to close the connection in the above case? Is there an alternative way to efficiently exploit prepared statements? Oracle can cache prepared statements more efficiently than dynamically constructed ones, but reusing previously prepared statements is even more efficient since fewer JDBC calls are required. The downside of course is that prepared statements belong to an open connection and thus are destroyed with the connection is destroyed. A clever session pooling mechanism might provide some way to retain prepared statements and re-prepare them only when absolutely necessary. There needs to be some tradeoff between keeping as many prepared statements as possible available without having too many sessions open. Also, there needs to be some configurable limit on the number of prepared statements to prevent using up too much memory. _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
