How to exploit prepared statements

[cjohan]

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 call getConnection, use the connection, 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.

Would unsetEntityContext be the correct place to close the connection in the above case?

[armint]

Most likely not closing the db connection will be bad. Every instance, even pooled ones without identities, is going to have an open connection. If you only have a few entities instantiated at a time, it's not much of a problem. However, for n number of entity beans you will have n connections and n statements open. Also, most of the time those connections will be idle, just taking up resources.

In my experience, creating prepared statements is not much of a performance problem.

[kashpaw]

The pool maintains a cache of the prepared statements ready for reuse, along with open connections. When you call close on the pooled connection it is just returned to the pool. The connection is only closed when the pool decides to, according to how it was configured.

Granted, this can be confusing if you're used to working only with vanilla jdbc connections.

[armint]

kashpaw, you're correct, but if an entity bean instance retains a connection, that's one connection that is not available to other clients. Until close() is called, the connection will be owned by the bean.

[kashpaw]

so close it already

[cjohan]

OK. OK. I've got more than 170,000 instances, so I'd better close it. :)

Thanks for the clarification of how the pooling works.