MyEclipse Database Explorer Tutorial

Outline

1. Preface

This document was written using MyEclipse and Oracle 9.2.x. All screenshots are based upon the default user interface settings for MyEclipse Enterprise Workbench, and Windows XP. If you experience difficulty with the instruction of this document, please see the User Feedback section for how to provide feedback to the MyEclipse documentation team.

2. Requirements

Below is a list of requirements for this Tutorial:

1. MyEclipse (see Resources for a download link)
2. You may use the included Derby database that is part of the MyEclipse integrated sandbox, or an externally installed database server that is supported by the MyEclipse Database Explorer such as:

3. Introduction

Development of Java enterprise applications typically involves working with relational databases. To support this dimension of development, MyEclipse provides the Database Explorer, a suite of database application development tools. The Database Explorer (DE) consists of the following features:

• Support for 25 preconfigured JDBC driver templates
• Create multiple database connections, to one or multiple databases
• Several database views:
  • Hierarchical database structure browser view, e.g., tables, columns, sequences
  • Table data editor
    
  • Detailed table properties viewer
  • Database snippet processing result history
  • Database connection properties viewer
• SQL editor with code completion
• New Table, Foreign Key and Index wizards
  
• SQL code generation utilities
• SQL snippet evaluation
• Database Explorer Perspective: A customizable organization of DE tools and views (see Figure 1)

Figure 1. MyEclipse Database Explorer Perspective

This Tutorial will demonstrate how to perform the following Database Explorer tasks:

1. Configuring a JDBC database driver
2. Creating a database profile and connection
3. Browsing a database and familiarization with the detailed DE database viewers
4. Editing SQL document
   
5. Executing SQL snippets
6. Using the Table, Foreign Key and Index wizards
   

4. Creating a Database Connection

This section demonstrates the process for configuring and connecting the Database Explorer to a Oracle database server. The process is similar for all other JDBC drivers. To learn about JDBC visit this  link .

Connecting the Database Explorer to a database is a 2 step process:

1. Configure the Database Explorer driver for your database
2. Open a database connection

Step 1 is a one-time configuration task for each database that you wish to use. Step 2 must be performed when MyEclipse is started or after any other event that results in the database connection being dropped, such as a database server restart.

4.1 Creating a Database Driver

The first task is to configure the Database Explorer JDBC driver for your database.  Drivers and their resulting connections are managed in the  Database Browser.

To access the Database Browser, open the Database Explorer Perspective. Select Window > Open Perspective > Other > MyEclipse Database Explorer followed by OK (see Figure 2).

Figure 2. Opening MyEclipse Database Explorer Perspective

Figure 3 shows the Database Explorer perspective with some of the main database tools highlighted. The first time you open the perspective the Database Browser view shown on the left will only contain the preconfigured MyEclipse Derby driver. Versions of MyEclipse prior to 5.5 will probably see an empty view.

Figure 3. Database Explorer Perspective

 You will need to create a new driver for each separate connection to a database. Create a new driver using these steps.

1. From the Database Browser view menu, select the New... menu item . This will open the New Database Driver wizard.
   
   
   Figure 4.  Launching New Datababase Driver Wizard

Figure 5. Completed Driver Wizard

2. Select Oracle (Thin driver) from the drop-down in the Driver template field.
3. Enter oracle as the Driver name.
   
4. Modify the template URL provided to the correct connection string. For our local Oracle server we are using: jdbc:oracle:thin:@localhost:1521:dbtest
5. Fill out the User name and Password fields with appropriate values for your server. If you do not wish to save your password, there is no need to fill in the Password field.
6. Select Add JARs button.
7. Browse to location of the JDBC driver JAR file or classes directory and select it. Select Open to return to the Driver configuration wizard.
   

Figure 6. Selecting Oracle JDBC driver JAR file

Note: Prior to starting this example the Oracle JDBC connector was downloaded to a local directory.

Inspect the value of the  Driver Class Name. The ojdbc14.jar file contains 2 possible driver classes which may be selected from the Driver Class Name drop-down list.

8. In the Driver Class Name field select or enter oracle.jdbc.driver.OracleDriver
9. Now click  Finish to create the connection profile shown in Figure 5.

