Guide to AWS Aurora RDS with Java

1. Introduction

Amazon Aurora is a MySQL and PostgreSQL compatible relational database built for the cloud that combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases.

In this tutorial, we’ll cover how to create and interact with Amazon RDS instance with Java, we’ll also connect and execute SQL tests on Amazon RDS.

Let’s start by setting up the project.

2. Maven Dependencies

Let’s create a Java Maven project and add AWS SDK to our project:

<dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>aws-sdk-java</artifactId>
    <version>2.24.9</version>
</dependency>

To view the latest version, check Maven Central.

3. Prerequisites

To use AWS SDK, we’ll need a few things to setup:

• AWS Account
• AWS Security Credentials
• Choosing AWS Region

We need an Amazon Web Services account. If you still don’t have any, go ahead and create an account

AWS Security Credentials are the access keys that allow us to make programmatic calls to AWS API actions. We can get these credentials in two ways, either by using AWS root account credentials from access keys section of the Security Credentials page or by using IAM user credentials from IAM console

We have to select an AWS region(s) where we want to store our Amazon RDS. Keep in mind that RDS prices vary by region. For more details, head over to the official documentation.

For this tutorial, we’ll use Asia Pacific (Sydney) (region ap-southeast-2).

4. Connect to AWS RDS Webservices

We need to create a client connection to access Amazon RDS web service.

RdsClient rdsClient = RdsClient.builder()
    .region(Region.AP_SOUTHEAST_2)
    .credentialsProvider(ProfileCredentialsProvider.create("default"))
    .build();

5. Amazon Aurora Instance

Now let’s create the Amazon Aurora RDS instance.

5.1. Create RDS Instance

To create the RDS instance, we need to instantiate a CreateDBInstanceRequest with the following attributes:

• DB Instance Identifier that is unique across all existing instances names in Amazon RDS
• DB Instance class specify configuration for CPU, ECU, Memory, etc., from Instance Class Table
• Database Engine. PostgreSQL or MySQL, we’ll use PostgreSQL
• Database master/super username
• Database master user password
• DB name to create an initial database with the specified name
• For Storage Type, specify an Amazon EBS volume type. The list is available here
• Storage allocation in GiB

CreateDbInstanceRequest instanceRequest = CreateDbInstanceRequest.builder()
    .dbInstanceIdentifier("Sydney")
    .engine("postgres")
    .multiAZ(false)
    .masterUsername(db_username)
    .masterUserPassword(db_password)
    .dbName(db_database)
    .storageType("gp2")
    .allocatedStorage(10)
    .build();

Now let’s create our first instance by calling the createDBInstance(): 

CreateDbInstanceResponse createDbInstanceResponse = rdsClient.createDBInstance(instanceRequest);

RDS instance will be created in a few minutes.

We won’t get the endpoint URL in the response as this call is asynchronous.

5.2. List DB Instance

In this section, we’ll see how to list the created DB instance.

To list the RDS instance, we need to use describeDBInstances of the AmazonRDS interface:

DescribeDbInstancesResponse response = rdsClient.describeDBInstances();
List<DBInstance> instances = response.dbInstances();
for (DBInstance instance : instances) {
    // Information about each RDS instance
    String identifier = instance.dbInstanceIdentifier();
    String engine = instance.engine();
    String status = instance.dbInstanceStatus();
    Endpoint endpoint = instance.endpoint();
    String endpointUrl = "Endpoint URL not available yet.";
    if (endpoint != null) {
        endpointUrl = endpoint.toString();
    }
    logger.info(identifier + "\t" + engine + "\t" + status);
    logger.info("\t" + endpointUrl);
}

Endpoint URL is the connection URL for our new DB instance. This URL will be provided as a host while connecting to the database.

5.3. Run JDBC Test

Now let’s connect our RDS instance and create our first table.

Let’s create a db.properties file and add the database information:

db_hostname=<Endpoint URL>
db_username=username
db_password=password
db_database=mydb

After creating the file, let’s connect to RDS instance and create the table named jdbc_test:

Properties prop = new Properties();
InputStream input = AwsRdsDemo.class.getClassLoader().getResourceAsStream("db.properties");
prop.load(input);
String db_hostname = prop.getProperty("db_hostname");
String db_username = prop.getProperty("db_username");
String db_password = prop.getProperty("db_password");
String db_database = prop.getProperty("db_database");

Connection conn = DriverManager.getConnection(jdbc_url, db_username, db_password);
Statement statement = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS jdbc_test (id SERIAL PRIMARY KEY, content VARCHAR(80))";
statement.executeUpdate(sql);

Afterward, we’ll insert and retrieve data from the table:

PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO jdbc_test (content) VALUES (?)");
String content = "" + UUID.randomUUID();
preparedStatement.setString(1, content);
preparedStatement.executeUpdate();

String sql = "SELECT  count(*) as count FROM jdbc_test";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    String count = resultSet.getString("count");
    Logger.log("Total Records: " + count);
}

5.4. Delete the Instance

To delete DB instance, we need to generate DeleteDBInstanceRequest. It requires the DB instance identifier and skipFinalSnapshot parameter.

The skipFinalSanpshot is to specify if we want to take the snapshot before deleting the instance:

DeleteDbInstanceRequest request = DeleteDbInstanceRequest.builder()
    .dbInstanceIdentifier(identifier)
    .skipFinalSnapshot(true)
    .build();

DeleteDbInstanceResponse response = rdsClient.deleteDBInstance(request);

6. Conclusion

In this article, we focused on the basics of interacting with the Amazon Aurora (PostgreSQL) RDS via Amazon SDK. This tutorial has focused on PostgreSQL there are also other options including MySQL.

Although the interaction method will remain the same across RDS. Aurora is a preferred choice for many customers because it is up to five times faster than standard MySQL databases and three times faster than standard PostgreSQL databases.

For more information visit Amazon Aurora.

And, as always, the code can be found over on Github.