AWS Elastic Beanstalk
Developer Guide (API Version 2010-12-01)
Did this page help you?  Yes | No |  Tell us about it...
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.

Using Amazon RDS and MySQL Connector/J

With Amazon Relational Database Service, you can quickly and easily provision and maintain a MySQL, Oracle, or Microsoft SQL Server instance in the cloud. For more information about Amazon RDS, go to http://aws.amazon.com/rds/.

This topic explains how you can use Amazon RDS and MySQL Connector/J with your AWS Elastic Beanstalk Java application.

Note

The instructions in this topic are for nonlegacy container types. If you have deployed an AWS Elastic Beanstalk application using a legacy container type, we recommend that you migrate to a nonlegacy container type to gain access to new features. For instructions on how to check the container type and migrate your application, see Migrating Your Application from a Legacy Container Type. For instructions on using MySQL Connector/J with applications running on legacy container types, see Using Amazon RDS and MySQL Connector/J (Legacy Container Types).

To use Amazon RDS from your AWS Elastic Beanstalk application, you need to do the following:

  1. Create an Amazon RDS DB instance.

  2. Download and install MySQL Connector/J.

  3. Establish a database connection in your code by using the connectivity information for your Amazon RDS DB instance.

  4. Deploy your application to AWS Elastic Beanstalk.

This topic walks you through the following:

  • Using a new Amazon RDS DB instance with your application

  • Using an existing Amazon RDS DB instance with your application

Using a New Amazon RDS DB Instance with Java

This topic walks you through creating a new Amazon RDS DB Instance and using it with your Java application.

  1. Sign in to the AWS Management Console and open the AWS Elastic Beanstalk console at https://console.aws.amazon.com/elasticbeanstalk/.

  2. Click your environment name and select Configuration.

  3. Scroll down to the Data Tier section and click Create a new RDS database.

  4. Enter a user name and password and click Save. Click Save again on the message about instance profiles, if shown. AWS Elastic Beanstalk updates your environment to include the new RDS instance and provides connection information to the web container as environment properties.

  5. Download and install MySQL Connector/J for your development environment. For download and installation instructions, go to http://dev.mysql.com/downloads/connector/j.

  6. Copy the MySQL Connector/J .jar file into the project's WebContent/WEB-INF/lib directory.

  7. Right click on WebContent/WEB-INF/lib/mysql_connector_java_5.1.34_bin.jar and click Add to Build Path to add the library to the classpath.

  8. Test the database connection by adding the following code to the main view of your Java Web Application (index.jsp).

    <%@ page import="java.sql.*" %>
    <%
      // Read RDS Connection Information from the Environment
      String dbName = System.getProperty("RDS_DB_NAME");
      String userName = System.getProperty("RDS_USERNAME");
      String password = System.getProperty("RDS_PASSWORD");
      String hostname = System.getProperty("RDS_HOSTNAME");
      String port = System.getProperty("RDS_PORT");
      String jdbcUrl = "jdbc:mysql://" + hostname + ":" +
        port + "/" + dbName + "?user=" + userName + "&password=" + password;
      
      // Load the JDBC Driver
      try {
        System.out.println("Loading driver...");
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded!");
      } catch (ClassNotFoundException e) {
        throw new RuntimeException("Cannot find the driver in the classpath!", e);
      }
    
      Connection conn = null;
      Statement setupStatement = null;
      Statement readStatement = null;
      ResultSet resultSet = null;
      String results = "";
      int numresults = 0;
      String statement = null;
    
      try {
        // Create connection to RDS instance
        conn = DriverManager.getConnection(jdbcUrl);
        
        // Create a table and write two rows
        setupStatement = conn.createStatement();
        String createTable = "CREATE TABLE Beanstalk (Resource char(50));";
        String insertRow1 = "INSERT INTO Beanstalk (Resource) VALUES ('EC2 Instance');";
        String insertRow2 = "INSERT INTO Beanstalk (Resource) VALUES ('RDS Instance');";
        
        setupStatement.addBatch(createTable);
        setupStatement.addBatch(insertRow1);
        setupStatement.addBatch(insertRow2);
        setupStatement.executeBatch();
        setupStatement.close();
        
      } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
      } finally {
        System.out.println("Closing the connection.");
        if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
      }
    
      try {
        conn = DriverManager.getConnection(jdbcUrl);
        
        readStatement = conn.createStatement();
        resultSet = readStatement.executeQuery("SELECT Resource FROM Beanstalk;");
    
        resultSet.first();
        results = resultSet.getString("Resource");
        resultSet.next();
        results += ", " + resultSet.getString("Resource");
        
        resultSet.close();
        readStatement.close();
        conn.close();
    
      } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
      } finally {
           System.out.println("Closing the connection.");
          if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
      }
    %>
  9. Place the following code in the body of the html portion of index.jsp to display the results.

    <p>Established connection to RDS. Read first two rows: <%= results %></p>

  10. Right-click your project in the Package Explorer and choose Run As, Run on Server.

  11. If your project is not configured to always deploy to the same environment, select the environment that your application is currently running on and click Finish.

