Why Prepared Statements are important and how to use them "properly"
For(int I = 0; I < 1000; ++I)
{
????????PreparedStatement ps =conn.prepareStatement("select a,b from t where c = " + I);
????????ResultSet rs = Ps.executeQuery();
????????Rs.close();
????????Ps.close();
}

Here the cache won't be used. Each iteration of the loop sends a different SQL statement to thedatabase. A new access plan is computed for each iteration and we're basically throwing CPU cyclesaway using this approach. However, look at the next snippet:

Here it will be much more efficient. The statement sent to the database is parameterized usingthe '?' marker in the sql. This means every iteration is sending the same statement to the databasewith different parameters for the "c=?" part. This allows the database to reuse the access plansfor the statement and makes the program execute more efficiently inside the database. Thisbasically let's your application run faster or makes more CPU available to users of thedatabase.

Things can get more complicated when we use a J2EE server. Normally, a prepared statement isassociated with a single database connection. When the connection is closed, the preparedstatementis discarded. Normally, a fat client application would get a database connection and then hold itfor its lifetime. It would also create all prepared statements eagerly or lazily.Eagerly means that they are all created at once when the application starts. Lazilymeans that they are created as they are used. An eager approach gives a delay when the applicationstarts but once it starts then it performs optimally. A lazy approach gives a fast start but as theapplication runs, the prepared statements are created when they are first used by the application.This gives an uneven performance until all statements are prepared but the application eventuallysettles and runs as fast as the eager application. Which is best depends on whether you need a faststart or even performance.

The problem with a J2EE application is that it can't work like this. It only keeps a connectionfor the duration of the request. This means that it must create the prepared statements every timethe request is executed. This is not as efficient as the fat client approach where the preparedstatements are created once, rather than on every request. J2EE vendors have noticed this anddesigned connection pooling to avoid this performance disadvantage.

When the J2EE server gives your application a connection, it isn't giving you the actualconnection; you're getting a wrapper. You can verify this by looking at the name of the class forthe connection you are given. It won't be a database JDBC connection, it'll be a class created byyour application server. Normally, if you called close on a connection then the jdbc driver closesthe connection. We want the connection to be returned to the pool when close is called by a J2EEapplication. We do this by making a proxy jdbc connection class that looks like a real connection.It has a reference to the actual connection. When we invoke any method on the connection then theproxy forwards the call to the real connection. But, when we call methods such as close instead ofcalling close on the real connection, it simply returns the connection to the connection pool andthen marks the proxy connection as invalid so that if it is used again by the application we'll getan exception.

Wrapping is very useful as it also helps J2EE application server implementers to add support forprepared statements in a sensible way. When an application calls Connection.prepareStatement, it isreturned a PreparedStatement object by the driver. The application then keeps the handle while ithas the connection and closes it before it closes the connection when the request finishes.However, after the connection is returned to the pool and later reused by the same, or anotherapplication, , then ideally, we want the same PreparedStatement to be returned to theapplication.

J2EE PreparedStatement Cache is implemented using a cache inside the J2EE server connection poolmanager. The J2EE server keeps a list of prepared statements for each database connection in thepool. When an application calls prepareStatement on a connection, the application server checks ifthat statement was previously prepared. If it was, the PreparedStatement object will be in thecache and this will be returned to the application. If not, the call is passed to the jdbc driverand the query/preparedstatement object is added in that connections cache.

We need a cache per connection because that's the way jdbc drivers work. Any preparedstatementsreturned are specific to that connection.

If we want to take advantage of this cache, the same rules apply as before. We need to useparameterized queries so that they will match ones already prepared in the cache. Most applicationservers will allow you to tune the size of this prepared statement cache.

In conclusion, we should use parameterized queries with prepared statements. This reduces theload on the database by allowing it to reuse access plans that were already prepared. This cache isdatabase-wide so if you can arrange for all your applications to use similar parameterized SQL, youwill improve the efficiency of this caching scheme as an application can take advantage of preparedstatements used by another application. This is an advantage of an application server because logicthat accesses the database should be centralized in a data access layer (either an OR-mapper,entity beans or straight JDBC).

Finally, the correct use of prepared statements also lets you take advantage of the preparedstatement cache in the application server. This improves the performance of your application as theapplication can reduce the number of calls to the JDBC driver by reusing a previous preparedstatement call. This makes it competitive with fat clients efficiency-wise and removes thedisadvantage of not being able to keep a dedicated connection.

If you use parameterized prepared statements, you improve the efficiency of the database andyour application server hosted code. Both of these improvements will allow your application toimprove its performance.