Dave Ford wrote:
> 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:
>
You are correct that this particular benefit of prepared statements is hard to come
by when using connection pools. At the JavaOne session on JDBC 3.0, they talked
about some work going on to try to make this better in the next spec version.
In the mean time, prepared statements have another very important benefit -- so
important it makes them helpful for even one-time use. That benefit is that
prepared statements deal with data conversions for you.
Consider trying to store a name like "O'Reilly" into a database with an SQL INSERT
or UPDATE statement. A naive JDBC program will end up trying to do something like
this:
update customers
set last_name = 'O'Reilly'
where customer_id = 123
with the predictable syntax error. If you use a prepared statement to create this
update statement, the JDBC driver deals with the embedded quote character for you
(in whatever manner is required for that particular database, so that your SQL
statement can stay as database-independent as possible).
Another place this really helps is on dates and times -- databases have lots of
different expectations for the string version of a date or time, but that's all
dealt with inside the JDBC driver if you are using prepared statements.
Craig McClanahan
===========================================================================
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
