How do you make long SQL invoked from other code readable?

Question

This is a very open question, but I think it can be very beneficial for SQL readability.

So you have a Java program, and you are trying to call a monster SQL statement from it, with many subqueries and joins. The starting point for my question is a string constant like this:

static string MONSTER_STATEMENT = 
  "SELECT  " +
  "   fields" +
  "WHERE "+
  "   fieldA = (SELECT a FROM TableC) " +
  "AND fieldB IN (%s)" +
  "AND fieldC = %d " +
  "FROM "
  "   tableA INNER JOIN tableB ON ...";

It later gets filled using String.format and executed.

What are you tricks for making this kind of stuff readable? Do you separate your inner joins. Do you indent the SQL itself inside the string? Where do you put the comments? Please share all of the tricks in your arsenal.

Answer 1

Such complex SQL should be in a prepared statement, not put together by a String.format to avoid SQL injection issues.

I do one of two things: Wrap it in a callable statement or put it in a configuration/xml file. Java source files are a bad place for complex SQL.

That being said, IDEA has a language formatting feature which will format strings in Java to look like a different language (based on the method they are passed to or based on an annotation), so that can help.

Edit: Given your scenario, I agree callable statements may not be workable. Prepared statements are still worth it, even for internal development, just because a parameter with an apostrophe can blow everything up (even if there is no attacker), so it is still worth it, IMO, to do the extra effort to create the string dynamically (String.format or anything else) in code only for the parts that cannot be passed as parameters (such temporary table names as in your example), and then use that as a prepared statement passing proper parameters for the rest.

As for keeping it less ugly, it looks like a configuration/xml type file is what I would go for.

Answer 2

My inclination is to format my SQL statement just as I would want it if I queried directly against the database using a management console like Management Studio. Thus, when I build a SQL statement, I take the time put in spaces and line breaks. The additional space consumed is inconsequential compared with the time savings of being able to read the SQL should I print it out or capture it using something SQL Profiler. So, I would be inclined to use StringBuilder to assemble my SQL like so:

StringBuilder sql = new StringBuilder();
sql.append("Select ....");
sql.append("\t\n, AdditionalCol, ...");
sql.append("\nFrom ...");
sql.append("\n  Inner Join ...");
sql.append("\n      On ColA =  ...");
sql.append("\nWhere Col1 = (");
sql.append("\n          Select a");
sql.append("\n          From TableC");
sql.append("\n          ");
sql.append("\n  And ColB In(%s)");
sql.append("\n  And ColC = %d");

Answer 3

A helper class that wrapps around the building process may help you. That could be one: http://openhms.sourceforge.net/sqlbuilder/

Anyway, always go with prepared statements.