Using Amazon RDS

Amazon Relational Database Service (RDS) is a scalable relational database service in the cloud. Supported database engines are MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and MySQL compatible Amazon Aurora engine. No changes are required in your application code to connect to RDS, that is, the code you are already using to connect to databases can be used with Amazon RDS.

Amazon manages the infrastructure provisioning, installing, and maintaining the database software. Database instances using Amazon RDS's MySQL, Oracle, SQL Server, and Oracle engines can be provisioned with General Purpose (SSD) storage, Provisioned IOPS (SSD) storage, or Magnetic storage. If your application needs predictable and consistent I/O performance, you can choose Provisioned IOPS (SSD) instead of using General Purpose (SSD) or Magnetic storage.

You can access RDS via the AWS Management Console, CLI, or API calls. RDS allows to you to efficiently scale compute and storage capacities. In addition, you can launch Read Replicas to offload read-based traffic from your primary database. High-availability features such as Multi-AZ can be configured to synchronously replicate the data to another instance in a different availability zone. In addition, support for automated backups and database snapshots can aid the DR process. SSD-backed storage option provides higher performance for frequently accessed or updated data. You can secure your data by isolating your instances in the VPC, and encrypt your data at rest.

How to do it…

  1. Create an RDS DB instance.

    Execute the following command to create an RDS DB instance named appdb and size 30 Gb.

    $ aws rds create-db-instance 
--db-instance-identifier appdb 
--allocated-storage 30 
--db-instance-class db.m1.small 
--engine mysql 
--master-username admin 
--master-user-password Passw0rd123$
  2. Retrieve DB instance information.

    Execute the following command to retrieve information about DB instance with name appdb in order to verify the details of the RDS instance created:

    $ aws rds describe-db-instances 
--db-instance-identifier appdb
  3. Download the database driver.

    Add the Maven dependency for the MySQL connector to access the database from the Java code.

    <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>
  4. Create an application database.

    The following sample code illustrates how to create our sample application database, called websupport database, on the RDS instance. Replace RDS DB endpoint, user name, and password with your own specific details.

    // Create database.
public static void CreateDatabase() throws Exception {

// Register database driver.
        Class.forName("com.mysql.jdbc.Driver");

// Database credentials.
        String userName = "username";
        String password = "password";

// RDS url.
        String dbUrl = "jdbc:mysql://RDS db endpoint/";

// Create connection to RDS instance.
        Connection con = DriverManager.getConnection(dbUrl, userName, password);

// Creates a Statement object for sending SQL statements to the
// database.
        Statement stm = con.createStatement();

// Create database query.
        String sql = "CREATE DATABASE websupport;";

// Execute query.
        stm.executeUpdate(sql);
    }
  5. Create a table.

    The following code sample creates a table in the application database. Replace the RDS DB endpoint, user name, password, and database name with your own specific details.

        // Create table.
    public static void CreateTable() throws Exception {

    // Register database driver.
        Class.forName("com.mysql.jdbc.Driver");

    // Database credentials.
        String userName = "username";
        String password = "password";

    // RDS url.
        String dbUrl = "jdbc:mysql://RDS db endpoint/websupport";

    // Create connection to RDS instance.
        Connection con = DriverManager.getConnection(dbUrl, userName, password);

    // Creates a Statement object for sending SQL statements to the
    // database.
        Statement stm = con.createStatement();

    // Create database query.
        String sql = "CREATE TABLE tickets (ID int, frm VARCHAR(20),msg VARCHAR(2000));";

    // Execute query.
        stm.executeUpdate(sql);
    }
  6. Insert rows into the table.

    The following sample code inserts a ticket record into the tickets table. Replace RDS DB endpoint, user name, password, and database name with your own specific details.

    // Insert record.
    public static void InsertRecord() throws Exception {

    // Register database driver.
        Class.forName("com.mysql.jdbc.Driver");

    // Database credentials.
        String userName = "username";
        String password = "password";

    // RDS url.
        String dbUrl = "jdbc:mysql://RDS db endpoint/websupport";

    // Create connection to RDS instance.
        Connection con = DriverManager.getConnection(dbUrl, userName, password);

    // Creates a Statement object for sending SQL statements to the
    // database.
        Statement stm = con.createStatement();

    // Create database query.
        String sql = "INSERT INTO tickets(ID, frm, msg) VALUES(1, '[email protected]', 'Issue while logging into site...');";

    // Execute query.
        stm.executeUpdate(sql);
    }

How it works…

In the first step, we created an RDS DB instance with a MySQL database engine. The main parameters, we specify, include a name for the instance, the amount of storage to be initially allocated, compute and memory capacity, the database engine to use, and the user name and password for the master user. After creating the DB instance, you can use the endpoint URL to access the database. Next, we retrieve the information about the newly created DB instance to verify the details (including the endpoint address).

In the next step, we downloaded the database driver, to access the application database from our Java code, by adding a Maven dependency for the MySQL connector. You can use the Connection and Statement classes to access database from your Java programs. The next several steps list the sample code to create an application database, a table, and finally insert a record.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset