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
- Using Object Viewer
- Exploring Databases
- Exploring SQL History
- Comparing Database Objects
- Generating SQL
- Importing/Exporting a Connection
- Deleting a Connection
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.
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.
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).
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.
Figure 4: Creating a table from a template
Click Refresh on Tables, as shown in Figure 5.
Figure 5: Refresh
The new table wlslog2 gets listed (see Figure 6).
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.
Figure 7: Open Object Viewer
The columns in the wlslog2 table get displayed, as shown in Figure 8.
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).
Figure 9: Table data does not get copied
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.
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.
Figure 11: Open Object Viewer for wlslog
The Object Viewer gets displayed (see Figure 12).
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.
Figure 13: Column options
Object Viewer provides some other features that are listed by right-clicking a column name, as shown in Figure 14.
Figure 14: Column options
As an example, to count rows, select Count Rows, as shown in Figure 15.
Figure 15: Count Rows
The row count gets displayed (see Figure 16).
Figure 16: Row count
To find/highlight a column, select Find/Highlight, as shown in Figure 17.
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.
Figure 18: Find/Highlight
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.
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).
Figure 20: Database Reports
The SQL history for the various SQL statements run may be obtained with Window>Database>SQL History, as shown in Figure 21.
Figure 21: Window>Database>SQL History
The SQL History gets listed, as shown in Figure 22.
Figure 22: SQL History
The SQL History may be customized. Click Append to add a history item, as shown in Figure 23.
Figure 23: SQL History>Append
To replace a history item, click Replace, as shown in Figure 24.
Figure 24: SQL History>Replace
To clear the history, click Clear History (see Figure 25).
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.
Figure 26: Filter SQL History
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.
Figure 27: Compare With Other Database Object
In the Compare Database Objects, wizard select source database, as shown in Figure 28. Click Next.
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.
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.
Figure 30: Overriding the Schema
The comparison of results between the wlslog and wlslog2 tables are shown in Figure 31.
Figure 31: Compare results
SQL for a database table may generated by selecting Generate To>SQL Script (see Figure 32).
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.
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.
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.
Figure 35: Selecting SQL Operation
The SQL Statement(s) get displayed in a dialog, as shown in Figure 36.
Figure 36: SQL to generate
Click Finish in Generate SQL from Database Objects, as shown in Figure 37.
Figure 37: Summary of Generate
Specify a File name to save, as shown in Figure 38.
Figure 38: Save
A SQL connection may be exported and imported. To export connection/s, right-click IDE Connections and select Export Connections (see Figure 39).
Figure 39: Export Connections
In Export Connections, select the connection/s and click Next, as shown in Figure 40.
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.
Figure 41: Choose Export File
Click Next in Destination File, as shown in Figure 42.
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.
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.
Figure 44: Import Connections
In Import Connections, select a Source File to import (see Figure 45).
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.
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.
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.
Figure 48: Imported connection MySQLConnection_1
To delete a connection, right-click the connection in Databases and select Delete (see Figure 49).
Figure 49: Selecting Delete
In Delete Confirmation, click Yes. The MySQLConnection_1 gets deleted, as shown in Figure 50.
Figure 50: MySQLConnection_1 Deleted
In this tutorial, we discussed some of the relatively advanced features in Oracle JDeveloper for MySQL database connectivity.