1. Begin with an SQL statement. Test and tune it using your favourite SQL editor, or ask the database developers to do it for you.

    1
    2
    3
    SELECT comp.* FROM companies comp
WHERE comp.name LIKE :companyName
ORDER BY com.name

  2. Add the SQL to a comment in your Java code.

    1
    2
    3
    4
    5
    6
    7
    8
    public void findCompaniesByName() {
    /*-
      @SQLComment(name="findCompaniesByName", resultClass="sk.vracon.sqlcomments.examples.pojo.Company", configClass=default)
      SELECT comp.* FROM companies comp
      WHERE comp.name LIKE :companyName
      ORDER BY com.name
     */
    ...

    Use the comment delimiter '/*-' to avoid formatting, or set your IDE's formatter to disable formatting of comments.

    Warning:Make sure the comment is not formatted. Line breaks matter.

    Add the @SQLComment annotation to the SQL statement. Set a statement name to be used as a reference. The attributes resultClass and configClass are used by the Maven plugin to generate the corresponding classes. Along with the result class, a mapper class is automatically generated to map database results to result class objects. The Java types used by the generated classes take into account the data types in your database. This ensures that SQLComments preserves type safety. See SQL Comments format for more.

  3. To generate the appropriate classes, define the SQLComments Maven plugin in your build.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    <build>
    <plugins>
        <plugin>
            <groupId>sk.vracon</groupId>
            <artifactId>sqlcomments-maven-plugin</artifactId>
            <executions>
                <execution>
                    <goals>
                        <goal>export</goal>
                        <goal>generate</goal>
                    </goals>
                    <configuration>
                        <jdbcDriverClass>org.hsqldb.jdbcDriver</jdbcDriverClass>
                        <databaseUrl>jdbc:hsqldb:res:testdb</databaseUrl>
                        <dbUserName>sa</dbUserName>
                        <dbPassword></dbPassword>
                    </configuration>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>org.hsqldb</groupId>
                    <artifactId>hsqldb</artifactId>
                    <version>2.3.2</version>
                </dependency>
                <dependency>
                    <groupId>org.graalvm.js</groupId>
                    <artifactId>js</artifactId>
                    <version>23.0.10</version>
                </dependency>
                <dependency>
                    <groupId>org.graalvm.js</groupId>
                    <artifactId>js-scriptengine</artifactId>
                    <version>23.0.10</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

     Use Maven to generate classes.

    1
    mvn compile

    Goal export  extracts all SQL statements into separate *.sql files. These files can also be created manually if you decide that storing SQL inside Java code is not suitable for you.

    Goal generate generates result, mapper and configuration classes from *.sql files. If @SQLComment annotation does not define resultClass or configClass, class will not be generated.

    There's also a third Maven goal, domain, which generates domain classes and basic SQL statements for CRUD operations. See more in Maven integration

  4. Finish your Java code.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    @Repository
public class CompanyRepository extends AbstractSQLCommentsRepository {
 
    public void findCompaniesByName(String namePrefix) {
        /*-
          @SQLComment(name="findCompaniesByName", resultClass="sk.vracon.sqlcomments.examples.pojo.Company", configClass=default)
          SELECT comp.* FROM companies comp
          WHERE comp.name LIKE :companyName
          ORDER BY com.name
         */
 
        FindCompaniesByNameConfig config = new FindCompaniesByNameConfig();
        config.setCompanyName(namePrefix + "%");
 
        List<Company> companies = list(config, CompanyMapper.INSTANCE);
 
        return companies;
    }
}

    See more in Spring integration and How to create Spring repository class

That's all. If you encounter an issue with your SQL statement, don't waste time reproducing the scenario in the application. Instead, copy the SQL directly into your preferred SQL editor and test it as you normally would.

Continue on Documentation and tutorials