Eclipse3.0 Configuration Sqlexplorer Access database

sql| Access | data | database

English Original address:
　　Http://www.onjava.com/pub/a/onjava/2005/05/11/sqlexplorer.html
Chinese Address:
　　Http://www.matrix.org.cn/resource/article/43/43630_Eclipse_SQLExplorer.html
Key words: Eclipse sqlexplorer MySQL JDBC

Sqlexplorer is a plug-in to the Eclipse integrated development environment that can be used to connect to a database from eclipse. The Sqlexplorer plug-in provides a graphical user interface (GUI) that accesses a database using SQL statements. By using Sqlexplorer, you can display data in tables, table structures, and tables, as well as extract, add, update, or delete tabular data. Sqlexplorer can also generate SQL scripts to create and query tables. Therefore, using sqlexplorer may be a better choice than a command-line client. In this guide, we will use the Sqlexplorer plug-in to build a JDBC connection from Eclipse3.0 to MySQL, an Open-source database.

This guide has the following chapters:

1. Overview
2. Preliminary installation
3. Configure Sqlexplorer
4. Accessing database data

Overview
By adding a sqlexplorer window to the integrated development environment, the Sqlexplorer plug-in configures an SQL client that accesses the database for Eclipse.

To demonstrate the use of the Sqlexplorer plug-in, we will create a sample table in MySQL, an Open-source database, and establish a JDBC connection from eclipse to the MySQL database. Next, we will extract and display the sample data in the Sqlexplorer graphical SQL client. Similarly, we will demonstrate the different features of the Sqlexplorer plug-in by updating and deleting the data in the sample table.

Prepare for installation

1. Download and install Eclipse 3.02

2. Download sqlexplorer file net.sourceforge.sqlexplorer_2.2.3.zip.

3. Unzip the sqlexplorer. zip file to the <eclipse>/eclipse directory. <Eclipse> is the directory for Eclipse installation. This will add the features and plugins directories of the Sqlexplorer Plug-ins to the features and plugins directories in Eclipse. (Translator Note: This is not very clear, it is actually the first to extract the Sqlexplorer. zip file, and then separate all the files in the features and plugins directory into the corresponding features and plugins directory in Eclipse, This is a common way to install the Eclipse plug-in, but you can also use link to install Plug-ins, please refer to the appropriate documentation on the Web

4. Restart Eclipse.

5. Install MySQL database.

6. Download connector/jto connect to the MySQL database by using the JDBC driver contained in one of the. jar files.

7. Log in to the MySQL database as a root user, commands are as follows:

>mysql-u Root

The root user does not have a password set. When you need to log in to the database by password, specify the following command:

>mysql-u root-p

8. Use the following command to connect to the test database, which is a sample database instance:

>use Test

9. Create a sample table in the test database. The sample table Catalog consists of the names of the articles on the Onjava. The SQL script used to create the sample table is listed below:

CREATE TABLE Catalog (catalogid INTEGER, Journal VARCHAR, Publisher VARCHAR (), Date VARCHAR (a), Title VARCHAR (45), Author Varchar (25));
INSERT into Catalog VALUES (' 1 ', ' Onjava ', ' oreilly ', ' April of the ', ' Five favorite from Features ', ' David 5.0 ');
INSERT into Catalog VALUES (' 2 ', ' Onjava ', ' oreilly ', ' Feb ', ' Introducing JBoss Remoting ', ' John Mazzitelli ');
INSERT into Catalog VALUES (' 3 ', ' Onjava ', ' oreilly ', ' March ', ' aspect-oriented annotations ', ' Bill Burke ');

Configure Sqlexplorer

After installing the Sqlexplorer plug-in, we will configure Sqlexplorer in Eclipse 3.02. First, open the Sqlexplorer window in Eclipse. Click the Open a Perspective button to open a window in the Eclipse integrated development environment. Figure 1 illustrates the "Open a Perspective" button.

Figure 1. Open windows

In the list of entries, select "Other ..." to display the Sqlexplorer plug-in, as shown in Figure 2.

Figure 2. "Other ... The menu item

In the Select Perspective box, select the Sqlexplorer window, as shown in Figure 3. By selecting the Sqlexplorer window, the features of the Sqlexplorer plug-in become available in Eclipse.

Figure 3. Sqlexplorer window

The Drivers tab shows different database drivers that are used to connect to different databases. The available databases now include DB2, MySQL, Oracle, Sybase, HSQLDB, SQL Server and PostgreSQL. For MySQL, we need to configure Sqlexplorer. To configure the MySQL driver, right-click the Mmmysql Driver node and select Change the Selected Driver as shown in Figure 4.

Figure 4. Modify Driver

In the Modify driver box, select the Extra class path label and click Add to add the MySQL driver. jar file (part of the downloaded connector/j) to the classpath. Figure 5 illustrates the way to add MySQL's JDBC driver to Eclipse's classpath.

Figure 5. Set 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 domain, specify the URL to connect to the database. A JDBC connection to the test database is created and the test database was created in advance of MySQL installation. The URL to connect to 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 is configured in the settings shown in Figure 6.

Figure 6. Set MySQL JDBC settings

To connect to the MySQL database and extract the tables, we need to create an alias for the connection. A connection alias describes the connection settings, JDBC driver, URL, username, and password. Select the Aliases tab in the Sqlexplorer window. Click 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 box, specify an alias. Select Mmmysql Driver to create an alias for the MySQL database. Specify the connection URL for the test database in the URL domain, jdbc:mysql://localhost/test. Figure 8 shows the MySQL alias settings.

Figure 8. Create a new Alias

The above action adds a MySQL alias to the Aliases tab, as shown in Figure 9. To modify an alias, right-click the alias node and select "Change the selected alias."

Figure 9. MySQL Alias

The MySQL alias is used to connect to the MySQL database and extract data from it. To open a database connection, right-click the MySQL alias node and select Open, as shown in Figure 10.

Figure 10. Open Alias

In the Connection box, specify the username and password to log in to the MySQL database, and then click OK to press the button. Figure 11 shows the login settings. By default, no password is required for root users.

Figure 11. Open connection

A JDBC connection to the MySQL database was established. Once connected, Eclipse displays a different database schema in the MySQL database, as shown in Figure 12.

Figure 12. List Database schemas

Support for other databases

An example of this JDBC connection is about configuring a connection to a MySQL database. You can configure JDBC connections to other databases by selecting the appropriate driver for the database in the Drivers tab. You can configure a JDBC connection to a database by simply specifying the driver class and connection URL for the selected database. The following are the driver classes, connection URLs, and driver. jar files for other databases.

DB2
o Driver Class:COM.ibm.db2.jdbc.app.DB2Driver
o Connection url:jdbc:db2:<database>
o Driver. Jar/.zip:db2java.zip

Sybase
o Driver Class:com.sybase.jdbc2.jdbc.SybDriver
o Connection url:jdbc:sybase:tds:o Driver. Jar/.zip:jconn2.jar

Oracle
o Driver Class:oracle.jdbc.driver.OracleDriver
o Connection url:jdbc:oracle:thin:@ o Driver. Jar/.zip:classes12.zip

Sql server
o Driver Class:com.microsoft.jdbc.sqlserver.SQLServerDriver
o Connection url:jdbc:microsoft:sqlserver://localhost:1433
o Driver. Jar/.zip:mssqlserver.jar, Msbase.jar, Msutil.jar

PostgreSQL
o Driver Class:org.postgresql.Driver
o Connection url:jdbc:postgresql://<server>:<port>/<database>
o Driver. Jar/.zip:postgresql.jar

In the list above,,<database> refers to the database instance,<port> is the database connection port,<sid> is the database sid,<server> is the database server.

Accessing database data
In the previous section, we configured the Sqlexplorer plug-in in Eclipse. Below, we will extract and modify the data from the sample table Catalog. If you are accessing a database from a command-line SQL client, use the following command to extract the data (on a single line):

Sql>select Catalogid, Journal, Publisher, Date,title, author from Catalog;

This will display the data as a text table. With graphical SQL client sqlexplorer, the data is displayed as a structured table. Sqlexplorer also generates SQL scripts to create tables and extract data from them. If you use the DESC command to display the structure of a table in a command-line client, only the column name, the type of column data, the length of the column data, and Non-null values are displayed. With Sqlexplorer, indexes, primary keys, and foreign key values are displayed.

Select the Database Structure View tab in Eclipse's Sqlexplorer window. To display the structure of the table Catalog, select the Database>test>table>catalog node in the Database Structure View. Figure 13 shows the structure of the table Catalog.

Figure 13. Database Structure View

The Columns tab shows the columns listed in the following table:

To display the data in the selected table, select the Preview label. Figure 14 shows the data in the table Catalog. Additional information about the table is displayed in the Indexes, Primary key, Foreign key, and Row Count labels respectively.

Figure 14. displaying tabular data

To get the SQL script to create the table, right-click the table node and select Create Table script, as shown in Figure 15.

Figure 15. Generating a table-building script

As shown in Figure 16, this generates the SQL script to create the selected table and displays it in the SQL Editor in the Sqlexplorer window.

Figure 16. Build Table Script

The data in the Preview tab of database Structure View is extracted using the default Select query, which includes all the columns in the table. To display the default Select query statement, right-click the table node and select Generate Select in Sql Editor, as shown in Figure 17.

Figure 17. Generate default Select query statement

As shown in Figure 18, the default query statement that extracts data from table Catalog is shown in SQL Editor. Note: There is no semicolon (;) at the end of the SELECT query statement that appears in SQL Editor.

Figure 18. Select Query Statement

A query statement may be customized to display only a few columns of data in a table. For example, modify the Select statement to display all but the catalogid columns. Select Execute SQL button to run the SQL script. The data that is obtained from the modified SELECT statement is displayed in the SQL Results box, as shown in Figure 19.

Figure 19. Extracting data from a table using a custom SELECT query statement

Next, a SQL script in SQL Editor will update the table Catalog. For example, change the title "Five Favorite Features from 5.0" to "New Features in JDK 5.0". As shown in Figure 20, the script to run the Update table Catalog in SQL Editor

Figure 20. Update SQL Scripts

The data in the table is updated. Run the default select query on the modified table, and the modified data will be displayed in the SQL Results box. Figure 21 shows the modified data for the table Catalog.

Figure 21. Modify table Data

Next, delete a row of data from the table using the Delete SQL statement, as shown in Figure 22. The catalogid= ' 3 ' will be removed from the table.

Figure 22. DELETE SQL Script

Run the default Select query statement to display the data in the modified table. The table in the SQL Results box no longer includes the rows that were deleted, as shown in Figure 23.

Figure 23. Delete a row of table data

By configuring the Sqlexplorer plug-in in Eclipse, the integrated development environment obtains many of the benefits of a graphical user interface (GUI) over command-line clients.

Summary

Example, we established a JDBC connection with the MySQL database and accessed one of the sample databases. SQL Explorer can also be used to configure connections to other databases, including DB2, Sybase, Oracle, HSQLDB, SQL Server, and PostgreSQL.

Resources

　　Sqlexplorer
　　Eclipse Integrated development Environment
　　MySQL Database

Deepak Vohra is a consultant and web Developer for Nubean.