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
- Creating a MySQL Database Service on Oracle Cloud Platform
- Enabling Access to MySQL Service
- Conclusion
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.
Figure 1: Selecting Role as Studio Developer
The JDeveloper IDE console is shown in Figure 2.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 10: SSH Public Key for VM Access
A SSH Key pair gets created. Click Download (see Figure 11) to download the 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.
Figure 12: Selecting Backup Destination
Select Initialize Data from Backup>Create Instance from Existing Backup as No, as shown in Figure 13.
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.
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.
Figure 15: Specifying Database Schema and Port
Click Next (see Figure 16).
Figure 16: Service Details>Next
On the confirmation page, click Create, as shown in Figure 17, to create the MySQL database service.
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).
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.
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.
Figure 20: Access Rules
Click the icon to manage the access rule ora_p2admin_mysql (see Figure 21) and select Enable.
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.
Figure 22: Enable Access Rule
The access rule gets enabled, as shown in Figure 23.
Figure 23: Access Rule for port 3306 enabled
Click the Click to return to services link, as shown in Figure 24.
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.
