Configuring Database Access in Eclipse 3.0 with SQLExplorerby Deepak Vohra05/11/2005 SQLExplorer is an Eclipse IDE database plugin that may be used to connect to a database from Eclipse. The SQLExplorer plugin adds a graphical user interface (GUI) to access a database with SQL. With SQLExplorer, you can display the tables, table structure, and data in the tables, and retrieve, add, update, or delete table data. SQLExplorer can also generate SQL scripts to create and query tables. Thus, using SQLExplorer may be preferable to using a command-line SQL client. In this tutorial, we shall establish a JDBC connection with the open source MySQL database from Eclipse 3.0 with the SQLExplorer plugin. This tutorial has the following sections:
OverviewThe SQLExplorer plugin configures Eclipse for SQL client access to a database, by adding an SQLExplorer "perspective" to the IDE. To demonstrate the SQLExplorer plugin, we shall create an example table in the open source MySQL database and establish a JDBC connection to the MySQL database from the Eclipse IDE. Next, we shall retrieve and display the example data in the SQLExplorer GUI SQL client. We shall also update and delete the example table data to demonstrate the different features of the SQLExplorer plugin. Preliminary Setup
Configuring SQLExplorerHaving installed the SQLExplorer plugin, we shall configure the SQLExplorer plugin in the Eclipse 3.01 IDE. First, set the SQLExplorer perspective in the Eclipse IDE. Click on the "Open a perspective" button in the Eclipse IDE to open a perspective. Figure 1 illustrates the "Open a perspective" button.
In the item list, select "Other..." to display the SQLExplorer plugin as shown in Figure 2.
In the Select Perspective frame, select the SQLExplorer perspective, as shown in Figure 3. By selecting the SQLExplorer perspective, the SQLExplorer plugin features become available in the Eclipse IDE.
Selecting the SQLExplorer perspective displays the features of the SQLExplorer plugin in Eclipse. The Drivers tab displays the different database drivers that may be used to connect to different databases. The available databases include DB2, MySQL, Oracle, Sybase, HSQLDB, SQL Server, and PostgreSQL. We shall configure the SQLExplorer with the MySQL database. To configure the MySQL driver, right-click on the MMMySQL Driver node and select Change the Selected Driver, as illustrated in Figure 4.
In the Modify Driver frame, select the Extra Class Path tab and click on the Add button to add the MySQL driver .jar file (which you downloaded as part of Connector/J) to the classpath. Figure 5 illustrates adding the MySQL JDBC driver to the Eclipse classpath.
Add the MySQL Connector/J driver .jar file, mysql-connector-java-3.0.16-ga-bin.jar, to the classpath. In the Example URL field, specify the connection URL to connect to the database. A JDBC connection will be created with the
A connection alias is required to connect to the MySQL database and retrieve the database tables. A connection alias specifies the connection settings; JDBC driver, URL, username, and password. Select the Aliases tab in the SQLExplorer perspective. Click on the "Create new Alias" button to create a new Alias, as shown in Figure 7.
In the "Create new Alias" frame, specify an alias name. Select the MMMySQL Driver to create a alias for the MySQL database. Specify the connection URL for the MySQL database
This adds a MySQL alias to the Aliases tab frame, which is illustrated in Figure 9. To modify an alias, right-click on the alias node and select "Change the selected Alias."
The MySQL connection alias connects to the MySQL database and retrieves the database data. To connect to the database, right-click on the MySQL alias node and select Open, as shown in Figure 10.
In the Connection frame, specify the User name and Password to log in to the MySQL database, and click on the OK button. Figure 11 shows the login settings. By default, a password is not required for the root user.
A JDBC connection gets established with the MySQL database. Once connected, Eclipse displays the different database schemas in the MySQL database, as illustrated in Figure 12.
Support for Other DatabasesThe example JDBC connection is configured with the MySQL database. A JDBC connection may be configured with another database by selecting the driver node for the database in the Drivers tab. By specifying the driver class and connection URL for the selected database, a JDBC connection gets configured with the database. The driver class, the connection URL, and the driver .jar file for some of the other databases are listed below:
In the above list, Accessing Database DataWe configured the Eclipse IDE with the SQLExplorer plugin in the previous section. Next, we shall retrieve and modify the data from the example table
This displays the data as a text table. With the GUI SQL client SQLExplorer, the data is displayed as a structured table. SQLExplorer also generates the SQL scripts to create a table and select from it. If a table structure is displayed in a command-line client with the Select the Database Structure View tab in the SQLExplorer perspective in Eclipse. To display the structure of the Catalog table, select the
The Columns tab displays the columns listed in the Table below:
To display the data in the table selected in the
To create a SQL script to create the table, right-click on the table node and select Create Table Script, as shown in Figure 15.
This creates the SQL script to create the selected table and displays it in the
The data displayed in the Preview tab of the Database Structure View is retrieved with the default Select query, which includes all of the columns in the table. To display the default Select query, right-click on the table node and select "Generate Select in Sql Editor," as shown in Figure 17.
The default query to retrieve data from the catalog table gets displayed in the SQL Editor, as Figure 18 illustrates. Note that the
The query may be customized to display only some of the columns in the table. For example, modify the Select query to display all of the columns except the
Next, the catalog table shall be updated with an SQL script in the SQL Editor. For example, modify the title from "Five Favorite Features from 5.0" to "New Features in JDK 5.0." The SQL script to update the catalog table is run in the SQL Editor as shown in Figure 20.
The table data gets updated. Run the default select query on the modified table to display the modified data in the SQL Results frame. Figure 21 shows the modified catalog table data.
Next, delete a row from the table with a
Run the default select query to display the modified table data. The SQL Results frame table does not include the deleted row, as shown in Figure 23.
By configuring the SQLExplorer plugin in Eclipse, the IDE acquires the advantages of a GUI SQL client over a command-line client. ConclusionFor the example database table, a JDBC connection was established with the MySQL database. The SQL Explorer may also be used to configure a connection with other databases, which include DB2, Sybase, Oracle, HSQLDB, SQL Server, and PostgreSQL. ResourcesDeepak Vohra is a NuBean consultant and a web developer. |
|