Solved

Questions on an Article about Prepared Statements and Connection Pooling

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
eclipse package explorer vs project explorer view 2 122
Java Loop 6 59
hibernate insert example 13 28
Desingning Refactoring existing code 2 24
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
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 …

786 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