7. Data Access with JDBC

Before using and configuring the database support, the application has to include the respective module dependency into its Maven configuration. Spring Cloud AWS JDBC support comes as a separate module to allow the modularized use of the modules.

<dependency>
	<groupId>org.springframework.cloud</groupId>
	<artifactId>spring-cloud-aws-jdbc</artifactId>
	<version>{spring-cloud-version}</version>
</dependency>

<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xmlns:jdbc="http://www.springframework.org/schema/cloud/aws/jdbc"
	   xmlns="http://www.springframework.org/schema/beans"
	   xsi:schemaLocation="http://www.springframework.org/schema/cloud/aws/jdbc
	   http://www.springframework.org/schema/cloud/aws/jdbc/spring-cloud-aws-jdbc.xsd">

 <aws-context:context-credentials>
  ...
 </aws-context:context-credentials>

 <aws-context:context-region region="..."/>

<jdbc:data-source
		 db-instance-identifier="myRdsDatabase"
		 password="${rdsPassword}">
</jdbc:data-source>
</beans>

@Service
public class SimpleDatabaseService implements DatabaseService {

 private final JdbcTemplate jdbcTemplate;

 @Autowired
 public SimpleDatabaseService(DataSource dataSource) {
 	this.jdbcTemplate = new JdbcTemplate(dataSource);
 }
}

<beans ..>

 <aws-context:context-credentials>
  ...
 </aws-context:context-credentials>

 <aws-context:context-region region="..."/>

 <jdbc:data-source
		 db-instance-identifier="myRdsDatabase"
		 password="${rdsPassword}">
		 <jdbc:pool-attributes initialSize="1" " maxActive="200" minIdle="10"
         		testOnBorrow="true" validationQuery="SELECT 1" />
 </jdbc:data-source>

</beans>

Spring Cloud AWS also supports the configuration of the data source within an @Configuration class. The org.springframework.cloud.aws.jdbc.config.annotation.EnableRdsInstance annotation can be used to configure one data source. Multiple ones can be used to configure more then one data source. Each annotation will generate exactly one data source bean.

The class below shows a data source configuration inside a configuration class

@Configuration
@EnableRdsInstance(dbInstanceIdentifier = "test",password = "secret", readReplicaSupport = true)
public class ApplicationConfiguration {
}

	Tip
	The configuration attributes are the same in the XML element. The required attributes are also the same for the XML configuration (the dbInstanceIdentifier and password attribute)

7.2.1 Java based data source pool configuration

It is also possible to override the pool configuration with custom values. Spring Cloud AWS provides a org.springframework.cloud.aws.jdbc.config.annotation.RdsInstanceConfigurer that creates a org.springframework.cloud.aws.jdbc.datasource.DataSourceFactory which might contain custom pool attributes. The next examples shows the implementation of one configurer that overrides the validation query and the initial size.

@Configuration
@EnableRdsInstance(dbInstanceIdentifier = "test",password = "secret")
public class ApplicationConfiguration {

    @Bean
    public RdsInstanceConfigurer instanceConfigurer() {
        return new RdsInstanceConfigurer() {
            @Override
        	public DataSourceFactory getDataSourceFactory() {
        		TomcatJdbcDataSourceFactory dataSourceFactory = new TomcatJdbcDataSourceFactory();
        		dataSourceFactory.setInitialSize(10);
        		dataSourceFactory.setValidationQuery("SELECT 1 FROM DUAL");
        		return dataSourceFactory;
        	}
        };
    }
}

	Tip
	This class returns an anonymous class of type org.springframework.cloud.aws.jdbc.config.annotation.RdsInstanceConfigurer, which might also of course be a standalone class.

The data sources can also be configured using the Spring Boot configuration files. Because of the dynamic number of data sources inside one application, the Spring Boot properties must be configured for each data source.

A data source configuration consists of the general property name cloud.aws.rds.<instanceName> for the data source identifier following the sub properties for the particular data source where instanceName is the name of the concrete instance. The table below outlines all properties for a data source using test as the instance identifier.

Amazon RDS allows to use MySQL read-replica instances to increase the overall throughput of the database by offloading read data access to one or more read-replica slaves while maintaining the data in one master database.

Spring Cloud AWS supports the use of read-replicas in combination with Spring read-only transactions. If the read-replica support is enabled, any read-only transaction will be routed to a read-replica instance while using the master database for write operations.

Caution

Using read-replica instances does not guarantee strict ACID semantics for the database access and should be used with care. This is due to the fact that the read-replica might be behind and a write might not be immediately visible to the read transaction. Therefore it is recommended to use read-replica instances only for transactions that read data which is not changed very often and where outdated data can be handled by the application.

The read-replica support can be enabled with the read-replica attribute in the datasource configuration.

