Configuring Database Access in Eclipse 3.0 with SQLExplorer
Pages: 1, 2

Support for Other Databases

The 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:

• DB2
  • Driver Class: COM.ibm.db2.jdbc.app.DB2Driver
  • Connection URL: jdbc:db2:<database>
  • Driver .jar/.zip: db2java.zip
• Sybase
  • Driver Class: com.sybase.jdbc2.jdbc.SybDriver
  • Connection URL: jdbc:sybase:Tds:<host>:<port>/<database>
  • Driver .jar/.zip: jconn2.jar
• Oracle
  • Driver Class: oracle.jdbc.driver.OracleDriver
  • Connection URL: jdbc:oracle:thin:@ <host>:<port>:<sid>
  • Driver .jar/.zip: classes12.zip
• SQLServer
  • Driver Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
  • Connection URL: jdbc:microsoft:sqlserver://localhost:1433
  • Driver .jar/.zip: mssqlserver.jar, msbase.jar, msutil.jar
• PostgreSQL
  • Driver Class: org.postgresql.Driver
  • Connection URL: jdbc:postgresql://<server>:<port>/<database>
  • Driver .jar/.zip: postgresql.jar

In the above list, <database> is the database instance, <port> is the database port, <sid> is the database SID, and <server> is the database server.

Accessing Database Data

We configured the Eclipse IDE with the SQLExplorer plugin in the previous section. Next, we shall retrieve and modify the data from the example table Catalog. If a database is accessed from a command-line SQL client, table data is retrieved with the following (all on one line):

SQL>SELECT catalogId, journal, publisher, date,
    title, author from Catalog;

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 DESC command, only the column name, column type, column size, and "not null" values get displayed. With SQLExplorer, the indexes, primary key, and foreign key values are also displayed.

Select the Database Structure View tab in the SQLExplorer perspective in Eclipse. To display the structure of the Catalog table, select the Database>test>TABLE>Catalog node in the Database Structure View. Figure 13 shows the Catalog table structure.

Figure 13. Database Structure View

The Columns tab displays the columns listed in the Table below:

Header	Description
Column Name	The column name in the table.
Data Type	The data type for the column.
Size	The column size.
Decimal Digits	The decimal digits in the column data.
Default Value	The default value of the able column.
Accept Null Value	Specifies if the column takes null values.
Comments	Comments on the table column.

To display the data in the table selected in the TABLE node, select the Preview tab. Figure 14 shows the table data for the Catalog table. Additional information about a table is displayed with the Indexes, Primary Key, Foreign Key, and Row Count tabs.

Figure 14. Listing the table data

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.

Figure 15. Creating table script

This creates the SQL script to create the selected table and displays it in the SQL Editor of the SQLExplorer perspective, which is shown in Figure 16.

Figure 16. Table script

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.

Figure 17. Generating the default select query

The default query to retrieve data from the catalog table gets displayed in the SQL Editor, as Figure 18 illustrates. Note that the SELECT queries displayed in the SQL Editor do not have a semicolon (;) at the end of the SQL statement.

Figure 18. Select query

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 CatalogId column. To run the SQL script, select the Execute SQL button. The data from the modified select query gets displayed in the SQL Results frame, as shown in Figure 19.

Figure 19. Selecting table data with custom SELECT query

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.

Figure 20. Update SQL script

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.

Figure 21. Modified table data

Next, delete a row from the table with a DELETE SQL statement in the SQL Editor, as shown in Figure 22. The table row with CatalogId='3' gets deleted from the table.

Figure 22. DELETE SQL Script

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.

Figure 23. Table data with row deleted

By configuring the SQLExplorer plugin in Eclipse, the IDE acquires the advantages of a GUI SQL client over a command-line client.

Conclusion

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

Resources

• SQLExplorer
• Eclipse IDE
• MySQL database

Deepak Vohra is a NuBean consultant and a web developer.

Return to ONJava.com.