Oracle JDeveloper is an integrated development environment (IDE) not only for Java but also for database access. In an earlier article, ” Using Oracle JDeveloper with MySQL Database Service on Oracle Cloud Platform, Part 1,” we discussed creating a MySQL database service on Oracle Cloud Platform. In this continuation article, we discuss some of the database features in JDeveloper to connect to the MySQL database service and create a table, add table data, and query the table.
This article has the following sections:
- Creating a Connection to MySQL Database Service
- Creating a Database Table
- Adding Table Data with SQL Worksheet
- Conclusion
Creating a Connection to MySQL Database Service
In this section, we shall create a connection to MySQL service in JDeveloper. Click New Application, as shown in Figure 1, to create a new database connection.
Figure 1: New Application
Alternatively, select File>New>Application, as shown in Figure 2.
Figure 2: File>New>Application
A third option is to select File>New>From Gallery, as shown in Figure 3.
Figure 3: File>New Gallery
All these selections launch the same New Gallery window, as shown in Figure 4. Select General>Connection in Categories Database Connection in Items. Click OK.
Figure 4: New Gallery>General>Connections>Database Connection
The Create Database Connection wizard gets launched, as shown in Figure 5. The default settings are for Oracle Database; we need to modify these for MySQL.
Figure 5: Create Database Connection
Select MySQL in the Connection Type drop-down (see Figure 6).
Figure 6: Selecting Connection Type as MySQL
With the Connection Type as MySQL, Oracle Settings get replaced with MySQL Settings, which include the Driver and Library, as shown in Figure 7. The Host Name field has a default setting of localhost, which needs to be replaced with the Public IP of the MySQL Service.
Figure 7: MySQL Settings
To obtain the Public IP of the MySQL service, click the mysqldb service link in the Oracle Cloud service dashboard, as shown in Figure 8.
Figure 8: The mysqldb Service link displays the service detail
In the service detail page, copy the Public IP from the Resources section, as shown in Figure 9.
Figure 9: Obtaining the Public IP
Copy and paste the Public IP in the Host Name field, as shown in Figure 10. Specify Database Name as mysqldb, which is configured when a new service is created, as shown in Figure 15. Optionally, also, modify the default Connection Name (MSQLConnection). Click Test Connection to test the database connection.
Figure 10: Specifying Host Name and Database Name
If a connection gets established, a Success message is displayed (see Figure 11). Click OK.
Figure 11: Create Database Connection>OK
A new connection gets added to the Resources window, as shown in Figure 12. Database schema mysqldb is listed in addition to the default schemas. The mysqldb schema node displays the Tables and Views nodes. The Structure window displays the connection detail.
Figure 12: Connection MySQLConnection added to Resources
A new connection may also be created from the Database node by selecting New Database Connection, as shown in Figure 13.
Figure 13: Database>New Database Connection
A Databases window may be added by selecting Window>Database>Databases, as shown in Figure 14.
Figure 14: Window>Database>Databases
The Databases window gets displayed, as shown in Figure 15. The Structure window displays the connection structure.
Figure 15: Databases Window
Creating a Database Table
In this section, we shall create a new database table in the MySQL database service. To create a new table, right-click Database>MySQLConnection>mysqldb>Tables in the Resources window and select New Table, as shown in Figure 16.
Figure 16: Resource>Database>MySQLConnection>mysqldb>Tables>New Table
A Create Table wizard gets launched, as shown in Figure 17. Because we launched the Create Table from mysqldb, the Database is selected, mysqldb. A default table Name is specified and one column is listed by default.
Figure 17: Create Table Wizard
Add column definitions for logid, category, type, servername, code, and msg with the primary key column of type INT as logid. Select the data type for a column in the Data Type drow-down, as shown in Figure 18.
Figure 18: Adding the Primary key column logid
Click the Add Column button, as shown in Figure 19, to add the other columns.
Figure 19: Add Column
Add the other columns, all of type VARCHAR, as shown in Figure 20.
Figure 20: Create Table>Columns
The DDL tab displays the DDL for creating a new table (see Figure 21).
Figure 21: Create Table>DDL
Click OK to create the database table, as shown in Figure 22.
Figure 22: Create Table>OK
A new table wlslog gets created, as shown in Resources window in Figure 23.
Figure 23: Resources>Database>MySQLConenction>mysqldb>Tables>wlslog
The table structure is displayed in the Structure window, as shown in Figure 24.
Figure 24: Table wlslog Structure
Click a table to display the structure for the table, as shown for the wlslog table. In the Databases window, the wlslog table is listed, as shown in Figure 25. The Structure window displays the columns.
Figure 25: Databases Window
Adding Table Data with SQL Worksheet
Next, we shall add data to the wlslog table using a SQL Script in SQL Worksheet. Select Tools>Database>SQL Worksheet (see Figure 26) to start a new SQL worksheet.
Figure 26: Tools>Database>SQL Worksheet
In the Select Connection dialog, select the MySQLConnection, as shown in Figure 27.
Figure 27: Selecting Connection as MySQLConnection
A SQL Worksheet gets opened, as shown in Figure 28.
Figure 28: SQL Worksheet
Copy and paste the following SQL script in the SQL Worksheet:
INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(1,'Notice','WebLogicServer','AdminServer','BEA-000365', 'Server state changed to STANDBY'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(2,'Notice','WebLogicServer','AdminServer','BEA-000365', 'Server state changed to STARTING'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(3,'Notice','WebLogicServer','AdminServer','BEA-000365', 'Server state changed to ADMIN'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(4,'Notice','WebLogicServer','AdminServer','BEA-000365', 'Server state changed to RESUMING'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(5,'Notice','WebLogicServer','AdminServer','BEA-000361', 'Started WebLogic AdminServer'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(6,'Notice','WebLogicServer','AdminServer','BEA-000365', 'Server state changed to RUNNING'); INSERT INTO wlslog(logid,category,type,servername,code,msg) VALUES(7,'Notice','WebLogicServer','AdminServer','BEA-000360', 'Server started in RUNNING mode');
Click the button to run the SQL script, as shown in Figure 29.
Figure 29: Running SQL Script
As the Script Output in Figure 30 indicates, data gets added to the wlslog table.
Figure 30: Script Output
After a SQL Worksheet has been opened and a SQL script run, more options became available in the Tools>Database (see Figure 31), in comparison to Figure 26.
Figure 31: Tools>Database
Conclusion
In this article, we discussed using JDeveloper to connect to MySQL database service on Oracle Cloud Platform, create a database table, and add table data. In the third article on using JDeveloper with MySQL database on Oracle Cloud Platform, we shall query the database table, export a result set, find and highlight records, and disconnect and re-connect a connection.
