db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Static SQl in Derby
Date Fri, 03 Aug 2007 18:55:02 GMT
If your application constructs SQL dynamically, it can be designed to  
exploit the compiled query aspects by following some guidelines:

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.


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:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

View raw message