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 said

• SQL-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.

Thoughts

I’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 name
public static final String SQL_thing = "thing";
// SQL Column names
public 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 name
public static final String HQL_Thing = "Thing";
// HQL field names
public 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 Example

If 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 Example

In 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 Example

for (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 Example

For 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_deleted
from 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.

Debugging

I 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.

Conclusion

Using 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.

I think this becomes more relevant when you have a larger project, and have dedicated ‘database developers’ who co-incidentally happen to be SQL ninjas too.

Pros:

1. There is a clean separation of responsibilities. Devs do the coding db developers write the scripts.

2. If both do a good job and the db design is right, the application will beat all other ORM frameworks hands down in performance.

Cons:

1. App developers and DB developers wont agree usually (I am the best driver syndrome)

2. If something goes wrong there will be too much of finger pointing.

I had worked on a project that used this approach, using iBatis – Currently myBatis

Just to add to @JVXR Answer, another major benefit is the ability to support multiple DB Engines. For Example, You can easily swap out SQL Sever TSQL for Oracle Pl-Sql.

This is particularly useful if you are selling packaged software, you can allow the client to use what ever DB they already own and can support with no additional cost.

Pros:
–It will be easier for the DB person to touch the sql.
–No rebuild needed when sql changes (assuming interfaces are the same)

Cons:
–negation of the pros above
–It can be convenient to see the fields in the sql next to the code that is utilizing that sql.

Having queries as stored procedures in the database is preferable because:

1> They are pre-compiled, hence will probably run faster.
2> You can update them independent of the code base. It is usually easier to run a sql script than it is to reinstall an application, particularly in a managed corporate style environment.
3> Easier to debug/test/performance tune – you can run the query easily free of the application.
4> Allows the database structure to be changed without needing to update the application.
5> Allows implementation on different database engines with one code base for the application, with the negative of multiple code bases for each DB. The syntax used for different db’s tends to be different enough that some tweaking will be needed unless you use the simplest of SQL (particularly with stored procedures).

The main con I can see is that the sql is not hidden in the compiled code, so is easier for an external party to read and reverse engineer the application which may or may not be important for a commercial application.