Architecture & DesignUsing MySQL Databases in Oracle JDeveloper

Using MySQL Databases in Oracle JDeveloper

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

MySQL is the leading open source relational database management system (RDBMS). Oracle JDeveloper is a popular Java IDE that supports connectivity to Oracle Database and other RDBMSes, including MySQL. Whereas most Java IDEs provide access to relational databases, JDeveloper provides some features especially designed to explore a relational database. These features include SQL Worksheet to run SQL statements, SQL Snippets that can be dragged and dropped onto a SQL Worksheet, an Object Viewer to explore a table, SQL History, comparison of Database objects, SQL script generation, and import/export of a database connection.

In this tutorial, you will learn to connect to and use a MySQL database in JDeveloper. This tutorial has the following sections:

This tutorial will be followed by others that will show you how to use SQL Worksheets, Snippets, the Object Viewer, and more.

Setting Up the Environment

I have used Microsoft Windows in this tutorial; however, another operating system such as Linux or Mac OS X also can be used. Regardless of the operating system, start by downloading and installing the following software:

Once you have downloaded and installed the software, you’re ready to begin.

Starting MySQL

To verify that the MySQL database installed a connection to the database the from MySQL Command Line Interface (CLI), you can start the MySQL CLI with the following command:

mysql -u root -p

Once executed, the MySQL CLI command prompt mysql> gets displayed, as shown in Figure 1.

MySQL CLI
Figure 1: MySQL CLI

Starting JDeveloper

When starting JDeveloper, select the Studio Developer role in Select Role, as shown in Figure 2. We need features from the Database Developer and Java Developer roles and because only one role may be selected. Select the Studio Developer, which includes all features.

Select role
Figure 2: Select role

The Oracle JDeveloper gets launched and the default Start Page is shown in Figure 3.

JDeveloper Start Page
Figure 3: JDeveloper Start Page

Reviewing and Setting JDeveloper Preferences

JDeveloper provides global preferences settings for Database connectivity. To set preferences, select Tools>Preferences.

In Preferences, select Database, as shown in Figure 4.

Database Preferences
Figure 4: Database Preferences

Click Database>Advanced to display advanced preferences, including SQL Array Fetch Size (default is 50), Display Null Value As (default is ‘null’), and Autocommit (default is ON), as shown in Figure 5. If need be, some of these advanced settings may be modified. As an example, if individual transactions are to be committed separately, set Autocommit to OFF be deselecting the checkbox.

Database>Advanced Preferences
Figure 5: Database>Advanced Preferences

Because you will be accessing a MySQL database, you’ll want to set the appropriate driver. Select the JDBC Drivers Options under Database from the left side of the preference dialog. You then should select the MySQL JDBC driver to be used, as shown in Figure 6.

JDBC Driver options
Figure 6: JDBC Driver options

Next, you’ll want to verify the Worksheet preferences by selecting Worksheet from the Database options on the left side of the Preference window. The Worksheet preferences include whether to open a Worksheet on connect, whether to close all worksheets on disconnect, whether to prompt for save file on close, the maximum number of rows to print in a script, and SQL History Limit, as shown in Figure 7. The default settings should suffice but, if required, some of these may be modified.

Database>Worksheet Preferences
Figure 7: Database>Worksheet Preferences

Creating a Connection to MySQL in JDeveloper

To create a connection to MySQL, select File>New>From Gallery from the main JDeveloper menu.

In the New Gallery dialog that is presented, select General>Connections in Categories and Database Connection in Items, as shown in Figure 8. Click OK.

Selecting database connection
Figure 8: Selecting database connection

The required features get loaded, including the JDeveloper Resource Palette. In the Create Database Connection window, specify a Connection Name (MySQLConnection), and select Connection Type as MySQL from the drop-down list, as shown in Figure 9.

Selecting Connection Type as MySQL
Figure 9: Selecting Connection Type as MySQL

Specify Username as root and specify the password for the root user that was configured when installing MySQL. The root is the default user. In MySQL Settings, choose the JDBC Driver class from the Library MySQL JDBC. Specify Host Name as localhost and Database Name as mysql. Click Test Connection, as shown in Figure 10.

Create database connection
Figure 10: Create database connection

If the result of Test Connection is “Success!” click OK to complete the connection configuration, as shown in Figure 11. If the connection fails, it could be due to several reasons, such as any of the configuration settings being wrong. The error message would provide detailed information of why a connection could not be established.

