Eclipse에서 SQL Explorer로 데이터 엑세스하기

Configuring Database Access in Eclipse 3.0 with SQLExplorer

by Deepak Vohra
05/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:

Overview
Preliminary Setup
Configuring SQLExplorer
Accessing Database Data

Overview

The 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

Download and install Eclipse 3.02.
Download the SQLExplorer file net.sourceforge.sqlexplorer_2.2.3.zip.
Extract the SQLExplorer .zip file to the <Eclipse>/eclipse directory. <Eclipse> is the directory in which Eclipse is installed. This adds the features and plugins directories of the SQLExplorer plugin to the features and plugins directories in Eclipse.
Restart the Eclipse IDE.
Install the MySQL database.
Download Connector/J, which is used to connect to the MySQL database via a JDBC driver contained in a .jar file.
Log in to the MySQL database with as root with the command:
```
>mysql -u root
```
A password is not required for the root user. To log in to the database with a password, specify the command:
```
>mysql -u root -p
```
Connect to the test database, an example database instance, with the command:
```
>use test
```

Create an example table in the test database. The example table, Catalog, is composed of ONJava articles. The SQL script to create the example table is listed below:

CREATE TABLE Catalog(CatalogId INTEGER, Journal VARCHAR(25),
 Publisher Varchar(25), 
 Date VARCHAR(25), Title Varchar(45), Author Varchar(25));

INSERT INTO Catalog VALUES('1', 'onjava',  
'OReilly', 'April 2005', 'Five Favorite Features from 5.0', 
'David Flanagan');

INSERT INTO Catalog VALUES('2', 'onjava',    
'OReilly', 'Feb 2005', 'Introducing JBoss Remoting', 
'John Mazzitelli');

INSERT INTO Catalog VALUES('3', 'onjava',   
 'OReilly', 'March 2005', 'Aspect-Oriented Annotations', 
'Bill Burke');

Configuring SQLExplorer

Having 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.

Figure 1. Opening a perspective

In the item list, select "Other..." to display the SQLExplorer plugin as shown in Figure 2.

The 'Other...' Perspective Menu Item
Figure 2. The "Other..." perspective menu item

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.

Figure 3. SQLExplorer perspective

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.

Figure 4. Modifying the Driver

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.

Figure 5. Setting the driver

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 test database, which is preconfigured in the MySQL install. The connection URL for the test database is jdbc:mysql://localhost/test. In the Driver Class Name field, specify the MYSQL JDBC driver as com.mysql.jdbc.Driver. The MMMySQL driver gets configured with the settings shown in Figure 6.

Figure 6. Setting the MySQL JDBC settings

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.

Figure 7. Create a new alias

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 test, jdbc:mysql://localhost/test, in the URL field. Figure 8 shows the MySQL alias settings.

Figure 8. Creating a new alias

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."

Figure 9. MySQL 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.

Figure 10. Opening an alias

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.

Figure 11. Opening a connection

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.

Figure 12. Listing the database schemas