Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Questions on an Article about Prepared Statements and Connection Pooling

Posted on 2002-03-26
2
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
randyd earned 300 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

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

715 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