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
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.
|Ignore Case||Ignore case in data. Upper/lower cases in the search term or table data are not taken into consideration and only the search text is used.|
|Whole Word||Find if a whole word exists in the table|
|Starts With||Find if a column data starts with the specified term|
|Wrap Search||Determines whether to wrap search|
|Highlight Color||The highlight color to use|
|Persist Highlight||Determines whether to persist highlight color after the Find/Highlight dialog is closed. Persist Highlight may be selected only if Highlight Row is also selected.|
|Highlight Row||Determines whether to highlight a row in the result of a search|
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
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.