MyEclipse Logo

MyEclipse Database Tools Tutorial

Table of Contents

  1. Introduction
  2. Suggested Audience
  3. System Requirements
  4. Getting Started
  5. Working with the Database
  6. Generation and Visualization
  7. Conclusion
  8. FAQ
  9. Resources
  10. Feedback

1. Introduction

Welcome to the Database Tools tutorial. In this tutorial we are going to cover some of the basic and advanced database-specific functionality contained in MyEclipse. Most of this functionality is contained in what is known as the Database Explorer, but the Database Explorer also serves as a starting point for other advanced database features like Hibernate mapping generation, Spring-Hibernation support and others.

Other MyEclipse tutorials are available that cover those additional functionalities, but for this tutorial we are going to focus on the specific database functionalities that any developer or DBA will find useful during development.

2. Suggested Audience

This tutorial is intended for developers who are somewhat familiar with MyEclipse so you'll recognize navigation within the IDE, and understand some of the more common concepts like "Views". This tutorial also assumes the reader has knowledge of databases and some basic knowledge of working with databases in Java (e.g. is familiar with JDBC drivers).

In this example we will be using MySQL as our DBMS, but the reader is free to use any other database that they feel comfortable with, as long as they are able to follow the instructions in the tutorial. The tutorial also assumes that you already have a DBMS configured for use and will not go into detail on how to do that since it varies from database to database.

If either MyEclipse or databases makes you feel uncomfortable, we certainly welcome you to follow through the tutorial anyway, even just skipping to the demonstrations (movies) to get a feel for the types of tools you have at your disposal in MyEclipse.

If you wish to learn more about either MyEclipse or MyEclipse's database features please have a look at either our product Documentation for more material or our Resources section respectively.

3. System Requirements

This tutorial was created with MyEclipse 5.1, a local install of MySQL 5.0 and the 5.0 version of the Connector/J MySQL JDBC driver. If you are using different version of these applications most of these screens and instructions should still be very similar.

If you are using a newer version of MyEclipse and notice portions of this tutorial looking different than the screens you are seeing, please let us know and we will make sure to resolve any inconsistencies.

4. Getting Started

To begin using the database functionality in MyEclipse you'll need a connection to a database. In order to get a connection to a database you first need to configure the JDBC driver that will be used to establish that connection. MyEclipse ships over 30 pre-configured templates for JDBC drivers so in most cases creating a new JDBC driver configuration shouldn't be harder than simply finding your template, using the drop down menu, and then adding the JAR to the classpath that contains the driver. If you cannot find a template for the driver you want to use, the driver templates are completely free-form so you can directly configure all the important details as necessary.

After configuring your JDBC driver, you will be able to create a connection to your database using the new connection wizard as shown below:

Figure 1. Creating a connection to the DB

After connecting to the database we can immediately start exploring the elements it contains. For the purpose of this tutorial we are going to stick to tables, but MyEclipse also has functionality to view and edit other database elements such as views, triggers, and stored procedures.

Within this database are 2 sample tables (we create the 3rd later on) and some simple records. If you'd like to create the same database for local use, the create-table scripts and sample data used in this tutorial can all be found in the Resources section below.

Now that we are connected to the database, we'll drill down to our user table and query it, to see what in it:

Figure 2. Querying the DB content

You now have an idea of how easy MyEclipse makes navigating your information, and it even generates your select statements for you. But how can we update the information in the database? Normally you would write update statements to change the values in your database, but that can be quite time consuming. Using the Edit Data feature in MyEclipse we can quickly change the values in our tables using a GUI. For example, let's change Ryan's COUNTRYCODE value from Japan (JP) to United States (US):

Figure 3. Editing content in the DB

So far so good. We've learned how to connect to our database, query our tables for information and even update our table's information. The basics are taken care of so let's see how MyEclipse handles some of the more advanced database features that developers and DBAs will be familiar with; things like indices, creating tables and foreign keys.

5. Working with the Database

As we mentioned in the section above we have taken a look at the database basics so far in MyEclipse, but stopping at the basics isn't enough. Developers and DBAs need more control over their DBMSs such as managing indices on their tables, creating and dropping tables, and manipulating foreign key constraints. MyEclipse handles all of this and we'll have a look at indices first.

