Start with SQL statement. Use your favourite SQL editor to test it and tune it up or ask database developers to do it for you.

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

Put SQL into 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="sk.vracon.sqlcomments.examples.config.FindCompaniesByNameConfig")
          SELECT comp.* FROM companies comp
          WHERE comp.name LIKE :companyName
          ORDER BY com.name
         */
        ...

Use /*- to avoid formatting or set formatter in you IDE to disable formatting comments.

Warning: Make sure that comment will be not formatted. Lines matters.

Mark SQL statement with @SQLComment annotation. Set statement name which will be used as a reference. Attributes resultClass and configClass are used by maven plugin to generate corresponding classes. Together with result class is automatically generated also a mapper class used to map database results to result class objects. Java types used by generated classes takes into account data types in your database. This way SQLComments preserves type safety.

To generate appropriate classes define 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
<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>
            </dependencies>
        </plugin>
    </plugins>
</build>

 Call maven to generate classes.

1
mvn compile

Goal export  extracts all SQL statements into separate *.sql files. These files could be created also by hand if you decide that storing SQL inside java code is not fit for you.

Goal generate generates from *.sql files result, mapper and configuration classes. If @SQLComment annotation doesn't define resultClass or configClass, class is not generated.

There's also a third maven goal domain which generates domain classes and basic SQL statements for CRUD operations.

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="sk.vracon.sqlcomments.examples.config.FindCompaniesByNameConfig")
          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, new CompanyMapper());
 
        return companies;
    }
}

 That's all. If you find a problem in your SQL statement, just copy it directly to your favourite SQL editor and test it as usual.