1. Always use parameter markers when constructing your SQL statements. So instead of generating SQL like "SELECT e.name, e.salary FROM EMPLOYEE e where e.name =\ "Johnny\"", generate SQL "SELECT e.name, e.salary FROM EMPLOYEE e where e.name =?" and save "Johnny" in a parameter list in your code. Later, pass the saved parameter to the prepared statement.
2. Always prepare your dynamically generated SQL statement. This is what allows the JDBC driver to compile the unique statements only once.
Craig On Aug 3, 2007, at 10:39 AM, Bryan Pendleton wrote:
With dynamic SQL, database access and authorization are determined at run time. The user of the application must have all required database privileges, and the database must determine the best way to access the required data at run time. However, with static SQL, access and authorization are determined at customization and bind time.I think Derby is somewhere in between these two. The SQL statements are parsed, compiled, and optimized at runtime, in a dynamic fashion. However, the database engine automatically maintains a cache of compiled statements, and a re-preparation of a SQL statement simply retrieves the already-compiled statement information from the cache for execution. So Derby achieves the flexibility and adaptability of the dynamic approach, but it also realizes substantial performance benefits by caching the results of the statement bind process and re-using them wherever possible. thanks, bryan
Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