<beans ..>
 <jdbc:data-source db-instance-identifier="RdsSingleMicroInstance"
	password="${rdsPassword}" read-replica-support="true">

 </jdbc:data-source>
</beans>

Spring Cloud AWS will search for any read-replica that is created for the master database and route the read-only transactions to one of the read-replicas that are available. A business service that uses read-replicas can be implemented like shown in the example.

@Service
public class SimpleDatabaseService {

	private final JdbcTemplate jdbcTemplate;

	@Autowired
	public SimpleDatabaseService(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Transactional(readOnly = true)
	public Person loadAll() {
		// read data on the read replica
	}

	@Transactional
	public void updatePerson(Person person) {
		// write data into database
	}
}

Amazon RDS supports a Multi-AZ fail-over if one availability zone is not available due to an outage or failure of the primary instance. The replication is synchronous (compared to the read-replicas) and provides continuous service. Spring Cloud AWS supports a Multi-AZ failover with a retry mechanism to recover transactions that fail during a Multi-AZ failover.

	Note
	In most cases it is better to provide direct feedback to a user instead of trying potentially long and frequent retries within a user interaction. Therefore the fail-over support is primarily useful for batch application or applications where the responsiveness of a service call is not critical.

The Spring Cloud AWS JDBC module provides a retry interceptor that can be used to decorate services with an interceptor. The interceptor will retry the database operation again if there is a temporary error due to a Multi-AZ failover. A Multi-AZ failover typically lasts only a couple of seconds, therefore a retry of the business transaction will likely succeed.

The interceptor can be configured as a regular bean and then be used by a pointcut expression to decorate the respective method calls with the interceptor. The interceptor must have a configured database to retrieve the current status (if it is a temporary fail-over or a permanent error) from the Amazon RDS service.

The configuration for the interceptor can be done with a custom element from the Spring Cloud AWS jdbc namespace and will be configured like shown:

<beans ..>
	<jdbc:retry-interceptor id="myInterceptor"
		db-instance-identifier="myRdsDatabase"
		max-number-of-retries="10" />
</beans>

The interceptor itself can be used with any Spring advice configuration to wrap the respective service. A pointcut for the services shown in the chapter before can be defined as follows:

<beans ..>
 <aop:config>
  <aop:advisor advice-ref="myInterceptor" pointcut="bean(simpleDatabaseService)" order="1" />
 </aop:config>
</beans>

	Caution
	It is important that the interceptor is called outside the transaction interceptor to ensure that the whole transaction will be re-executed. Configuring the interceptor inside the transaction interceptor will lead to a permanent error because the broken connection will never be refreshed.

The configuration above in combination with a transaction configuration will produce the following proxy configuration for the service.

Spring Cloud AWS supports database instances that are configured with CloudFormation. Spring Cloud AWS can use the logical name inside the database configuration and lookup the concrete database with the generated physical resource name. A database configuration can be easily configured in CloudFormation with a template definition that might look like the following example.

"myRdsDatabase": {
 	"Type": "AWS::RDS::DBInstance",
 	"Properties": {
 	 "AllocatedStorage": "5",
 	 "DBInstanceClass": "db.t1.micro",
 	 "DBName": "test",
 	 "Engine": "mysql",
 	 "MasterUsername": "admin",
 	 "MasterUserPassword": {"Ref":"RdsPassword"},
 	 ...
 	}
 },
 "readReplicaDatabase": {
 	"Type": "AWS::RDS::DBInstance",
 	"Properties": {
 	 "AllocatedStorage" : "5",
 	 "SourceDBInstanceIdentifier": {
 	 	"Ref": "myRdsDatabase"
 	 },
 	 "DBInstanceClass": "db.t1.micro"
 	}
 }
}

The database can then be configured using the name set in the template. Also, the read-replica can be enabled to use the configured read-replica database in the application. A configuration to use the configured database is outlined below:

<beans>
 <aws-context:stack-configuration/>

 <jdbc:data-source db-instance-identifier="myRdsDatabase" password="${rdsPassword}" read-replica-support="true"/>
</beans>

Amazon RDS instances can also be configured using RDS database specific tags, allowing users to configure database specific configuration metadata with the database. Database instance specific tags can be configured using the user-tags-map attribute on the data-source element. Configure the tags support like in the example below:

<jdbc:data-source
	db-instance-identifier="myRdsDatabase"
	password="${rdsPassword}" user-tags-map="dbTags" />

That allows the developer to access the properties in the code using expressions like shown in the class below:

public class SampleService {

	@Value("#{dbTags['aws:cloudformation:aws:cloudformation:stack-name']}")
	private String stackName;
}

	Note
	The database tag aws:cloudformation:aws:cloudformation:stack-name is a default tag that is created if the database is configured using CloudFormation.