dcsimg
February 21, 2018
Hot Topics:

Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 2

  • February 9, 2018
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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

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.

RDS DB Instance>Security Groups Link
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.

Security group>Inbound>Edit
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.

Modifying Inbound rule
Figure 3: Modifying Inbound rule

The Inbound rules get modified to allow all traffic, as shown in Figure 4.

Inbound rules modified to allow all traffic
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.

RDS>Instances>mysqldb
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.

Obtaining the RDS Instance Endpoint and Port
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.

Obtaining DB Name
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.

New Application
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.

Selecting Database Connection in New Gallery
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.

Selecting Connection Type as MySQL
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).

MySQL Settings
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.

Test Connection
Figure 12: Test Connection

The Connecting dialog gets displayed, as shown in Figure 13.

Connecting
Figure 13: Connecting

If the output from Test Connection is Success Connected To: MySQL, click OK, as shown in Figure 14.

Create Database Connection>OK
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.

MySQLConnection added to Resources
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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date