For more information on getting started using the MySQL Connector/J to access your MySQL database, go to http://dev.mysql.com/doc/connector-j/en/index.html.

Using an Existing Amazon RDS DB Instance with Java

With Amazon Relational Database Service you can quickly and easily provision and maintain a MySQL Server instance in the cloud. This topic discusses how you can use Amazon RDS and the MySQL Connector/J with your AWS Elastic Beanstalk application.

To use an existing Amazon RDS DB Instance and Java from your AWS Elastic Beanstalk application

  1. Create an AWS Elastic Beanstalk environment in one of the following ways:

    • Create a new application with a new environment. For instructions using the AWS Elastic Beanstalk console, see Creating New Applications. For instructions using Eclipse, see Develop, Test, and Deploy. You do not need to create an Amazon RDS DB Instance with this environment because you already have an existing Amazon RDS DB Instance.

    • Launch a new environment with an existing application version. For instructions using the AWS Elastic Beanstalk console, see Launching New Environments. You do not need to create an Amazon RDS DB instance with this environment because you already have an existing Amazon RDS DB instance.

  2. Configure your Amazon RDS DB security group to allow access from the Amazon EC2 security group used by your AWS Elastic Beanstalk application. For instructions on how to find the name of your EC2 security group using the AWS Management Console, see Amazon EC2 Security Groups. For more information, go to the "Authorizing Network Access to an Amazon EC2 Security Group" section of Working with DB Security Groups in the Amazon Relational Database Service User Guide.

  3. Download and install MySQL Connector/J for your development environment. For download and installation instructions, go to http://dev.mysql.com/downloads/connector/j.

  4. Create a JDBC connection string that includesyour Amazon RDS DB instance's public DNS name, port number, and (optionally) database name and login credentials. The following example shows a JDBC connection string that would connect to the employees database on an Amazon RDS instance at mydbinstance.abcdefghijkl.us-east-1.rds.amazonaws.com using port 3306, with the user name "sa" and the password "mypassword".

    jdbc:mysql://mydbinstance.abcdefghijkl.us-east-1.rds.amazonaws.com:3306/employees?user=sa&password=mypassword
  5. Configure your AWS Elastic Beanstalk environment to pass the string to your AWS Elastic Beanstalk application as an environment property. For instructions on how to do this, go to Using Custom Environment Properties with AWS Elastic Beanstalk.

  6. Retrieve the JDBC connection string from the environment property passed to your server instance from AWS Elastic Beanstalk and use MySQL Connector/J to access your Amazon RDS database. The following code example shows how to retrieve the JDBC_CONNECTION_STRING custom environment property from a Java Server Page (JSP).

    <p>
        The JDBC_CONNECTION_STRING environment variable is:
        <%= System.getProperty("JDBC_CONNECTION_STRING") %>
    </p>

    For more information on getting started using the MySQL Connector/J to access your MySQL database, go to http://dev.mysql.com/doc/connector-j/en/index.html.

  7. Copy the MySQL Connector/J .jar file into the Tomcat WEB-INF/lib directory.

  8. Deploy your updated application to your existing AWS Elastic Beanstalk environment. For information on how to deploy a new application version to an existing environment using the AWS Elastic Beanstalk console, see Step 4: Deploy New Version. For information on how to deploy your application using Eclipse, see Develop, Test, and Deploy.