Testing connection and completing connection
Figure 11: Testing connection and completing connection

A new MySQL JDBC Connection gets created and gets added to the Resources Palette, as shown in Figure 12.

MySQLConnection
Figure 12: MySQLConnection

We shall use the mysql database for which the Tables and Views may be listed in the Resource Palette, as shown in Figure 13.

The mysql database
Figure 13: The mysql database

Another view that lists the IDE database Connections in addition to the Resources view is the Databases view. Select Window>Database>Databases to open the Databases view, as shown in Figure 14.

Window>Database>Databases
Figure 14: Window>Database>Databases

The MySQLConnection JDBC connection is also displayed in the Databases view, as shown in Figure 15.

Databases>IDE Connections
Figure 15: Databases>IDE Connections

A new connection may be created from the Databases palette by selecting the New Connection icon, as shown in Figure 16.

New Connection
Figure 16: New Connection

As another option to create a new connection, right-click IDE Connections and select New Connection, as shown in Figure 17.

IDE Connections>New Connection
Figure 17: IDE Connections>New Connection

As indicated in the message in Figure 18, the IDE database connections appear in both the Databases and the Resources windows.

IDE Database Connections appear in both the Databases and Resources windows
Figure 18: IDE Database Connections appear in both the Databases and Resources windows

The structure of a connection is displayed in the Structure window, as shown in Figure 19.

Structure of a connection
Figure 19: Structure of a connection

Configuring a Secure Connection with SSH

When configuring a new JDBC connection, a SSH connection may be created by selecting SSH in the Create Database Connections dialog you saw earlier (Figures 11 to 13). You can see the location of the SSH button in Figure 20.

SSH
Figure 20: SSH

In SSH Tunnel Configuration, click Use SSH, as shown in Figure 21. Specify a SSH Host Name, SSH User, and Authentication password to configure a SSH connection. The SSH connection details are different for different users, based on the SSH configuration. For configuring SSH in MySQL server, refer to https://blogs.oracle.com/mysql/guide-to-install-and-configure-ssh-in-a-mysql-server-on-windows.

Configuring a SSH tunnel configuration
Figure 21: Configuring a SSH tunnel configuration

Creating a Database Table

Next, you will see how to add a table to the mysql database. From the Resource list, right-click Tables and select New Table, as shown in Figure 22.

Tables>New Table
Figure 22: Tables>New Table

The Create Table window should be displayed, as shown in in Figure 23. From this window, you can select a database as well as specify the table name and column names. For this project, choose the mysql database you created earlier.

Create Table
Figure 23: Create Table

With the mysql database selected, you can then specify a table name. Set the table name to wlslog for this example. You can then add your table columns. Go ahead and add a PK (primary key) column time_stamp of type VARCHAR and size 255 with Not Null option, as shown in Figure 24.

Adding a PK column
Figure 24: Adding a PK column

You can click the plus icon (Add Column), as shown in Figure 25, to add more columns.

Add Column
Figure 25: Add Column

Add columns category, type, servername, code, and msg all of type VARCHAR. The final results should match what is shown in Figure 26.

Adding table columns
Figure 26: Adding table columns

There are a number of tabs in the Create Table window, in addition to Columns tab. The Constraints tab displays the table constraints, the Indexes tab the indexes, the DDL tab, and the SQL CREATE TABLE statement, as shown in Figure 27.

DDL
Figure 27: DDL

Once you’ve added all of your columns and settings, you should click OK in the Create Table window to create the table, as shown in Figure 28.

Note: If you try to create a table with the same name as an existing table, the error message “An object already exists with that name” will be displayed, and you will need to select a new name.

The wlslog table
Figure 28: The wlslog table

To display the table properties, right-click wlslog and select Properties.

The table properties get displayed, as shown in Figure 29.

Table Properties
Figure 29: Table Properties

Summary

At this point, you have created a MySQL database containing a table and columns from JDeveloper! A table also can be created by running a CREATE TABLE SQL statement in a SQL Worksheet. SQL Worksheets will be covered in the next article, “Using SQL Worksheets in JDeveloper.” You’ll also learn how to use JDeveloper Worksheets to run other SQL commands against your MySQL database and not only create tables, but also manipulate data.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories