AWS Relational Database Service (RDS) is a managed service for Oracle, MySQL, PostgreSQL, and SQL Server databases. In the first of three articles, “Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 1,” we created an RDS instance for MySQL database and modified the RDS instance to allow access from JDeveloper on a local machine. In this continuation article, we shall connect to the MySQL database on RDS with JDeveloper. This article has the following sections:
- Using JDeveloper with MySQL Database
- Enabling Connectivity
- Obtaining the Connection Parameters for MySQL Database
- Creating a Connection to MySQL Database in JDeveloper
- Conclusion
Using JDeveloper with MySQL Database
Oracle JDeveloper provides built-in support for MySQL database, but connectivity to the RDS DB instance has to be enabled. In the following sections, we shall enable connectivity to the RDS DB instance, obtain the connection parameters to connect to the RDS DB instance, and create a connection to the RDS DB instance.
Enabling Connectivity
By default, a new RDS Instance has Security Group Inbound rules set to allow connection only from a specific IP address range in a CIDR block, which could be a single IP address. To allow connectivity from the IP address of the machine on which JDeveloper is installed the Security Group, Inbound rules need to be modified. From the RDS DB instance Details section, click the Security Group link, as shown in Figure 1.
Figure 1: RDS DB Instance>Security Groups Link
The security group console gets displayed. Select the Inbound tab and click Edit to modify the Security group rule, as shown in Figure 2.
Figure 2: Security group>Inbound>Edit
In Edit inbound rules, modify the source IP Address to the IP Address of the machine on which JDeveloper is installed. If the IP address of the machine on which JDeveloper is installed is not known or if access to multiple IP addresses is to be added, select Type as All traffic, Protocol as All, Port Range as 0-65535, and Source as Anywhere, as shown in Figure 3. Click Save.
Figure 3: Modifying Inbound rule
The Inbound rules get modified to allow all traffic, as shown in Figure 4.
Figure 4: Inbound rules modified to allow all traffic
Obtaining the Connection Parameters for MySQL Database
To connect to the MySQL database on RDS, we need to obtain the hostname and port information for the database. Click the mysqldb RDS instance, as shown in Figure 5.
Figure 5: RDS>Instances>mysqldb
In the mysqldb instance, detail page the Connectsection lists the connection parameters. Copy the Endpoint (see Figure 6). The Endpoint is the hostname of the RDS DB instance. The Port is 3306 by default. The Publicly accessible should be Yes.
Figure 6: Obtaining the RDS Instance Endpoint and Port
The database name is obtained from the DB Name setting in Configurations, as shown in Figure 7.
Figure 7: Obtaining DB Name
Creating a Connection to MySQL Database in JDeveloper
To create a new connection, click New Application in JDeveloper, as shown in Figure 8.
Figure 8: New Application
In New Gallery, select General>Connections in Categories and Database Connection in Items and click OK, as shown in Figure 9.
Figure 9: Selecting Database Connection in New Gallery
In the Create Database Connection wizard, specify a Connection Name (MySQLConnection) and select Connection Type as MySQL, as shown in Figure 10.
Figure 10: Selecting Connection Type as MySQL
With MySQL selected as the Connection Type, the Driver Class gets set to com.mysql.jdbc.Driver and the Library gets set to MySQL Library (see Figure 11).
Figure 11: MySQL Settings
Specify Username as the Master username configured when creating a new RDS DB instance. Specify Password as the Master password configured when creating a new RDS DB instance. Specify Host Name as the Endpoint copied in the Connect section in Figure 6. Specify Database Name as the DB Name copied from DB Name in Details>Configurations in Figure 7. JDBC Port is the Port from Connect section in Figure 6 and is 3306 by default. Click Test Connection, as shown in Figure 12.
Figure 12: Test Connection
The Connecting dialog gets displayed, as shown in Figure 13.
Figure 13: Connecting
If the output from Test Connection is Success Connected To: MySQL, click OK, as shown in Figure 14.
Figure 14: Create Database Connection>OK
The MySQLConnection gets created and gets added to the Resources view, as shown Figure 15. The MySQLConnection Structure view displays the structure of the connection, which includes the connection name, connection type, host name, port, database name, user name, and JDBC driver.
Figure 15: MySQLConnection added to Resources
Conclusion
In this article, we discussed using Oracle JDeveloper to connect to MySQL instance on AWS Relational Database Service (RDS). Some of the salient configuration features are:
- If JDeveloper is in a different VPC than the RDS DB instance, the RDS DB instance must be made Publicly accessible.
- A Security group Inbound rule for the IP Address of the machine on which JDeveloper is installed must be added.
In the 3rd article, we shall discuss using JDeveloper with Oracle Database on RDS.