Table 2.  New Connection Profile  wizard field definitions

The new driver will now show in the DB Browser view. 

Figure 7. Database Browser with new Oracle driver

4.2 Opening a Database Connection 

1. Right click the driver and select Open connection... in the Database Browser view (see Figure 8).

Figure 8. Opening a database connection

If the connection profile is configured without the Save password option then the  Open Database Connection Wizard will appear (see Figure 9). 

Figure 9. Database Connection Wizard

2. Enter the correct password.
3. Enable the Autocommit mode if you need changes to the database to be committed immediately. The default autocommit mode can be set on the MyEclipse Database Explorer Preferences.
4. Select OK to initiate the database connection process.

At this point a new database connection will be opened and displayed in the Database Browser.

Figure 10. New database connection in Database Explorer

Note: Connections will not be closed if you close the Database Browser view or switch to a different perspective.

5.  Working with Databases

This section will introduce you to the Database Explorer tools that enable database browsing and SQL development. These tools include:

1. Database Browser for exploring database structure
2. SQL Editor
3. SQL Editor - snippet execution
4. New Table, Foreign Key and Index wizards
5. Table Data Editor
6. SQL and DDL Generation
7. SQL Execution History
8. Table Information Report
9. Database Connection Information Report

5.1 Browsing a Database

With the database connection established, expand the connection node in the Database Browser to view the database structure as shown in Figure 11.   

Figure 11. Browsing database structure

Expand the database structure to reveal table metadata such as tables, views, sequences as shown in Figure 12.

Figure 12. Browsing Table Information

5.2 SQL Editing and Snippet Execution

The Database Explorer tool set includes a smart SQL Editor. The SQL Editor may be associated with an open database connection that enables it to perform code completion of SQL statements and to execute SQL snippets.  Please pay close attention to the Instructions in step-4 for enabling code completion. The following task will introduce basic SQL editing and snippet execution tasks. 

1. Open a new SQL Editor by selecting File > New > SQL Editor from the MyEclipse menubar as shown in Figure 13.
   
   
   Figure 13. Opening new SQL Editor
   
   
2. Enter the location for the new SQL file.
   
   
   Figure 14. Location of SQL Editor file

3. Select the database connection and schema to which this editor will be associated (see Figure 150).

Note : This step is required in order for code completion and snippet execution to function correctly.

Figure 15. Associating SQL Editor database connection and schema

4. Now you can type in SQL statement taking advantage of code assist (ctrl+space) in each section of your SQL as demonstrated in the sequence of Figures 21a - 21d.

Special Notice regarding SQL code completion   The SQL code completion features requires the metadata of an editor's associated database be preloaded. By default, SQL code completion is disabled to avoid severe performance problems that may result from reading the metadata of very large database or retrieving this information over very low bandwidth database connections. To enable the SQL code completion feature, open the Database Explorer preference, from the MyEclipse menubar select Window > Preferences > MyEclipse > Database Explorer Enable the Load all database metadata when connection established option     To optimize metadata retrieval consider using a schema filter. Select this link to learn more.

Figure 16a. Standard SQL syntax code assistance

Figure 16b. Code assist for schema names

Figure 16c. Code assist for table names

Figure 16d. Completed SQL statement

5. A useful SQL Editor feature is to synchronize the Database Browser view with the context of the SQL Editor. In the SQL Editor, select a table name with the Ctrl key pressed. This will position the Database Browser to the selected table. 

Figure 17. Activating Database Browser synchronization for the DEPT table in the SCOTT schema

6. Execute the SQL statement by pressing the  icon or Ctrl + F9.

The  action will execute the selected snippet. In the case no statements are selected, as in this example, the entire SQL Editor text will be executed. The query results are presented in the SQL Results view as shown in Figure 22.

Figure 18. Results from query execution

5.3 Working with Tables, Foreign Keys and Indices

MyEclipse provides a number of wizards and actions making it easy to create and delete Tables, Relationships and Indices.

Creating a New Table

1. Right click the schema in which you want to create the Table and select New Table from the context menu.


Figure 19. Invoking the New Table Wizard

2. Complete the Table Info tab as shown in Figure 24.

   
   Figure 20. New Table Wizard - Table Info

3. Select the Columns tab and then click Add to add a new column.

   
   Figure 21. New Table Wizard - Columns

4. Complete the Column Wizard as shown and press Finish.

   
   Figure 22. New Column Wizard - Primary Key

5. Click Add again to add a second column, complete it as shown and press Finish.

   
   Figure 23. New Column Wizard

6. Select Finish in the Primary wizard to create the table in your schema.

   
   Figure 24. New Table Wizard

7. The new table will appear in the DB Browser view immediately.

   
   Figure 25. New Table Created

Creating a New Foreign Key

1. Right click the TEST table you just created and select New Foreign Key from the context menu.


Figure 26. Invoking the New Foreign Key Wizard

2. Select the BOOK table from the Referenced table name dropdown. This will initialize the Referenced key name list with keys from the BOOK table.
   Then select the first row in the Foreign key columns list and select the ID column from the test table.


Figure 27. New Foreign Key Wizard

3. Enter MY_FK as the Foreign key name and you may choose Cascade from the On Delete drop down.
   Press Finish to complete the wizard and create the foreign key.

   
   Figure 28. New Foreign Key Wizard Completed

4. Reselect the TEST table in the DB Browser and go to the Foreign Key tab in the Table/Object Info view to confirm the creation of the MY_FK foreign key.

   
   Figure 29. New Foreign Key in the Table/Object Info view

Creating a New Index

1. Right click the TEST table and select New Index from the context menu.


Figure 30. Invoking the New Index Wizard

2. Enter MY_INDEX in the Index name field.
   Click Add... to add a column to the index.


Figure 31. New Index Wizard

3. Select the Order column and choose Descending
   Select Finish to complete the wizard and create the Index.


Figure 32. New Index Wizard Completed

4. Reselect the TEST table in the DB Browser and go to the Index tab in the Table/Object Info view to confirm the creation of the MY_INDEX index.

   
   Figure 33. New Index in the Table/Object Info view

Dropping an Index

1. Select the Table containing the Index in the DB Browser view.
2. Select the Index tab in the Table/Object Info view.
3. Right click the Index you wish to delete and select Drop Index from the context menu.


Figure 34. Dropping an Index

4. You will be asked to confirm the drop. Click OK.

Dropping a Foreign Key

1. Select the Table containing the Foreign Key in the DB Browser view.
2. Select the Foreign Key tab in the Table/Object Info view.
3. Right click the Foreign Key you wish to delete and select Drop Foreign Key from the context menu.


Figure 35. Dropping a Foreign Key

4. You will be asked to confirm the drop. Click OK.

Dropping a Table

1. Right the Table you wish to drop in the DB Browser view.
2. Select Drop Table from the context menu.




Figure 36. Dropping a Table
3. You will be asked to confirm the drop. Click OK.

5.4 Editing Table Data

The Database Explorer provides an Edit table view which can be used to edit records already present in your database. To use this feature, begin by opening the view.

1. From the Database Browser select a table, open the context-menu (right-click menu), and select Edit Data (see Figure 37).

Figure 37. Launching Table Data Editor on echo_message table

2. Use the text box to enter a filter for the rows (if desired). In this case we enter deptno = 20
   
3. Click Apply to filter the rows according to the condition.
   
4. Select the cell you wish to edit and type in the new value directly.

In the example below we are editing the SAL column value in row 1.

Figure 38. Table Data Editor

5. Press Enter to save your changes to the database, or Esc to cancel the edit operation.

5.5 Using SQL Generation Utilities

The Database Explorer provides two SQL generation utilities, a table creation script generator and an example SQL query generator.

To generate a Data Definition Language (DDL) script for an existing table do the following:

1. Select the table in the Database Browser
2. From the context-menu (right-click menu) select Generate > DDL (see Figure 39)
   Professional subscribers also have an advanced DDL generation capability when working with Oracle

Figure 39. Activating "Create Table Script" feature

This feature will generate a DDL script for recreating the selected table and display it in a new SQL Editor (see Figure 40)

