Every SQL statement, whether used inside a Java class or in a separate .sql file, must comply with the following rules:

  1. The @SQLComment annotation marks the start of a statement.
  2. The end of the statement is the first empty line after the @SQLComment annotation, the end of the file, or end of a Java comment.
  3. Statement can contain SQL placeholders.
  4. Statement can contain row conditions.
  5. Statement can contain replacements.

Example with all features:

1
2
3
4
5
6
7
8
9
10
11
-- Statement declaration at row 3 identifies statement and is mandatory.
-- Result, mapper and configuration classses will be generated by Maven plugin.
-- @SQLComment(name="fullFeatured", resultClass="example.FullFeatured", configClass="example.FullFeaturedConfig")
SELECT DISTINCT comp.id, comp.name
FROM companies comp
    JOIN users user ON company.id = user.companyId         -- Row condition     //@ userName != null
WHERE 1 = 1
     AND comp.id = :companyId              -- SQL placeholder will be used in prepared statement.
    AND user.name LIKE :userName           -- Row is by default added only if all SQL placeholders have non-null values
    AND comp.id > 5                        -- Row condition script     //@ jsVar > 3
ORDER BY ${orderBy}                        -- Replacement - it will be directly replaced by value

The statement is processed row by row. A row is added to the result string if:

  1. All SQL placeholders have non-null values unless these placeholders are configured to accept null values.
  2. The row condition returns a boolean value 'true'.

 

@SQLComment

The main construct that marks the start of a statement. This annotation is read by the Export and Generate Maven goals.

The annotation and all its parameters must be written on one line. The SQL statement must start immediately after the annotation.

Parameters:

Parameter Description
name Name of statement. Mandatory.
If no other parameters are used, the name can be written directly without a parameter.
resultClass Name of generated result class. Optional.
A mapper class is also generated together with the result class. The mapper class has the 'Mapper' name suffix and is placed in the 'sqlcomments' subpackage of the result class.
If no result class is set, neither a result class nor a mapper class will be generated.
If the keyword default is used, a result class with the name of the statement will be generated in the same package.
configClass Name of configuration class name. Optional.
The configuration class contains all the SQL placeholders, replacement parameters and script variables that are mentioned in the statement.
If no configuration class is set, nothing is generated.
If the keyword default is used, a configuration class will be generated with the name of the statement and the suffix 'Config', in the 'sqlcomments' subpackage.
database Name of database. Optional.
The database name must match the one defined in the used database dialect. If the parameter is omitted, the statement is used as the default for all databases.

Examples:

Example Description
@SQLComment("getAllBooks") Declaration of statement named "getAllBooks". No results, mapper or configuration classes are generated.
@SQLComment(name="findBooksByName", configClass="package.BooksByName") Declaration of statement named "findBooksByName". No result and mapper classes are generated. Configuration class is generated with defined name.
 @SQLComment(name="loadBookWithAuthor", resultClass=default, configClass=default)  Declaration of statement named "loadBookWithAuthor". There are generated all classes. Result class named LoadBookWithAuthor in the same package as source file. Mapper class LoadBookWithAuthorMapper and configuration class LoadBookWithAuthorConfig are generated in 'sqlcomments' subpackage of source file.
@SQLComment(name="getAllBooks", database="oracle") Declaration of statement named "getAllBooks" used only with Oracle DB. Statement is extracted into "getAllBooks.oracle.sql" file. No result, mapper or configuration classes are generated.

 

SQL placeholders

SQL placeholders are common feature of SQL editors and are used to represent values.

During processing, the placeholders are tested for null values. If at least one placeholder in a row has a null value, the row is skipped. Accepting null values can be configured by adding such placeholders to a list. See the StatementGenerator parameter acceptNullParameters.

Unfortunately, java.sql API do not support such placeholders and they must be replaced with the character '?'. Use the StatementGenerator methods to create statement string or a PreparedStatement if you need direct access to java.sql API.

 

Row conditions

Row conditions offer another way to control whether a processed row is added to the result string. The condition must be placed in a SQL comment and start with the letters '//@'. The condition ends at the end of the line. By default, JavaScript is used as the scripting language. Although it is possible to use other script engines, this is highly discouraged and the Maven plugin doesn't support it.

A processed row is only added to the result if the condition returns the boolean value 'true'.

 

Replacements

Replacement values are parameters that are replaced directly by their value. The replacement value is not processed further. If a replacement has a null value, this is retained.

Warning: The replacement breaks the syntax of the SQL statement. Such a statement is not valid for database tools.