Each SQL statement, no matter if it is used inside a Java class or in separate .sql file, must conform next rules:

  1. As a start of statement is supposed @SQLComment annotation.
  2. End of statement is first empty line after @SQLComment annotation, end of file or 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 identify 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

 Statement is processed row by row. Row is added to result string if:

  1. All SQL placeholders have non-null values unles these placeholders are configured to accept null.
  2. Row condition return boolean value 'true'.

 

@SQLComment

Main construct marking start of statement. This annotation is read by Export and Generate maven goals.

Annotation together with all its parameters must be written in one line. Immediatelly after annotation must start SQL statement.

 Parameters:

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

Examples:

Example Description
@SQLComment("getAllBooks") Declaration of statement named "getAllBooks". No result, mapper nor configuration classes are generated.
@SQLComment(name="findBooksByName", configClass="package.BooksByName") Declaration of statement named "findBooksByName". No result and mapper classes are generated. Generated configuration class with specified 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 has name LoadBookWithAuthorMapper and is generated in 'sqlcomments' subpackage of source file. Generated configuration class has name LloadBookWithAuthorConfig and is placed in 'sqlcomments' subpackage of source file.
@SQLComment(name="getAllBooks", database="oracle") Declaration of statement named "getAllBooks" used only for Oracle DB. Statement is extracted into "getAllBooks.oracle.sql" file. No result, mapper nor configuration classes are generated.

 

SQL placeholders

SQL placeholders are used as a vaues markers. For java.sql usage these placeholders must be replaced with '?'. Use StatementGenerator methods to create statement string or PreparedStatement.

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

 

Row conditions

Row conditions are another way how to control whether processed row will be added to result string. Condition must be placed in SQL comment and starts with letters '//@'. Condition ends at the end of line. By default there's used JavaScript as a script language. Even it is possible to use other script engines, it is highly discouraged and maven plugin doesn't support it.

Processed row is added to result only if condition returns boolean value 'true'.

 

Replacements

Replacements are parameters which are directly replaced by their value. There's no further processing of replacement value. If replacement has a null value, it is kept as is.

Warning: Replacement breaks the SQL statement syntax. Such statement is not valid for database tools.