Note

To connect to Tomcat RDS environments, you must load the driver explicitly using Class.forName(<driverClassName>) prior to the call to DriverManager.getConnection() in the Java code.

Troubleshooting Database Connections

If and when you run into issues connecting to a database from within your application, the web container log and database itself are two good places to look for information.

Logs

You can view all of the logs from your AWS Elastic Beanstalk environment from within Eclipse. If you don't have the AWS Explorer view open, click the arrow next to the orange AWS icon in the toolbar and choose Show AWS Explorer View. Expand <guilabe>AWS Elastic Beanstalk</guilabe> and your environment name, and then right-click the server and choose Open in WTP Server Editor.

Click the log tab of the server view to see the aggregate logs from your environment. Use the refresh button at the upper right corner of the view whenever you need to open the latest logs.

Scroll down and locate the TomCat logs from /var/log/tomcat7/catalina.out. If you loaded the web page from our earlier example several times, you might see the following in the output.

-------------------------------------
/var/log/tomcat7/catalina.out
-------------------------------------
INFO: Server startup in 9285 ms
Loading driver...
Driver loaded!
SQLException: Table 'Beanstalk' already exists
SQLState: 42S01
VendorError: 1050
Closing the connection.
Closing the connection.

Everything sent to standard output by the web application will show up in the web container log. This example tries to create the table on every page load and ends up catching a SQL exception on every page load after the first one.

Note

This is okay for an example, but in real world applications you'll keep your database definitions in schema objects, perform transactions from within model classes, and coordinate requests with controller servlets.

RDS

You can connect directly to the Amazon RDS instance in your AWS Elastic Beanstalk environment using the My SQL client application.

First you'll need to open the security group to your Amazon RDS instance to allow traffic from your computer.

  1. Open the AWS Elastic Beanstalk console at https://console.aws.amazon.com/elasticbeanstalk/.

  2. Click the name of your environment and click Configuration.

  3. Under Network Tier, in the RDS section, click Editthe gear icon.

  4. Select View in RDS Console next to the DB endpoint.

  5. On the RDS Dashboard instance details page, under Security and Network, click the security group starting with rds- next to Security Groups.

  6. In the security group details, select the Inbound tab and click Edit

  7. Add a rule for My SQL (similar to the one that is already there) that allows traffic from your IP address or range.

  8. Click Save. The changes take effect immediately.

Return to the AWS Elastic Beanstalk configuration details for your environment and note the endpoint. You will use the domain name to connect to the RDS instance.

Install My SQL client and initiate a connection to the database on port 3306. In Windows, install the My SQL Workbench application from the My SQL home page and follow the prompts.

In Linux, install the My SQL client using the package manager for your distribution. The following example works on Ubuntu and other Debian derivatives.

// Install My SQL client
$ sudo apt-get install mysql-client-5.5
...
// Connect to database
$ mysql -h aas839jo2vwhwb.cnubrrfwfka8.us-west-2.rds.amazonaws.com -u username -ppassword ebdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.5.40-log Source distribution
...

Once connected, you can run SQL commands to see the status of the database, whether your tables and rows have been created, and other information.

mysql> SELECT Resource from Beanstalk;
+--------------+
| Resource     |
+--------------+
| EC2 Instance |
| RDS Instance |
+--------------+
2 rows in set (0.01 sec)