Eugene Hutorny

Where to keep SQL statements? This question arises almost for any more-less sophisticated Java application that accesses DBMS. Two most common approaches are: (1) as string constants in Java and (2) as text in external files. Both these approaches have significant drawbacks. Java strings get complicated when a string is too long to fit in one line, leading to a difficult to read and hard to maintain SQL code. SQL in external files are not easy to lookup and require some mapping technique to associate statement in the external file with a place in Java where it is needed. Also, as development goes, the text file adheres with more and more orphan SQL statements and there is no easy way to clean them up. This article suggests an approach that combines two mentioned above and inherits their strong sides.
The idea is very simple – keep SQL statements in the source Java file as comments following the definition of a string member, load them at run-time and assign to the corresponding member. The following example gives a grasp on this technique.

public class SQLpeople {
  public static final String sqlSELECTall = Comments.load();
  /*--
    SELECT * FROM people;
    --*/
}

To make it available at run-time, this source file should be packaged together with the class file. That’s everything you need to make SQL statements loaded…
Well, almost everything :). One minor thing left is an implementation of Comments.load(). You may implement it in your own or download from SourceForge an open source implementation (under LGPLv3 license).

Dialectism

If your application is designed to run with different DBMS engines, maintaining SQL in different dialects may become a challenge. The Comments class proposes a solution as illustrated below:

public class SQLpeople {
  public static final String sqlSELECTtopN = Comments.load(getCurrentDBMS());
  /*--
    --: pgsql
    SELECT * FROM people LIMIT ?;
    --: mssql
    SELECT TOP ? * FROM people;
    --*/
}

Where getCurrentDBMS() is your static function that returns name of the current DBMS, and --: pgsql and --: mssql are discriminator values designating statements appropriate for different DBMS.

How it works

The Comments class just a friendly decorator created for the sake of convenience. Its method load() uses StackFrame to gather information about invocation point and passes name of the source file, line number (N), and class name to the CommentRetriever.retrieve(), which opens the source file as a resource stream, skips N lines, reads content until it finds --*/ in the line and then uses regex to extract content between /*-- and --*/. If the discriminator parameter is null, retrieve returns extracted content. Otherwise it finds designators --: <discriminator> and tests its equity against the parameter. If they are equal method returns content of the section between the current discriminator and the next one or end of the block. If block contains no discriminator equal to the parameter, it returns content before the first discriminator, unless default values are disabled by NODEFAULTS option.
That’s all you need to start using this class, for advanced topics please refer to Java docs.

Guidelines on authoring comments

There should be no characters between the end of the load() line and sequence /*-- that opens the block, except, of course, white spaces (space, tab, new line).

public class SQLpeople {
  /*
   * allowed comment
   */

  public static final String sqlSELECTall = Comments.load(); -- allowed comment

  /* disallowed comment */
  /*--
    SELECT * FROM people;
    --*/

}

Discriminator takes entire line, there should be no other text in this line

Errors

By default error messages are returned as output value and prepended with "--", are returned as the result of load() method. Although, this behavior can be opted (see Java docs for details).