Architecture & DesignUsing Advanced Oracle JDeveloper Features for MySQL Databases

Using Advanced Oracle JDeveloper Features for MySQL Databases

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

The “Using MySQL Databases in Oracle JDeveloper” article introduced using JDeveloper with MySQL. In the “Using Oracle JDeveloper Worksheets” article, we discussed using SQL Worksheets in JDeveloper. In the previous article, “Using Oracle JDeveloper Snippets with MySQL,” we covered using SQL Snippets within Oracle JDeveloper. In this article, you’ll learn about some of the other features in JDeveloper that can be used with MySQL. This includes the following:

Using a Table as a Template

A database table already created may be used as a template to create another table.

To use a table as a template, right-click the table (wlslog) in Databases and select Use as Template, as shown in Figure 1.

Use as Template
Figure 1: Use as Template

A Create Table dialog gets displayed, as shown in Figure 2. Specify a new table name (wlslog2) and optionally delete any columns not to be added to the new table. Then, click Copy Columns to copy columns from other tables; this is done to copy columns not in the wlslog table but in some other table.

Create Table
Figure 2: Create Table

In Copy Columns to Table wlslog2, select a Schema and a Table and select column/s to add to the new table being created from a template. Multiple columns are selected with Shift. Click OK (see Figure 3).

Copy Columns to Table wlslog
Figure 3: Copy Columns to Table wlslog

Column/s from other table(s) get added to the wlslog2 table definition. Click OK in Create Table, as shown in Figure 4.

Creating a table from a template
Figure 4: Creating a table from a template

Click Refresh on Tables, as shown in Figure 5.

Refresh
Figure 5: Refresh

The new table wlslog2 gets listed (see Figure 6).

New table wlslog2
Figure 6: New table wlslog2

Only the table DDL gets copied—not the table data. Right-click the wlslog2 table and select Open Object Viewer, as shown in Figure 7.

Open Object Viewer
Figure 7: Open Object Viewer

The columns in the wlslog2 table get displayed, as shown in Figure 8.

Columns in wlslog2 table
Figure 8: Columns in wlslog2 table

Run a SQL query on the table created by using the wlslog table as a template. No data gets listed (see Figure 9).

Table data does not get copied
Figure 9: Table data does not get copied

Using Object Viewer

JDeveloper provides an Object Viewer to display details for an object. The preferences for the Object Viewer may be set in Preferences, as shown in Figure 10.

Object Viewer Preferences
Figure 10: Object Viewer Preferences

To open Object Viewer for a table, right-click the table and select Open Object Viewer, as mentioned earlier. Open Object Viewer for table wlslog, as shown in Figure 11.

Open Object Viewer for wlslog
Figure 11: Open Object Viewer for wlslog

The Object Viewer gets displayed (see Figure 12).

Object Viewer for wlslog
Figure 12: Object Viewer for wlslog

For display settings to auto-fit columns or to apply some of the other column settings, right-click the table header row and select one of the options, as shown in Figure 13.

Column options
Figure 13: Column options

Object Viewer provides some other features that are listed by right-clicking a column name, as shown in Figure 14.

Column options
Figure 14: Column options

As an example, to count rows, select Count Rows, as shown in Figure 15.

Count Rows
Figure 15: Count Rows

The row count gets displayed (see Figure 16).

Row count
Figure 16: Row count

To find/highlight a column, select Find/Highlight, as shown in Figure 17.

Find/Highlight
Figure 17: Find/Highlight

In the Find/Highlight dialog, specify “msg” and the msg column gets highlighted, as shown in Figure 18. The Find/Highlight feature seems redundant for a table with few columns but is useful for a table with several (100s or more) columns.

Find/Highlight
Figure 18: Find/Highlight

Exploring Databases

As discussed earlier, the Databases view may be used to display database connections. To get database reports, select Window>Database>Database Reports, as shown in Figure 19.

Window>Database>Database Reports
Figure 19: Window>Database>Database Reports

The database reports, including Data Dictionary Reports and User Defined Reports, get listed in Databases palette (see Figure 20).

Database Reports
Figure 20: Database Reports

Exploring SQL History

The SQL history for the various SQL statements run may be obtained with Window>Database>SQL History, as shown in Figure 21.

Window>Database>SQL History
Figure 21: Window>Database>SQL History

The SQL History gets listed, as shown in Figure 22.

SQL History
Figure 22: SQL History

The SQL History may be customized. Click Append to add a history item, as shown in Figure 23.

SQL History>Append
Figure 23: SQL History>Append

To replace a history item, click Replace, as shown in Figure 24.

SQL History>Replace
Figure 24: SQL History>Replace

To clear the history, click Clear History (see Figure 25).

Clear History
Figure 25: Clear History

The Filter may be used to filter the SQL History. As an example, you can list only the SQL History statements with “SELECT,” as shown in Figure 26.

Filter SQL History
Figure 26: Filter SQL History

Comparing Database Objects

Database objects may be compared with other database objects. As an example, to compare the MySQL table wlslog with other database objects, right-click wlslog and select Compare With Other Database Object, as shown in Figure 27.

Compare With Other Database Object
Figure 27: Compare With Other Database Object

In the Compare Database Objects, wizard select source database, as shown in Figure 28. Click Next.

Selecting Source Database
Figure 28: Selecting Source Database

In Select Objects, select the database objects to compare by moving them from Available to Selected. As an example, select the wlslog and wlslog2 tables for comparison, as shown in Figure 29. Click Next.

Selecting objects for comparison
Figure 29: Selecting objects for comparison

The target schema must be over-ridden and different to the source schema when a single database connection is used for both the destination and source. Click Override Schema and select wlslog, as shown in Figure 30.

Overriding the Schema
Figure 30: Overriding the Schema

The comparison of results between the wlslog and wlslog2 tables are shown in Figure 31.

Compare results
Figure 31: Compare results

Generating SQL

SQL for a database table may generated by selecting Generate To>SQL Script (see Figure 32).

Generate To>SQL Script
Figure 32: Generate To>SQL Script

The Generate SQL from Database Objects wizard gets started, as shown in Figure 33. Specify the SQL file name to generate. Click Next.

Generate SQL from Database Objects wizard
Figure 33: Generate SQL from Database Objects wizard

In Select Objects, select the Schema (mysql) and the object (wlslog), as shown in Figure 34. Click Next.

Selecting Objects to generate SQL for
Figure 34: Selecting Objects to generate SQL for

In Specify operation, select the SQL operation from Create, Replace, and Alter. Click Show SQL, as shown in Figure 35.

Selecting SQL Operation
Figure 35: Selecting SQL Operation

The SQL Statement(s) get displayed in a dialog, as shown in Figure 36.

SQL to generate
Figure 36: SQL to generate

Click Finish in Generate SQL from Database Objects, as shown in Figure 37.

Summary of Generate
Figure 37: Summary of Generate

Specify a File name to save, as shown in Figure 38.

Save
Figure 38: Save

Importing/Exporting a Connection

A SQL connection may be exported and imported. To export connection/s, right-click IDE Connections and select Export Connections (see Figure 39).

Export Connections
Figure 39: Export Connections

In Export Connections, select the connection/s and click Next, as shown in Figure 40.

Selecting connections to export
Figure 40: Selecting connections to export

Connection/s get exported as an XML file. In Destination File, select a folder and specify a file name. Then, click Save, as shown in Figure 41.

Choose Export File
Figure 41: Choose Export File

Click Next in Destination File, as shown in Figure 42.

Specifying Destination File
Figure 42: Specifying Destination File

In Password Handling, options to encrypt all passwords with a key or remove all passwords from the exported connections are provided. Select “Remove all passwords..” and click Next, as shown in Figure 43.

Password Handling for Export
Figure 43: Password Handling for Export

In Summary, click Finish. Connection/s get exported. To import a connection, right-click IDE Connections and select Import Connections, as shown in Figure 44.

Import Connections
Figure 44: Import Connections

In Import Connections, select a Source File to import (see Figure 45).

Specifying source file to import
Figure 45: Specifying source file to import

In Password Handling, again two options are provided; either use a key to decrypt all passwords or remove all passwords from the exported connections. Select “Remove all passwords…”, as shown in Figure 46.

Password handling for import
Figure 46: Password handling for import

Next, select the connection/s to import, as shown in Figure 47, and click Next. For Duplicate Connections, keep the default setting of Replace.

Selecting connections to import
Figure 47: Selecting connections to import

Click Finish in Summary.

The connection previously exported gets imported with a different connection name, as shown in Figure 48.

Imported connection MySQLConnection_1
Figure 48: Imported connection MySQLConnection_1

Deleting a Connection

To delete a connection, right-click the connection in Databases and select Delete (see Figure 49).

Selecting Delete
Figure 49: Selecting Delete

In Delete Confirmation, click Yes. The MySQLConnection_1 gets deleted, as shown in Figure 50.

MySQLConnection_1 Deleted
Figure 50: MySQLConnection_1 Deleted

Summary

In this tutorial, we discussed some of the relatively advanced features in Oracle JDeveloper for MySQL database connectivity.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories