JDBC with Generic DAOs in Microserver

A lot of Microservices will have to talk to a database of some kind. We've integrated some options for relational databases out of the box.

To keep our services small, we tend not to build large domain models, pulling back and writing only data relevant to the Microservice.

In Microserver we've included the following options

Spring JDBC Template

By injecting the SQL class into your services you will have access to Spring's simple and powerful JDBC Template for direct JDBC. This can be used with the BeanPropertyRowMapper to automatically map resultset data back to your entities. To switch it on :

 @Microserver(springClasses = { Classes.JDBC_CLASSES })

For superior query performance you can also turn on ROMA row mapping.

 @Autowired
 RowMapperService rowMapper
 rowMapper = rowMapperService.getRowMapper(JdbcEntity.class);

To add ROMA Spring configuration use

 @Microserver(springClasses = { Classes.JDBC_CLASSES, Classes.ROMA_ROW_MAPPER }

You will also need to add ROMA as a runtime dependency

    repositories {
          maven { url 'https://github.com/serkan-ozal/maven-repository/raw/master/' }
    }
    testCompile ('org.springframework:spring-jdbc-roma:1.0.0-RELEASE'){
          exclude(module: 'javassist')
          exclude(module: 'org.springframework')
    }

See ROMA Row Mapper for more details ## Spring Data Generic JDBC DAO

Tomasz Nurkiewicz has greated a very excellent Generic Spring DATA DAO based on the Spring JDBC Template. To use it extend JdbcRepository and provide RowMappers and RowUnmappers. For the RowMapper you can use either BeanPropertyRowMapper or ROMA for automatic result mapping.

e.g.

 @Repository
 public class JdbcEntityRepository extends JdbcRepository<JdbcEntity, Long> {

    private final static RowUnmapper<JdbcEntity> unmapper = (obj) ->(HashMapBuilder.<String,Object>of("name",obj.getName(),
																			"value",obj.getValue(),"id",obj.getId(),
																			"version",obj.getVersion())).build(); 

    @Autowired 
    public JdbcEntityRepository(RowMapperService service) {
        super(service.getRowMapper(JdbcEntity.class),unmapper, "t_jdbc_repo");
  
  }

}

This will then make a range of default DAO methods available. See Spring Data JDBC for more details.

Groovy SQL

Groovy isn't a dependency in Microserver, so we haven't included it as part of the core. Groovy provides a very productive development platform for small services where ultra high performance isn't essential. In particular Groovy offers excellent facilities for XML & JSON Processing (web services & REST) and very simple SQL processing with Groovy SQL.

Using Groovy SQL with Microserver is straightforward 👍

  @Autowired
  DataSource dataSource


 public Sql createSql(){
       new Sql(dataSource)
  }

Elsewhere you can execute SQL Queries (From the Groovy site)

 def getPersons() {
     def persons = []
     sql.eachRow('Select * from Person') {
       persons << it.toRowResult()
   }
   persons
}