Hi Jeff,
I guess you are right about performance, at least for the time being with MySQL. However I would encourage you to use PreparedStatements for the following reasons:
1. more portable - as you say, you will get big benefits in Oracle, for example, this way
2. easier for the programmer - if any of your variable data might contain quote characters or other chars requiring escape sequences, the JDBC driver handles all this for you if you use PreparedStatements. If you use the ordinary Statement object, then the String you construct for the query must have all these things escaped by hand.
In essence, even if there is no significant performance benefit, I'd say you get more robust code for virtually no extra cost. Just my $0.02 worth.
Hope this helps
Keith
-----Original Message-----
From: Jeff Mathis [mailto:jmathis@stripped]
Sent: Wed 21/05/2003 23:39
To: java@stripped
Cc:
Subject: Statements vs PreparedStatements
Hi folks,
I'm using the 3.07 release with mysql 4.04. All our tables are InnoDB
tables. The API has a class per table, with several classes sharing
inheritance heirarchies as appropriate. I've got a central DBStore class
that everything goes through for queries. I have my own set of
"Attribute" classes, LongAttr, IntAttr, DateAttr, etc, that everything
coming from the database becomes rather than using java.sql objects or
primitives. These classes take care of formatting themselves for
queries, using or not using quotes when necessary, etc. When loading
from a resultset, I always call the appropriate getXXX method with a
column index.
For everything I do, I create a statement from my connection, issue the
query, look at the result set, then close the statement. Because mysql
does not "compile" sql and cache it, I have been of the opinion that it
is not necessary to create prepared statements. In contrast, with an
Oracle database, use of PreparedStatements is almost required.
The question is, would using PreparedStatements speed things up? I don't
think I'm suffering from any performance hit now. About the only thing
I've noticed is when tables get a lot of columns (~80 or so), setting
all the attributes seems to be slower. But, programmatically, is it
wrong to do things as I've indicated?
always looking for improvements ...
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company jmathis@stripped
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
