What are the advantages and disadvantages of externalising SQL queries in Java (or similar general-purpose object-oriented programming language.  Externalizing means storing all SQL in a separate non-java file or files.  Several good examples are here.Does it make debugging harder or easier?
Externalising SQL Queries in Java
java;sql;orm;relational database
Pro:Isolates changes to your code if you ever have to change databases, or could provide a mechanism for supporting multiple SQL dialects, optimizations, or databases with the same Java code as @Morons saidSQL-only changes can be made after deployment.Separation of programmer duties and DBA duties as @JVXR said.A really talented and hardworking DBA should be able to write SQL and manually load database fields into Java objects with better performance (faster run-time) than an ORM tool.  The same was said of compilers vs. assembly language for years, and you don't see us writing much assembly any more.  Where it matters, this is a pro, but I would say it does not often matter.Con:How do you meaningfully manage your database sessions if you've hidden the way your code interacts with the database in a separate non-Java file?  Seems it would make this more difficult.The point of ORM (Object-Relational-Mapping) tools like Hibernate is to let you work with Java objects and query abstractions and let the ORM tool do the queries for you.  Querying a table and loading each column into the proper field of your Java object is a lot of programming work.  Maintaining that code is lots of work too.  ORM tools do nice things like provide proxy objects that eliminate unnecessary queries, while letting you work with database data as type-safe Java objects - without you writing any of the code to do that.  If you then start writing lots of manual SQL queries, you are defeating the purpose of ORM.  Some SQL almost always ends up being written and that could go in an external (non-Java) file.  But usually the less, the better.  It's a question of what you want to spend your time on.  If you use hardly any SQL, it doesn't matter so much where you put it.In most applications, the real work is carried out in the database queries and updates and the code closest to those queries and updates.  Hiding those queries in another file seems to obscure it - encouraging the programmer to be ignorant of the database.  That could be good division of labor, or even a useful abstraction (such as what ORM tools provide) or it could be an unnecessary hurdle to understanding.  Good naming of the SQL routines could mitigate this issue somewhat.Increased separation of Java and Database code could be a barrier to refactoring.  Instead of searching for the field, you need to find the queries that use it, then find where each query is used.  An extra step.ThoughtsI've been moving in the opposite direction - I generate my ORM Java objects from the database after every database change and I have it also generate SQL and HQL (Hibernate Query Language) tokens for each column and each table.  In every SQL query or HQL symbolic query, I append the token instead of the field name.  This way I can search for where each field is used in the application, either as a Java object field, in a HQL query, or in a SQL query.Here is a typesafe Hibernate-style Criteria query example, querying un-deleted records from the thing table from a single company that have an owner:In Thing.java:// SQL Table namepublic static final String SQL_thing = thing;// SQL Column namespublic static final String SQL_id = id;public static final String SQL_company_id = company_id;public static final String SQL_owner_id = owner_id;public static final String SQL_is_deleted = is_deleted;// HQL object namepublic static final String HQL_Thing = Thing;// HQL field namespublic static final String HQL_id = id;public static final String HQL_company = company;public static final String HQL_owner = owner;public static final String HQL_isDeleted = isDeleted;In-Java SQL ExampleIf I had to drop down to SQL, e.g. to query thing IDs, it would look like the following.  Please note that since symbol is a final String, the concatenation is done by the compiler, not at runtime, even in Java, even with the + operator:jdbcConn = HibernateUtil.getJdbcConnection(); String query = select  + Thing.SQL_id +                from  + Thing.SQL_thing +                where  + Thing.SQL_company_id +                = ? and  + Thing.SQL_is_deleted +                = b'0' and  + Thing.SQL_owner_id +                is not null;PreparedStatement stmt = jdbcConn.prepareStatement(query);stmt.setLong(1, someCompany.getId());ResultSet results = stmt.executeQuery();while (results.next()) {    Long thingId = results.getLong(1));    // do stuff with things IDs}Obviously, I try not to drop to SQL, unless I absolutely need to for performance reasons, or to do something like running a SQL report.Criteria Query ExampleIn a code which builds data for a screen of the application:Crit<Thing> tCrit = Crit.create(Thing.class);tCrit.add(Restrictions.eq(Thing.HQL_company, someCompany));tCrit.add(Restrictions.eq(Thing.HQL_isDeleted, false));tCrit.add(Restrictions.isNotNull(Thing.HQL_owner));for (Thing t : tCrit.list()) {    // do stuff with things}I somewhat try not to use criteria, except for performance.  Again, I want to stay in Java-land as much as possible.Pure-Java ORM Examplefor (Thing t : someCompany.getThings()) {    if ( t.getIsDeleted() ||         (t.getOwner() == null) ) {        continue;    }    // do stuff with things}Clearly we are bringing back extra records (some ownerless and deleted Things), but if there are many Things and most are not deleted and have owners, the performance hit will be minimal.External SQL ExampleFor reference, here is what an external-SQL implementation might look like.  External SQL file (WEB4J-like syntax):NON_DELETED_THINGS_WITH_OWNERS {select t.id, t.company_id, t.owner_id, t.is_deletedfrom thing where t.company_id = ? and    t.is_deleted = b'0' and    t.owner_id is not null; }I'm not showing or think much about how the external SQL file query gets made available to Java.  Presumably a tool like WEB4J would take care of that, but you need to specify the SQL file or files somehow and your tool makes those queries available, I'm imagining as a String in this example, but it could be a prepared statement or some other abstraction.Company Query Method:public Set<Thing> getNonDeletedThingsWithOwners(Connection jdbcConn) {    PreparedStatement stmt = jdbcConn.prepareStatement(NON_DELETED_THINGS_WITH_OWNERS);    stmt.setLong(1, this.id);    ResultSet results = stmt.executeQuery();    Set<Thing> things = new HashSet<>();    while (results.next()) {        things.add(Thing.valueOf(results));    }    return things;}Thing Factory Method:public static Thing valueOf(ResultSet rs) throws SQLException {    Thing t = new Thing();    t.id = rs.getLong(SQL_id);    t.company_id = rs.getLong(SQL_company_id);    t.owner_id = rs.getLong(SQL_owner_id);    t.isDeleted = rs.getBoolean(SQL_is_deleted);    return t;}Usage:Set<Thing> thingSet = someCompany.getNonDeletedThingsWithOwners();for (Thing t : thingSet) {    // do stuff with things}Compare this to the Pure-Java ORM Example or the Criteria Query Example - this is a ton of code, spread across four files!  If you need the performance and the control, then invest the energy.  But when ORM can save me from this, I think I'll let it.DebuggingI assume here that you have a tool that reliably generates your Java database objects for you.With the Pure-Java example, there is no debugging of SQL or queries unless performance is an issue.  You could still perform the wrong tests (e.g. forget to exclude ownerless things), but it's pretty straight-forward - keeps you focused on what you are doing, thus minimizing this possiblity.  You can ask your ORM to spit out the actual queries in your log file if you need to tune them or see that you query the thing table 100 separate times or something like that, but this code results in a single query.It's possible to pass the wrong type of object or nulls to the Criteria query.  Also, since it's wordy, it takes longer to see what you are actually doing and bugs are easier to creep into your criteria.With In-Java SQL, you have all the issues above, plus getting the SQL syntax right.  I admit my symbols make this a little more complicated, but I feel the refactoring plus is bigger than the readability minus.With the External SQL example, you have all of the error possibilities mentioned above, plus all the extra code from loading the results into the object.  Plus the multiple files that can get out of synch.  What if you rename, delete, or add a field - will you get a compilation error?  Will you find all the places you need to change?  Or do you have to find it in testing.I'd have to say that from a debugging point of view, ORM with pure Java is a clear win, with additional debugging every step you move away from there.ConclusionUsing ORM and symbols has saved me countless hours of refactoring difficulties and lets me make database changes with ease because when something breaks, it won't compile.  When code gets big, refactoring is the only way to manage complexity, and it gets harder as it gets bigger, so above a certain size of code base, ease of refactoring becomes a primary goal.ORM also eliminates opportunities for bugs and the performance cost for this is usually minimal.  You can still drop-down to SQL as necessary, but I think having less SQL in your application is a better goal than putting SQL in separate files.