Creating repository class is easy. All you have to do is:
1. Add dependency to your pom.xml
<dependency>
<artifactId>sqlcomments-spring</artifactId>
<groupId>sk.vracon.sqlcomments</groupId>
<version>0.0.1</version>
</dependency>
2. Create a new Java class and extend AbstractSQLCommentsRepository. Extending AbstractSQLCommentsRepository is not necessary but it provides useful methods for CRUD operations, constructing SQL statements and processing results.
import org.springframework.stereotype.Repository;
import sk.vracon.sqlcomments.spring.AbstractSQLCommentsRepository;
@Repository
public class GenericTestRepository extends AbstractSQLCommentsRepository {
...
}
3. Implement your methods. Put SQL statements into implemented method. Don't forget to add @SQLComment annotation before statement otherwise maven plugins will ignore it.
Warning: Lines matters! Make sure your IDE will not format comments with SQL statements.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public CompanyInfo getCompanyInfo(Long companyId) {
/*-
@SQLComment(name="loadCompanyInfo", resultClass="example.CompanyInfo", configClass="example.CompanyInfoConfig")
SELECT comp.id, comp.name, country.name, emp.name
FROM companies comp
JOIN countries country ON country.id = company.countryId
JOIN employees emp ON emp.id = company.ceo
WHERE comp.id = :companyId
*/
// All classes are generated by Maven
return null;
}
4. Generate classes using maven.
Add sqlcomments-maven-plugin to 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.sqlcomments</groupId>
<artifactId>sqlcomments-maven-plugin</artifactId>
<version>0.0.1</version>
<executions>
<execution>
<id>exportSqlFromMainSources</id>
<goals>
<goal>export</goal>
</goals>
</execution>
<execution>
<id>generateClasses</id>
<goals>
<goal>generate</goal>
<goal>domain</goal>
</goals>
<configuration>
<packageName>example.domain</packageName>
<tables>
<TableName1/>
<TableName2/>
<TableName3/>
</tables>
<jdbcDriverClass><!-- JDBC Driver Class --></jdbcDriverClass>
<databaseUrl><!-- Database URL --></databaseUrl>
<dbUserName><!-- Database User Name --></dbUserName>
<dbPassword><!-- Database Password --></dbPassword>
</configuration>
</execution>
</executions>
<dependencies>
<!-- JDBC Driver Library -->
</dependencies>
</plugin>
</plugins>
</build>
See Maven Integration for maven plugin details.
5. Finish your method.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public CompanyInfo getCompanyInfo(Long companyId) {
/*-
@SQLComment(name="loadCompanyInfo", resultClass="example.CompanyInfo", configClass="example.CompanyInfoConfig")
SELECT comp.id, comp.name, country.name, emp.name
FROM companies comp
JOIN countries country ON country.id = company.countryId
JOIN employees emp ON emp.id = company.ceo
WHERE comp.id = :companyId
*/
// All classes are generated by Maven
CompanyInfoConfig config = new CompanyInfoConfig();
// Setters have data types corresponding to database
config.setCompanyId(companyId);
return singleResult(config, new CompanyInfoMapper());
}