dcsimg
April 19, 2018
Hot Topics:

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

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

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

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.

New Application
Figure 1: New Application

Alternatively, select File>New>Application, as shown in Figure 2.

File>New>Application
Figure 2: File>New>Application

A third option is to select File>New>From Gallery, as shown in Figure 3.

File>New Gallery
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.

New Gallery>General>Connections>Database Connection
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.

Create Database Connection
Figure 5: Create Database Connection

Select MySQL in the Connection Type drop-down (see Figure 6).

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

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

The mysqldb Service link displays the service detail
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.

Obtaining the Public IP
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.

Specifying Host Name and Database Name
Figure 10: Specifying Host Name and Database Name

If a connection gets established, a Success message is displayed (see Figure 11). Click OK.

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

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

Database>New Database Connection
Figure 13: Database>New Database Connection

A Databases window may be added by selecting Window>Database>Databases, as shown in Figure 14.

Window>Database>Databases
Figure 14: Window>Database>Databases

The Databases window gets displayed, as shown in Figure 15. The Structure window displays the connection structure.

Databases Window
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.

Resource>Database>MySQLConnection>mysqldb>Tables>New Table
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.

Create Table Wizard
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.

Adding the Primary key column logid
Figure 18: Adding the Primary key column logid

Click the Add Column button, as shown in Figure 19, to add the other columns.

Add Column
Figure 19: Add Column

Add the other columns, all of type VARCHAR, as shown in Figure 20.

Create Table>Columns
Figure 20: Create Table>Columns

The DDL tab displays the DDL for creating a new table (see Figure 21).

Create Table>DDL
Figure 21: Create Table>DDL

Click OK to create the database table, as shown in Figure 22.

Create Table>OK
Figure 22: Create Table>OK

A new table wlslog gets created, as shown in Resources window in Figure 23.

Resources>Database>MySQLConenction>mysqldb>Tables>wlslog
Figure 23: Resources>Database>MySQLConenction>mysqldb>Tables>wlslog

The table structure is displayed in the Structure window, as shown in Figure 24.

Table wlslog Structure
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.

Databases Window
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.

Tools>Database>SQL Worksheet
Figure 26: Tools>Database>SQL Worksheet

In the Select Connection dialog, select the MySQLConnection, as shown in Figure 27.

Selecting Connection as MySQLConnection
Figure 27: Selecting Connection as MySQLConnection

A SQL Worksheet gets opened, as shown in Figure 28.

SQL Worksheet
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.

Running SQL Script
Figure 29: Running SQL Script

As the Script Output in Figure 30 indicates, data gets added to the wlslog table.

Script Output
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.

Tools>Database
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.






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