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:
- Setting Up the Environment
- Reviewing and Setting JDeveloper Preferences
- Creating a Connection to MySQL in JDeveloper
- Creating a Database Table
This tutorial will be followed by others that will show you how to use SQL Worksheets, Snippets, the Object Viewer, and more.
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.
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.
Figure 1: MySQL CLI
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.
Figure 2: Select role
The Oracle JDeveloper gets launched and the default Start Page is shown in Figure 3.
Figure 3: JDeveloper Start Page
JDeveloper provides global preferences settings for Database connectivity. To set preferences, select Tools>Preferences.
In Preferences, select Database, as shown in Figure 4.
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.
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.
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.
Figure 7: Database>Worksheet Preferences
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.
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.
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.
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.
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.
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.
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.
Figure 14: Window>Database>Databases
The MySQLConnection JDBC connection is also displayed in the Databases view, as shown in Figure 15.
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.
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.
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.
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.
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.
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.
Figure 21: Configuring a SSH tunnel configuration
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.
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.
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.
Figure 24: Adding a PK column
You can click the plus icon (Add Column), as shown in Figure 25, to add more columns.
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.
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.
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.|
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.
Figure 29: Table Properties
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.