Solved

Questions on an Article about Prepared Statements and Connection Pooling

Posted on 2002-03-26
2
221 Views
Last Modified: 2008-02-01
Elo people of EE,

<warning:  asker is a n00b>

I have a question regarding the use of prepared statements in J2EE.  I came upon an article regarding the use of prepared statements:

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

Probably some of you must have been familiar with it.  Anyway, it says there that the efficient way to use prepared statements is to place it in this form:

PreparedStatement ps = conn.prepareStatement("select a,b from t where c = ?");
For(int I = 0; I < 1000; ++I)
{
        ps.setInt(1, I);
        ResultSet rs = ps.executeQuery();
        Rs.close();
}
ps.close();

and not like:

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();
}

because each call to conn.prepareStatement() is the same as recompiling the same SQL all over again like its a different SQL.  <correct me on this as this'll be a key part of my question>

Now that's fine and dandy, until i continued reading the rest of the page that somewhat gives a different air.  I came upon a line that says:

"...after the connection is returned to the pool and later reused by the same, or another application, , then ideally, we want the same PreparedStatement to be returned to the application. ..."

To my understanding, what was wanted here was that after preparing the statement in a particular connection and returning it to the connection pool, then when it is reused by another app, say another jsp, the previously prepared statement that was made on that connection can be used again.  

After that came a section about the J2EE prepared statement cache which basically says, that each connection object in the connection pool contains one of these caches.  and that a cache "keeps a list of  prepared statements".  Perhaps, as another assumption, this list was the result of a previous usage of the said connection.

Finally, at the ending, it says:
"If we want to take advantage of this cache, the same rules apply as before. We need to use parameterized queries so that they will match ones already prepared in the cache. "

I am assuming that the rules the author was referring to was the pseudocode i posted above.

Now my questions are:
1.  If i'm using a J2EE server, would repeated calls to conn.prepareStatement() on the same sql be still inefficient (as shown in the second pseudocode) ?  Because it states recently that if a parameterized query matches that of what's in the cache then in effect i would still be using a cached ps instead of making a new access plan for the same sql like a database cache.

2.  Am i correct in saying that a prepared statement in the cache of a connection still carried over and can be possibly used by another app?  Would it be like, a connection object at one time, will become 'complete' with a list of prepared statements in its cache?

32.  What would be the best pattern for use in prepared statements for use in client/servers?  <links would be nice :)>


Follow up questions:
1.  Is IBM Websphere 3.5.3 be considered a J2EE server that uses the mentioned J2EE connection scheme?
0
Comment
Question by:Anche
2 Comments
 
LVL 3

Accepted Solution

by:
randyd earned 100 total points
ID: 6896463
1. yes.  you should minimize the calls to any function to reduce overhead if at all possible.  in your example, you would be forcing a bunch of lookup in the cache and some stack to pass the results back...  for no benefit.

2. there are memory limitations to how many are kept around, i think its based on how many times the particular query has been accessed (stays around longer if used more) and the number that will fit into the cache memory. (not sure where this is parameterized)

3. your example works well.  it's up to you to decide whether that loop should be in a GUI client or in some servlet or something... depends on your overall architecture.

hope this helps
randy
0
 

Author Comment

by:Anche
ID: 6917549
well, this'll suffice i guess.  thanks!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
The viewer will learn how to implement Singleton Design Pattern in Java.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now