title: Java Programming - Where should SQL statements be stored?
body: <p>Where should an JDBC-compliant application store its SQL statements and why?</p>
  
  <p>So far, I managed to identify these options:</p>
  
  <ul>
  <li>Hardcoded in business objects</li>
  <li>Embedded in <a href="http://en.wikipedia.org/wiki/SQLJ" rel="noreferrer">SQLJ</a> clauses</li>
  <li>Encapsulate in separate classes e.g.
  <a href="http://en.wikipedia.org/wiki/Data_Access_Object" rel="noreferrer">Data Access Objects</a></li>
  <li>Metadata driven (decouple the object
  schema from the data schema -
  describe the mappings between them in
  metadata)</li>
  <li>External files (e.g. Properties or
  Resource files)</li>
  <li>Stored Procedures</li>
  </ul>
  
  <p>What are the “Pros” and “Cons” for each one?</p>
  
  <p>Should SQL code be considered “code” or “metadata”?</p>
  
  <p>Should stored procedures be used only for performance optimisation or they are a legitimate abstraction of the database structure?</p>
  
  <p>Is performance a key factor the decision? What about <a href="http://en.wikipedia.org/wiki/Vendor_lock-in" rel="noreferrer">vendor lock-in</a>?</p>
  
  <p>What is better – loose coupling or tight coupling and why?</p>
  
  <p><em>EDITED: Thank you everyone for the answers – here is a summary:</em></p>
  
  <p><strong>Metadata driven i.e. Object Relational Mappings (ORM)</strong></p>
  
  <p>Pros:</p>
  
  <ul>
  <li>Very abstract - DB server can be
  switched without the need to change
  the model</li>
  <li>Wide-spread - practically a standard</li>
  <li>Cuts down the amount of SQL needed</li>
  <li>Can store SQL in resource files</li>
  <li>Performance is (usually) acceptable</li>
  <li>Metadata driven approach</li>
  <li>(Database) vendor independence</li>
  </ul>
  
  <p>Cons:</p>
  
  <ul>
  <li>Hides SQL and true developers
  intentions</li>
  <li>SQL difficult to be reviewed/changed
  by DBA</li>
  <li>SQL might still be needed for odd
  cases</li>
  <li>Can force usage of a proprietary
  query language e.g. HQL</li>
  <li>Does not lend itself to optimisation
  (abstraction)</li>
  <li>Can lack referential integrity</li>
  <li>Substitutes for lack of SQL knowledge
  or lack of care to code in the DB</li>
  <li>Never match native database
  performance (even if it comes close)</li>
  <li>Model code is very tight coupled with
  the database model</li>
  </ul>
  
  <p><strong>Hardcoded/encapsulated in DAO layer</strong></p>
  
  <p>Pros:</p>
  
  <ul>
  <li>SQL is kept in the objects that
  access data (encapsulation)</li>
  <li>SQL is easy to write (speed of
  development)</li>
  <li>SQL is easy to track down when
  changes are required</li>
  <li>Simple solution (no messy
  architecture)</li>
  </ul>
  
  <p>Cons:</p>
  
  <ul>
  <li>SQL cannot be reviewed/changed by DBA</li>
  <li>SQL is likely to become DB-specific</li>
  <li>SQL can become hard to maintain</li>
  </ul>
  
  <p><strong>Stored Procedures</strong></p>
  
  <p>Pros:</p>
  
  <ul>
  <li>SQL kept in the database (close to
  data)</li>
  <li>SQL is parsed, compiled and optimised
  by the DBMS</li>
  <li>SQL is easy for DBA to review/change</li>
  <li>Reduces network traffic</li>
  <li>Increased security</li>
  </ul>
  
  <p>Cons:</p>
  
  <ul>
  <li>SQL is tied to the database (vendor
  lock-in)</li>
  <li>SQL code is harder to maintain</li>
  </ul>
  
  <p><strong>External files (e.g. Properties or Resource files)</strong></p>
  
  <p>Pros</p>
  
  <ul>
  <li>SQL can be changed without a need to
  rebuild the application</li>
  <li>Decouples the SQL logic from the
  application business logic</li>
  <li>Central repository of all SQL
  statements – easier to maintain</li>
  <li>Easier to understand</li>
  </ul>
  
  <p>Cons:</p>
  
  <ul>
  <li>SQL code can become un-maintainable</li>
  <li>Harder to check the SQL code for
  (syntax) errors</li>
  </ul>
  
  <p><strong>Embedded in SQLJ clauses</strong></p>
  
  <p>Pros:</p>
  
  <ul>
  <li>Better syntax checking</li>
  </ul>
  
  <p>Cons:</p>
  
  <ul>
  <li>Ties too closely to Java</li>
  <li>Lower performance than JDBC</li>
  <li>Lack of dynamic queries</li>
  <li>Not so popular</li>
  </ul>