Use a persistence layer for your SQL, one that can be verified at compile time (eg. Hibernate, OJB, ObjectStore, EJB Object Persistence).  

One good approach is to place all application uses of SQL code (e.g. HSQL) in a small set of files so it can be easily reviewed.  Syntax errors in any text HSQL or mismatches against the database will not be detected at compile time, but at least the code will be partly validated by the compile process.

Trap: Don't move all your SQL to a .property file.  Property files are only for modifying application behavior without a rebuild and it is unlikely a System Administrator will modify SQL on their own without asking for a rebuild and retest of the entire application.  Property files tend to disassociate information from where it is used, so the ramifications of a change are more likely to be missed.  Also, missing properties are only picked up when the application is running, not at compile time.

And avoid stored procedures completely if you can. Most production applications don't need stored procedures at all; they slow development through extra coordination with database administrators and reduce future migration options.

blog comments powered by Disqus