Solved

Questions on an Article about Prepared Statements and Connection Pooling

Posted on 2002-03-26
2
229 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 56
Java string replace 11 54
Adding multiple JVM environments to RedHat 6 7 47
Problem to Alipay 10 43
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
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…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

839 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