Rob, I assume you react to my question.
Although I generally understand the point of having all SQLs in one place, it seems to me that having the text in the source code has its benefits too. Especially if I revisit the code, eg. to check indexes of bind variables. Also editing lengthy SQL statements in a properties file would seem a bit tedious to me, as my display is only so wide. And in a few cases I stuff specific non-constant constructs into the SQL, which would probably require to parcel out single SQL query into several items in the properties file.
Rob Prime wrote:All you need to do is make sure that the columns you use in code / other configuration are still going to be there.
This is an important point. Do you use or know any tool to accomplish this for SQLs stored separately?
To make sure that I don't access non-existent schema objects I have a
String constant defined for every table, column, sequence and view I access with the DAO classes and I rigorously use these constants in my SQL statements, never typing the object names directly into SQL statements. These constants are generated automatically from schema definitions. That way if the object gets renamed or deleted, I get compile-time error.
I strongly prefer to check existence of schema objects before runtime, though there are certainly more ways to do so and certainly this can be done for SQL statements stored separately from DAO classes. Indeed, having the SQL statements separately might even allow for better syntax check, maybe using Oracle's explain plan or equivalents for other DBs. The parcelled-out statements would still pose a problem, though.