Creating an index can be an important performance tool for developers. DBMSs will typically create indices on keys in a table. However, there may be other fields that are used regularly in ORDER BY or WHERE clauses that require an index as a performance enhancement. 

In this tutorial let's hypothetically say we do a lot of ordering on the FIRSTNAME of our users, so let's create an index on that field:

Figure 4. Creating and removing an index

Creating a Table

During design, a developer of a database-enabled application will be modeling the domain of his application and creating corresponding tables within which he can persist the application's data. Let's have a look at how MyEclipse handles table creation by adding a new table called user_relationship that represents a pair of user IDs that are related in some way.

Figure 5. Creating a new table

Now that we've created the user_relationship table we should make both the new fields foreign key references to the ID field of the user table. This is necessary because in order for two users to have a valid relationship, they both have to be valid users and that dictates that they need to be represented by records in the user table.

Figure 6. Creating foreign keys

So far so good. We have created our new table and even enforced our key constraints appropriately for it. Let's keep going and see what else MyEclipse can do.

6. Generation and Visualization

Now that we have our new user_relationship table along with the appropriate constraints, let's say that we want to get an idea of how our domain model is looking for our application. That's going to be easy since MyEclipse has a very cool and easy to use ER Diagram generation wizard that will visualize our table and their relationships for us.

Figure 8. Creating an ER Diagram

Naturally, as the table structure and relationships are changed you can refresh the diagram view to see those changes reflected in it. The diagram view can also be exported or an image file and included in a design document which will make documenting an early design, or understanding an existing complex system, much easier.

Generating DDL

Lastly, we want to cover the DDL generation functionality in MyEclipse as this feature is important during the development stages where a team may need to replicate or backup a database they are designing.

To generate the DDL (e.g. "create-table statements") for your specific table you can simply right click on it in the table node and select Generate > DDL:

Figure 7. Generating DDL for the new table

With the DDL generated into a SQL editor for you, you can easily run it, modify and run it or just save it to a .sql file for backup purposes.

7. Conclusion

We hope you have found the introduction to the MyEclipse Database Tools presented in this tutorial helpful. If you have a favorite database feature that we didn't cover in this tutorial and you think we should have, let us know.

Or, if you have comments about this tutorial or suggestions/questions for us, please let us know. We always value our user's feedback especially on educational materials such as these.

8. FAQ

  1. Does MyEclipse support Oracle, MS SQL Server, PostgreSQL, HypersonicSQL and other DBMSs?
    • Yes, MyEclipse's database functionality is built on top of the JDBC standard. That means any database that has a high quality JDBC driver should function just fine with MyEclipse.
  2. I am having a hard time using the Database Explorer, it is acting erratic.
    • Almost every time we have gotten reports of database functionality problems in the Database Explorer it was due to an old or buggy JDBC driver (for example the older classes12.zip driver for Oracle). Normally upgrading your driver or switching to another high quality driver solves the problem.
  3. I cannot see or create foreign keys in MySQL like you did in the tutorial, why?
    • You need to make sure you are using the InnoDB database type or other database type that supports foreign keys in MySQL. The older default MyISAM table type does not support foreign keys.
  4. How can I fine-tune the limits of my result sets or the look of my ER Diagrams?
    • All these settings can be found under Window > Preferences > MyEclipse > Database Explorer
  5. MyEclipse FAQ

9. Resources

Below are links to resources that we hope will help answer most of the questions you could have while working your way through this tutorial. You will notice that a lot of the material linked is geared towards MySQL; this is intentional. A lot of the database basics are common across all major DBMSs and since our tutorial uses MySQL we decided to try and keep everything applicable and easy to follow. Have a look:

Resources


MySQL

Database Basics

Other JDBC Drivers for other DBMSs


10. Feedback

We would like to hear from you! If you liked this tutorial, has some suggestions or even some corrections for us please let us know. We track all user feedback about our learning material in our Documentation Forum.  Please be sure to let us know which piece of MyEclipse material you are commenting on so we can quickly pinpoint any issues that arise.