Actually, this depends on the DBMS.
If I recall correctly, Oracle e.g. keeps the access plan of all statements
over all connections.
So, in this case a statement that was prepared once can be re-used by
another connection.
(there's only the small overhead of looking up what connection matches in
the cache)
My rule is, when you're in doubt, use prepared statements instead of normal
ones.
geert 'Darling' Van Damme
> -----Original Message-----
> From: A mailing list about Java Server Pages specification and reference
> [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Ford
> Sent: zaterdag 24 juni 2000 17:10
> To: [EMAIL PROTECTED]
> Subject: PreparedStatement and Connection Pools
>
>
> My understanding is that the benefit of a PreparedStatement is that you
> "prepare it" ONCE and then "execute it" over and over. Thus, the
> server has
> a chance to optimize complicated SQL statements ahead of time.
> So, based on
> this reasoning, one might choose to "prepare" all of your
> statements in the
> servlet's init() method. Then execute them in the servlet's service (or
> doGet) method over and over.
>
> My Question is, how does one receive this "prepare" benefit when
> using J2EE
> style connection pooling? Here is my code:
>
> try{
> String SQL = ....
> Context nCtx = new InitialContext();
> DataSource ds = (DataSource)nCtx.lookup("jdbc/pooled/mysql_ss1");
> conn = ds.getConnection();
> ps = conn.prepareStatement(sql);
> rs = ps.executeQuery();
> /* use the ps here */
> }
> finally{
> if(ps!=null) ps.close();
> if(conn!=null) conn.close();//this really "releases" rather
> than "closes"
> the connection
> }
>
> Since the scope of conn is local to the service method, and ps is
> connected
> to conn, how can a ps ever live longer than the service method?
>
> Thanks,
>
> Dave Ford
>
> P.S. Is it politically correct, to post to more than one newsgroup?
>
> ==================================================================
> =========
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
> JSP-INTEREST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
>
>
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
