dcsimg
August 15, 2018
Hot Topics:

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

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

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

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.

Run Statement
Figure 1: Run Statement

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

Result Set
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.

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

Count Rows
Figure 4: Count Rows

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

Row Count
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).

Find/Highlight
Figure 6: Find/Highlight

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

Find/Highlight Dialog
Figure 7: Find/Highlight Dialog

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

Find/Highlight Option

Description
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.

Using Find/Highlight to find matching data
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.

Found 5 matches
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.

Highlighted Rows
Figure 10: Highlighted Rows

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

Row Highlight removed when dialog closed
Figure 11: Row Highlight removed when dialog closed

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

Persist Highlight Option selected
Figure 12: Persist Highlight Option selected

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

Highlighted rows
Figure 13: Highlighted rows

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

Closing the Find/Highlight Dialog
Figure 14: Closing the Find/Highlight Dialog

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

Highlighted rows stay highlighted even after Find/Highlight Dialog is closed
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).

Clearing the Find Result Highlighting
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.

Highlighting on Find Records cleared
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.

Export…
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.

Source/Destination wizard
Figure 19: Source/Destination wizard

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

Export Summary
Figure 20: Export Summary

The export script gets generated (see Figure 21).

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

Disconnect
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.

Save dialog
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.

Connect
Figure 24: Connect

Exporting a Connection

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

Export
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.

Export Catalogs and Connections >Export
Figure 26: Export Catalogs and Connections >Export

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

Export completed successfully
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.

Delete
Figure 28: Delete

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

Delete Confirmation
Figure 29: Delete Confirmation

The connection gets deleted, as shown in Figure 30.

MySQLConnection deleted
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).

Selecting Delete
Figure 31: Selecting Delete

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

Delete Service
Figure 32: Delete Service

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

Deleting Service
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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

Thanks for your registration, follow us on our social networks to keep up-to-date