Figure 40. Newly created Table Script DDL

To generate an example SELECT statement for a table do the following:

1. Select the table in the Database Browser
2. From the context-menu (right-click menu) select Generate > Select Statement (see Figure 41)

Figure 41. Generate SQL Select statement from table

This feature will generate a SELECT * statement and display it in a new SQL Editor (see Figure 42)

Figure 42. New created SELECT statement in SQL Editor

5.6  Viewing Database and Table Details

This section provides a brief description of the features for viewing database and table detail.

SQL History

The SQL History view presents a list of all recent SQL statements executed on the database connection selected in the Database Browser.  This view provides useful menu actions such as reopening the SQL statement in a new editor as shown in Figure 43.

Figure 43. SQL History List 

Database Connection Info

The Database Connection Information view presents detailed information about the database connection selected in the Database Browser.  As you select different database connections the information is updated accordingly.

Figure 44. Database Connection Information

Table Information

The Table Information view presents detailed metadata information for the selected database table in the Database Browser (see Figure 45) .    The metadata information is presented in read-only mode.

Figure 45. Table Information view

6. Advanced Features

There are advanced features, such as performance optimizations, that Database Explorer users who work with large databases should know about. In this section we introduce schema filtering to enable users to determine how much metadata to fetch when a database connection is opened.

6.1  Schema Filtering

When opening a database connection in the Database Browser, the default behavior is to fetch information about each database schema. The Database Explorer allows you to optionally filter the schemas managed by a database connection. The primary reasons for applying a schema filter are optimization related, such as:

• Increase connection performance when opening a database connection
• Limit the amount of information presented by the Database Browser
• Increase metadata retrieval performance for SQL code completion support

Schema filters are created as part of a connection profile's definition and are therefore managed using the Connection Profile Wizard.

To create a schema filter do the following:

1. In the Database Browser, select the connection profile upon which a schema filter will be created.
2. From the connection profile's context-menu, or the Database Browser's view menu, select the  Edit  icon.
3. In the Connection Profile Wizard select Next to continue to the Schema Details of the wizard (see Figure 46).  By default, the Display all schemas option is selected. 
4. Select the Display the selected schemas option. Initially the schema list will be empty.
5. Select Add to view the entire list of schemas available from the database connection.  

Note: The Add action will attempt to connect to the database and retrieve a list of all schemas.


Figure 46. Schema Details page of the Connection Profile Wizard

6. Select the schemas that the connection should manage from the full list of the database's schemas (see Figure 47).
7. Select OK to accept your schema selections and return to the Connection Profile Wizard
   
   
   Figure 47. Selecting schemas to manage
   

Figure 48 shows that the"Selected schema list" has been updated with the newly selected schemas.

8. Select Finish to complete the filter specification.
   
   
   Figure 48. Completed Connection Profile Wizard with schema filtering
   

9. Close and reopen all connections for this profile for the schema filtering to take effect.

Figure 49 depicts a database connection for the MySQL Test database with filter enabled.

Figure 49. Database connection with specified schemas

6.2  Changing the Command Separator

The default command separator is the semicolon (;). This implies that all SQL commands and queries will be terminated by the ; character.
However, in more complex scripts, the ; is often used as an internal separator while other strings are used as the command separator. Such scripts will not execuite correctly when the semicolon is the command separator.

You can change the command separator by using the SQL Editor's Command Separator combo to select from an existing set of separators or you may type in your own.

Figure 50. An example of an SQL Server script where the ; is used as an internal separator and "go" as the command separator.

7. Summary

This concludes your tutorial into using the MyEclipse Database Explorer features. Additional Tutorial documents are available that introduce working with Hibernate along with the Database Explorer, as well as working Web Projects, editing HTML, application server configuration, and enterprise application projectst. For more information visit the MyEclipse Tutorial library.

8. User Feedback

If you have comments or suggestions regarding this document please submit them to the MyEclipse Documentation Forum.

9. References

• Download MyEclipse .
  
• To learn more  about JDBC visit this  link from Sun Microsystems.
  
• Visit the MyEclipse Tutorial Library for more MyEclipse tutorials.