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

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

In “Using Oracle JDeveloper with MySQL Database Service on Oracle Cloud Platform, Part 1,” a MySQL database service is created on Oracle Cloud Platform. In the “Using Oracle JDeveloper with MySQL Database Service on Oracle Cloud Platform, Part 2” article, a connection is created to the MySQL database service with Oracle JDeveloper and a database table created and table data added. In this article, we discuss querying the table and exporting the result set to a SQL script. We also discuss some of the other result set features, such as finding number of records and finding and highlighting records. This article has the following sections:

• Querying Table Data with SQL Worksheet
• Listing SQL History
• Finding the Number of Records in a Result Set
• Finding and Highlighting Records in a Result Set
• Exporting a Result Set
• Disconnecting and Connecting
• Exporting a Connection
• Deleting a Connection
• Deleting MySQL Service
• Conclusion

Querying Table Data with SQL Worksheet

Open another SQL Worksheet and add a SELECT statement to query the wlslog table, as shown in Figure 1. The button used to run a single SQL Statement, as shown in Figure 1, is different from the button used to run a SQL script.

Figure 1: Run Statement

The result set from the SQL query gets listed, as shown in Figure 2.

Figure 2: Result Set

Listing SQL History

To list the SQL history, which is a listing of SQL statements run in the near past, select SQL History from the toolbar, as shown in Figure 3.

Figure 3: SQL History

Finding the Number of Records in a Result Set

The result set in the example has only seven rows and determining the number of rows does not require any additional tool, but if a result set has several rows (thousands of rows), it may take significant scrolling to find the total number of rows in the result set without a tool feature such as Count Rows. Right-click the result set and select Count Rows…, as shown in Figure 4, to find the number of rows.

Figure 4: Count Rows

The Row Count gets listed, as shown in Figure 5.

Figure 5: Row Count

Finding and Highlighting Records in a Result Set

JDeveloper has a feature to find data rows (also called records) in a result set based on some search criterion/criteria such as that a record contains some term. To use the feature, right-click in the result-set and select Find/Highlight (see Figure 6).

Figure 6: Find/Highlight

A Find/Highlight dialog gets displayed, as shown in Figure 7.

Figure 7: Find/Highlight Dialog

Several find/highlight options are available. These are shown in Table 1.

Table 1: Find/Highlight Options

As an example, specify the term BEA-000365 in the Find/Highlight dialog and select the Whole Word option, as shown in Figure 8. Select Enter.

Figure 8: Using Find/Highlight to find matching data

As shown in Figure 9, the message “Found 5 matches” shows that five records are found.

Figure 9: Found 5 matches

The result of the find does not indicate which the matching records are. To indicate which records are matching with the find, select the Highlight Row option and select the Color, an option which becomes available only if Highlight Row is selected. Select Enter and the records with matching data get highlighted, as shown in Figure 10.

Figure 10: Highlighted Rows

If the Find/Highlight dialog is closed, the highlight from the selected rows also gets removed (see Figure 11).

Figure 11: Row Highlight removed when dialog closed

To persist the row highlighting, also select the option Persist Highlight, as shown in Figure 12.

Figure 12: Persist Highlight Option selected

Select Enter as before, and the matching rows get highlighted, as shown in Figure 13.

Figure 13: Highlighted rows

Selecting Persist Highlight adds another feature. Close the Find/Highlight dialog, as shown in Figure 14.

Figure 14: Closing the Find/Highlight Dialog

The highlighted rows stay highlighted, as shown in Figure 15.

Figure 15: Highlighted rows stay highlighted even after Find/Highlight Dialog is closed

To clear the highlighted records in a result set, click the X button (see Figure 16).

Figure 16: Clearing the Find Result Highlighting

The highlighting on the records gets removed, as shown in Figure 17. But, the number of records matched message is still listed.

Figure 17: Highlighting on Find Records cleared

Exporting a Result Set

To export the result set, right-click in the result set and select Export…, as shown in Figure 18.

Figure 18: Export…

The Export feature exports all records in the result set, not just the highlighted records. The Source/Destination wizard gets launched, as shown in Figure 19. The Connection is selected by default and cannot be modified. The Export Data section has the Format,as insert,by default, which generates insert statements in the export script. The Line Terminator is set to environment default, which is a newline character. The Table Name is set to EXPORT_TABLE by default. The Save As is set to Single File and the default Encoding is set also. The File name field specifies the file to export to and may be modified from the default file name of export.sql. Click Next.

Figure 19: Source/Destination wizard

Click Finish in the Export Summary, as shown in Figure 20.

Figure 20: Export Summary

The export script gets generated (see Figure 21).

Figure 21: Export Script

Disconnecting and Connecting

To disconnect a connection, right-click a connection in the Databases window and select Disconnect, as shown in Figure 22.

Figure 22: Disconnect

Before a connection is disconnected, confirmation Save dialogs are opened to save any unsaved scripts such as the MySQLConnection~1.sql script shown in Figure 23. Click Yes to save the script.

Figure 23: Save dialog

A Save window gets opened to provide the file directory location. Select a directory and file name and click Save. To connect a disconnected connection, right-click the connection in Databases and select Connect, as shown in Figure 24.

Figure 24: Connect

Exporting a Connection

To export a connection, right-click the connection and select Export, as shown in Figure 25.

Figure 25: Export

The Export Catalogs and Connections dialog gets started (see Figure 26). Specify an Archive File Name; it will have a .rcx suffix. For error handling during export, two options are provided: Fail On First Error and Ignore Errors. Click Export.

Figure 26: Export Catalogs and Connections >Export

The export gets completed successfully, as shown in Figure 27. Click OK.

Figure 27: Export completed successfully

Deleting a Connection

To delete a MySQLConnection connection, right-click the connection in the Databases window or the Resources window and select Delete, as shown in Figure 28.

Figure 28: Delete

Click Yes in Delete Confirmation, as shown in Figure 29.

Figure 29: Delete Confirmation

The connection gets deleted, as shown in Figure 30.

Figure 30: MySQLConnection deleted

Deleting MySQL Service

To delete a MySQL database service, select Delete from the icon to manage the service (see Figure 31).

Figure 31: Selecting Delete

In Delete Service dialog, select Force service deletion and click Delete, as shown in Figure 32.

Figure 32: Delete Service

The mysqldb service starts to get deleted, as shown in Figure 33.

Figure 33: Deleting Service

Conclusion

In three articles, we discussed using JDeveloper with MySQL database service in Oracle Cloud platform. First, we created a MySQL service and subsequently we connected to the service with JDeveloper. We demonstrated the different JDeveloper features for databases, such as creating a table, adding table data and querying table using a SQL Worksheet, exporting a result set, finding and highlighting records, and disconnecting and connecting a connection.