== Data Access with JDBC

Spring has a broad support of data access technologies built on top of JDBC like `JdbcTemplate` and dedicated ORM (JPA,
Hibernate support). Spring Cloud AWS enables application developers to re-use their JDBC technology of choice and access the
https://aws.amazon.com/rds/[Amazon Relational Database Service] with a declarative configuration. The main support provided by Spring
Cloud AWS for JDBC data access are:

* Automatic data source configuration and setup based on the Amazon RDS database instance.
* Automatic read-replica detection and configuration for Amazon RDS database instances.
* Retry-support to handle exception during Multi-AZ failover inside the data center.

[NOTE]
====
Amazon Aurora database clusters (both serverless and provisioned) are not supported by Spring Cloud AWS. Please follow https://github.com/awspring/spring-cloud-aws/issues/50[this issue] for more information.
====

=== Configuring data source
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.

==== Maven dependency configuration

The Spring Cloud AWS JDBC module comes as a standalone module and can be imported with the following dependency declaration.

[source,xml,indent=0]
----
<dependency>
	<groupId>io.awspring.cloud</groupId>
	<artifactId>spring-cloud-aws-jdbc</artifactId>
	<version>{spring-cloud-version}</version>
</dependency>
----

==== Basic data source configuration
The data source configuration requires the security and region configuration as a minimum allowing Spring Cloud AWS to retrieve
the database metadata information with the Amazon RDS service. Spring Cloud AWS provides an additional `jdbc` specific namespace
to configure the data source with the minimum attributes as shown in the example:

[source,xml,indent=0]
----
<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>
----

The minimum configuration parameters are a unique `id` for the data source, a valid `db-instance-identifier` attribute
that points to a valid Amazon RDS database instance. The master user password for the master user. If there is another
user to be used (which is recommended) then the `username` attribute can be set.

With this configuration Spring Cloud AWS fetches all the necessary metadata and creates a
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html[Tomcat JDBC pool] with the default properties. The data source
can be later injected into any Spring Bean as shown below:

[source,java,indent=0]
----

@Service
public class SimpleDatabaseService implements DatabaseService {

 private final JdbcTemplate jdbcTemplate;

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

It is possible to qualify the data source injection point with an `@Qualifier` annotation to allow multiple data source
configurations inside one application context and still use auto-wiring.

==== Data source pool configuration
Spring Cloud AWS creates a new Tomcat JDBC pool with the default properties. Often these default properties do not meet the
requirements of the application with regards to pool size and other settings. The data source configuration supports the configuration
of all valid pool properties with a nested XML element. The following example demonstrates the re-configuration of the data source
with custom pool properties.

[source,xml,indent=0]
----
<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>
----

A full list of all configuration attributes with their value is available https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html[here].

=== Configuring data source with Java config
Spring Cloud AWS also supports the configuration of the data source within an `@Configuration` class. The
`io.awspring.cloud.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.

CAUTION: Since 2.3.0 `@EnableRdsInstance` annotation has been deprecated. We suggest using configuration Spring Boot auto-configuration instead.

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

[source,java,indent=0]
----
@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)

==== Java based data source pool configuration
It is also possible to override the pool configuration with custom values. Spring Cloud AWS provides a
`io.awspring.cloud.jdbc.config.annotation.RdsInstanceConfigurer` that creates a
`io.awspring.cloud.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.

[source,java,indent=0]
----
@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 `io.awspring.cloud.jdbc.config.annotation.RdsInstanceConfigurer`,
which might also of course be a standalone class.


=== Configuring data source in Spring Boot
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.instances` with `[index]` dereferences for the multiple data sources
following the sub properties for each particular data source. The table below
outlines all properties for a data source.

[cols="3*", options="header"]
|===
|property
|example
|description

|cloud.aws.rds.instances[0].dbInstanceIdentifier
|test
|The identifier for the db instance

|cloud.aws.rds.instances[0].password
|verySecret
|The password for the db instance test

|cloud.aws.rds.instances[0].username
|admin
|The username for the db instance test (optional)

|cloud.aws.rds.instances[0].readReplicaSupport
|true
|If read-replicas should be used for the data source (see below)

|cloud.aws.rds.instances[0].databaseName
|fooDb
|Custom database name if the default one from rds should not be used

|===


=== Read-replica configuration
Amazon RDS allows to use MySQL, MariaDB, Oracle, PostgreSQL and
Microsoft SQL Server https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html[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 https://en.wikipedia.org/wiki/ACID[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.

[source,xml,indent=0]
----
<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.

[source,java,indent=0]
----
@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
	}
}
----

=== Failover support
Amazon RDS supports a https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html[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:

[source,xml,indent=0]
----
<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:

[source,xml,indent=0]
----
<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.

image::jdbc-retry-interceptor.png[Retry interceptor]

=== CloudFormation support
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.

[source,json,indent=0]
----
"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:

[source,xml,indent=0]
----
<beans>
 <aws-context:stack-configuration/>

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

=== Database tags
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:

[source,xml,indent=0]
----
	<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:

[source,java,indent=0]
----
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.
====

=== IAM Permissions
Following IAM permissions are required by Spring Cloud AWS:

[cols="2"]
|===
| Describe database instances
| `rds:DescribeDBInstances`

|===

Sample IAM policy granting access to RDS:

[source,json,indent=0]
----
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "rds:DescribeDBInstances",
            "Resource": "db-arn"
        },
}
----
