Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
Perhaps send this question over to programmers stack exchange? This question applies equally to C# and Python and a host of other programming languages, though the availability of a good ORM library is a factor. –  Leonid Sep 5 '12 at 16:06
1  
The quality of this question needs to be improved dramatically no matter on what site it ends up being answered. See How to Ask for guidance. –  codesparkle Sep 5 '12 at 17:50
    
Cool - i'd never seen that done before. Thanks for letting me know that people were doing this. You also might want to check: javapractices.com/topic/TopicAction.do?Id=105 –  GlenPeterson Sep 5 '12 at 19:51
add comment

migrated from codereview.stackexchange.com Sep 5 '12 at 17:14

This question came from our site for peer programmer code reviews.

5 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
I totally agree to your point. but considering the aspect that, in the future we decide to change the database. We could do it by just changing the queries in the external file. –  shamis Sep 6 '12 at 13:58
    
True, if making that change had no side effects. –  GlenPeterson Sep 6 '12 at 15:08
    
will there be considerable amount of lag (on an average) due to externalising the sqls (the I/O) ? –  shamis Sep 10 '12 at 14:33
    
I see no reason for significant lag. I was concerned more with the possibility of introducing a bug when the program expected results formatted one way and the changed query now suddenly sometimes returns null when it never did before, or false instead of null or b'0' instead of false or has other changes. All my concerns can be summed up by saying that the program and SQL are generally designed and developed together and that separating them discourages the programmer/DBA from thinking about the side effects a change in one arena might cause in the other. –  GlenPeterson Sep 12 '12 at 3:42
add comment

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.

share|improve this answer
    
the problem with stored procedures is that we have to maintain that in multiple environments, this makes it difficult to keep its integrity. Also the fact that multiple versions would be accessing and implementing different procedures for different versions would be a lot of trouble –  shamis Sep 6 '12 at 14:02
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
Mybatis is a pretty awesome !ORM framework. –  Michael K Sep 5 '12 at 20:27
    
Those are cons for work separation per layer. Not necessarily cons of externalizing sql. A developer could put the sql in files or storedProcs without passing the sql work to someone else. The ability to facilitate work separation is a pro, but the negatives of work separation is not a con for external sql. –  mike30 Sep 5 '12 at 20:40
    
"the application will beat all other ORM frameworks hand down in performance" [citation needed] –  TMN Sep 6 '12 at 12:08
    
Please downnload and run hibernate-distribution-3.5.1-Final\project\testsuite\src\test\perf\org\hibernate‌​\test\perf\NewerPerformanceTest.java –  JVXR Sep 6 '12 at 19:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.