dcsimg
August 17, 2018
Hot Topics:

Using Oracle JDeveloper with MySQL Database Service on Oracle Cloud Platform, Part 1

  • January 5, 2018
  • By Deepak Vohra
  • Send Email »
  • More Articles »

Oracle JDeveloper is a Java IDE with support for Java EE and Oracle Application Development Framework (ADF). In an earlier article, we discussed using Oracle JDeveloper with Oracle Database on Oracle Cloud Platform. JDeveloper provides support for the commonly used relational databases, including IBM DB2, Apache Derby, SQL Server, and MySQL. In the next three articles, we shall discuss using JDeveloper to access a MySQL database service on Oracle Cloud Platform and perform database tasks such as creating a table, adding table data, querying a table, and exporting a result set. The first article has the following sections:

Setting the Environment

An Oracle Cloud Platform account is required and a free trial is available. The only software required to be installed is Oracle JDeveloper 12c. After installation, when JDeveloper is started, a user is prompted to select a role. Select the role Studio Developer (All features), as shown in Figure 1.

Selecting Role as Studio Developer
Figure 1: Selecting Role as Studio Developer

The JDeveloper IDE console is shown in Figure 2.

JDeveloper Console
Figure 2: JDeveloper Console

Creating a MySQL Database Service on Oracle Cloud Platform

After creating an Oracle Cloud account, a user is sent an Identity Domain in addition to a user name and password. A URL to access the Cloud services is also sent, such as the one in the preceding link. Use the URL log into the Oracle Cloud Services platform and select Create Instance from the Dashboard, as shown in Figure 3.

Dashboard>Create Instance
Figure 3: Dashboard>Create Instance

In the Create Instance dialog, select the All Services tab and click Create for the MySQL service, as shown in Figure 4.

All Services>MySQL>Create
Figure 4: All Services>MySQL>Create

The Oracle MySQL Cloud Service gets launched, as shown in Figure 5. Click Go to Console to start creating a MySQL Database service.

Oracle MySQL Cloud Service>Go to Console
Figure 5: Oracle MySQL Cloud Service>Go to Console

In the console, click Create Service (see Figure 6) to start creating a new MySQL database service.

Create Service
Figure 6: Create Service

In the Create Service wizard, specify a Service Name (mysqldb as an example) and select a Region, which could be No Preference, as shown in Figure 7. These are the only two required fields on the first page. Optionally, specify a Description and Notification Email. The Metering Frequency has a default value hard-coded. Click Next.

Specifying Service Name and Region
Figure 7: Specifying Service Name and Region

Next, provide the service details, which include the Configuration, MySQL Configuration, Backup and Recovery Configuration, and Initialize Data From Backup sections, as shown in Figure 8. Select a Compute Shape, which is a pre-defined configuration for CPU and RAM. The default setting of OC3 is a pre-defined configuration comprised of 1.0 OCPU and 7.50 GB RAM.

Selecting Compute Shape
Figure 8: Selecting Compute Shape

Next, click the Edit button for SSH Public Key to select, or create a public key for the instance, as shown in Figure 9. A public key is used if the VM instance is to be accessed directly in a Linux bash shell, but is not required if connecting to the MySQL service from an IDE such as JDeveloper. Regardless of whether the SSH Public key is to be used, the public key is required to be created.

SSH Public Key>Edit
Figure 9: SSH Public Key>Edit

The SSH Public Key for VM Access dialog gets launched, as shown in Figure 10. Click the Create a New Key option and click Enter to generate a new SSH Public key.

SSH Public Key for VM Access
Figure 10: SSH Public Key for VM Access

A SSH Key pair gets created. Click Download (see Figure 11) to download the key pair.

Downloading the SSH Key Pair
Figure 11: Downloading the SSH Key Pair

The SSH key pair zip file gets downloaded and the public key gets added to the SSH Public Key field. In Backup and Recovery Configuration, select from one of the Backup Destination options—Both Cloud and Disk Storage, Cloud Storage, and None. The storage option None is shown to be selected in Figure 12.

Selecting Backup Destination
Figure 12: Selecting Backup Destination

Select Initialize Data from Backup>Create Instance from Existing Backup as No, as shown in Figure 13.

Setting Initialize Data from Backup
Figure 13: Setting Initialize Data from Backup

In MySQL Configuration, the Usable Storage has a default value of 25 GB. Specify a password for the root user with the password requirements shown in Figure 14. Both the Password and Confirm Password fields must specify the same value.

Password requirements
Figure 14: Password requirements

Specify a Database Schema Name (mysqldb as an example), as shown in Figure 15. The MySQL Port has a default value of 3306.

Specifying Database Schema and Port
Figure 15: Specifying Database Schema and Port

Click Next (see Figure 16).

Service Details>Next
Figure 16: Service Details>Next

On the confirmation page, click Create, as shown in Figure 17, to create the MySQL database service.

Confirmation>Create
Figure 17: Confirmation>Create

The MySQL service starts to get created, as shown by the "Creating service…" message in Figure 18. Click Refresh periodically to update the service status. A message is sent after the service has been created if a Notification Email is provided when creating the service (refer to Figure 7).

Creating MySQL Service
Figure 18: Creating MySQL Service

When the MySQL service gets created, the Submitted On timestamp gets replaced with a Created On timestamp, as shown in Figure 19. A Storage value for the service instance gets allocated. The hourglass in the service icon also gets removed.

MySQL Service created
Figure 19: MySQL Service created

Enabling Access to MySQL Service

By default, a new MySQL service cannot be accessed from the wider public Internet. To enable access from the Internet, including a JDeveloper from a local machine, click the icon to manage the service and select Access Rules, as shown in Figure 20.

Access Rules
Figure 20: Access Rules

Click the icon to manage the access rule ora_p2admin_mysql (see Figure 21) and select Enable.

Selecting Enable for Access Rule to allow access on port 3306
Figure 21: Selecting Enable for Access Rule to allow access on port 3306

In the Enable Access Rule dialog, click Enable, as shown in Figure 22.

Enable Access Rule
Figure 22: Enable Access Rule

The access rule gets enabled, as shown in Figure 23.

Access Rule for port 3306 enabled
Figure 23: Access Rule for port 3306 enabled

Click the Click to return to services link, as shown in Figure 24.

Click to return to services
Figure 24: Click to return to services

Conclusion

In this article, we created a MySQL service on Oracle Cloud Platform and enabled external Internet access to the service. In subsequent articles, we shall discuss connecting to the service with JDeveloper and use the different JDeveloper features for databases.






Comment and Contribute

 


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

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

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